How to Join _Sent to _Open in SFMC SQL

Why this join matters

_Sent records every email deployment to a subscriber. _Open records pixel-tracked opens. Analysts join them constantly to calculate open rates, time-to-open, and engagement after send.

Both views share the same engagement event grain — four columns that must match together.

The engagement quadrinity

Never join on JobID alone. SFMC allows multiple batches and list contexts within a job; omitting ListID or BatchID can duplicate or drop rows.

• JobID — the send job
• ListID — the list or publication list used
• BatchID — the deployment batch
• SubscriberID — the recipient subscriber record

Example query

SELECT TOP 1000
  s.SubscriberKey,
  s.EventDate AS SentDate,
  o.EventDate AS OpenDate,
  o.IsUnique
FROM _Sent s
INNER JOIN _Open o
  ON s.JobID = o.JobID
  AND s.ListID = o.ListID
  AND s.BatchID = o.BatchID
  AND s.SubscriberID = o.SubscriberID
WHERE s.EventDate >= DATEADD(day, -30, GETDATE())
  AND o.EventDate >= DATEADD(day, -30, GETDATE())
ORDER BY s.EventDate DESC

Practitioner tips

• Filter EventDate on both tables early — _Open has aggressive retention and large scans time out.
• Use IsUnique = 1 on _Open when you need first-open metrics per job.
• Parent BU queries see child sends; child BU scope may require Ent._Subscribers for profile attributes.