Query: Subscriber Status

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.

If the source data extension includes duplicate entries, deselect Primary Key for the SubscriberKey value.

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.listname,
       de.subscriberkey,
       l.[status]
FROM   [MY_DE_NAME] AS de
       INNER JOIN [_listsubscribers] AS l
               ON de.subscriberkey = l.subscriberkey
WHERE  l.listname = MY_LIST_NAME 

Finalize the Query Activity

Customize the query. Use the line numbers in Automation Studio to identify which lines to modify.

• On line 4, replace MY_DE_NAME with the name of the source data extension.
• On the last line, replace MY_LIST_NAME with the name of your subscriber list.
• If the source data extension doesn’t include a SubscriberKey field, replace de.subscriberkey on lines 2 and 6 with de.yourfield. For example, de.emailaddress or de.customerid.

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 Other Fields

To return other fields from the source data extension, add those fields to the target data extension and the query.

For example, if your source data extension includes a PolicyNumber field, add a field for PolicyNumber to the target data extension. Then in the query, insert de.policynumber in the select statement.

SELECT l.listname,
       de.subscriberkey,
       de.policynumber,
       l.[status]
FROM   [MY_DE_NAME] AS de
       INNER JOIN [_listsubscribers] AS l
               ON de.subscriberkey = l.subscriberkey
WHERE  l.listname = MY_LIST_NAME 

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