0

I have a go to query retrieve details about my triggered email sends for analytical purposes. It has always worked.

However, for one triggered send in particular the query is returning 0 rows; despite the external key existing when I navigate to Interactions > Triggered Emails.

Any thoughts would be much appreciated.

SELECT
  s.SubscriberKey
, j.JOBID
, j.EmailID as EMAIL_ID
, ls.EmailAddress as EMAIL_ADDRESS
, j.EmailName as EMAIL_NAME
, j.EmailSubject as SUBJECT
, DATEADD(hh, 6, s.EventDate) AS SEND_DATE
, DATEADD(hh, 6, o.EventDate) AS FIRST_OPEN
, op.Opens AS TIMES_OPENED_CNT
, DATEADD(hh, 6, c.EventDate) AS FIRST_CLICK
, cl.Clicks AS TOTAL_CLICKS_CNT
, ISNULL(B.IsUnique, 0) as [Bounce Indicator]
, b.BounceCategory AS BOUNCE_CATEGORY
, ISNULL(U.IsUnique,0) as [Blocked Domain Indicator]
, DATEADD(hh, 6, u.EventDate) as [Blocked Domain Date]
FROM _Sent s
LEFT JOIN _Job 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 (
  select
    jobid
  , ListID
  , BatchID
  , subscriberid
  , count(subscriberid) AS Clicks
  from _click
  group by 
    subscriberid
  , jobid
  , ListID
  , BatchID
) cl ON s.JobID = cl.JobID AND s.ListID = cl.ListID AND s.BatchID = cl.BatchID AND s.SubscriberID = cl.SubscriberID 
LEFT JOIN (
  select distinct
    jobid
  , ListID
  , BatchID
  , subscriberid
  , count(distinct url) AS URLs
  from _click
  group by 
    subscriberid
  , jobid
  , ListID
  , BatchID
) url ON s.JobID = url.JobID AND s.ListID = url.ListID AND s.BatchID = url.BatchID AND s.SubscriberID = url.SubscriberID
LEFT JOIN (
  select
    jobid
  , ListID
  , BatchID
  , subscriberid
  , count(subscriberid) AS Opens
  from _open
  group by subscriberid, jobid, ListID, BatchID
) op ON s.JobID = op.JobID AND s.ListID = op.ListID AND s.BatchID = op.BatchID AND s.SubscriberID = op.SubscriberID
LEFT JOIN ent._ListSubscribers ls ON s.SubscriberID = ls.SubscriberID
inner join [MY_JOURNEY_NAME] ju on s.SubscriberKey = ju.Email
where 
s.TriggeredSendCustomerKey in ('71156', '71157', '71493', '71494', '71952', '71953')
1
  • Please update your question to include details about the primary key of the target data extension. Mar 20, 2023 at 17:17

2 Answers 2

1

You could use the Journey data views to select any sends for your Journey:

SELECT
  s.SubscriberKey
, j.JOBID
, j.EmailID as EMAIL_ID
, ls.EmailAddress as EMAIL_ADDRESS
, j.EmailName as EMAIL_NAME
, j.EmailSubject as SUBJECT
, DATEADD(hh, 6, s.EventDate) AS SEND_DATE
, DATEADD(hh, 6, o.EventDate) AS FIRST_OPEN
, op.Opens AS TIMES_OPENED_CNT
, DATEADD(hh, 6, c.EventDate) AS FIRST_CLICK
, cl.Clicks AS TOTAL_CLICKS_CNT
, ISNULL(B.IsUnique, 0) as [Bounce Indicator]
, b.BounceCategory AS BOUNCE_CATEGORY
, ISNULL(U.IsUnique,0) as [Blocked Domain Indicator]
, DATEADD(hh, 6, u.EventDate) as [Blocked Domain Date]
FROM _Sent s
LEFT JOIN _Job 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 (
  select
    jobid
  , ListID
  , BatchID
  , subscriberid
  , count(subscriberid) AS Clicks
  from _click
  group by 
    subscriberid
  , jobid
  , ListID
  , BatchID
) cl ON s.JobID = cl.JobID AND s.ListID = cl.ListID AND s.BatchID = cl.BatchID AND s.SubscriberID = cl.SubscriberID 

LEFT JOIN (
  select distinct
    jobid
  , ListID
  , BatchID
  , subscriberid
  , count(distinct url) AS URLs
  from _click
  group by 
    subscriberid
  , jobid
  , ListID
  , BatchID
) url ON s.JobID = url.JobID AND s.ListID = url.ListID AND s.BatchID = url.BatchID AND s.SubscriberID = url.SubscriberID
LEFT JOIN (
  select
    jobid
  , ListID
  , BatchID
  , subscriberid
  , count(subscriberid) AS Opens
  from _open
  group by subscriberid, jobid, ListID, BatchID
) op ON s.JobID = op.JobID AND s.ListID = op.ListID AND s.BatchID = op.BatchID AND s.SubscriberID = op.SubscriberID
LEFT JOIN ent._ListSubscribers ls ON s.SubscriberID = ls.SubscriberID
inner join (

  select
    convert(varchar(36), ja0.VersionID) VersionID /* UniqueIdentifier datatype doesnt play nicely with JO-INs */
  , convert(varchar(36), ja0.ActivityID) ActivityID
  , ja0.ActivityName
  , ja0.ActivityExternalKey
  , ja0.JourneyActivityObjectID
  , ja0.ActivityType
  from _JourneyActivity ja0

) ja on ja.ActivityId = s.TriggererSendDefinitionObjectID
inner join (

  select
    convert(varchar(36), j0.VersionID) VersionID
  , convert(varchar(36), j0.JourneyID) JourneyID
  , j0.JourneyName
  , j0.VersionNumber
  , j0.CreatedDate
  , j0.LastPublishedDate
  , j0.ModifiedDate
  , j0.JourneyStatus
  from _Journey j0

) jr on jr.versionid = ja.versionid
inner join [MY_JOURNEY_NAME] ju on s.SubscriberKey = ju.Email
where 
jr.journeyName = 'MY_JOURNEY_NAME'
2
  • Thanks, I'm seeing this error in query studio '.The correlation name 'j' is specified multiple times in a FROM clause'. However _job view is only being joined once Mar 21, 2023 at 11:57
  • Fixed an alias issue in my query. Query Studio will error on certain things in queries that Query Activities won't, so keep that in mind. Mar 21, 2023 at 13:02
0

The sends that are shown under Interactions>Triggered Emails are Triggered sends and not journey builder sends. So if you are trying to find the key for your journey builder sends, then the right external key would be under Interactions>Journey Builder>Your journey name folder.

2
  • You are correct, I actually meant Triggered Emails, not Journey. I have edited my question. Mar 20, 2023 at 15:19
  • Can you paste in the structure of your MY_JOURNEY_NAME DE Mar 20, 2023 at 15:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .