How to Query Data Views in Salesforce Marketing Cloud

If you have worked inside Salesforce Marketing Cloud for any length of time, you have probably heard someone on your team mention Data Views in passing. They are easy to overlook because they do not show up in your account as something you can click on, configure, or browse. They sit quietly underneath the platform, exposed only when you write SQL inside Automation Studio.

That is unfortunate, because Data Views are where the most useful engagement data in SFMC actually lives. Opens, clicks, sends, bounces, unsubscribes, job metadata — all of it is stored in system tables your queries can join against. Once you understand how to use them, you can answer questions that are otherwise impossible to answer with Data Extensions alone.

This guide walks through the Data Views every SFMC team should know, where you run queries against them, the SQL patterns that come up over and over in real campaigns, and the platform changes from 2025 that are worth paying attention to in 2026.

What Data Views actually are

Data Views are read-only system tables Salesforce Marketing Cloud exposes inside its SQL engine. You do not create them, you do not manage their schema, and you cannot insert rows into them. They are populated automatically as your sends, opens, clicks, bounces, and other tracking events happen.

You query them the same way you query a Data Extension, except their names start with an underscore — _Sent, _Open, _Click, _Bounce, _Subscribers, and so on. From the SQL engine's perspective, they look like ordinary tables. The difference is that Salesforce manages the data, the retention window, and the schema for you.

The other thing worth knowing up front is that Data Views are session-level. You cannot run a SQL Query Activity outside of a business unit and expect cross-account results. The view returns rows scoped to the account where the query runs.

The Data Views every SFMC team should know

There are dozens of Data Views in Marketing Cloud, but a small number cover the majority of real campaign reporting and audience work. These are the ones we reach for constantly.

• _Subscribers — every subscriber known to your account, with status, email address, locale, and bounce count.
• _Sent — every send event, with JobID, SubscriberKey, and EventDate. The starting point for almost every engagement query.
• _Open — open events. Filter on IsUnique = 1 when you only want the first open per subscriber per job.
• _Click — click events with LinkName, LinkContent, and URL. Again, IsUnique = 1 is useful for first-click-per-job analysis.
• _Bounce — bounce events with BounceCategory, BounceType, and SMTPCode. Essential for deliverability work.
• _Unsubscribe — unsubscribe events with SubscriberKey and EventDate.
• _Job — metadata for every send job: JobID, EmailName, DeliveredTime, and the from-address used.
• _ListSubscribers — the join between subscribers and lists, useful when you need to scope a query to a specific publication list.

There are also more specialised views for SMS (_SMSMessageTracking, _UndeliverableSMS), MobilePush, and Journey Builder activity, but the eight above will cover most reporting and segmentation work for an email-led program.

Where you run the queries

Queries against Data Views run inside Automation Studio as a SQL Query Activity. You write a SELECT statement, point it at a target Data Extension, and choose an update mode — overwrite, append, or update.

A few rules to remember. The SQL flavour is a restricted variant of T-SQL, so most standard Microsoft SQL Server functions work but stored procedures, cursors, and temporary tables do not. Query Activities have a 30-minute execution limit. If a query exceeds the limit, it fails outright and the target Data Extension is left untouched.

The result of a Query Activity always lands in a Data Extension. There is no preview-rows-in-the-UI option in production runs. If you want to validate the shape of your output before writing to a real target, point it at a sandbox Data Extension first.

Query patterns that come up constantly

The same handful of patterns cover the bulk of real campaign work. Here are the ones we end up writing or generating most often.

Unengaged subscribers in the last 90 days

This is the foundation of almost every reactivation and sunset program. The shape: subscribers who received at least one email recently but did not open any of them. You join _Subscribers to _Sent on SubscriberKey, filter _Sent.EventDate to the last 90 days, and exclude any SubscriberKey present in _Open over the same window.

Best for: reactivation audiences, deliverability cleanup, sunset journeys.

Top clickers by link

Useful when you need to follow up with people who clicked a specific call to action — a product page, a webinar registration, a pricing link. The trick is filtering _Click on LinkName or URL, then joining back to _Subscribers for the address. Add IsUnique = 1 if you only want first-click-per-job counts.

Hard-bounce cleanup

Bounces are categorised by BounceCategory and BounceType. For deliverability hygiene, you usually want subscribers with a hard bounce in the last N days. Join _Bounce with _Subscribers on SubscriberKey and filter on BounceCategory = 'Hard bounce'.

Send performance by job

Joining _Job, _Sent, _Open, and _Click on JobID gives you sends, unique opens, and unique clicks per email. This is the basic shape of any custom engagement dashboard built on top of SFMC tracking data.

Common pitfalls to avoid

A few mistakes are easy to make on your first or second Data View query, and harder to debug than they should be.

1. Joining on the wrong key. Most Data Views expose both SubscriberID (an internal numeric ID) and SubscriberKey (your business identifier). Use SubscriberKey for joins to your own Data Extensions. SubscriberID can change if a subscriber is rebuilt and is not stable for downstream use.
2. Forgetting the IsUnique flag on _Open and _Click. By default, these views contain every event, not just the first per subscriber per job. If you forget to filter, your counts will be inflated.
3. Underestimating retention. Most Data Views hold roughly six months of data. Anything older has been rolled off. If you need long-term engagement history, you must copy it into a Data Extension on a schedule.
4. Using GETDATE() without thinking about timezone. GETDATE() returns the server time, which is Central Time in SFMC. For UTC or local-market windows you usually want DATEADD and DATEDIFF with explicit offsets.
5. Querying without a target schema match. A Query Activity that lands in a Data Extension with the wrong schema will fail on every field mismatch. Always confirm the target columns match your SELECT list before scheduling the activity.

What changed in 2025 and 2026

Two updates from the recent release cycle are worth flagging for anyone writing queries today.

First, retention on automation-related Data Views was extended from 31 days to roughly six months in 2025. This was a long-requested change. For years, anyone building cohort analysis had to mirror these views into a Data Extension on a daily schedule just to retain anything beyond a month. The longer retention does not remove the need for archival, but it does make ad-hoc reporting much more useful out of the box.

Second, Salesforce introduced a SQL Query Activity Optimizer dashboard in mid-2025. Each query in your account now receives a performance risk score along with concrete recommendations. Common flags include missing indexes on joined fields, overly broad SELECT * patterns, and date filters that pull more rows than necessary. If you run anything against Data Views at scale, it is worth spending half an hour clicking through the dashboard's flagged queries.

Neither change rewrites the playbook, but both reduce the amount of defensive work teams have historically had to do around Data Views.

See this in action with QAiry

Writing Data View queries is the kind of work that rewards practice and punishes inattention. A missed IsUnique flag or a wrong join key can quietly skew an entire audience. That is exactly the kind of pattern recognition we built qairy.com/product-demos around. You describe the audience you want in plain language, and QAiry generates the SQL against the right Data Views, with the right joins and the right exclusions, ready to drop into a Query Activity.

If you would like to see how it handles your team's most common segmentation patterns, you can try it on a real account at qairy.com/try-it-free or get in touch and we will walk through it with you.