I thought the code below would work, but its only giving me Sent IF they opened and IF they clicked.
I assumed a LEFT JOIN would not do that . Any thoughts?
Example for this JobID the Counts should be: Sent 4102, Openers 543, Clickers 113
But the query result is only giving me 42 results (sent+opened+clicked)
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
WHERE JobID = 499749
) 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
1 answer
Hi Jason,
Sorry for the inconvenience. In case you don't receive a response here, may I also suggest joining the group below to collaborate with the experts for best practice and advice.
https://trailblazers.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000001pQ5CAI
Hope this helps.
Regards,
#.redirect
