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?

3 Answers 3


@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.


You can use this data view: _BusinessUnitUnsubscribes

Marketing Cloud documentation: the _BusinessUnitUnsubscribes data view

Query example:

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

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

  • Note that this will return the status of the subscribers held at the Parent Business Unit - not the child business unit. Dec 20, 2017 at 15:29
  • @AnonWonderer As far as I know The AllSubscribers are maintained at the account level.AllSubscribers List is the same across all child Business Units and parent Business unit Dec 20, 2017 at 15:33
  • The multi-org is enabled in our marketing cloud account and if I use ENT._Subscribers in the query, the results is the same of the query configured in the Corporate Business Unit in order to extract the 'unsubscribed' contacts from All Subscribers .
    – cmbob
    Dec 20, 2017 at 16:28

You must log in to answer this question.

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