We have pulled out email history from _Open Data View.

However, for most records - TriggererSendDefinitionObjectID is empty.

To understand which email was opened, we had no choice - but match on JobIDs from the _Job DataView. So we started by querying _Job into a DataExtension, for intermediate results.

select JobID, EmailID, EmailName, EmailSubject, DeliveredTime
from _Job

When we manually search a specific JobID which exists in _Open, many are missing from _Job.

What does this mean? What are we missing? Also, is the fact that most TriggererSendDefinitionObjectID are missing is because List Sends / Manual sends don't get this populated?

Thanks in advance for your help!


_Job data view is the only BU specific data view - and has no reference to subscribers. Hence you will only see the jobs which were performed from the BU in which you query it. Whereas for any other view - _Click, _Sent, _Bounce, etc., these are subscriber level activities, and hence the data views are global (you need to prepend them with ENT. when querying from a child BU).

You can filter records in these data views by querying WHERE "OYBAccountID" = 'MID' (MID being the BU you need the subset of data for)

| improve this answer | |
  • Thanks for your thoughts. Good to know that. Thing is, there are no multiple BUs in this Marketing Cloud Account - AND I do see other jobs for some of the sends. In addition, in the full _Open data export the OYBAccountID is empty for all records... Naveen has explained why jobs in _Job could be missing in _Open, I just can't see how I see jobs in _Open missing from _Job; I manually tested existence of jobs in the middle of the date range (3 months back) for jobs on the same date - some of the jobs show in _Job and others don't! – LimeWeb Jun 26 at 18:42

Yes, you are right. Regarding the TriggeredsendobjectId the value will get populated only if the emails are sent via Journey / Triggered send. If the email is just a manual send / user initiated send then it won't get populated.

Regarding on Job Id are not matching with _Open and _job data view, there could be a chance where an email has sent to a small group of audience and if they haven't open the email then those Job Id will not be coming under the _Open data view.

Let me know if this helps.

| improve this answer | |
  • oddly, it's the other way around - those in _Open do not appear in _Job ! – LimeWeb Jun 26 at 18:20

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.