Skip to main content
Salesforce

Return to Question

added 36 characters in body; edited tags
Source Link
Adrian Larson ♦
Adrian Larson ♦
  • 151.4k
  • 38
  • 247
  • 431

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

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

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

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()))
Source Link
inMusic
inMusic
  • 1
  • 2

Query did not opens in the past 180 days

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