[SalesForce] Query on system data views in Automation Studio

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.

Best Answer

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, 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

Related Topic