1

I'm trying to automate my monthly reporting and I have some queries that pull total opens, clicks etc and then figures out unique opens, clicks. I'm trying to pull total delivered for a job and total sent, but I don't see that as an option in the Data View. Is there another way to get this information?

https://help.marketingcloud.com/en/documentation/automation_studio/using_automation_studio_activities/using_the_query_activity/data_views/data_view_job/

Improve this question
4
  • COUNT * in Jobs should give you number sent, on another table Count * in the bounces table, and subtract the difference for Number Delivered?
    – 0xsegfault
    Commented Aug 1, 2017 at 14:56
  • Yes I suppose. Is that how number delivered is calculated? I can give that a try and see if it matches up with the send data.
    – Stan Alachniewicz
    Commented Aug 1, 2017 at 14:57
  • 1
    Not a 100% sure of how its calculating and I would definitely validate it with send counts over taking advice off some guy on Stack Exchange :)
    – 0xsegfault
    Commented Aug 1, 2017 at 15:00
  • @Data_Kid I don't think that's it because counting all rows in _Jobs just gives me the number of jobs delivered, but not total each job was sent to right?
    – Stan Alachniewicz
    Commented Aug 1, 2017 at 17:31

1 Answer 1

Reset to default
3

Based on the assumption that every email not delivery is reccorded on the bounced data view, 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

The you can subtract the totals to arrive at number delivered.

Improve this answer
1
  • What about the exclusion (if there are any)? How to consider exclusions in the sql script? OR it is correct to say that exclusion won't exist in SENT data view so we do not need to do anything for exclusion in the query? and total - bounce should give an accurate count?
    – kuml
    Commented Jul 16, 2018 at 15:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .