2

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.

6

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

  • Hi Johannes, thanks for your help! I opened a case with support to verify if they enabled access to those data views for our account. Hopefully they get back to me soon:) – Jens Jul 19 '18 at 10:21
  • It works! They had to enable access for our account. – Jens Jul 20 '18 at 13:27
  • Nice. I wish you all the best for the future ;) – Johannes Schapdick Jul 21 '18 at 8:19
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. :-)

  • Hi Carrie thanks for your help! I checked in Service Cloud if we set up any business units, but I only found 1 business unit, which I assume is the parent business unit? Despite this I tried querying using the "ent" prefix, which throws the following error message: "The 'Ent.' prefix can only be used to query data in a parent account from a child account." – Jens Jul 19 '18 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.