sql-tracking-query.sql

SELECT
    s.SubscriberKey,
    ls.EmailAddress,
    j.EmailName,
    j.EmailSubject,
    j.FromName,
    j.FromEmail,
    s.EventDate AS SentDate,
    o.EventDate AS OpenDate,
    op.Opens AS NumberOfOpens,
    c.EventDate AS ClickDate,
    cl.Clicks AS NumberOfClicks,
    url.URLs AS NumberOfLinksClicked,
    b.EventDate AS BouncedDate,
    b.BounceCategory,
    u.EventDate AS UnsubscribedDate
FROM _Sent s
LEFT JOIN _Job 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
LEFT JOIN (select 
    jobid,
    ListID,
    BatchID,
    subscriberid, 
    count(subscriberid) AS Clicks
from _click
group by subscriberid, jobid, ListID, BatchID) cl ON s.JobID = cl.JobID
    AND s.ListID = cl.ListID
    AND s.BatchID = cl.BatchID
    AND s.SubscriberID = cl.SubscriberID
LEFT JOIN (select distinct
    jobid,
    ListID,
    BatchID,
    subscriberid, 
    count(distinct url) AS URLs
from _click
group by subscriberid, jobid, ListID, BatchID) url ON s.JobID = url.JobID
    AND s.ListID = url.ListID
    AND s.BatchID = url.BatchID
    AND s.SubscriberID = url.SubscriberID    
LEFT JOIN (select 
    jobid,
    ListID,
    BatchID,
    subscriberid, 
    count(subscriberid) AS Opens
from _open
group by subscriberid, jobid, ListID, BatchID) op ON s.JobID = op.JobID
    AND s.ListID = op.ListID
    AND s.BatchID = op.BatchID
    AND s.SubscriberID = op.SubscriberID    
LEFT JOIN _ListSubscribers ls ON s.SubscriberID = ls.SubscriberID

Hi, you are such a big help. i need to add email and Name which i tried to pull from a sync DE. I tried to add it in the query but i kept getting time out error.
Is there anything you can suggest?

Please post your query to Stack Exchange, you’ll be able to get help quickly over there Thanks!

…

On Thu, 15 Jul 2021 at 18:12, Buratchi ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Hi, you are such a big help. i need to add email and Name which i tried to pull from a sync DE. I tried to add it in the query but i kept getting time out error. Is there anything you can suggest? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <https://gist.github.com/b6bed85471cba5d007c7b03175cc7cc0#gistcomment-3814089>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFR6EA2CEY5FRVLPQDF2ZKLTX4CG3ANCNFSM5AN3ZVHQ> .