Query on system data views in Automation Studio

Question

I was wondering if someone perhaps know how we can identify the system data views we use/have in our account?

According to this article we should be able to query for instance the data view: ListSubscribers in Automation Studio, to find subscribers on lists in our account. As so we want to query on the 'All Subscribers' list and join this data view with our synchronized data extensions, in order for us to identify which unwanted contacts can be deleted.

However we are not able to query on this data views, and neither are we able to do so on other data views. When validating the syntax it throws the following error:

"the is not a known data extension or system data view. You can only query existing data extensions or system data views.".

SELECT top 100 * FROM _ListSubscribers 

I suspect we are not able to query on system data views due to restrictions in our account, but I am not sure.

Answer 1

First of all ask support if they enable you to access the dataviews. I believe they have to enable it for your account.

Create a data extension with these fields:

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

Create an SQL Activity:

SELECT
SubscriberID, DateUndeliverable, DateJoined, DateUnsubscribed, Domain, EmailAddress, BounceCount, SubscriberKey, SubscriberType, Status, Locale
FROM
_Subscribers

Target it to the DataExtension you just created in step 1.

The same procedure for ListSubScribers:

SELECT
AddedBy, AddMethod, CreatedDate, DateUnsubscribed, EmailAddress, ListID, ListName, ListType, Status, SubscriberID, SubscriberType
FROM
_ListSubscribers

For all other dataextensions follow the same steps. Here is an overview of many dataviews https://help.salesforce.com/articleView?id=mc_as_data_views.htm&type=5

Answer 2

Also, if you are in a child business unit, you may need to add an "ent." prefix depending on the data view you are trying to query (e.g. _subscribers).

Example:

Select
SubscriberId, DateJoined
FROM
ent._Subscribers

But, keep in mind, the subscriber status is at the parent BU (versus the child BU), and if you wish to know subscribers status at the child BU level. For your reference, there is a pretty good discussion/information here about data views and business units here: Pulling data from data views for one business unit only using query

Again, that's only useful if you're in a situation where you are in a business unit. :-)