I have some queries that pull total opens, clicks, etc., and then make out unique opens and clicks as I try to automate my monthly reporting. In the sent Data View, there is no option to know the total delivered for a job and the total sent when I am trying to pull them. Is there any other way to get this information?
Log in to share your answer and help other learners.
Log in to answerBest Answer · By JanBask Salesforce Expert
Answered on Aug 29, 2023
By assuming that every mail not delivered is recorded on the data view, which is bounced, you can try this to get the totals.
SELECT
s.JobID
,TotalSends
,TotalBounces
,(TotalSends - TotalBounces) AS TotalDelivered
FROM
(SELECT
JobID
,MAX(row) as TotalSends
FROM
(SELECT
JobID
,row_number()over(partition by JobID order by SubscriberKey ASC) as row
FROM _Sent
) f
GROUP BY f.JobID) s
INNER JOIN (SELECT
JobID
,MAX(row) as TotalBounces
FROM
(SELECT
JobID
,row_number()over(partition by JobID order by SubscriberKey ASC) as row
FROM _Bounce ) d
GROUP BY d.JobID) b
ON s.JobID = b.JobID
After that you can subtract the totals to get the number delivered.