1

My SQL is a little rusty, but this query should be simple enough. I'm trying to get a list of all the emails in the all subscriber list that have a status of "unsubscribed" or "held" in the system. The following query errors on "_Subscribers is not a known data extension or system data view." I'm sure I don't have the table name correct, but I'm not sure what it is or how to find it.

SELECT EmailAddress, SubscriberKey, [Status]
FROM _Subscribers
WHERE [status] is ('unsubscribed','held')
1

3 Answers 3

2

If you're getting that error, I assume you are trying to access the _Subscribers Data View from a child Business Unit. Try the following (and note the IN operator):

SELECT EmailAddress, SubscriberKey, Status
FROM ENT.[_Subscribers]
WHERE [Status] IN ('unsubscribed','held')
2
  • Thank you Eliot. I'm still getting this error: An error occurred while checking the query syntax. Errors: ENT.[_Subscribers] is not a known data extension or system data view. You can only query existing data extensions or system data views. Feb 7, 2017 at 16:57
  • Not all SFMC accounts have the Enterprise tables enabled via Query Activities, You may want to contact support. Feb 28, 2019 at 0:09
0

Eliot answer is correct but you try this

SELECT EmailAddress, SubscriberKey, Status
FROM _Subscribers
WHERE Status IN ('unsubscribed','held')
4
  • Thanks. Again, I'm getting this same error: An error occurred while checking the query syntax. Errors: _Subscribers is not a known data extension or system data view. You can only query existing data extensions or system data views. Feb 7, 2017 at 18:14
  • It's rare, but I've seen accounts where you need support to activate the Data Views for querying. Are you able to query any other data views? Try: "SELECT * FROM _Open" as an example Feb 7, 2017 at 20:05
  • Nope. How do I turn on data views for querying? I guess that's step #1 :) Feb 7, 2017 at 20:13
  • Create a case with SFMC support and they should be able to enable it in short time. If you've never made a case before, do as follows: Hover over name at the top right of the screen within Marketing Cloud -> Salesforce Help & Training -> Select Contact Support / Create a Case at bottom right of screen -> General Email Functionality. Feb 7, 2017 at 20:34
0

You can try this also.

select j.JobID ,u.EventDate, s.EmailAddress, s.SubscriberKey
From _job j Join _Unsubscribe u on j.JobID = u.JobID
Join _Subscribers s on u.SubscriberID = s.SubscriberID
Where u.IsUnique = 1 And u.Eventdate > dateadd (d,-30,getdate())

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .