Individuals who have opened at least one email within a 30 day period, but have not clicked a single one

Question

I am trying to identify a segmented group of individuals who have opened at least one of my emails within the last 30 days, but have not clicked on a single one. The query syntax is verified however, when the query runs it does not pull any results even though there should be.

select 
    s.EmailAddress
from _Subscribers s
left outer join _Sent se on (s.SubscriberID = se.SubscriberID)
where 
se.EventDate > dateadd(day,-30,getdate()) 
AND ListID = 1458345 
AND se.SubscriberID IN (
    SELECT se.SubscriberID 
    FROM _Open 
    WHERE 
        se.EventDate > dateadd(day,-30,getdate())
) 
AND se.SubscriberID NOT IN (
    SELECT se.SubscriberID 
    FROM _Click 
    WHERE 
        se.EventDate > dateadd(day,-30,getdate())
)

Answer 1

My suggestion would be to use the _Job data view to drive the date criteria on your query. Then you can use the JobID in your join and sub-queries.

The EventDate for _Open and _Click can happen any time after the send.

select distinct
  s.emailaddress
from _job j 
inner join _sent s on (s.jobid = j.jobid)
where 
j.pickuptime >= dateadd(day,-30,getdate())
and s.subscriberid in (
  select 
    o.subscriberid 
  from _open o
  where 
  o.jobid = j.jobid
  and o.subscriberid = s.subscriberid
  and o.isunique = 1
) 
and s.subscriberid not in (
  select 
    c.subscriberid 
  from _click c
  where 
  c.jobid = j.jobid
  and c.subscriberid = s.subscriberid
  and c.isunique = 1
)

It's also a good practice to run your query in an Automation or Program to ensure it's not returning a runtime error. Your query would return multiple EmailAddresses. I'm assuming the Primary Key on your target Data Extension can accommodate duplicate ones.