If you want to get a number delivered by sent data view, how do you do that?
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?
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.