unsubscribed contacts from All Subscribers - Child Business Unit

Question

I need to extract unsubscribed contacts from All Subscribers in a child Business Unit in Marketing Cloud using SQL Query.

I tried to use this code, but it didn't give me any results.

SELECT DateUnsubscribed, EmailAddress 
FROM _Subscribers 
WHERE Status = 'unsubscribed'

Do you have any suggestions?

Answer 1

@cmbob I've ran into this issue as well. How it was explained to me by support is that the business unit level data view, _Subscribers, only contains records that have a different status from the parent. So, if a records is 'active' in both the parent and child, then that record will not exist in this data view.

The best way I have found for determining the Business Unit level subscriber status is by running a Tracking Extract from the Business Unit and include 'All Subscribers' in your extract. The resulting subscribers.txt file will contain that status relative to that business unit.

Answer 2

You can use this data view: _BusinessUnitUnsubscribes

Marketing Cloud documentation: the _BusinessUnitUnsubscribes data view

Query example:

select BusinessUnitID,
SubscriberID,
SubscriberKey, UnsubDateUTC, 
UnsubReason
from _BusinessUnitUnsubscribes
where businessUnitId = '12345678'

Answer 3

Yes you can absolutely do that using the _subscribers system data view. But since you are running the query in Child BU, you need to use ENT._Subscribers instead of just _Subscribers

Small correction see above