Query did not opens in the past 180 days

Question

I have the following query to find all subscribers that have not opened in the past 180 days:

Target Data extension has: SUBSCRIBER RELATIONSHIP ContactKey relates to Subscribers on Subscriber Key

all fields are nullable in target DE other than the Contact Key which is the primary key(only need the contact key and email address to pull into the target data extension)

Query validates and runs, yet no data populates the target DE?

Query:

SELECT distinct s.subscriberkey as ContactKey, e.EmailAddress as Email
FROM _Sent s
JOIN _subscribers e
on s.subscriberkey=e.subscriberkey
WHERE s.EventDate > dateadd(Day,-180,getdate())
AND s.subscriberkey NOT IN (
SELECT e.subscriberkey
FROM _Open 
WHERE EventDate > dateadd(Day,-180,getdate()))

Answer 1

Please try with below query.

Select distinct
s.SubscriberKey,
sub.EmailAddress,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
left join [_open] o
on s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and o.SubscriberID is NULL
left join _Subscribers sub
on s.SubscriberKey = sub.SubscriberKey
where
s.EventDate > dateadd(d,-180,getdate())

Answer 2

Query for Subscribers who have have been sent an email in the last 180 days, but have NOT Opened in the last 180 days.
Try the following:

SELECT DISTINCT
    sub.SubscriberKey AS ContactKey
    ,sub.EmailAddress AS Email
FROM _Subscribers sub
INNER JOIN _Sent s ON s.SubscriberKey = sub.SubscriberKey
INNER JOIN _Open o ON o.SubscriberKey = sub.SubscriberKey
WHERE s.EventDate > dateadd(d,-180,getdate())
AND o.EventDate > dateadd(d,-180,getdate())

Result:
A distinct list of Subscribers with a "Send Event" in the last 180 days, but no "Open Event" recorded by SFMC in the last 180 days.

Note:
Depending on your Business Units and Subscriber Status setup, you may need to use run this from your sending BU and reference ent._Subscribers

Answer 3

the following query worked pulling in contact key and email address into the target DE:

SELECT distinct s.subscriberkey as ContactKey, e.EmailAddress as Email FROM _Sent s JOIN ENT._subscribers e on s.subscriberkey=e.subscriberkey WHERE s.EventDate > dateadd(Day,-180,getdate()) AND s.subscriberkey NOT IN ( SELECT subscriberkey FROM _Open WHERE EventDate > dateadd(Day,-180,getdate()))

Thankyou