Business unit subscriber status (or business unit membership) has been one of those frustrating and elusive things in SFMC for a long time. Since the platform only stores a business unit-level status for a subscriber if they’ve been sent an email from the child business unit, the various sources of this status in the platform are not accurate.
Here are my go-to queries to arrive at this data set. I’d suggest scheduling a daily Automation with these queries in the parent business unit.
All Subscribers in all business units
This query creates a row for every subscriber in every business unit. Since subscribers exist at the enterprise level, they could potentially have a send (or unsubscribe) in any child business unit.
You’ll need to update this query to add your own MIDs and business unit names.
Unsubscribes per business unit
This one sets the status for the subscriber if an explicit unsubscribe in a business unit has been recorded.
You’ll need to update the CASE statement for your own set of MIDs and business unit names.
Unsubscribes are the only deliverability status recorded at the business unit level.
Due to the dependencies on the System Data Views, if you have more than a few million subscribers, you’ll be hard-pressed to keep these queries running under the 30-minute timeout window. In other words, this solution doesn’t scale very well if you have a large subscriber base.
All of this assumes that your business units are configured to limit unsubscribes to the business unit level (i.e. The “Subscribers will be unsubscribed from this BU only” option is selected in Setup > Business units > Unsubscribes settings).
The business unit subscriber status data is helpful in a few areas in SFMC.
If Marketing Cloud is not your database of record for deliverability and status by business unit is a key data point, this can be a good source to extract and load into an external data warehouse.
You can use this data as a source for suppressing opt-outs so your pre-send counts more accurately reflect actual send counts.
If you have any reporting needs relating to business unit membership and cross-over you can utilize this data source. I don’t recommend treating SFMC like a reporting platform, but the flexibility of Query Activities and Data Extensions can’t be denied.
- Prior to this method, I used tracking extracts as David Devoy describes here on SFSE.
- Charlie Fay has good article about business unit subscriber status and some details on the SFMC data model
- Known Issue: New Marketing Cloud Business Units don’t have the _BusinessUnitUnsubscribes Data View Available
- Josh DeBlank has a outlined a WSProxy method for retrieving business unit unsubscribe also. This has the same scale limitations as my solution.