SFMC SQL: Subscribers Who Opened but Didn't Click

Find subscribers who opened but never clicked using _Open, _Click and NOT EXISTS. A working SFMC query, setup steps and the pitfalls to avoid.

You want to re-engage people who opened your email but never clicked, and SFMC has no built-in filter for it. The answer is a query activity that reads _Open, then removes anyone found in _Click for the same period. That is the core of any SFMC opened but not clicked SQL query.

Below you get a working query for the last 30 days, a variant for one specific send, a copyable checklist and the pitfalls that quietly skew the results. If you would rather skip the syntax, you can also generate SFMC SQL from plain English.

‍

Opened but not clicked in SFMC: what the data actually says

‍

Salesforce Marketing Cloud records engagement in system data views. Every open lands in _Open and every click lands in _Click, one row per event, keyed by SubscriberKey and JobID (the send).

If you are new to these tables, our guide to querying data views in Salesforce Marketing Cloud covers the basics.

‍

Opens and clicks are separate tables

There is no single "opened but not clicked" flag. You have to find openers in one view and subtract clickers from another, which is exactly what a NOT EXISTS subquery does well.

Because each open and click is its own row, one person can appear many times. Always use DISTINCT or a GROUP BY so a keen reader is not counted ten times.

‍

Subscriber level or send level?

Subscriber level asks: did this person open anything in the last 30 days without clicking anything? It suits re-engagement and nurture audiences.

Send level asks: did this person open one specific email and skip its links? It suits follow-ups to a single campaign. Both queries are below.

‍

SQL for subscribers who opened but did not click in 30 days

‍

Here is the subscriber-level query. It uses generic names, so swap Customers_Master for your own master data extension.

‍

The query

SELECT DISTINCT
  o.SubscriberKey,
  c.EmailAddress
FROM _Open o
INNER JOIN Customers_Master c
  ON o.SubscriberKey = c.SubscriberKey
WHERE o.EventDate >= DATEADD(DAY, -30, GETDATE())
  AND NOT EXISTS (
    SELECT 1
    FROM _Click k
    WHERE k.SubscriberKey = o.SubscriberKey
      AND k.EventDate >= DATEADD(DAY, -30, GETDATE())
  )

‍

Set up the query activity like this: target data extension Opened_Not_Clicked_30d, primary key SubscriberKey, update type Overwrite so the audience is rebuilt each run.

‍

Line by line

SELECT DISTINCT returns each subscriber once, however many times they opened.
FROM _Open o reads every open event, which is the pool of candidates.
INNER JOIN Customers_Master c brings in the email address and drops keys that no longer exist in your master table.
The first EventDate filter keeps only opens from the last 30 days.
NOT EXISTS with the _Click subquery removes anyone who clicked at all in that same window.
DATEADD(DAY, -30, GETDATE()) builds the rolling cutoff, so the audience refreshes itself each run.

‍

Create the target data extension with SubscriberKey (Text, 254, primary key) and EmailAddress (EmailAddress, 254). Match the field types to your source to avoid conversion errors.

For more on cutoffs and how GETDATE() behaves across time zones, see our guide to SFMC SQL date functions.

‍

Variant: opened one email but did not click it

‍

Sometimes you want a follow-up to a single send, for example a webinar invite where people read the message but ignored the registration link.

‍

Filtering by JobID

SELECT DISTINCT
  o.SubscriberKey,
  c.EmailAddress
FROM _Open o
INNER JOIN Customers_Master c
  ON o.SubscriberKey = c.SubscriberKey
WHERE o.JobID = 123456
  AND NOT EXISTS (
    SELECT 1
    FROM _Click k
    WHERE k.SubscriberKey = o.SubscriberKey
      AND k.JobID = o.JobID
  )

‍

Replace 123456 with the JobID of your send. You can find it in Tracking or by querying the _Job data view by email name. The NOT EXISTS check now compares JobID as well, so a click on a different email does not exclude the subscriber.

‍

Why NOT EXISTS and not a LEFT JOIN

A LEFT JOIN to _Click with WHERE k.SubscriberKey IS NULL returns the same people. NOT EXISTS is easier to read and does not multiply rows when a subscriber clicked several links.

‍

Quick answer: build the audience in six steps

‍

Create a target data extension with SubscriberKey and EmailAddress, and set SubscriberKey as the primary key.
In Automation Studio, add a SQL Query activity and paste the query above.
Point the activity at the target data extension and choose Overwrite.
Click Validate, then run it manually once and check the row count.
Add the query to an automation scheduled before your send, so the audience is fresh.
Use the data extension as your send audience or as a Journey Builder entry source.

‍

What to send to this audience

These readers showed interest but did not act, so the message should lower the effort. Try a shorter email, one clear link, or a different angle on the same offer rather than repeating the original send.

Keep a holdout group that receives nothing, so you can see whether the follow-up genuinely moved clicks. Also cap frequency, because people who open without clicking can still be fatigued.

‍

Goal	Filter to change
Shorter or longer window	Change -30 in both DATEADD calls
One specific campaign	Filter on JobID in both views
Only sendable contacts	Join to your sendable data extension and keep its status filter
Keep a history	Switch the update type to Append and add a date field

‍

Not sure which update type to pick? Read our breakdown of Overwrite vs Update vs Append in query activities.

‍

Pitfalls that skew opened-not-clicked audiences

‍

Apple Mail Privacy Protection and bot activity

Apple Mail Privacy Protection can register opens that a human never made, which inflates your openers list. Some security scanners also trigger clicks on links, which can wrongly remove people from the audience.

Treat the result as "likely interested" rather than proof, and test it against a clean control before changing your strategy.

‍

Retention, timeouts and the wrong audience

Data views only hold a limited window of tracking data (check your account, as retention depends on your setup). A 90-day lookback will return incomplete results if the data has aged out.

Large accounts can also hit the query activity limit of 30 minutes. If that happens, narrow the window or follow our guide to fixing SFMC SQL query timeouts.

Finally, remember that "opened but not clicked" is not the same as "unengaged". For the opposite problem, see how to find unengaged contacts in SFMC.

Salesforce documents the supported SQL in its Trailhead module on querying data with SQL.

‍

FAQ

‍

How do I find subscribers who opened but did not click in SFMC?

Select subscribers from the _Open data view and exclude those found in _Click using NOT EXISTS. Filter both on EventDate for the same period and write the result to a data extension with a query activity.

‍

Can I filter opens and clicks without SQL?

Filtered data extensions and Audience Builder can segment on tracked engagement, but combining an open condition with a missing click across a rolling window is awkward there. SQL gives you full control and repeats on a schedule.

‍

Should I use IsUnique in this query?

You do not need it. The queries here count each subscriber once with DISTINCT, which avoids depending on a flag that practitioners often find confusing.

‍

Why is my result smaller or larger than expected?

Check the time window, the join to your master data extension, and whether older tracking data has aged out of the data views. Apple Mail Privacy Protection can also inflate the opener count.

‍

Build this audience in seconds with QAiry

‍

QAiry turns a request like subscribers who opened but didn't click in 30 days into production-ready SFMC SQL and a Data Extension, installed as an app in your own SFMC. You still review the result, you just skip the blank editor.

Ready to try it? Install QAiry free or watch a product demo.

Related guides