Query: Subscribers by Date or Time Frame

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.listid,
       l.listname,
       l.createddate AS DateAdded
FROM   [_listsubscribers] AS l
WHERE  l.listid = MY_LIST_ID
       AND createddate BETWEEN 2021-11-01 AND 2021-12-01

Finalize the Query Activity

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

• On line 7, replace MY_LIST_ID with the ID of your subscriber list. If you don’t know your list ID, go to Email Studio, select Subscribers | Lists | My List, and view its properties.
  
• On the last line, replace the date range with your time frame in YYYY-MM-DD format.

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: Find Subscribers by Date

To find subscribers who were added on a specific date, use this code and update the last line with your date.

SELECT l.subscriberkey,
       l.emailaddress,
       l.listid,
       l.listname,
       l.createddate AS DateAdded
FROM   [_listsubscribers] AS l
WHERE  l.listid = MY_LIST_ID
       AND l.createddate BETWEEN 2021-11-01 00:00:00 AND 2021-11-01 23:59:59