Query: Journey Builder Bounced Email Messages

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 Data Extension to Store Contact Data

In Contact Builder or Email Studio, create a standard data extension with these properties and fields.

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 Target Data Extension for Journey Email Tracking

Create a standard data extension with these properties and fields. This data extension is the destination for your query's data output.

Create a Query Activity

In Automation Studio, create an SQL query with these properties. To ensure that bounce data is finalized, wait at least 72 hours after the send to run this query.

Sample Query

Copy and paste this query.

SELECT j.journeyname,
       j.versionnumber,
       ja.activityname  AS EmailName,
       s.eventdate      AS SendTime,
       b.eventdate      AS BounceTime,
       su.emailaddress,
       su.subscriberkey AS ContactKey,
       su.subscriberid  AS ContactID,
       s.jobid,
       s.listid,
       s.batchid,
       cpd.addressline1,
       cpd.addressline2,
       cpd.city,
       cpd.state,
       cpd.zip,
       cpd.homephone,
       cpd.mobilephone
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 [_bounce] AS b
               ON s.jobid = b.jobid
                  AND s.listid = b.listid
                  AND s.batchid = b.batchid
                  AND s.subscriberid = b.subscriberid
       INNER JOIN [_subscribers] AS su
               ON s.subscriberid = su.subscriberid
       INNER JOIN contactprofiledata AS cpd
               ON s.subscriberkey = cpd.contactkey
WHERE  ja.activitytype IN ( EMAIL, EMAILV2 )
       AND s.eventdate < Dateadd(hour, -72, 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 29 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.

Query Variation: Capture Bounces for All Journeys

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

AND j.journeyname = MY_JOURNEY_NAME