2

Just like the below SQL to see who bounced in Journey Send Emails, can expand this to include everyone? If they've bounced or not?

select 
s.JobID, 
s.ListID, 
s.BatchID, 
s.EventDate as 'SendTime', 
b.EventDate as 'BounceTime', 
su.EmailAddress,
j.JourneyName, 
j.VersionNumber, 
ja.ActivityName as 'EmailName' 
from [_Sent] s 
join [_JourneyActivity] ja 
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID 
join [_Journey] j 
on ja.VersionID = j.VersionID 
join [_Sent] 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 b.SubscriberID is not null
  • I've updated my answer since you updated your question, but I'm unsure what you current issue is, please add clarity to your question/issue – EazyE May 17 at 17:37
0

Have you tried joining the bounce data view on Job ID/Subscriber Id? https://help.salesforce.com/articleView?id=mc_as_data_view_bounce.htm&type=5

  • That worked. Much thanks. – JFH May 20 at 14:56
1

EDIT: I believe you are now trying to get bounce data, for all subscribers even if they didn't bounce. I would update your query with a left join.

SELECT
s.JobID, 
s.ListID, 
s.BatchID, 
s.EventDate AS 'SendTime', 
b.EventDate AS 'BounceTime', 
su.EmailAddress,
j.JourneyName, 
j.VersionNumber, 
ja.ActivityName AS 'EmailName' 
FROM [_Sent] s 
JOIN [_JourneyActivity] ja 
ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID 
JOIN [_Journey] j 
ON ja.VersionID = j.VersionID 
LEFT 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') 

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.