SQL query to find subscribers who were sent with the same Email Name

Question

I've been using marketing cloud for almost a year now but not really exploring the SQL queries since i'm not really familiar with this. I'm also aware that my friend set up my account with lots of user initiated emails.

Now I'm in the dilemma on how can I find subscribers that were sent with a specific email name at a time range. Example i have 15 JobId with the same Email name and i wanted to collate all subscribers from these JobIDs with in one data extension.

On this DE, I will be needing the following columns:

SubscriberKey JobID BatchID SendDate OpenDate ClickDate

Answer 1

Bookmark this - it will become your best friend https://help.salesforce.com/articleView?id=mc_as_data_views.htm&type=5

Marketing Cloud uses Data Views to store information about events that have to do with email. These views are kept for a soft 90-ish days before the tracking is archived on their side (depends on how much you send and to how many users = amount of data they have to keep available etc)

You can access these data views in a query by referencing them as a table like as follows: _Jobs, _Sent, _Open, etc.

From this it sounds like you are looking for a particular email name and user it was sent to - you can do this by inner joining the _Job, _sent, _click, _open, and _subscribers data views:

Select DISTINCT
t1.JobID,
t1.EmailName,
t2.SubscriberKey,
t3.EmailAddress,
t2.EventDate as 'SentDt',
t4.EventDate as 'OpenDt',
t5.EventDate as 'ClickDt'
FROM
_Job t1
INNER JOIN
_Sent t2
ON
t1.JobID = t2.JobID
INNER JOIN
_Subscribers t3
t2.SubscriberID = t3.SubscriberID
Left Join
_Open t4
ON
t2.SubscriberID = t4.SubscriberID AND t2.ListID = t4.ListID AND t2.BatchID = t4.BatchID AND t2.JobID = t4.JobID
LEFT JOIN
_Click t5
ON
t2.SubscriberID = t5.SubscriberID AND t2.ListID = t5.ListID AND t2.BatchID = t5.BatchID AND t2.JobID = t5.JobID
WHERE t1.EmailName = 'Your Exact Email Creative Name' AND
t2.EventDate BETWEEN '10/01/2019' AND '10/02/2019'

Write it to a data extension with JobID NUMBER, EmailName Text(250), SubscriberKey Text(250),EmailAddress as Text(250), SentDt DATE, OpenDt DATE Nullable, ClickDt Date Nullable.

This query should give you a list of the contacts that were sent a certain email within a certain time frame.

Keep in mind - you might get duplicates if the contact was sent the email more than once, opened more than once, or clicked more than once. If you want specifics or don't care as long as they were sent and either opened or clicked, you should:

• add a MAX() around your EventDates and add a Group By t1.JobID, t1.EmailName, t2.SubscriberKey, t3.EmailAddress
• Remove JobID from the Select query all together and remove from your destination data extension.

Be careful to read notes about the enterprise-level accounts and the timestamps being in different time zones.

Hope this helps!

Answer 2

I tried this query to look for subscriber's opendate senddate, and clickdate by JobId and it works like charm.

Select distinct s.SubscriberKey, s.JobID, s.BatchID, convert(char(19),o.EventDate,20) as OpenDate, convert(char(19),c.EventDate,20) as ClickDate, convert(char(19),s.EventDate,20) as SendDate from [_sent] s left join [_open] o on s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1 left join [_click] c on s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1 where s.JobID = 154670

i'm asking for your help guys to tweaked this to pull these info in the name fields plus email address and job ID in reference to EmailName.