0

I have a requirement where i need to fetch subscribers who have opened at least one email in the last 5 emails delivered to them. Is there any way to achieve this. I have tried Data Views like _Subsribers and _Sent.

  • 3
    Please provide what you've tested already and what are the problems/errors you are facing. – Rachid Mamai Jan 9 at 9:23
  • Answer: Yes. Question can be closed or needs to be edited a lot. – Johannes Schapdick Jan 9 at 9:30
2

You can try something like this

SELECT 
r.SubscriberKey,
j.EmailName, 
s.EventDate as SentDate, 
o.EventDate as OpenDate, 
FROM [YOUR DATA EXTENSION]r LEFT JOIN _Sent s ON r.SubscriberKey= s.SubscriberKey
LEFT JOIN _Job as j ON s.JobID = j.JobID 
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
WHERE j.EmailName = 'first email name'
OR j.EmailName = 'second email name'
OR j.EmailName = 'third email name'
OR j.EmailName = 'forth email name'
OR j.EmailName = 'fifth email name'
| improve this answer | |
  • 1
    The emphasis on this answer is LIKE this. There is no timing or last 5 jobs or something involved to be dynamic. This answer is static – Johannes Schapdick Jan 9 at 10:36
  • 1
    You are right @Johannes Schapdick – Duicug Jan 9 at 10:54
  • Thanks for your response @Duicug. We are not checking any particular email delivery. Last 5 emails could be any of the emails in marketing cloud anf then we need to check if anyone of those last 5 deliveries has been opened. Currently i don't know feasibility of this. – R Suri Jan 13 at 7:15

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