0

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()))
0

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())
| improve this answer | |
  • there is no need to join with the _subscriber table if you only want to know which subscribers didnt open. Only if you really want to have the email-address you need to do so. You can delete left join _Subscribers sub on s.SubscriberKey = sub.SubscriberKey – Johannes Schapdick Oct 10 '19 at 7:53
0

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

| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.