0

When I select from _Sent and LEFT JOIN _Job it returns null values for fields selected from _Job.

I'm querying from the Parent BU so this shouldn't be the BU specific issue.

SELECT 
s.SubscriberKey,
j.EmailName,
j.EmailSubject,
jy.JourneyName,
jy.VersionNumber,
s.EventDate AS event_date,
'Sent' as event_type
FROM _Sent s
LEFT JOIN _Job j ON s.JobID = j.JobID
LEFT JOIN _JourneyActivity ja ON j.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN _Journey jy ON ja.VersionID = jy.VersionID
WHERE
s.EventDate > dateadd(hh,-24,getdate()) and jy.JourneyID = 'xxxxxxxxxxxxxx'

But when I flip the select from _Sent to _Job, and LEFT JOIN _Sent, it doesn't return null values.

SELECT 
s.SubscriberKey,
j.EmailName,
j.EmailSubject,
jy.JourneyName,
jy.VersionNumber,
s.EventDate AS event_date,
'Sent' as event_type
FROM _Job j
LEFT JOIN _Sent s ON s.JobID = j.JobID
LEFT JOIN _JourneyActivity ja ON j.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN _Journey jy ON ja.VersionID = jy.VersionID
WHERE
s.EventDate > dateadd(hh,-24,getdate()) and jy.JourneyID = 'xxxxxxxxxxxxxx'

What am I doing wrong?

Should I always be selecting from _Job first then joining the other engagement data views. E.g Click, Open etc.

Thanks.

2 Answers 2

2

"I'm querying from the Parent BU so this shouldn't be the BU specific issue."

It very likely is.

While most other data views tell you the BU context in OYBAccountId and can be queried from Parent BU, _Job data view does not.

The _Job data view contains only jobs from the BU you are running the query in.

So: Querying _Job in Parent returns only jobs that were performed in parent.

In contrast: Querying _Sent from Parent returns all sends and uses OYBAccountId to tell you where they occurred.

So in order to get jobs from a child BU, you could retrieve the data in the child BU(s) _jobs Data View(s) with an SQL in each Child BU, store the data in one shared DE each, and pick it up in a followup query in Parent. That query can JOIN the shared DE with your other data already gathered in Parent.

1
  • Thanks Jonas. To clarify - the journey data I'm querying is in the Parent BU, so I dont need any data from Child BUs hence why I was confident it's not the BU specific issue.
    – MJB
    Feb 24, 2022 at 13:23
2

JobIDs for any Triggered Sends and Journey Builder emails don't get updated until a publish event occurs. The _Job data will drop-off after 6 months if a trigger or Journey Builder email doesn't get published.

For that reason, when selecting by date you should always should start with _Sent data and then join to _Job.

Or, what I like to do is maintain a canonical record of jobs using the Send and TriggeredSendSummary SOAP object data.

I've outlined that process on here on my blog:

If you've got a historical record of job data (JobID = ID) that also illustrates the publish events, you can start at Send or TriggeredSendSummary and join to _Sent.

An additional benefit of maintaining this data: you get a slew of aggregated activity data that you can use for many other reporting needs.

2
  • Thanks Adam. "For that reason, when selecting by date you should always should start with _Sent data and then join to _Job." Does this also apply to other Email data views such as Click, Open etc?
    – MJB
    Feb 24, 2022 at 14:06
  • It depends on what date is most important -- the "send" date or the date the activity occurred. If the send date is most important, then start with a source that doesn't drop data after 6 months. Feb 24, 2022 at 14:10

You must log in to answer this question.

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