Query to get unsubscribes from TriggeredSendCustomerKey not working

Question

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.

Answer 1

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)