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. Jan 9, 2020 at 9:23
  • Answer: Yes. Question can be closed or needs to be edited a lot. Jan 9, 2020 at 9:30

1 Answer 1


You can try something like this

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'
  • 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 Jan 9, 2020 at 10:36
  • 1
    You are right @Johannes Schapdick
    – Duicug
    Jan 9, 2020 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, 2020 at 7:15

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