0

I noticed that the report from Analytics Builder does not have this field. Is there a way to know this with SQL? Only the report "Journey Builder Email Send Summary" shows me is the Soft, Hard and Block. I know that the Technical is a type of Soft Bounce, but i would like to know the separates records.

Other the field that i miss is the Total Open and Click rates (only has the unique rates).

3

Here’s an example of a very generic query, that will pull all the tracking data from Data Views into one table. All you need to do is create a Data Extension with the following fields:

SubscriberKey
EmailName
SentDate
OpenDate
OpenIsUnique
ClickDate
ClickIsUnique
BounceDate
BounceCategory
BounceSubcategory
BounceType
UnsubscribeDate
JourneyActivityObjectID

and run the following query, either in Query Studio or in Automation Studio:

SELECT 
s.SubscriberKey, 
j.EmailName, 
s.EventDate as SentDate, 
o.EventDate as OpenDate, 
o.IsUnique as OpenIsUnique,
c.EventDate as ClickDate, 
c.IsUnique as ClickIsUnique,
b.EventDate as BounceDate, 
b.BounceCategory, 
b.BounceSubcategory,
b.BounceType,
u.EventDate as UnsubscribeDate,
ja.JourneyActivityObjectID

FROM _Sent s LEFT JOIN _Job as j ON s.JobID = j.JobID 
LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
LEFT JOIN _Bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1
LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID and s.ListID = u.ListID and s.BatchID = u.BatchID and s.SubscriberID = u.SubscriberID and u.IsUnique = 1
LEFT JOIN _JourneyActivity ja ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID

You can filter further down using the JourneyActivityObjectID column - if it's not empty, this determines that the Send was a Journey Builder send.

You can read more about Data Views here.

| improve this answer | |
0

Regardless how the email was sent all bounces are logged in the _bounce dataview. You can join this to the _journeyactivity dataview to narrow it down to an journey\activity

| improve this answer | |

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.