1

We're making some use of suppression lists and I'm finding that although they seem to live in _ListSubscribers I'm not getting query results when writing SQL to query their ID or Name. Attached is a couple sample queries. What am I missing?

SELECT * FROM _listsubscribers
WHERE 1=1 
AND ListID = '[1111111]'

and...

SELECT * FROM _listsubscribers
WHERE 1=1 
AND ListName = 'My great suppression list'

Data Extension is a carbon copy of _listsubscribers and is formatted as follows:

AddedBy / Number
AddMethod / Text / 17
CreatedDate / Date (Nullable)
DateUnsubscribed / Date (Nullable)
ListID / Number (Nullable)
ListName / Text / 50 (Nullable)
ListType / Text / 16 (Nullable)
Status / Text / 12 (Nullable)
SubscriberID / Number (Nullable)
SubscriberKey / Text / 254 
SubscriberType / Text / 100 (Nullable)

I've also read the documentation and they recommend a slightly different query and DE, listed below, still no results.

Query:

SELECT
SubscriberKey,
EmailAddress,
ListName,
ListType,
Status,
CreatedDate AS DateAdded,
DateUnsubscribed
FROM _ListSubscribers
WHERE 1=1 
AND ListName = 'My great suppression list'

Data Extension:

SubscriberKey / Text / 255 (Key)
EmailAddress / EmailAddress / 254
ListName / Text / 100
ListType / Text / 50 (Nullable)
Status / Text / 25
DateAdded / Date
DateUnsubscribed / Date (Nullable)
  • Why are you using where 1=1 – Samuel Dare Feb 21 '18 at 17:57
  • 1
    1=1 is a basic SQL include so that any statement after can start with an AND or OR statement. Just personal preference, and has no impact on other queries. – Matthew Wash Feb 21 '18 at 18:59
3

The ListID field in the ListSubscribers system data view is an integer. I would recommend changing the syntax in your where clause from '[1111111]' to 1111111. Also, I would recommend not using the wildcard in you select statement, but specifically call out the column you want to select.

Example:

SELECT AddedBy, AddMethod, CreatedDate, DateUnsubscribed
 , EmailAddress, ListID, ListName, ListType
 , Status, SubscriberID, SubscriberKey, SubscriberType 
FROM _listsubscribers
WHERE ListID = 1111111
  • 1
    question: can you confirm your query is completing successfully? I would recommend running in an automation to do this. – David Devoy Feb 21 '18 at 20:28

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.