Join the SFMC _Sent and _Job Data Views to Report on Email Sends
Use a join when a report needs send-event rows from `_Sent` alongside email-job fields from `_Job` in Salesforce Marketing Cloud Engagement. First lookup and confirm all the available columns in the `_Sent` data view reference and the `_Job` data view reference before building the query.
Join Send Events to Job Metadata
The following template uses `JobID` as the join field. Verify that `JobID` is available in both data views in your account before using it.
Replace `123456` in the examples with the job ID you want to report on.
SELECT
s.EventDate,
s.SubscriberKey,
s.SubscriberID,
s.JobID,
j.EmailName,
j.EmailSubject,
j.JobStatus,
j.SchedTime,
j.DeliveredTime
FROM _Sent AS s
INNER JOIN _Job AS j
ON j.JobID = s.JobID
WHERE s.JobID = 123456This result is structured at the `_Sent` row level. Job fields selected from `_Job` appear alongside each matching send-event row.
Return Send Events Without Requiring a Job Match
Use a `LEFT JOIN` when the report should retain `_Sent` rows even when the query does not return matching `_Job` metadata.
SELECT
s.EventDate,
s.SubscriberKey,
s.JobID,
j.EmailName,
j.JobStatus
FROM _Sent AS s
LEFT JOIN _Job AS j
ON j.JobID = s.JobID
WHERE s.JobID = 123456An `INNER JOIN` excludes rows without a match. A `LEFT JOIN` retains the rows from `_Sent` and returns `NULL` for selected `_Job` fields when no match is returned.
Count the Rows Returned by the Join
`COUNT(*)` counts result rows. Use it when the intended metric is the number of `_Sent` rows returned by the query.
SELECT
s.JobID,
j.EmailName,
COUNT(*) AS SentEventCount
FROM _Sent AS s
LEFT JOIN _Job AS j
ON j.JobID = s.JobID
GROUP BY
s.JobID,
j.EmailNameFor a count of distinct job identifiers in `_Sent`, use `COUNT(DISTINCT s.JobID)`:
SELECT
COUNT(DISTINCT s.JobID) AS JobCount
FROM _Sent AS sValidate every selected field against the two data-view references before scheduling the query. Add only fields documented for the view from which they are selected.
Share With Others
Platinum
