SFMC Data Views
with Generative AI

Operational truth

Use system-level data — jobs, subscribers, tracking events — as a reliable source for measurement across your SFMC environment.

Enterprise reporting

Standardise KPIs across brands, BUs, and campaigns with consistent definitions from system-level Data Views.

Faster decisions

Identify issues — bounces, complaints, low engagement — early and act with confidence using the right data immediately.

_Subscribers

Subscriber identity and status — active, held, unsubscribed — plus operational counters.

_Job

Send job metadata — subject, send times, send classification. Useful for reporting rollups.

_Sent

Send events — the baseline for performance calculations and engagement joins across all Data Views.

_Open

Open events — unique and total. Use unique opens for KPI consistency across campaigns and teams.

_Click

Click events and link-level performance. Great for content and CTA optimisation across sends.

_Bounce / _Complaint / _Unsubscribe

Deliverability and list health signals — critical for sender reputation management.

Example prompt

Best practice: use _Sent as the baseline and compute uniques from IsUnique = 1.

Generated SQL

SELECT
  s.JobID,
  MIN(s.EventDate)   AS FirstSendDate,
  COUNT(1)           AS TotalSends,

  COUNT(DISTINCT CASE WHEN o.IsUnique = 1 THEN o.SubscriberKey END) AS UniqueOpens,
  COUNT(DISTINCT CASE WHEN c.IsUnique = 1 THEN c.SubscriberKey END) AS UniqueClicks,
  COUNT(DISTINCT CASE WHEN b.IsUnique = 1 THEN b.SubscriberKey END) AS UniqueBounces,

  CAST(COUNT(DISTINCT CASE WHEN o.IsUnique = 1 THEN o.SubscriberKey END) AS FLOAT)
    / NULLIF(COUNT(1), 0) AS OpenRate,

  CAST(COUNT(DISTINCT CASE WHEN c.IsUnique = 1 THEN c.SubscriberKey END) AS FLOAT)
    / NULLIF(COUNT(1), 0) AS ClickThroughRate,

  CAST(COUNT(DISTINCT CASE WHEN b.IsUnique = 1 THEN b.SubscriberKey END) AS FLOAT)
    / NULLIF(COUNT(1), 0) AS BounceRate

FROM _Sent s
LEFT JOIN _Open   o ON s.JobID = o.JobID AND s.SubscriberKey = o.SubscriberKey
LEFT JOIN _Click  c ON s.JobID = c.JobID AND s.SubscriberKey = c.SubscriberKey
LEFT JOIN _Bounce b ON s.JobID = b.JobID AND s.SubscriberKey = b.SubscriberKey
WHERE s.EventDate >= DATEADD(day, -7, GETDATE())
GROUP BY s.JobID;