Clarifying a workaround to get business-unit subscriber status

Question

When a business unit uses the option “Subscribers will be unsubscribed from this business unit only”, the subscriber status of a user relative to that specific business unit is not easily accessible. Knowledge Article 000230283 details a way to obtain these subscriber statuses, but I do not fully understand how the steps mentioned there actually work.

While it is not explicitly stated, the steps suggest that importing an extract of the parent _ListSubscribers table into a new list created for the business unit will filter the inbound list to subscribers for the business unit only. Is this because the resulting list is filtered to subscribers applicable to the business unit? As an example, if a subscriber is unsubscribed for the given business unit only, where in this workflow is that subscriber flagged as unsubscribed, rather than subscribed (as they would be in the _ListSubscribers table)?

Answer 1

This is an ongoing issue for any accounts where unsubscribes are maintained in BU level. As a result when a contact is created/imported to All Subs for any BU for the first time the ENT.All Subscribers will retain that status in the parent level. And this doesn't change even when that subscriber has already unsubscribed from that BU.

Ideally, _ListSubscribers should give you the right status of that subscriber however it is not the case. It still shows you the status of the parent level and there is a workaround to get the right status in BU level. The setup takes an automation build and is limited to 1-hour schedule run unless used an API to call the automation more frequently.

Here is the process:

• Step 1: Create a list in that BU where you want to retain the status of the subscribers. I call it 'Mock All Subscribers'.

• Step 2: This is the painful process - you would need a full list of the SubscriberKey and EmailAddress for that BU. Please keep the status field empty as it will get populated by the system during the next step. Import those contacts in a DE with three fields:

  • Subscriber Key (Not Empty)
  • Email Address (Not Empty)
  • Status (Empty)

• Step 3: Create an automation that will export this DE to the FTP using Data Extract and File Transfer activity.

• Step 4: Create an import activity to import that csv file back to the 'Mock All Subscribers List'.
• Step 5: Go to the property of the 'Mock All Subscribers List' and grab the List ID which you will use in the SQL Query to pull in the unsubscribes.

Example Query:

SELECT SubscriberKey, EmailAddress, Status
FROM [_ListSubscribers] allsub
WHERE allsub.ListID = 123 
AND allsub.Status = 'Unsubscribed'

Note: The 'Mock All Subscribers' list upon the import activity will retain the correct status of the subscribers.

Reference:

• Use the SQL Query Activity
• Import Activity
• File Transfer Activity
• Data Extract Activity