Unlock Salesforce Marketing Cloud Data Views with Generative AI

Overview

What are SFMC Data Views?

Data Views are system tables that expose operational and tracking data—subscriber status, sends, opens, clicks, bounces, unsubscribes, complaints, and more. They’re essential for performance analytics, deliverability monitoring, and journey optimization, but they typically require SQL to access.

Operational truth

Use system-level data (jobs, subscribers, tracking events) as a reliable source for measurement.

Enterprise reporting

Standardize KPIs across brands, BUs, and campaigns with consistent definitions.

Faster decisions

Identify issues (bounces, complaints, low engagement) early and act with confidence.

How QAiry works

From question to query—without SQL friction

QAiry translates natural language into SFMC SQL, selecting the right Data Views, join keys, and filters so the output is actionable in Automation Studio.

1) Ask in plain language

Describe the insight you need—campaign health, engagement, deliverability, or journey performance.

Which domains had the highest hard bounce rate in the last 7 days? Break down by domain and total bounces.

2) QAiry generates best-practice SQL

QAiry outputs SQL aligned with SFMC Data View patterns (e.g., JobID / SubscriberKey joins, unique open/click logic, date filtering).

Give me a 7-day summary by JobID: sends, unique opens, unique clicks, bounces, unsubscribes.

3) Use the result immediately

Run the query in Automation Studio for quick insight, operational dashboards, or scheduled reporting workflows.

Ad-hoc analysis KPI monitoring Deliverability checks Journey optimization

Core data views

Common Data Views and example prompts

Below are frequently used Data Views and practical QAiry prompts you can run to answer real operational questions.

_Subscribers

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

List active subscribers with email address and status.

_Job

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

Show the top 10 sends by total volume last month (with email subject).

_Sent

Send events, used as the baseline for performance calculations and engagement joins.

How many emails were sent to Gmail vs Outlook in the last 30 days?

_Open

Open events (unique and total). Use unique opens for KPI consistency.

Which email had the highest unique open rate yesterday?

_Click

Click events and link-level performance. Great for content and CTA optimization.

Top 5 most-clicked links in the last 7 days by total unique clicks.

_Bounce / _Complaint / _Unsubscribe

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

Which campaigns had unsubscribe rate above 0.3% last month?

Sample insights generated via QAiry prompts in Marketing Cloud Engagement

Advanced use cases

High-impact analytics teams run weekly

Standardize deliverability and performance monitoring across teams with repeatable, trusted questions.

Engaged segments

Identify subscribers who opened or clicked recently for retargeting or priority messaging.

Find subscribers who clicked at least once in the last 14 days.

Re-engagement audiences

Build cohorts with no opens/clicks over a defined period to run win-back strategies.

Subscribers with no opens in the last 90 days (exclude recent new joiners).

Deliverability monitoring

Spot domains with elevated hard bounces or complaint rates and act quickly.

Domains with hard bounce rate above 1% in the last 7 days.

Reporting

Join multiple Data Views automatically

QAiry builds multi-view reporting in one step — combining sends, unique opens, unique clicks, and bounces by JobID. This is the foundation for consistent campaign health monitoring and weekly KPI rollups.

Example prompt

Generate a 7-day summary by JobID: Total Sends, Unique Opens, Unique Clicks, Unique Bounces, plus Open Rate, CTR, Bounce Rate.

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

SQL example (collapsible)

View example 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;

Note: Tracking Data Views have retention limits; plan rollups accordingly.

Output Data Extension (example)

Example of what the target Data Extension looks like after the Query Activity runs (sample rows shown).

Target DE Primary Key: JobID

JobID	FirstSendDate	TotalSends	UniqueOpens	UniqueClicks	UniqueBounces	OpenRate	CTR	BounceRate
821045	2026-02-03 09:12	125,400	49,870	10,942	1,506	39.77%	8.72%	1.20%
821046	2026-02-04 08:55	98,120	34,640	7,105	2,060	35.30%	7.24%	2.10%
821102	2026-02-06 10:05	210,880	92,340	17,990	1,265	43.79%	8.53%	0.60%
821140	2026-02-08 07:40	75,600	21,140	3,210	1,965	27.96%	4.25%	2.60%