Why does selecting from _Sent and LEFT JOIN _Job return null values from _Job

Question

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.

Answer 1

"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.

Answer 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:

• Retrieving and Storing Aggregated Send Data
• Retrieving and Storing Aggregated Triggered Send Data

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.