The below should give you what you need. Be careful though as depending on the volume of records in the Open DV, this can time out.
SELECT FORMAT(GetDate(), 'MMMM') as Month,
FORMAT(GetDate(), 'yyyy') as Year,
COUNT(SubscriberKey) as Count
WHERE FORMAT(eventdate, 'MMMM') = FORMAT(GetDate(), 'MMMM')
AND FORMAT(eventdate, 'yyyy') = FORMAT(GetDate(),...
I think this should work
datename(month,s.EventDate) as SentMonth,
1 as Emails_Sent,
j.EmailName as EmailName
LEFT JOIN _Open o ON o.JobID = s.JobID AND o.SubscriberID = s.SubscriberID AND o.IsUnique = 1
INNER JOIN _Job j ON j.JobID = s.JobID
WHERE s.EventDate between '06/01/2019 00:00:00.000' and '...
The ROW_NUMBER() function is great if you have an order that you need to find or maintain. However if you are looking to find duplicates (no order needed), then you should try the COUNT(*) function, as below:
FROM [Channel_Audience] a
INNER JOIN (
Yes, if the object holding this field is synchronized and this fields appears in Synchronized Data Extensions.
No, if it's not synchronized.
Another way to retrieve it if you don't want to synchronize it would be to use the RetrieveSalesforceObjects() AMPscript function, but that would only make sense on a CloudPage.
I'd do something like this with left joins:
'Rolling' as [Group]
when em.account_number is not null then 'Y'
when sm.account_number is not null then 'Y'
from [12mth_shoppers] s
left join (
select distinct em0.account_number
from [email marketable] em0
) em on em....
If you need to get the value before the @ character, you'll need to use something like:
SELECT LEFT(Email, CHARINDEX('@',Email)-1) FIELD_ALIAS FROM YOUR_DE
Replace the FIELD_ALIAS with the name of the field where you want to store this value. Also, replace Email with the field from where you'll be getting the emails..
Basically, we are using the LEFT ...
If I understand correctly that you basically want a "list of events" in your data extension, regardless of type, I would do the following:
Write three separate queries into three DEs. You already have those queries listed, if I'm not mistaken.
(i call the three resulting DEs "sendevents", "openevents", "clickevents" below).
UNION the three resulting ...
The issue is your o table fields.
Change o.department__c to o.department
SELECT a.PersonContactId as "ContactId",a.PersonEmail as "Email",a.FirstName
INNER JOIN (
SELECT ContactID__c, MAX(Department__c) as "Department"
GROUP BY ContactID__c) o
ON o.ContactID__c = a....
For future reference, I did solve it. Marketing Cloud allows the query to run, but when there isn't a result (because it didn't meet all the requirements), it doesn't populate the DE. Although it appears "green" on the GUI, because it did run the query, but didn't found any record do input.
As you are missing some information necessary for the send in the best case you would create two new data extensions:
CSV-Import Data Extension (with all the fields you need from your CSV)
Data Extension for your Send/Journey (with all the fields of DE 1 and all the necessary additional information you already have in Marketing Cloud)
In order for this to ...