TROUBLESHOOTING JOURNEY SENDS
First step towards troubleshooting in SFMC is to install Query Studio from AppExchange.
Once you have access to Query studio you can write SQL queries and troubleshoot the issues quickly.
If you are building a complex queries for a use case, you can write and execute the queries to see if it returns the desired output. Once, you are satisfied with your query you can save and add it to query activity.
You can also save the output into a data extension if required, by default query studio stores the output into a data extension under the folder QueryStudioResults. If you have to execute another SQL with the output of the previous result, you can reference the Data Extensions and apply it in your FROM statement.
FAQ :
1. How to find out who all did not receive email during journey send ?
Best practice is to enable send logging for your account. This will help a-lot during troubleshooting.
Keep data view link handy : https://help.salesforce.com/s/articleView?id=sf.mc_as_data_views.htm&type=5
Step1 :
If you are aware of the journey Name leverage _JourneyActivity and _Journey data view to find out the email activity names
SQL:
Select
ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey,
_JourneyActivity.VersionID
,JourneyName
from _JourneyActivity inner join _Journey on
_JourneyActivity.VersionID=_Journey.VersionID
where JourneyName='Journey 1'
Step 2:
If you are unaware of the journey Name leverage _JourneyActivity , _Journey and _Sent data view to find out the email activity names
Select
ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey
, _JourneyActivity.VersionID
, JourneyName
from _JourneyActivity
inner join
_sent on
_JourneyActivity.JourneyActivityObjectID=_sent.TriggererSendDefinitionObjectID
inner join
_Journey on
_JourneyActivity.VersionID=_Journey.VersionID
where DATEDIFF(D,_sent.eventdate,GETDATE())=0
Step 3:
Find out how many attempted send for the email activity using _sent data view
SELECT
JOBID
, EVENTDATE
, SUBSCRIBERKEY
FROM _SENT
INNER JOIN
(
Select
ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey,
_JourneyActivity.VersionID
,JourneyName
from _JourneyActivity inner join _Journey on
_JourneyActivity.VersionID=_Journey.VersionID
where JourneyName='Journey 1'
and ActivityName='Activity 1'
) JOURNEY
ON JourneyActivityObjectID =TriggererSendDefinitionObjectID
Step 4 :
From the attempted sends, how many were bounced [Bounced are included in the attempted Sends]
SELECT
JOBID
, EVENTDATE
, SUBSCRIBERKEY
, BounceCategory
FROM _bounce
INNER JOIN
(
Select
ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey,
_JourneyActivity.VersionID
,JourneyName
from _JourneyActivity inner join _Journey on
_JourneyActivity.VersionID=_Journey.VersionID
where JourneyName='Journey 1'
and ActivityName='Activity 1'
) JOURNEY
ON JourneyActivityObjectID =TriggererSendDefinitionObjectID
Step 5 :
Find out Not Sent from Target Data Extension
Select
SubscriberKey
,EmailAddress
from TargetDataExtension
where SubscriberKey not in
(SELECT
SUBSCRIBERKEY
FROM _SENT
INNER JOIN
(
Select
ActivityName
, ActivityID
, JourneyActivityObjectID
, ActivityType
, ActivityExternalKey,
_JourneyActivity.VersionID
,JourneyName
from _JourneyActivity inner join _Journey on
_JourneyActivity.VersionID=_Journey.VersionID
where JourneyName='Journey 1'
and ActivityName='Activity 1'
) JOURNEY
ON JourneyActivityObjectID =TriggererSendDefinitionObjectID )
Step 6 :
Find out Not Sent from Target Data Extension [Excluding Auto Suppression , Suppression and list detective ] , Send ID in the filter is the Job ID
<script runat="server">
Platform.Load("core","1");
try{
var cols = ["BatchID","EventDate","SendID","SubscriberKey",
"EventType","TriggeredSendDefinitionObjectID",
"EventType"];
var prox = new Script.Util.WSProxy();
var filterNull = {
Property: "SendID",
SimpleOperator: "Equals",
Value: '1234'
};
var filterNotNull = {
Property: "TriggeredSendDefinitionObjectID",
SimpleOperator: "isNotNull",
Value: ''
};
var res = prox.retrieve("NotSentEvent", cols,filterNull);
//Write(Stringify(res.Results));
Write(res.Results.length);
}
catch(ex)
{
Write(ex.message);
}
</script>
Step 7:
To find out hard errors , click on Activity Summary > View Contact Details > View Summary > Click Details > Hard Error
Full use case for troubleshooting
great
ReplyDeleteAwesome
ReplyDelete