Query: Journey Builder Sends by Email Across Versions

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,
       Cast(s.eventdate AS DATE) AS Date,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Sends
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 > Dateadd(day, -7, Getdate())
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(s.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey

Finalize the Query Activity

Customize the query. To identify which lines to modify, use the line numbers in Automation Studio.

• On line 14, 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 11 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 line 14 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 days up to 180. For example, we modified line 15 in this query to retrieve 14 days of data.

SELECT j.journeyname,
       Cast(s.eventdate AS DATE) AS Date,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Sends
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 > Dateadd(day, -14, Getdate())
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(s.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey

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

SELECT j.journeyname,
       Cast(s.eventdate AS DATE) AS Date,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Sends
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 Dateadd(day,-1,Getdate()) AND Getdate() 
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(s.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey

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. Make sure to add fields for Opens and OpenDate to your target data extension.

SELECT j.journeyname,
       Cast(o.eventdate AS DATE) AS OpenDate,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Opens
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 j.journeyname = MY_JOURNEY_NAME
       AND o.eventdate BETWEEN Dateadd(day, -1, Getdate()) AND Getdate()
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(o.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey

Query Variation: Capture Clicks

To capture clicks instead of sends, use this code. Make sure to add fields for Clicks and ClickDate to your target data extension.

SELECT j.journeyname,
       Cast(c.eventdate AS DATE) AS ClickDate,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Clicks
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 j.journeyname = MY_JOURNEY_NAME
       AND c.eventdate BETWEEN Dateadd(day, -1, Getdate()) AND Getdate()
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(c.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey

Query Variation: Capture Bounces

To capture bounces instead of sends, use this code. Make sure to add fields for Bounces and BounceDate to your target data extension.

SELECT j.journeyname,
       Cast(b.eventdate AS DATE) AS BounceDate,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Bounces
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 j.journeyname = MY_JOURNEY_NAME
       AND b.eventdate BETWEEN Dateadd(day, -1, Getdate()) AND Getdate()
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(b.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey

Query Variation: Capture Complaints

To capture complaints instead of sends, use this code. Make sure to add fields for Complaints and ComplaintDate to your target data extension.

SELECT j.journeyname,
       Cast(c.eventdate AS DATE) AS ComplaintDate,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Complaints
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 j.journeyname = MY_JOURNEY_NAME
       AND c.eventdate BETWEEN Dateadd(day, -1, Getdate()) AND Getdate()
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(c.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey

Query Variation: Capture Unsubscribes

To capture unsubscribes instead of sends, use this code. Make sure to add fields for Unsubscribes and UnsubscribeDate to your target data extension.

SELECT j.journeyname,
       Cast(u.eventdate AS DATE) AS UnsubscribeDate,
       ja.activityname           AS EmailName,
       ja.activityexternalkey,
       Count(*)                  AS Unsubscribes
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 j.journeyname = MY_JOURNEY_NAME
       AND u.eventdate BETWEEN Dateadd(day, -1, Getdate()) AND Getdate()
GROUP  BY j.journeyname,
          j.journeyid,
          Cast(u.eventdate AS DATE),
          ja.activityname,
          ja.activityexternalkey