Query Activity to extract all the customers who have not opened

Question

I'm trying to extract all the customer who have not opened the email I have created an SQL activity and Data Extension but it doesn't seem like its pulling all the customer who have not opened the email. see code below - have I missed something

SELECT de.Email
FROM  Immigration_EM de
LEFT JOIN _subscribers sub ON sub.EmailAddress = de.email
LEFT JOIN _Sent s ON s.SubscriberID = sub.SubscriberID
LEFT JOIN _open o ON o.SubscriberID = s.SubscriberID
LEFT JOIN _job j ON j.JobID = s.JobID
WHERE o.SubscriberKey IS NULL
AND j.JOBID= 285269

I will be updating the jobID to Emailname so I can automate it, also I want to only pull the customer who have been sent an email within the last 7 days.

any help would be much appreciated.

thanks in advance Pete

Answer 1

I would do it something like this:

SELECT EmailAddress FROM  Immigration_EM WHERE EmailAddress NOT IN (
   SELECT EmailAddress FROM _subscribers sub 
   INNER JOIN _sent s ON sub.SubscriberID = s.SubscriberID
   INNER JOIN _open o ON o.SubscriberID = sub.SubscriberID
   WHERE s.EventDate > DATEADD(day, -7, GETDATE()) 
 )

Do note that "opens" means something very specific, that the user has taken action that we can record as an "open". Explicitly opening the email will do it, as will downloading the pictures. A mere preview pane (like in Outlook) does not count towards opens.

Good luck!