Query subscribers who have not opened an email in the last XX days

Question

i have been trying to work on a query which returns subscribers who have not opened an email within the last 60 days. However my query returns all the opens they have done in the last 60 days.

i am using the _open data view.

i think i have to do a join to another view to determine have they been sent and have they opened in the last X days.

Hope someone can point me in the right direction TIA

Answer 1

Try this:

SELECT 
  s.SubscriberKey,
  s.EventDate AS Sent_Date,
  'False' AS Opened
FROM _Sent s
LEFT JOIN _Open o
ON s.SubscriberKey = o.SubscriberKey
  WHERE o.SubscriberKey IS NULL 
        AND CAST(s.EventDate AS DATE) >= CAST(DATEADD(dd, -60, CURRENT_TIMESTAMP) AS DATE) 
        AND CAST(s.EventDate AS DATE) < CAST(CURRENT_TIMESTAMP AS DATE)

This will return all sent emails in the last 60 days that have not opened any emails.

What it does is it brings in the last 60 days worth of sends and LEFT JOINS the _Sent data view with the _Open data view where there is no match, In theory this would mean the subscriber never opened an email in the last 60 days because they did not exist in the _Open data view.

Answer 2

You are right. _Open only captures the Open events. It will not have the 'not-opened' ones. So you'll have to do a MINUS between _Sent and _Open on the combination of JobID and SubscriberId

Answer 3

SELECT DISTINCT s.SubscriberKey, s.SubscriberId AS EmailId, s.jobid

FROM _Sent s LEFT JOIN _Open o ON s.SubscriberKey = o.SubscriberKey AND o.IsUnique = 1 WHERE o.SubscriberKey IS NULL