0

How Can i join _Subscribers to this below query to get email address also?

Select 
s.SubscriberKey,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate,
c.[Click],
o.[Open]
FROM ( 
    SELECT
    sn.SubscriberKey,
    sn.JobID,
    sn.BatchID,
    sn.EventDate
    FROM [_sent] sn
) s
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Open], JobID
    FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Click], JobID
    FROM [_Click] cl
    GROUP BY cl.SubscriberKey, cl.JobID
) c
ON s.SubscriberKey = c.SubscriberKey
WHERE s.JobID = c.JobID
AND s.JobID = o.JobID
AND s.JobID IS NOT NULL
0

Simply add an INNER JOIN between _Sent and _Subscribers at some point. Assumption: This query runs in the Parent BU.

SELECT 
sn.SubscriberKey,
sub.EmailAddress
FROM _Sent sn
INNER JOIN
_Subscribers sub
on sn.SubscriberID = sub.SubscriberID

Something else: Generally it is recommended to split your query when working with data views. You will see strong declines in performance (and thus: timeouts) once you have several JOINS on data views in one big query and have 6 months of data. I'd hazard a guess and say that your query will start running into timeouts in a large volume setup sooner or later.

When splitting the query, it's pretty much up to you if you start with the INNER JOIN or end with the INNER JOIN.

I'd go in this order:

1) INNER JOIN sent / subscribers

2) LEFT JOIN result of 1 / Open

3) LEFT JOIN result of 2 / Click

hope this helps!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.