Salesforce Docs Portal

Query: Bounce History

Find a history of individual bounce responses, and identify how to resolve them.

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.

Target Data Extension BounceHistory
External Key BounceHistory
Retention Setting On
Delete Individual Records
Retention Period 180 Days
Note
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 SubscriberID Number X    
X JobID Number X    
X ListID Number X    
X BatchID Number X    
  EventDate Date      
  EmailAddress Email Address      
  SubscriberKey Text   254  
  EmailName Text   100  
  Domain Text   100  
  BounceCategory Text   50  
  SMTPBounceReason Text   4000  
  InsertedDate Date     Select use the current system date

Create a Query Activity

In Automation Studio, create an SQL query with these properties.

Query Name BounceHistory
External Key BounceHistory

Sample Query

Copy and paste this query.

SELECT b.SubscriberID,
       b.JobID,
       b.ListID,
       b.BatchID, 
       b.EventDate,
       l.EmailAddress,
       b.SubscriberKey,
       j.EmailName,
       b.Domain, 
       b.BounceCategory,
       b.SMTPBounceReason
FROM   _listsubscribers AS l 
      INNER JOIN _bounce AS b 
              ON l.SubscriberID = b.SubscriberID 
      INNER JOIN _job AS j
              ON b.JobId = j.JobID
WHERE  b.EventDate > DATEADD(dd, -3, GETDATE())

Finalize the Query Activity

Link the query to the target data extension.

Target Data Extension BounceHistory
Data Action Update

Save your query activity, and then create and activate an automation that uses the activity. To continuously update your bounce history, run the automation every 24 hours.

Query Variation: Capture Bounces for a Specific Email

To capture bounces for a specific email, add this line to the end of the query and replace MY_EMAIL_NAME with the name of the email. 

AND j.emailname = MY_EMAIL_NAME

Query Variation: Capture Bounces for a Specific Job

To capture bounces for a specific job, add this line to the end of the query and replace MY_JOB_ID with the job ID. 

AND j.jobid = MY_JOB_ID

See Also