Skip to content

Instantly share code, notes, and snippets.

@zuzannamj
Last active Jul 15, 2021
Embed
What would you like to do?
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
@Buratchi

This comment has been minimized.

Copy link

@Buratchi Buratchi commented Jul 15, 2021

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?

@zuzannamj

This comment has been minimized.

Copy link
Owner Author

@zuzannamj zuzannamj commented Jul 15, 2021

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