---
schema_version: "1"
id: 1f9b472a64274e8a58536ade4ca017d0110be9ac2e4d31014940704288c2f4f8
title: "Query: Journey Builder Bounced Email Messages"
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/mktg/264-0-0/mc_as_query_journey_builder_bounced_email_messages.md
canonical_url: https://help.salesforce.com/s/articleView?id=mktg.mc_as_query_journey_builder_bounced_email_messages.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: ed644597c3f97177a91370eacde34fd45194b9269273e7809b03b036e11a6e71
description: Create a list of contacts to send direct mailers to, based on bounced email messages from Journey Builder.
products:
  - Marketing
  - Marketing Cloud Engagement
release: "264"
---
# Query: Journey Builder Bounced Email Messages

Create a list of contacts to send direct mailers to, based on bounced email messages from Journey Builder.

## 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.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small "><colgroup><col style="width:50%"> <col style="width:50%"></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Target Data Extension</td><td class="slds-cell-wrap" style="vertical-align:top;">ContactProfileData</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">External Key</td><td class="slds-cell-wrap" style="vertical-align:top;">ContactProfileData</td></tr></tbody></table>

![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-mktg-mc_journeys_and_automations-264-0-0-production-enus/e08cdd71-c1c1-4ae5-a5e9-8a6dad583ad4/images/icon_note.png)

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 | ContactKey | Text | X | 254 |   |
|   | EmailAddress | EmailAddress | X | 254 |   |
|   | AddressLine1 | Text | X | 500 |   |
|   | AddressLine2 | Text |   | 500 |   |
|   | City | Text | X | 100 |   |
|   | State | Text | X | 2 |   |
|   | Zip | Text | X | 10 |   |
|   | HomePhone | Phone |   | 50 |   |
|   | MobilePhone | Phone |   | 50 |   |

## 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.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small "><colgroup><col style="width:50%"> <col style="width:50%"></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Target Data Extension</td><td class="slds-cell-wrap" style="vertical-align:top;">EmailBouncesbyJourney</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">External Key</td><td class="slds-cell-wrap" style="vertical-align:top;">EmailBouncesbyJourney</td></tr></tbody></table>

     
| Primary Key | Name | Data Type | Required | Length | Default Value |
| --- | --- | --- | --- | --- | --- |
|   | JourneyName | Text | X | 100 |   |
|   | VersionNumber | Number | X |   |   |
|   | EmailName | Text | X | 100 |   |
|   | SendTime | Date | X |   |   |
|   | BounceTime | Date | X |   |   |
|   | EmailAddress | EmailAddress | X | 254 |   |
|   | ContactKey | Text | X | 254 |   |
| X | ContactID | Number | X |   |   |
| X | JobID | Number | X |   |   |
| X | ListID | Number | X |   |   |
| X | BatchID | Number | X |   |   |
|   | AddressLine1 | Text | X | 500 |   |
|   | AddressLine2 | Text |   | 500 |   |
|   | City | Text | X | 100 |   |
|   | State | Text | X | 2 |   |
|   | Zip | Text | X | 10 |   |
|   | HomePhone | Phone |   | 50 |   |
|   | MobilePhone | Phone |   | 50 |   |

## 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.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small "><colgroup><col style="width:50%"> <col style="width:50%"></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Query Name</td><td class="slds-cell-wrap" style="vertical-align:top;">EmailBouncesByJourney</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">External Key</td><td class="slds-cell-wrap" style="vertical-align:top;">EmailBouncesByJourney</td></tr></tbody></table>

## 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.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small "><colgroup><col style="width:50%"> <col style="width:50%"></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Target Data Extension</td><td class="slds-cell-wrap" style="vertical-align:top;">EmailBouncesByJourney</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Data Action</td><td class="slds-cell-wrap" style="vertical-align:top;">Overwrite</td></tr></tbody></table>

## 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
```

#### See Also

- [Create a Data Extension in Contact Builder](https://help.salesforce.com/s/articleView?id=mktg.mc_cab_create_a_new_data_extension.htm&type=5&language=en_US)
- [Create a Data Extension in Email Studio (Enhanced)](https://help.salesforce.com/s/articleView?id=mktg.mc_es_create_data_extension.htm&type=5&language=en_US)
- [Build a SQL Query Activity](https://help.salesforce.com/s/articleView?id=mktg.mc_as_use_the_sql_query_activity.htm&release=264.0.0&type=5 "In Automation Studio, build a SQL query activity that retrieves the data that you’re interested in, and then add it to an automation.")
- [Optimizing a SQL Query Activity](https://help.salesforce.com/s/articleView?id=mktg.mc_as_optimize_the_query_activity.htm&release=264.0.0&type=5 "SQL query activities time out after 30 minutes. You can prevent timeouts and failures by optimizing your SQL query activities. If your query consistently runs longer than 10 minutes, we recommend using a different tool, such as Salesforce Customer Data Platform, to transform your data.")
- [SQL Reference](https://help.salesforce.com/s/articleView?id=mktg.mc_as_sql_reference.htm&release=264.0.0&type=5 "Get SQL query information for Automation Studio’s SQL Query activity at a glance.")