Can someone validate the following query for me?

I am trying to retrieve all subscribers that were sent an email in the last 6 months and have not clicked any?

SELECT distinct s.SubscriberID     
FROM _Sent s 
WHERE s.EventDate > dateadd(day,-180,getdate()) 
AND s.SubscriberID NOT IN (
  SELECT o.SubscriberID
  FROM _Click o
  WHERE o.EventDate >  dateadd(day,-180,getdate())
AND s.SubscriberID is not null

The targeted data extensions contains: SubscriberId field, am I right to think the Contacts added to the DE are those that were sent an email in the last 6 months but not clicked it because they do not exist in the _Click data view?

  • 2
    SFMC data views (_Sent and _Click) only retain data for the past 6 months, so your filters for dates are unnecessary. Other than that, your query seems good. I usually recommend using LEFT JOINS instead of IN or EXISTS - but is not necessary – Gortonington Sep 25 '18 at 13:18

Your Answer

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

Browse other questions tagged or ask your own question.