Tracking Email Engagement with Data Views: Scope and Limitations

Data views are the closest thing Marketing Cloud has to a tracking database you can query. They are also the easiest place to build a number that is confidently wrong.

Retention is the first constraint

The tracking views hold a rolling window, not your full history. If your year-on-year comparison quietly depends on data older than that window, the comparison is not what you think it is. The fix is boring and reliable: extract into your own data extensions on a schedule, and report from those.

What they contain, roughly

• Sent, Open, Click, Bounce, Unsubscribe — the event views, one row per event.
• Job — one row per send job, and the anchor for almost every useful join.
• Subscribers and ListSubscribers — status, not engagement.
• Journey and JourneyActivity — journey-level context that the event views do not carry on their own.

Where reporting drifts

Opens depend on image loading, and privacy features inflate and flatten them at the same time. Clicks are more honest but still count bots. Unique versus total is the single most common reconciliation argument, and it is almost always a missing DISTINCT.

SELECT
  j.EmailName,
  COUNT(DISTINCT s.SubscriberKey) AS Sent,
  COUNT(DISTINCT c.SubscriberKey) AS UniqueClickers
FROM _Sent s
JOIN _Job j ON j.JobID = s.JobID
LEFT JOIN _Click c ON c.JobID = s.JobID AND c.SubscriberKey = s.SubscriberKey
WHERE s.EventDate > DATEADD(day, -30, GETDATE())
GROUP BY j.EmailName

A working habit

Write down, once, which number your business calls "the open rate" and which view produces it. Half the reporting disputes I have sat through were two correct queries answering two different questions.