SFMC SQL Query Examples for Marketers
Practical SFMC SQL query examples for marketers, from recent openers to bounce suppression lists, using the data views you already have in your account.
SFMC SQL Query Examples for Marketers
SQL has a reputation problem on marketing teams.
It looks like something only the data team should touch, so audience requests pile up in a queue and campaigns wait for someone else to run them.
The reality is friendlier than the reputation. A small set of query patterns covers most of what a marketer actually needs from Salesforce Marketing Cloud.
This post walks through where SQL runs inside SFMC, the data views you will reach for again and again, and worked examples you can adapt to your own account.
None of it requires a computer science background. It requires knowing where your data lives and being precise about what you want back.
What SQL Does Inside SFMC
Where the query runs
Marketers write SQL in SFMC through the SQL Query Activity in Automation Studio.
You write a statement that defines an audience, the query runs against your data, and the rows it returns are written into a target data extension.
That target data extension is what you then use as a sending audience, a suppression list, or the input to another step in an automation.
The important mental model is that the query does not send anything. It produces a list. Sending is a separate decision you make afterward.
What you are allowed to query
Your SQL can read from two kinds of sources: your own data extensions, and the system data views that Salesforce maintains for you.
Data extensions hold the data you imported or collected, such as CRM records, purchases, or preferences.
Data views hold subscriber records and the last six months of tracking data for your account, generated automatically as you send.
Most real audiences pull from both at once, and the join between them is where the useful logic lives.
The Data Views You Will Use Most
Subscriber and status data
The _Subscribers data view holds one row per subscriber along with their current status.
It includes columns such as SubscriberKey, EmailAddress, Status, DateJoined, DateUnsubscribed, and BounceCount.
When you need to know who is active, who unsubscribed, and who is held, this is the first place you look.
A common starting query is simply everyone whose status is active:
SELECT SubscriberKey, EmailAddress FROM _Subscribers WHERE Status = 'Active'
Engagement events
Engagement lives in event-level data views, where each row is a single event rather than a single person.
_Sentrecords each email sent._Openrecords each open._Clickrecords each link click._Bouncerecords each bounce, with a category and reason.
Every one of these carries a SubscriberKey and a JobID. The SubscriberKey ties the event to a person, and the JobID ties it to a specific send.
Because the tables are event-level, a single subscriber can appear many times. That detail shapes almost every query you write against them.
Query Examples for Common Audiences
The examples below are deliberately small.
Each is a complete query you could paste into a query activity and adapt by swapping a column or shifting a date window.
Recent openers
Say you want everyone who opened an email in the last 30 days.
The _Open data view has an EventDate column, so you filter on it and remove duplicates:
SELECT DISTINCT SubscriberKey FROM _Open WHERE EventDate > DATEADD(day, -30, GETDATE())
The DISTINCT keyword keeps one row per subscriber even if they opened several times.
Without it, a subscriber who opened five times would land in your audience five times, which inflates counts and can cause duplicate sends downstream.
The same shape works for clicks. Swap _Click in for _Open and you have everyone who clicked a link in the window, which is a stronger engagement signal than an open alone.
A suppression list from bounces
Now say you want to exclude anyone who hard bounced.
The _Bounce data view carries SubscriberKey, BounceCategory, and SMTPBounceReason, so you filter on the category:
SELECT DISTINCT SubscriberKey FROM _Bounce WHERE BounceCategory = 'Hard bounce'
You write this into a suppression data extension and exclude it at send time.
The payoff is cleaner deliverability without anyone scrubbing lists by hand every week.
Joining Data Across Views
Matching events to people
The most useful audiences come from combining views, not querying one in isolation.
Because SubscriberKey appears in every data view, it is the column you join on.
To find subscribers who were sent an email but never opened it, join _Sent to _Open and keep only the rows with no matching open:
SELECT DISTINCT s.SubscriberKey FROM _Sent s LEFT JOIN _Open o ON s.SubscriberKey = o.SubscriberKey WHERE o.SubscriberKey IS NULL
That LEFT JOIN with an IS NULL filter is the standard pattern for "did something, did not do this other thing."
Adding your own profile data
You can join a data view to your own data extension in exactly the same way.
Join _Open to a profile data extension on SubscriberKey to layer engagement on top of attributes like region, plan tier, or lifecycle stage.
This is where SQL earns its place. One statement expresses a condition that would take many manual filter steps to assemble, and it runs the same way every time.
Limits Worth Knowing Before You Rely On Them
The six-month window
System data views retain roughly six months of tracking data.
If you query _Open or _Click for engagement older than that, the rows are simply not there to find.
For a longer history, teams copy tracking data into a permanent data extension on a schedule.
Plan for that before you promise anyone a two-year engagement lookback, because the raw data view will not deliver it.
Where the results land
A SQL Query Activity writes its rows into a target data extension, and you choose whether each run overwrites or appends.
Overwrite keeps the audience current. Append keeps history but can pile up duplicates if you are not careful.
Getting this update type wrong is a frequent cause of stale or doubled audiences, so confirm the setting every time you build a new activity.
Turning Queries Into Repeatable Work
Name and document everything
A query you wrote three months ago is a stranger unless you named it clearly.
Give each query activity a descriptive name and keep a short note on which audience it builds and why.
Future you, and anyone who inherits the account, will spend far less time reverse engineering logic that was labeled well in the first place.
Test on a small extension first
Before pointing a query at a production audience, run it into a scratch data extension and read the row count.
If the count looks wrong, the logic is wrong, and it is much cheaper to catch that here than after a campaign has gone out.
A quick sanity check on counts is the single habit that prevents the most embarrassing sending mistakes.
See QAiry in action
These patterns are learnable, but writing, joining, and debugging SQL still takes time that most marketing calendars do not have to spare.
QAiry lets you describe the audience in plain language and generates the SFMC-ready SQL for you, so you move from idea to sending list without waiting in a query queue. See it work at qairy.com/product-demos, or try it on your own data at qairy.com/try-it-free.
