Salesforce Docs Portal

Query: Subscriber Status

Find subscribers on a list and their statuses. Or, find only subscribers with a specific status, such as active or unsubscribed.

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.

Target Data Extension DataExtensionWithStatus
External Key DataExtensionWithStatus
Note
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.
Primary Key Name Data Type Required Length Default Value
X SubscriberKey Text X 254  
  Status Text X 25  
X ListName Text X 100  

Create a Query Activity

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

Query Name DataExtensionWithStatus
External Key DataExtensionWithStatus

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.

Target Data Extension DataExtensionWithStatus
Data Action Overwrite

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

See Also