1

I want to exclude subscribers who have not ‘opened’ or ‘clicked’ email in last 6 months. I have the below sql query. Is this SQL query correct? Does the final results of this sql query results have only email-addresses that have not engaged?

select  email from All_Active_Subscribers
where email not in (
    select EmailAddress from _Subscribers s
    left join _Click c on c.subscriberId = s.subscriberId and c.EventDate > dateadd(dd,-180,getdate())
    left join _Open o on o.subscriberId = s.subscriberId and o.EventDate > dateadd(dd,-180,getdate())
    and c.subscriberId is not null or o.subscriberId is not null
    ) 
group by email
        
1

Here's how I'd do it:

select
email 
from All_Active_Subscribers
where not exists (
  select top 1 c.*
  from _Click c 
  where c.subscriberId = s.subscriberId
)
and not exists (
  select top 1 o.*
  from _Open o
  where o.subscriberId = s.subscriberId
)

The reason I would not use joins... Well, it's a one-to-many situation where you don't want multiple matches. The "not exists" is a better option because you don't have to group or aggregate just to return a single subscriber.

The data views are limited to 6 months worth of data, so no need to include any date criteria.

If you did need to limit it by date (e.g in the last 60 days), I'd do the date math like this -- which I've found to be the most performant method:

select
email 
from All_Active_Subscribers
where not exists (
  select top 1 c.*
  from _Click c 
  where c.subscriberId = s.subscriberId
  and c.eventDate >= convert(date,getDate()-60)
)
and not exists (
  select top 1 o.*
  from _Open o
  where o.subscriberId = s.subscriberId
  and o.eventDate >= convert(date,getDate()-60)
)
3
  • thanks for your response. Because we are using “All_Active_Subscribers” DE and then removing records that have unengaged using “where not exists” query . As a result will the Targeted DE has customers who have unengaged or engaged ? – user0151 Mar 12 at 14:07
  • The results of the first query would give you subscribers who have not opened or clicked in the last 6 months -- so unengaged. – Adam Spriggs Mar 12 at 16:05
  • thank you for your reply – user0151 Mar 12 at 16:13

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.