How QAiry Handles Data Views
SFMC data views hold your engagement history but only answer SQL. Here is what they retain, where queries go wrong, and how QAiry reads that layer.
How QAiry Handles Data Views
Ask a marketer where their engagement history lives in Salesforce Marketing Cloud and you usually get a pause.
The answer is data views: system tables that hold send, open, click, bounce, unsubscribe and subscriber records for your account.
They hold the most useful data in the platform, and they are invisible in most of the interface.
You cannot browse a data view the way you browse a data extension. You reach it by writing SQL inside a Query Activity in Automation Studio.
That single constraint explains why so much engagement reporting in SFMC ends up queued behind a technical team.
This post covers what data views are, the rules Salesforce documents around them, and how QAiry reads that layer when you ask a question in plain language.
What data views actually are
System tables, not data extensions
Data extensions are yours. You create them, name them, and decide what goes in.
Data views are Salesforce's. They are populated automatically as your sends run, and their schema is fixed.
Salesforce documents more than twenty of them, covering email, SMS, journeys, automations and social forwards.
The practical consequence is that you cannot fix a data view. If a column is not there, no amount of configuration will add it.
What you can do is know the shape of each one well enough to stop asking it for something it will never return.
The underscore naming convention
Every system data view starts with an underscore. _Sent, _Open, _Click, _Bounce, _Unsubscribe, _Job, _Subscribers, _ListSubscribers, _Journey and _JourneyActivity are the ones most segmentation work touches.
The rest cover narrower ground: _Complaint for spam complaints, _FTAF for forward-to-a-friend, _SMSMessageTracking for MobileConnect, _AutomationInstance for automation health.
Knowing which view answers which question is half the job. The other half is knowing what is not in there.
The six-month window most teams forget
What Salesforce actually retains
Salesforce frames data views as giving you up to six months of subscriber and journey information.
For several views the six-month limit is stated outright: open, click, bounce, complaint and forward-to-a-friend data is retained for six months.
One view is far shorter. _ReconcilableDispositionView keeps seven days.
Salesforce also notes that some data views let you retrieve more than six months, but warns that queries returning large datasets take longer and can affect system performance.
Why this changes the question you can ask
A request like everyone who has never opened an email from us sounds simple and is quietly impossible against data views alone.
What you can answer is everyone who has not opened in the last six months, which is a different segment with a different size.
Teams that miss this build reactivation audiences on a false premise, then wonder why the numbers move every quarter.
Where data view queries usually go wrong
Timezone and rounding surprises
Click and open tracking data displays in Central Standard Time, does not observe Daylight Savings Time, and is rounded to the nearest second.
If your campaign windows are defined in local time, a naive WHERE clause on EventDate will quietly pull the wrong day at the edges.
Business unit scope traps
Several views behave differently depending on where you run them.
_BusinessUnitUnsubscribesruns on the parent account only, not on child business units._Subscribersreturns results at the enterprise level only, and does not include subscriber attributes.- When a triggered send does not add subscribers to a list, enterprise-level views exclude those sends, opens, clicks, unsubscribes, bounces and forwards.
That last one catches large orgs constantly. The data is not missing, it simply lives in the sending business unit rather than the parent.
Expecting fields a view does not carry
The third recurring mistake is assuming a view holds the attributes you want to filter on.
It often does not. _Subscribers gives you subscriber records and statuses, but subscriber attributes are not part of it.
In Enterprise 2.0 accounts, profile attributes get their own home: creating one adds a column to _EnterpriseAttribute, and queries there can return those profile columns alongside the documented ones.
So a segment defined on a custom profile field is not a single-view query. It is a join, and knowing that up front saves an afternoon.
How QAiry reads the data view layer
Mapping a question to the right view
QAiry starts from the question, not the schema. You describe the audience you want in your own words.
It then resolves which views carry the evidence: engagement questions land on _Open and _Click, deliverability questions on _Bounce, send history on _Sent and _Job, list membership on _ListSubscribers.
Where a question spans several views, QAiry builds the joins rather than asking you to.
Choosing the join keys
Engagement records carry the identifiers SFMC generates at send time, including job, batch, list and subscriber keys.
QAiry joins on SubscriberKey and JobID rather than on email address, because address-based joins silently break the moment a contact appears twice.
It also writes explicit column lists. Salesforce recommends naming the exact fields you need instead of using SELECT *, and that advice matters more on data views than anywhere else.
Showing its work
The SQL QAiry produces is readable, not a black box. You see the views it selected, the keys it joined on, and the date boundaries it applied.
That matters because the person approving the query is usually not the person who wrote the request.
A reviewer can confirm in a few seconds that the retention window was respected and the business unit scope is right, which is faster than reverse-engineering a query someone hand-wrote last quarter.
How QAiry keeps queries inside SFMC's limits
Writing for the thirty-minute ceiling
A Query Activity times out after thirty minutes. Salesforce goes further and suggests that if a query consistently runs longer than ten minutes, you should be transforming that data somewhere else, such as Data Cloud.
QAiry writes against that ceiling by default: bounded date ranges, filters that an index can actually use, and no retrieval of thirty days of history when the question only needs twenty-four hours.
Staging instead of one giant query
Salesforce's own guidance on large jobs is to divide a query with multiple joins into smaller ones, write intermediate results to holding tables, then unite them at the end.
QAiry produces that shape when a request warrants it, rather than handing you a single statement that only fails after twenty-nine minutes.
The practical guidance for automations is similar: one query per step, and schedules set off the hour so your job is not competing with everyone else's 8:00 run.
What this changes day to day
The data view layer does not get simpler. It gets handled.
A marketer asks for a segment in a sentence. What comes back is SQL that respects the retention window, the timezone quirk, the business unit scope and the runtime limit.
The person who used to write that query is still the person who reviews it. They just stop being the bottleneck for every request that reaches them.
See QAiry in action
Data views are the part of SFMC where small mistakes are expensive and hard to spot, which makes them a good test of whether generated SQL is actually production ready.
You can watch QAiry work through a real data view query at qairy.com/product-demos, or try it against your own account at qairy.com/try-it-free.
