[SalesForce] Trying to query All Subscribers list

just joined the Stack Exchange but have used SFMC for years. I'm in an Enterprise 2.0 account and trying run a basic query to return email address and subscriberID for every record in the All Subscribers list. We'll use this to match back subscriber behavior in our SQL Server DB.

To get back a smaller subset of data, I limited the query to the first 100 rows. I setup this query in a child BU:

SELECT top 100 EmailAddress, SubscriberID FROM _ListSubscribers 
WHERE ListID = 258

I retrieved the ListID of 258 from the Properties tab of the All Subs list. I ran in Automation Studio and it ran successfully but returned zero records. To test the syntax, I switched to another list (using that list's ListID) in the child account and it returned the expected records.

I figured this was somehow related to querying All Subs from a child account, so setup the exact same DE, query and Automation in the parent account. I ran the automation and it again returned zero records.

This seems like such a basic query. What SFMC quirk am I missing here?

Best Answer

If you're querying from a child account, you'll need to prefix the view name with ent.

SELECT top 100 
EmailAddress
, SubscriberID 
FROM ent._ListSubscribers 
WHERE ListID = 258

Reference: Query Activity

Related Topic