Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT
s.SubscriberKey,
j.EmailName,
s.EventDate as SentDate,
o.EventDate as OpenDate,
c.EventDate as ClickDate,
b.EventDate as BounceDate,
b.BounceCategory,
u.EventDate as UnsubscribeDate
FROM _Sent s 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
LEFT JOIN _Click c ON s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
LEFT JOIN _Bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1
LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID and s.ListID = u.ListID and s.BatchID = u.BatchID and s.SubscriberID = u.SubscriberID and u.IsUnique = 1
@web20opensource

This comment has been minimized.

Copy link

@web20opensource web20opensource commented Mar 11, 2020

Can you include a example of the response, probably with a couple of rows? Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment