Skip to content

Instantly share code, notes, and snippets.

@zuzannamj
Created September 27, 2019 20:20

Revisions

  1. zuzannamj created this gist Sep 27, 2019.
    14 changes: 14 additions & 0 deletions generic-tracking-query.sql
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,14 @@
    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