1

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.

1 Answer 1

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) 
1
  • 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, 2020 at 9:54

You must log in to answer this question.

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