Data Views and Data Extensions in SFMC: The SQL Queries I Use on Every Project

If AMPscript is how you personalize a message, SQL over Data Views and Data Extensions is how you decide who gets it. Automation Studio’s SQL Query activity is one of the most underused superpowers in Salesforce Marketing Cloud (SFMC), and most of the value comes from knowing what the system Data Views expose.

Here’s the reference I keep coming back to.

Data Extensions vs Data Views

• Data Extensions (DEs) are your own tables — you create them, you populate them, you query them.
• Data Views are system tables that SFMC maintains automatically. They hold engagement and subscriber data you can’t see in the UI as tables, but can query with SQL. They’re read-only and, importantly, they hold roughly the last 6 months of tracking data.

You query both the same way, in a SQL Query activity that writes its result into a target Data Extension.

The Data Views worth memorizing

Query patterns I actually use

1. Everyone who opened in the last 30 days

SELECT DISTINCT s.SubscriberKey
FROM _Open o
JOIN _Sent s ON o.JobID = s.JobID AND o.SubscriberKey = s.SubscriberKey
WHERE o.EventDate > DATEADD(DAY, -30, GETDATE())

2. Non-openers (a re-engagement segment)

Sent something, but no open in the window:

SELECT DISTINCT s.SubscriberKey
FROM _Sent s
WHERE s.EventDate > DATEADD(DAY, -90, GETDATE())
AND s.SubscriberKey NOT IN (
    SELECT o.SubscriberKey FROM _Open o
    WHERE o.EventDate > DATEADD(DAY, -90, GETDATE())
)

3. Clickers of a specific link

SELECT DISTINCT c.SubscriberKey, c.URL
FROM _Click c
WHERE c.URL LIKE '%/black-friday%'
AND c.EventDate > DATEADD(DAY, -14, GETDATE())

4. Hard bounces to suppress

SELECT DISTINCT b.SubscriberKey
FROM _Bounce b
WHERE b.BounceCategory = 'Hard bounce'

5. An engagement score per subscriber

Roll opens and clicks into a simple recency/frequency signal you can act on:

SELECT s.SubscriberKey,
       COUNT(DISTINCT o.JobID) AS Opens,
       COUNT(DISTINCT c.JobID) AS Clicks,
       MAX(o.EventDate) AS LastOpen
FROM _Sent s
LEFT JOIN _Open o ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN _Click c ON s.SubscriberKey = c.SubscriberKey
WHERE s.EventDate > DATEADD(DAY, -180, GETDATE())
GROUP BY s.SubscriberKey

Things that trip people up

• SFMC SQL is a subset of T-SQL. No INSERT/UPDATE/DELETE — a Query activity only ever produces a SELECT that overwrites (or updates/appends to) a target DE. No stored procedures, no temp tables.
• The 6-month window is real. If you need history beyond that, snapshot the Data Views into your own DEs on a schedule. This is the single most common thing teams forget until they need year-over-year data and it’s gone.
• Opens are unreliable post-MPP. Apple’s Mail Privacy Protection inflates opens. Lean on clicks and conversions for real engagement.
• Mind the JOIN keys. Join on SubscriberKey (and JobID when you care about a specific send), not on email address.
• Set the target DE’s primary key to match your GROUP BY, or “Update” runs won’t dedupe the way you expect.

Why this matters

Most SFMC “we can’t segment on that” problems are actually “nobody wrote the query” problems. Once you’re comfortable with Data Views, you can build re-engagement journeys, suppression lists, engagement scoring, and reporting DEs without exporting a single row to another tool. It’s the difference between operating the platform and actually controlling it.