[SalesForce] Pulling Data from _Subscribers at BU level with SQL Query

I have a 2 Business Units setup, with a parent BU. Data is not meant to be shared between the 2.

The purpose: I want to understand how to differentiate Subscribers from different BUs as they are not meant to be shared between the 2 brands.

I understand that Subscriber Filter is the solution here, but even with that, I need to be able to populate the Brand property (if that is my filter) accordingly.

I would like to query the Subscribers BU. If I query at the child BU level – I get to records. I am unable to use ent._Subscribers.

Is there any prerequisite setup needed before I can run a query on the All Subscribers list? This is a new SFMC Account.

Appreciate if someone can share some insight with me on this.

Best Answer

There are multiple levels to your question, I will try to focus more on the background, architectural question than on the specifics (of subscriber filters and queries onto _subscribers from child BU):

A 2-BU, Parent-Child setup is typically not used for hard separation of data for two brands, where BU "A" gets only Data of brand A, and BU "B" gets only data for Brand B. In the default setup, the child is meant to inherit data from the Parent BU. By inheritance it is meant that from the child you can always run an SQL query against data extensions in the Parent / Enterprise BU to pull specific data from there, using the ENT. Prefix. You can configure the Data extensions freely to choose what field(s) you want to use for filtering with SQL.

This querying is a one-way street were the Child selects from Parent. This means that with only two BUs, the Parent would always have some data for both Brands A + B, not only A. Even if you set up completely separate DEs with separate external imports, much of the tracking data and _Subscribers will be shared - i.e. available for Admins in Parent BU.

The Child BU could be made to have only a data subset B, but an admin in the Child BU could not be physically stopped from accessing parent data via SQL, which would always be theoretically available in automation studio.

To have two separate Brands A and B, you would in this setup typically run with 3 BUs: 1 Parent BU serving 2 children and if necessary, limit the users in the Child BUs to non-admins, which are kept from accessing Automation Studio and All Subscribers List. This effectively keeps them from accessing Parent Data through SQL and SSJS. They would still have AMPScript, but that is why its functionality was limited accordingly this year.

This setup makes a lot of sense as a lot of functionality will be shared by both BUs, and this would be rightly located "above" the two children in the Parent to avoid code or functionality duplication. This is also where Admins would work on shared functionality, content etc.

With automated SQL queries running in the backend in both Child BUs, you would then only pull the relevant data into their context from ENT(erprise).

In contexts that include multiple Sales Cloud connections (not sure if applicable to your case), you can switch to a multi-org configuration via support, but THINK THIS THROUGH and read all the documentation you can find, starting with the links below. If you do not work with the Sales Cloud Connector, then it is likely not worth going through.

https://help.salesforce.com/articleView?id=mc_co_multi_org_account_configuration.htm&r=https%3A%2F%2Fwww.google.com%2F&type=5

https://help.salesforce.com/articleView?id=mc_co_faqs_for_multi_org.htm&type=5

You cannot revert the multi-org setting. Be aware, there always is a Shared All Subscribers List - even in Multi Org setups. Salesforce Documentation for multi-org states as a workaround to limit view access to All Subscribers for non-admins.

Hope this helps somewhat, even though I didn't address the particular question, which I think leads the wrong way.

Edit:

As to "why can we not query all Subscribers from child BU":

Several points -

https://help.salesforce.com/articleView?id=mc_as_data_view_subscribers.htm&type=5

Documentation states: 1. "Subscriber attributes are not available" (these are the attributes you defined, not the default ones in the data view.)

  1. "When profile attributes are created in Enterprise 2.0 accounts, new columns are added to the _EnterpriseAttribute table. Data view queries in Enterprise 2.0 accounts can return results from profile attribute columns in addition to the columns listed here."

https://help.salesforce.com/articleView?id=mc_as_data_view_enterpriseattribute.htm&type=5

However: 3. "NOTE This (enterpriseAttribute) data view is only available in the Parent account and cannot be used in a business unit."

So it looks like your query should not work, although no reason is given in documentation. You can write your query on these fields in the Parent BU into a Shared Data Extension, to only make a data subset available to the Child(ren).

If data visibility is an issue, then you might additionally want to remove view access from All Subscribers for Child BU users.

  • please note:

You CAN query _Subscribers from Child BU if you limit your query to the fields in _Subscribers Data view as documented. This just ran in my Child BU successfully and did in fact output data:

SELECT SubscriberKeyD,DateJoined,EmailAddress FROM _subscribers

Note, these are all standard fields.

Also, a query from Child BU into ENT._Subscribers validates green in my account. Before you try and reproduce once more, please verify with support if you have the following business rule enabled, and if no, enable it and try again :)

system_data_views

Related Topic