0

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

  • Can you update your question with a query that you wrote as a starting point? If you provide a query we can advise on how to fix but we try to avoid doing the work for you. – gNerb Oct 24 '19 at 17:46
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!

| improve this answer | |
  • Hello @Bread2287 Appreciated your reply and yes, i'm just looking for for a particular email name and user it was sent to. This is promising and got excited about this. One speedbump though. I kept getting an "Error updating filter" whenever i write the data extension. – JM Palomaria Oct 24 '19 at 19:04
  • Are you creating the data extension from scratch? Or using an existing one? Make sure it is not a filtered data extension (in both the source DE and the destination DE -- it could cause problems)? It needs to be a standard data extension. You can also try to just create a brand new destination data extension to test the query that everything is writing as expected. – Bread2287 Oct 24 '19 at 20:35
  • Hello @Bread2287 yes im doing the DE from scratch. Ill test it again now and see how it goes. – JM Palomaria Oct 25 '19 at 5:00
  • Hello @Bread2287 i'm still encountering error when creating the DE. i'll post something that i was able to made it work. – JM Palomaria Oct 25 '19 at 16:28
  • Hello @Bread2287 i figured it out. the i checked sendable in making the DE that's why I encountered the error. I was able to make the DE but after writing the query, I got no results at all – JM Palomaria Oct 27 '19 at 16:04
0

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

Here's the target data extension

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.

| improve this answer | |
  • @Bread2287 Hope you can help me out on this cuz i think we are on the right track with your answer earlier. – JM Palomaria Oct 25 '19 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.