Query: Subscribers in a Publication or Suppression List

Before You Begin

To use this example, you need a working knowledge of SQL. You can copy and paste the code samples, but you must configure them for your setup.

Create a Target Data Extension

In Contact Builder or Email Studio, create a standard data extension with these properties and fields. This data extension is the destination for your query's data output.

Note If you create the data extension in Email Studio, the Nullable column replaces the Required column. If a field isn’t required, select Nullable. If a field is required, don’t select Nullable.

Create a Query Activity

In Automation Studio, create an SQL query with these properties.

Sample Query

Copy and paste this query.

SELECT l.subscriberkey,
       l.emailaddress,
       l.listname,
       l.listtype,
       l.[status],
       l.createddate AS DateAdded,
       l.addmethod,
       l.dateunsubscribed
FROM   [_listsubscribers] AS l
WHERE  l.listname = MY_LIST_NAME

Finalize the Query Activity

On the last line, replace MY_LIST_NAME with the name of your publication or suppression list.

Link the query to the target data extension.

Save your query activity, and then create and activate an automation that uses the activity.

Query Variation: Return Subscribers with a Specific Status

To return results for subscribers with a specific status, such as active, add this line to the end of the query. You can also replace active with another status, such as bounced, held, or unsubscribed.

AND l.[status] = active