Query: Journey Builder Sends in Last 24 Hours

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 j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       s.eventdate      AS SendTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       s.subscriberid   AS ContactID,
       s.jobid,
       s.listid,
       s.batchid
FROM   [_sent] AS s
       INNER JOIN [_journeyactivity] AS ja
               ON s.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON s.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
AND s.eventdate > Dateadd(hour, -24, Getdate())        
AND j.journeyname = MY_JOURNEY_NAME

Finalize the Query Activity

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

• On the last line, replace MY_JOURNEY_NAME with the name of your journey.
• If you’re querying from the child business unit of an Enterprise 2.0 account, modify line 16 to add the ent. prefix.
  • Original: INNER JOIN [_subscribers] AS su
  • Modified: INNER JOIN ent.[subscribers] AS su

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: Capture Sends for All Journeys

To capture sends for all journeys, remove the last line from the query.

AND j.journeyname = 'MY_JOURNEY_NAME'

Query Variation: Capture a Different Time Frame

To capture data for a different time frame, use any number of hours or days up to 180 days. For example, we modified line 19 in this query to retrieve 7 days of data.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       s.eventdate      AS SendTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       s.subscriberid   AS ContactID,
       s.jobid,
       s.listid,
       s.batchid
FROM   [_sent] AS s
       INNER JOIN [_journeyactivity] AS ja
               ON s.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON s.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
  AND s.eventdate > Dateadd(day, -7, Getdate())        
  AND j.journeyname = MY_JOURNEY_NAME

To pull a specific range, such as all activity from yesterday, use this code.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       s.eventdate      AS SendTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       s.subscriberid   AS ContactID,
       s.jobid,
       s.listid,
       s.batchid
FROM   [_sent] AS s
       INNER JOIN [_journeyactivity] AS ja
               ON s.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON s.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
       AND j.journeyname = MY_JOURNEY_NAME
       AND s.eventdate BETWEEN Cast(Dateadd(day, -1, Getdate()) AS DATE) AND
                               Cast(
                               Getdate() AS DATE)

Depending on how much data you’re retrieving, a query spanning more than 30 days can time out and fail. If your query times out, try a shorter time frame.

Query Variation: Capture Opens

To capture opens instead of sends, use this code. You must also add a field for OpenTime to your target data extension.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       o.eventdate      AS OpenTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       o.subscriberid   AS ContactID,
       o.jobid,
       o.listid,
       o.batchid
FROM   [_open] AS o
       INNER JOIN [_journeyactivity] AS ja
               ON o.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON o.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
       AND o.eventdate > Dateadd(hour, -24, Getdate()) 
       AND j.journeyname = MY_JOURNEY_NAME

Query Variation: Capture Clicks

To capture clicks instead of sends, use this code. You must also add a field for ClickTime to your target data extension.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       c.eventdate      AS ClickTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       c.subscriberid   AS ContactID,
       c.jobid,
       c.listid,
       c.batchid
FROM   [_click] AS c
       INNER JOIN [_journeyactivity] AS ja
               ON c.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON c.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
       AND c.eventdate > Dateadd(hour, -24, Getdate()) 
       AND j.journeyname = MY_JOURNEY_NAME

Query Variation: Capture Bounces

To capture bounces instead of sends, use this code. You must also add a field for BounceTime to your target data extension.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       b.eventdate      AS BounceTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       b.subscriberid   AS ContactID,
       b.jobid,
       b.listid,
       b.batchid
FROM   [_bounce] AS b
       INNER JOIN [_journeyactivity] AS ja
               ON b.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON b.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
       AND b.eventdate > Dateadd(hour, -24, Getdate()) 
       AND j.journeyname = MY_JOURNEY_NAME

Query Variation: Capture Complaints

To capture complaints instead of sends, use this code. You must also add a field for ComplaintTime to your target data extension.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       c.eventdate      AS ComplaintTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       c.subscriberid   AS ContactID,
       c.jobid,
       c.listid,
       c.batchid
FROM   [_complaint] AS c
       JOIN [_sent] AS s
         ON c.jobid = s.jobid
            AND c.listid = s.listid
            AND c.batchid = s.batchid
            AND c.subscriberid = s.subscriberid
       INNER JOIN [_journeyactivity] AS ja
               ON s.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON c.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
       AND c.eventdate > Dateadd(hour, -24, Getdate()) 
       AND j.journeyname = MY_JOURNEY_NAME

Query Variation: Capture Unsubscribes

To capture unsubscribes instead of sends, use this code. You must also add a field for UnsubscribeTime to your target data extension.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       u.eventdate      AS UnsubscribeTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       u.subscriberid   AS ContactID,
       u.jobid,
       u.listid,
       u.batchid
FROM   [_unsubscribe] AS u
       JOIN [_sent] AS s
         ON u.jobid = s.jobid
            AND u.listid = s.listid
            AND u.batchid = s.batchid
            AND u.subscriberid = s.subscriberid
       INNER JOIN [_journeyactivity] AS ja
               ON s.triggerersenddefinitionobjectid = ja.journeyactivityobjectid
       INNER JOIN [_journey] AS j
               ON ja.versionid = j.versionid
       INNER JOIN [_subscribers] AS su
               ON u.subscriberid = su.subscriberid
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
       AND u.eventdate > Dateadd(hour, -24, Getdate()) 
       AND j.journeyname = MY_JOURNEY_NAME