This seems like it should work, but it doesn't...

select s.TriggeredSendCustomerKey, 
COUNT(s.TriggeredSendCustomerKey) as Unsubscribes
from [01_Sent Data View] s
where s.SubscriberKey IN (
    select SubscriberKey
    from _Unsubscribe
group by s.TriggeredSendCustomerKey

It's pulling numbers, but the numbers do not match the filters in the Sent tracking of Marketing Cloud. I tried distinct, but that didn't change anything. I've tried rearranging this query in so many ways and nothing works. It seems pretty straightforward so I can't figure out what I am doing wrong.

the [01_Sent Data View] table is basically all individual sends from August. I pulled those first and now I need to pull people who unsubscribed from that list so I need to see if they are in the _Unsubscribe table. I think my issue is that SubscriberKey may show up multiple times in [01_Sent Data View] because a person may have gotten more than one email, but I don't know how to count them only one time.


Ok I figured out what was going wrong. I was selecting from my sent data view table which for some reason had multiple JobIDs per TriggeredSendKey. This was making it difficult to return one value so I reversed it to pull from the unsubscribes and specify a date range.

SELECT distinct u.SubscriberKey, u.JobID
FROM _Unsubscribe u
JOIN _Job j
ON u.JobID = j.JobID
WHERE j.TriggeredSendCustomerKey = 41050
and u.EventDate > DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND u.EventDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 
  • 1
    The JobID for a given Triggered Send will change when the definition is republished. If you have edited an email in a running journey, after clicking ‘Done’, a saving window will initiate. This counts as republishing the Triggered Send and the email activity will have a new JobID.
    – charliefay
    Apr 29 '20 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.