0

I would like to retrieve the list of bounces of all the emails are included in the different journeys on a daily basis.

I know we would have to use the Data Views but I need help to build the query as it requires to join _Bounces, _Journey, _JourneyActivity (if I am not mistaken).

Could someone provide some light on how the query should look like? This is what I have so far:

Would it work like this?

select
j.JourneyName,
j.VersionNumber,
ja.ActivityName as 'EmailName',
s.EventDate as 'SendTime',
su.EmailAddress,
su.SubscriberKey as 'ContactKey',
s.SubscriberID as 'ContactID' ,
b.EventDate,
b.Domain,
b.BounceCategoryID,
b.BounceCategory,
b.BounceSubcategoryID,
b.BounceSubcategory,
b.BounceTypeID,
b.BounceType,
s.JobID,
s.ListID,
s.BatchID
from [_Sent] s
join [_JourneyActivity] ja 
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j
on ja.VersionID = j.VersionID
join [_Subscribers] su
on s.SubscriberID = su.SubscriberID
join [_Bounce] b 
on s.TriggererSendDefinitionObjectID = b.TriggererSendDefinitionObjectID 

Thanks!

1
  • 1
    This might help (though you may have to tweak).... 1) Join _Bounce 2) Remove the Journey Name from the Query (to fetch all Journeys) ..... help.salesforce.com/s/… Oct 25 '21 at 7:13
1

There is another really close to what @VishalKumarCV suggested in the comments - Query: Journey Builder Bounced Email Messages.

A little bit updated query from the article:

select
j.JourneyName,
j.VersionNumber,
ja.ActivityName as 'EmailName',
s.EventDate as 'SendTime',
b.EventDate as 'BounceTime',
su.EmailAddress,
su.SubscriberKey,
s.JobID,
s.ListID,
s.BatchID
from [_Sent] s
join [_JourneyActivity] ja on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] j on ja.VersionID = j.VersionID
join [_Bounce] b on s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID
join [_Subscribers] su on s.SubscriberID = su.SubscriberID
where ja.ActivityType in ('EMAIL','EMAILV2')
and s.EventDate < cast(cast(dateadd(hh,-72,getdate()) as date) as datetime)
and b.SubscriberID is not null

Notes:

  • You can include your MasterDE to reference custom fields (would be similar to "ContactProfileData" DE in the linked use-case above)
  • This query would capture all journeys
  • If the account is part of an Enterprise 2 account - add ENT. prefix before [_Subscribers] s
  • Because bounce data is not finalized for 72 hours after a send, searching for bounces in a send before 72 hours have passed is not recommended.
  • Review other prepared SQL use-cases on the right-side menu here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.