Review Marketing Cloud bounce data using Automation Studio

To quickly create the automation, query activity, and data extension described below, use Package Manager with the BounceHistory.zip package.

Create data extension

Purpose: A data extension stores the results of a query activity created in the next step. Its contents can be reviewed directly or used as the basis of further reporting. 

Navigate to Contact Builder > Data Extensions > Create

On the Properties screen: 
 

Click Next. On the Data Retention Policy screen:
 

Click Next. On the Attributes screen:
 

Click Complete. The data extension is created in the root folder, as a location was not selected on the Properties screen, but it can be moved without affecting the automation. 
 

Create SQL Query activity

Purpose: SQL Query activities allow the use of standard query language to retrieve data from a combination of data extensions and data views. Here, the activity type is used to retrieve a result from joining the _Bounce, _Job, and _Subscribers data views. 

Navigate to Journey Builder > Automation Studio > Activities, and click Create Activity. Select the SQL Query activity type.

Set Name: BounceHistory.
The folder location can be altered without affecting the automation. Click Next. 

Set Query to:

SELECT b.JobID, b.ListID, b.BatchID, b.SubscriberID, b.EventDate, s.EmailAddress, 
s.SubscriberKey, j.EmailName, b.Domain, b.BounceCategory, LEFT(b.SMTPBounceReason,1000) [SMTPBounceReason] 
FROM _Bounce b
JOIN ENT._Subscribers s ON s.SubscriberID = b.SubscriberID -- Remove ENT prefix if running from enterprise parent
JOIN _Job j ON j.JobID = b.JobID
WHERE b.EventDate > DATEADD(dd, -3, GETDATE())

⚠️ This query includes subscriber emails from the _Subscriber data view. As Marketing Cloud stores its subscriber model at the enterprise level, the enterprise-level data view must be invoked through the "ENT." prefix as written above. When running this query at the enterprise parent business unit, the prefix must be removed -- if you forget, an error message will remind you!

Click Validate Syntax to check for typos, then Next.

On the Target Data Extension screen, navigate to and select the BounceHistory data extension created earlier.

Set Data Action = Update, click Next, then Finish on the last page. 
 

Create Automation

Purpose: An automation is used to execute a single query activity on a recurring basis. 

Navigate to Journey Builder > Automation Studio > Overview and click New Automation. Title it BounceHistory for consistency.

From Starting Sources, drag Schedule onto the Starting Source column and click Configure. Set Start Date to a time later today. Set Repeat: Daily every 1 day(s) and End: Never. Click Done. 

From Activities, drag a SQL Query block onto the canvas, which creates a SQL Query activity in the Step 1 column, and click Choose on the newly-created activity block. 

Navigate to and select the BounceHistory SQL Query activity created earlier. Verify that the query displayed is as entered previously, and click Done. 

On the automation canvas, click Save. 
The automation is still in a paused status. To activate it, click the Active button, then Activate.

Once the automation completes, results can be reviewed directly in the BounceHistory data extension. As the automation continues to run on a scheduled basis, data will continue to insert. To analyze the growing log of data, try using data filters to narrow results. For example, filter the BounceHistory data extension by JobID and Domain to identify trouble spots in a single send. 
 

Create Data Filter

Purpose: As a result of our prior activities, we have a running history of bounce data in the BounceHistory data extension. A data filter on BounceHistory is created to focus on bounces from a particular JobID and domain. 

Navigate to Email Studio > Subscribers > Data Filters and click Create. 

Under Specify the data to build the filter from select Data Extension and click OK.

Under Select a Source, navigate to the BounceHistory data extension, select it, and click OK.

On the Create Filter screen, from the Fields menu on the left, drag JobID to the canvas on the right. Set it to is equal to, and provide a JobID. Drag domain to the canvas, set it to is equal to, and provide a domain like gmail.com. 

Click Save. Set Name to BounceHistory_Filtered and click Save again.

Click Create Data Extension on the top right, naming it BounceHistory_Filtered for consistency. Select a folder destination, and click Save. 

The resulting filtered data extension is a subset of BounceHistory using the specified criteria. As new data arrives in BounceHistory or if the critiera must be changed, the same filtered data extension can be refreshed with it by clicking the Refresh button on the top right. 

To quickly change filtering criteria with the data extension on screen, click Edit on the second left-hand panel where the existing criteria appears, then make changes and click Save & Refresh.