0

We would like to get subscriber opens and clicks for all emails from all versions of our journey in the last 6 months (I know that's the limit for data views). I've found the official SQL query but I have troubles filling the real-life data in it. Also, it seems clicks and opens are not covered in the SQL SF provides...

Specifically (my questions are in the SQL comments):

select j.JourneyName, /* HOW TO OUTLINE THIS? AS e.g. 'New Purchase Journey 2019' */
cast(s.EventDate as date) as [Date],
ja.ActivityName as 'EmailName',
ja.ActivityExternalKey,
count(s.SubscriberID) as [Sends]
from [_Sent] s
join [_JourneyActivity] ja 
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join [_Subscribers] su
on s.SubscriberID = su.SubscriberID
where ja.ActivityType in  ('EMAIL','EMAILV2') /* DO I NEED TO OUTLINE ALL EMAIL NAMES FROM ALL JOURNEY VERSIONS FROM LAST 6 MONTHS? AND HOW TO DO IT  - AS 'New Purchase  1', 'Follow Up Purchase 2', 'Purchase Confirmation 2', etc. ? */
and j.JourneyName = <JourneyName> /* AGAIN; DO I NEED TO REPEAT THE JOURNEY NAME AND HOW - LIKE 'New Purchase Journey 2019' ? */
and s.EventDate > dateadd(dd,-7,getdate()) /* HOW TO PULL DATA FROM LAST 6 MONTHS, NOT JUST 7 DAYS */
group by j.JourneyName,j.JourneyID,cast(s.EventDate as date),ja.ActivityName,ja.ActivityExternalKey

P.S: THERE ARE NO CLICKS AND OPENS IN THIS SQL, HOW TO ADD THEM FROM RESPECTIVE DATA VIEWS?

1

You need to join the _Open and _Click data views, either on the TriggererSendDefinitionObjectID which relates to JourneyActivityObjectID in _JourneyActivity data view, or on JobID which relates to JobID in the _Sent data view.

JOIN _Open o ON
o.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
JOIN _Click c ON
c.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID

OR

JOIN _Open o ON
o.JobID = s.JobID
JOIN _Click c ON
c.JobID = s.JobID

Take a look here to better understand how Data Views relate to each other.

3
  • Thanks. And what about the part: where ja.ActivityType in ('EMAIL','EMAILV2') - Do I need to write here all email names from all journey versions dating back last 6 months? If yes, do I need to write the exact email names, in this case e.g. 'New Purchase 1', 'Follow Up Purchase 2', 'Purchase Confirmation 2', etc. ? Finally, the part where the journey needs to be included in the filter, namely: j.JourneyName = <JourneyName> - Do I need to write here the exact name of the journey, in this case - 'New Purchase Journey 2019' ?
    – nix9247
    Aug 30 '19 at 13:43
  • You can filter by journey name, or you can just leave this out and get results for all your journeys. Regarding ja.ActivityType in ('EMAIL','EMAILV2') - leave this exactly like this. Possible values for ActivityType may include WAIT, EMAILV2, ENGAGEMENTDECISION etc, so you just want to get the ones that are ('EMAIL','EMAILV2')
    – zuzannamj
    Aug 30 '19 at 13:47
  • Hi, I've used this code per your instruction but I get no result. The code: 'select j.JourneyName, cast(s.EventDate as date) as [Date], ja.ActivityName as 'EmailName', ja.ActivityExternalKey, count(s.SubscriberID) as [Sends] from [_Sent] s join [_JourneyActivity] ja on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID join [_Journey] j on ja.VersionID = j.VersionID join [_Subscribers] su on s.SubscriberID = su.SubscriberID where ja.ActivityType in ('EMAIL','EMAILV2') group by j.JourneyName,j.JourneyID,cast(s.EventDate as date),ja.ActivityName,ja.ActivityExternalKey'
    – nix9247
    Aug 30 '19 at 13:57

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.