1

We have all subscribers in 1 main data extensions "Audience_DE"

within this data extension, we have one field, "Email_Sign_up" YES / NO

All our emails are based on this field, we only send email if "Email_Sign_up" is YES.

What should be the correct approach to get subscribers who did not open email in last 100 days and they have "Email_Sign_up" as YES in "Audience_DE"?

If I save "Open" data view data in one data extension,

Is this a correct approach?

Select all customers from "Audience_DE" where "Email_Sign_up" = 'YES' and Email in (select SubscriberKey from openDataView where EventDate > DateAdd(Day,-100,GetDate())

Is it necessary to join with sent data view? if yes, why?

Improve this question
2
  • 1
    I think your above query should read and Email NOT in... which ought to work.
    – Timothy
    Mar 21, 2018 at 16:09
  • Why NOT IN (vs IN)? Is it because I am adding days? If I was doing WHERE DATEDIFF(day,EventDate,GETDATE() then I will do "IN"? if I do NOT IN, isn't going to look for audience who never opened their email (meaning they are not in open Dataview at all)? VS what I am trying to do here is look for audience who did not open email in last 100 days so their records will be there in _open data view and event date will determine the logic.
    – kuml
    Apr 25, 2018 at 12:03

1 Answer 1

Reset to default
1

I think it just needs to be NOT IN rather than IN:

SELECT SubscriberKey
FROM AudienceDE
WHERE Email_Sign_UP = YES 
AND SubscriberKey NOT IN
(SELECT SubscriberKey
FROM _open
WHERE EventDate > DateAdd(Day,-100,GetDate())
)
Improve this answer
2
  • Hi @r0bsta if I do NOT IN, isn't going to look for audience who never opened their email (meaning they are not in open Dataview at all)? VS what I am trying to do here is look for audience who did not open email in last 100 days so their records will be there in _open data view and event date will determine the logic. No?
    – kuml
    Apr 25, 2018 at 11:43
  • 1
    that query will get you exactly what you originally asked for ie people who have YES for Email_Sign_Up, and who have not opened an email within the last 100 days - these people may or may not be in the data view as they may or may not have opened emails before then. If you are trying to look for people who have opened an email but a greater period of time ago than 100 days, that's a different story...
    – r0bsta
    Apr 26, 2018 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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