How to Find Unengaged Contacts in SFMC
A practical guide to finding unengaged contacts in SFMC: the SQL pattern over _Sent, _Open, and _Click, how to refine it, and what to do next.
How to Find Unengaged Contacts in SFMC
Every email list has a quiet majority that has stopped opening, stopped clicking, and stopped reacting in any visible way.
If you cannot see them clearly, you keep emailing them by accident. If you keep emailing them, your sender reputation slowly erodes and your engaged readers pay the price.
Finding those contacts in Salesforce Marketing Cloud is not difficult, but it is fiddly.
The data lives across three system tables, the time windows matter, and the audience has to translate into something Journey Builder or a recurring send can actually use.
This post walks through how SFMC tracks engagement under the hood, how to write a query that surfaces unengaged subscribers, how to refine it for real campaigns, and what to do with the audience once you have it.
Why unengaged contacts deserve their own segment
The drag on deliverability
Mailbox providers watch how recipients react to your messages. Long stretches of zero opens, zero clicks, and zero replies signal that your mail is no longer wanted.
That signal slowly pushes your future sends into the promotions tab, into the updates folder, or in the worst case directly into spam. The cost is invisible in your SFMC reports but very visible in revenue.
The drag on sender reputation
Reputation is built across your entire sending domain, not per campaign. A single dormant cohort can pull the whole reputation down with it.
Keeping unengaged contacts out of your routine broadcasts protects the readers who actually want to hear from you. It also makes A/B test results more reliable, because your sample is closer to your real audience.
How SFMC tracks engagement under the hood
The role of system data views
Marketing Cloud records every send, every open, and every click in three system data views: _Sent, _Open, and _Click. They are read-only, automatically maintained, and queryable from Automation Studio.
Each row carries a numeric SubscriberID, a JobID, and an EventDate. The _Subscribers data view sits alongside them and resolves SubscriberID back to the SubscriberKey and EmailAddress you see in the UI.
For an honest engagement signal, the four tables you almost always need together are:
_Sentfor the universe of messages a subscriber received_Openand_Clickfor any positive interaction_Subscribersfor status, tenure, and the human-readable key
The six month retention window
These views hold roughly six months of history by default. Anything older has rolled off, and no query can bring it back.
That cap matters because it sets the longest possible look-back window for an unengaged audience. If you want a 12 month definition, you have to start persisting your own engagement data in a regular Data Extension long before you need it.
A query pattern that actually works
Joining sends to opens and clicks
The shape is simple. Start from _Sent, left join to _Open and _Click, then keep only the rows where the joins find nothing.
That gives you every subscriber who has received mail in the window but never engaged with any of it.
A clean version reads like this:
SELECT DISTINCT s.SubscriberID, su.SubscriberKey, su.EmailAddress FROM _Sent s JOIN _Subscribers su ON s.SubscriberID = su.SubscriberID LEFT JOIN _Open o ON s.SubscriberID = o.SubscriberID LEFT JOIN _Click c ON s.SubscriberID = c.SubscriberID WHERE o.SubscriberID IS NULL AND c.SubscriberID IS NULL
Adding a tenure filter
You probably do not want to flag a subscriber who joined a week ago and has not had time to engage. Add a tenure filter on the _Subscribers view.
A common rule is to require at least 180 days since the subscriber joined: AND su.DateJoined < DATEADD(dd, -180, GETDATE()). Adjust the number to match your usual cadence and the patience of your business.
Watching out for timezones and keys
System data views report timestamps in Central time, not your local time. A one-day drift is the difference between including the most recent send and silently missing it.
It is also tempting to join everything on SubscriberKey, but the system views key on the numeric SubscriberID. Always join on SubscriberID, then resolve SubscriberKey through _Subscribers at the end.
Refining the audience for real campaigns
Distinguishing brand new contacts
Subscribers in their first few weeks behave differently from long-time members. Their engagement signal is still forming, and treating them as dormant produces ugly results.
It is usually safer to exclude anyone added in the last 30 to 90 days. The exact window depends on how often you send and how quickly your welcome flow is supposed to land its first impressions.
Excluding holds, bounces, and unsubscribes
Unengaged is not the same as undeliverable, and it is not the same as opted out.
Add a filter that keeps only subscribers with a Status of Active. Then exclude anyone present in the _Bounce data view in the recent window, and anyone who has unsubscribed at the list or publication level. Each of those groups needs a different response.
Storing and refreshing the audience
Persisting the result in a Data Extension
Create a Data Extension called something like Unengaged_Contacts with only the fields you will need downstream: SubscriberKey, EmailAddress, LastEngagementDate, and a small flag for the segment.
Set SubscriberKey as the primary key so future runs upsert cleanly without producing duplicates. Mark it as sendable, with a relationship back to your master Contact list.
Scheduling the right cadence
Wire the query into an Automation Studio activity and run it weekly. Daily is overkill for a slow-moving signal, monthly is too slow to react to the win-back path.
A weekly refresh keeps suppression and reactivation logic close to real behaviour without spiking compute or flooding your activity log.
What to do with the unengaged list once you have it
Run a structured win-back
Build a short journey: two or three messages spaced a week or two apart, each with a clear value proposition and a single call to action.
Anyone who opens or clicks during the journey naturally drops out of the unengaged bucket on the next refresh. The journey itself does not need any special logic for that, the segment definition handles it.
Suppress the rest from routine sends
Subscribers who do not engage with the win-back move into a long-term suppression segment. Exclude them from your recurring broadcasts and your batch promotions.
You still have the option to send them milestone messages, account notices, or transactional content, but you stop wasting impressions on a permanently quiet inbox. Over a quarter, that single change is often the biggest single lever a CRM team has on deliverability.
See QAiry in action
Most of this should be something a marketing operator can ask for in one sentence, not a multi-day project.
QAiry generates the SQL, the Data Extension, and the Automation Studio schedule from a natural language request like find contacts who have received mail in the last six months but have not opened or clicked, excluding anyone who joined in the last 60 days. If you want to see what that looks like end to end, you can try it at qairy.com/try-it-free or watch a short walkthrough at qairy.com/product-demos.
