Aftermarket_Reporting_Initial_Email - Initial (Step 2)
Tue Feb 14 2023 21:56:46 GMT+0000 (Coordinated Universal Time)
Saved by
@rogerstamara
SELECT
mstr.EmailType, mstr.LanguageCode, mstr.TriggeredSendCustomerKey
, Count(s.EventDate) AS Sent
, Count(o.EventDate) AS Opens
, Count(c.EventDate) AS Clicks
, Count(b.EventDate) AS Bounces
, Count(u.EventDate) AS Unsubscribes
FROM _Sent s
LEFT JOIN Aftermarket_Reporting_Master mstr ON mstr.subscriberkey = s.subscriberkey
LEFT JOIN _Job AS job
ON job.JobID = s.JobID
LEFT JOIN _Open AS o
ON o.JobID = s.JobID
AND o.ListID = s.ListID
AND o.BatchID = s.BatchID
AND o.SubscriberKey = s.SubscriberKey
AND o.IsUnique = 1
LEFT JOIN _Click AS c
ON s.JobID = c.JobID
AND c.ListID = s.ListID
AND c.BatchID = s.BatchID
AND c.SubscriberKey = s.SubscriberKey
AND c.IsUnique = 1
LEFT JOIN _Bounce AS b
ON s.JobID = b.JobID
AND b.ListID = s.ListID
AND b.BatchID = s.BatchID
AND b.SubscriberKey = s.SubscriberKey
AND b.IsUnique = 1
LEFT JOIN _Unsubscribe AS u
ON s.JobID = u.JobID
AND u.ListID = s.ListID
AND u.BatchID = s.BatchID
AND u.SubscriberKey = s.SubscriberKey
AND u.IsUnique = 1
WHERE
s.TriggeredSendCustomerKey in ('142092')
AND DATEDIFF(m,getdate(),s.eventdate) = -1
GROUP BY mstr.EmailType, mstr.LanguageCode, mstr.TriggeredSendCustomerKey
content_copyCOPY
Comments