0

I want to get all external keys from email triggers in a journey for stopping/publishing/restarting.
So far, I can get their JourneyActivityObjectID (TriggeredSendDefinitionObjectID) through the SQL below.
How do I use these IDs to get the corresponding External IDs (CustomerKey)? Also, I wonder if these IDs have a corresponding property in TriggeredSendDefinition?

SELECT
a.VersionID as VersionID,
a.JourneyID as JourneyID,
a.JourneyName as JourneyName,
a.VersionNumber as VersionNumber,
a.JourneyStatus as JourneyStatus,
b.JourneyActivityObjectID as JourneyActivityObjectID,
b.ActivityName as ActivityName,
b.ActivityType as ActivityType
FROM
_Journey as a
INNER JOIN
_JourneyActivity as b
ON a.VersionID = b.VersionID
WHERE
a.JourneyName = 'Journey Name' /* my journey name */
AND
a.JourneyStatus = 'Running'
AND
b.ActivityType = 'EMAILV2'

Can get the external key in manual by going to Interactions - Triggered Emails enter image description here

Please let me know If I went for a wrong approach. Thanks.

3
  • 3
    It is the TriggeredSendCustomerKey on _sent data view. help.salesforce.com/s/… Oct 11 '21 at 15:01
  • @Mishra You are right. Thank you so much! I just joined _Sent as 3rd table on JourneyActivityObjectID = TriggererSendDefinitionObjectID and can get CustomerKey after that.
    – duyduc27
    Oct 11 '21 at 16:01
  • @Mishra I tested and I only could get CustomerKey if these email triggers were already sent, otherwise can't query the Customerkey. Eventually, I used _Job as 3rd table instead of _Sent, in addition to that data I used Distinct statement in SQL to deduplicate since every time we changed in the email activity (e.g. change commercial send to transactional send) the system will create a new job in _Job data.
    – duyduc27
    Oct 13 '21 at 7:39
0

Thanks to @Swati Mishra for your help. To whom it may interest, my final SQL to get the external keys from email triggers will look like this:

SELECT DISTINCT
a.VersionID as VersionID,
a.JourneyID as JourneyID,
a.JourneyName as JourneyName,
a.VersionNumber as VersionNumber,
a.JourneyStatus as JourneyStatus,
b.JourneyActivityObjectID as JourneyActivityObjectID,
b.ActivityName as ActivityName,
b.ActivityType as ActivityType,
c.TriggeredSendCustomerKey as TriggeredSendCustomerKey,
'Refresh' as Remark
FROM
_Journey as a
INNER JOIN
_JourneyActivity as b
ON a.VersionID = b.VersionID
INNER JOIN
_Job as c
ON b.JourneyActivityObjectID = c.TriggererSendDefinitionObjectID
WHERE
a.JourneyName = 'My Journey Name'
AND
a.JourneyStatus = 'Running'
AND
b.ActivityType = 'EMAILV2'

TriggeredSendCustomerKey is the external key.
Note: In this context, I wanted to get data to process refreshing all email contents in a journey.

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.