2

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

4

Try this:

SELECT 
  s.SubscriberKey,
  s.EventDate AS Sent_Date,
  'False' AS Not_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.

  • Happy to help @AndrewC – thechrishaddad Aug 15 '17 at 2:31
  • I have three questions on this solution: 1. Is the CAST call required? As the field types are already DATE/TIME 2. Is the last call required? Since the first data statement covers the second as well? 3. Does this take FOREVER to run? – George Rosedale Sep 21 '17 at 18:19
0

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

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.