Common SQL
Recent Sends
Recent Sends
SELECTs.SubscriberKey,MAX(s.eventdate) AS eventdate,COALESCE(j.EmailName,ja.activityname) AS EmailName,s.jobid,jy.JourneyName,COUNT(s.eventdate) as CountFROM _Sent s
LEFT JOIN _Job jON s.jobid = j.jobid
join [_JourneyActivity] ja on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join _Journey jyon ja.VersionID = jy.VersionID
WHERE s.eventdate >= DATEADD(d,-60,GETDATE())AND s.subscriberKEy IS NOT NULLAND s.jobid is not null
GROUP BY s.SubscriberKey,COALESCE(j.EmailName,ja.activityname),s.jobid,jy.JourneyName
LEFT JOIN _Job jON s.jobid = j.jobid
join [_JourneyActivity] ja on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join _Journey jyon ja.VersionID = jy.VersionID
WHERE s.eventdate >= DATEADD(d,-60,GETDATE())AND s.subscriberKEy IS NOT NULLAND s.jobid is not null
GROUP BY s.SubscriberKey,COALESCE(j.EmailName,ja.activityname),s.jobid,jy.JourneyName
Recent engagement
SELECTy.SubscriberKey,y.eventdate,y.EmailName,y.jobid,y.JourneyName,COALESCE(y.opendate,y.clickdate) AS 'opendate',y.clickdate
FROM(SELECTs.SubscriberKey,MAX(s.eventdate) as eventdate,COALESCE(j.EmailName,ja.activityname) AS EmailName,s.jobid,jy.JourneyName,(SELECT MAX(o.eventdate) AS 'opendate' FROM _Open o WHERE s.subscriberkey = o.subscriberkey AND s.jobid = o.jobid AND o.eventdate >= DATEADD(d,-60,GETDATE()) GROUP BY o.subscriberkey, o.jobid) AS 'opendate',(SELECT MAX(c.eventdate) AS 'clickdate' FROM _click c WHERE s.subscriberkey = c.subscriberkey AND s.jobid = c.jobid AND c.eventdate >= DATEADD(d,-60,GETDATE()) GROUP BY c.subscriberkey,c.jobid) AS 'clickdate'FROM _Sent s
LEFT JOIN _Job jON s.jobid = j.jobid
join [_JourneyActivity] ja on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] jyon ja.VersionID = jy.VersionID
WHERE s.eventdate >= DATEADD(d,-60,GETDATE())AND s.subscriberKEy IS NOT NULLAND s.jobid is not null
GROUP BY s.SubscriberKey,COALESCE(j.EmailName,ja.activityname),s.jobid,jy.JourneyName) yWHERE y.opendate IS NOT NULLOR y.clickdate IS NOT NULL
FROM(SELECTs.SubscriberKey,MAX(s.eventdate) as eventdate,COALESCE(j.EmailName,ja.activityname) AS EmailName,s.jobid,jy.JourneyName,(SELECT MAX(o.eventdate) AS 'opendate' FROM _Open o WHERE s.subscriberkey = o.subscriberkey AND s.jobid = o.jobid AND o.eventdate >= DATEADD(d,-60,GETDATE()) GROUP BY o.subscriberkey, o.jobid) AS 'opendate',(SELECT MAX(c.eventdate) AS 'clickdate' FROM _click c WHERE s.subscriberkey = c.subscriberkey AND s.jobid = c.jobid AND c.eventdate >= DATEADD(d,-60,GETDATE()) GROUP BY c.subscriberkey,c.jobid) AS 'clickdate'FROM _Sent s
LEFT JOIN _Job jON s.jobid = j.jobid
join [_JourneyActivity] ja on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] jyon ja.VersionID = jy.VersionID
WHERE s.eventdate >= DATEADD(d,-60,GETDATE())AND s.subscriberKEy IS NOT NULLAND s.jobid is not null
GROUP BY s.SubscriberKey,COALESCE(j.EmailName,ja.activityname),s.jobid,jy.JourneyName) yWHERE y.opendate IS NOT NULLOR y.clickdate IS NOT NULL