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 Answer 1

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
    Commented Aug 30, 2019 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
    Commented Aug 30, 2019 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
    Commented Aug 30, 2019 at 13:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .