Different subscriber status between All Subscriber list and _Subscriber data view

Question

A bit of context: I am building an automation that retrieves all subscribers that have a mismatch between their subscriber status in the All Subscriber list and the hasOptedOutOfEmail field on the CRM and updates the subscriber status accordingly (CRM is the "winner", so the subscriber status on the all subscriber list must always reflect the hasOptedOutOfEmail field).

This is the query I built in order to retrieve those records (it currently retrieves only one record for testing purposes):

Select c.Id as [Subscriber Key], EmailAddress as [Email Address],
CASE  
    when Status  = 'unsubscribed' then 'active'
    when Status != 'unsubscribed' then 'unsubscribed' 
end as Status 
from ent._Subscribers allSub
join ent.Contact_Salesforce_1 c on allSub.SubscriberKey = c.Id
where
((c.hasOptedOutOfEmail = 'true' AND allSub.Status != 'unsubscribed')
OR (c.hasOptedOutOfEmail = 'false' AND allSub.Status = 'unsubscribed'))
and c.Id = '0030E00001AS8ToQAL'

Query runs and then the whole process of data extraction-file transfer-file import is run in the automation.

By checking that record in the All subscribers list, everything looks fine: the subscriber initially had an active status, but the hasOptedOutOfEmail field on the CRM was true, so the status gets correctly updated in the list:

By querying the _Subscribers data view though, it still has an Active status:

Am I missing something here? Shouldn't the _Subscribers data view reflect the All Subscriber list? Why is there a mismatch between the two?

Answer 1

All Subscribers is not a direct representation or "frontend" to the ENT._Subscribers Data view.

You can simply deduce this by looking at the fields it shows and the documentation on the data view: https://help.salesforce.com/s/articleView?language=en_US&type=5&id=sf.mc_as_data_view_subscribers.htm.

Both contain different things.

In contrast, All Subscribers is an aggregation of data from multiple sources, and it shows you a calculated Business Unit level "status" depending on where you are logged in, NOT the status that is to be found on ENT._subscribers.

ENT._subscribers is always at Parent level, as it is ENTerprise level. So: Querying ENT._Subscribers retrieves the status that All Subscribers shows you when logged into Parent BU. If you go to Parent BU, you will see the status matches your retrieval.

However: From what you show, you seem to be interested in a BU level status, not the Parent Status. You likely have the Business Unit setting set to "unsubscribe from this BU only" in your Child BU.

Next problem:

Querying _subscribers without ENT. prefix is not possible / returns no data. This is simply not where the data about the BU level subscription status is kept.

You could find this on the ENT._businessUnitUnsubscribes Data view >> If a user is unsubscribed on BU level, their subscriberKey shows up there. (note that this is a completely different logic from how most other data views work)

https://help.salesforce.com/s/articleView?id=sf.mc_as_data_view_businessunitunsubscribes.htm&type=5

Next problem:

HasOptedOutOfEmail is a true / false field.

If unsubscription is not true / false, but "depends on where you are", HasOptedOutOfEmail can in no way reflect this correctly.

This is what the documentation means when it states:

Marketing Cloud Connect doesn’t support business unit-level unsubscribes for Enterprise 2.0 accounts. Subscribers are included in the All Subscribers list at the enterprise level and unsubscribed from all business units in the enterprise.

source: https://help.salesforce.com/s/articleView?id=sf.mc_co_unsubscribes.htm&type=5

In summary:

You might have to think about your solution design. If HasOptedOutOfEmail is supposed to be your single point of truth, you WILL lose context and you will create wrong effects. More likely, you'll have to find a way to expose the actual status from SFMC in Sales Cloud better than trying to impose the "simple" Sales Cloud logic onto a "complex" problem.