[SOLVED] Find the ‘total sent’ for each JOB IDs in last 1 month along with ‘list name’ it was sent to in Marketing cloud

Find the ‘total sent’ for each JOB IDs in last 1 month along with ‘list name’ it was sent to in Marketing cloud

Currently by using this query, I get the number of total_sends for a particular JOB id. But I want to modify the Query so that it shows total_sends , ListName , Date for that each job ID within a specified timeframe.

Select 
count(s.EmailAddress) 
from [_Job] j 
join [_Sent] se 
on j.JobID = se.JobID 
join [_Subscribers] s 
on se.SubscriberID = s.SubscriberID 
where 
se.IsUnique = 1 and 
se.EventDate > dateadd(d,-20,getdate()) and 
j.JobID =11111

Here is the example of how my final Table should look like, enter image description here Here is the link to Data View/Table I am querying - https://sfmarketingcloudhome.files.wordpress.com/2021/02/dataviews_2021_v2.png

Solution

I haven't tested it yet but will something like this work for you? Make sure to modify / add the columns in your data extension if you are using Automation Studio.

Select 
count(s.EmailAddress) as total_sends,
j.SchedTime,
ls.Listname
from [_Job] j 
join [_Sent] se 
on j.JobID = se.JobID 
join [_Subscribers] s 
on se.SubscriberID = s.SubscriberID 
join _ListSubscribers ls ON se.SubscriberID = ls.SubscriberID
where 
se.IsUnique = 1 and 
se.EventDate > dateadd(d,-20,getdate()) and 
j.JobID =11111