Skip to content

Instantly share code, notes, and snippets.

@zuzannamj
Created September 27, 2019 20:20
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save zuzannamj/cb9dd5e8b463e48ce007cf14f458e138 to your computer and use it in GitHub Desktop.
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
Copy link

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