0

I'm trying to figure out how to extract users that did not open an email with specific jobID's. This is what I have so far that doesn't seem to be working correctly. Any help would be appreciated.

SELECT
s.SubscriberKey
, s.JobID
FROM _Sent s 
WHERE s.SubscriberKey NOT IN (
  SELECT 
  o.SubscriberKey 
  FROM _Open o  
  WHERE s.JobID = 22008 OR s.JobID = 22007
)
  • What's not working? Are you getting an error or zero results or what? Hard to help without these kind of details. – Adam Spriggs Mar 1 '17 at 17:05
  • The number i'm getting does not match what the tracking results show. I'm getting a number that's much larger. I also made sure to set the SubscriberKey as the primary key in the data extension so I don't get duplicates. I'm not sure why the number is so large. The number should be around 400k and i'm getting 686k. – Gen X Mar 1 '17 at 19:15
2

Your query is selecting subscribers from ALL sends and then excluding anyone who opened Job 22008 OR 22007. Is this what you are looking for?

What I think you are wanting is a list of only subscribers that were sent job 22008 or 22007 and exclude anyone who opened one of these two emails. Is this a correct way to re-state your requirements?

To accomplish this, I would first select distinct subscribers from these two jobs:

select distinct s.subscriberKey
from _sent s
where s.JobID = 22008 OR s.JobID = 22007

Then I would update the query to exclude subscribers who had opened an email that corresponds to one of these two job:

select distinct s.subscriberKey
from _sent s
where s.JobID = 22008 OR s.JobID = 22007
and s.SubscriberKey NOT IN (
  SELECT o.SubscriberKey 
  FROM _Open o  
  WHERE o.JobID = s.JobID
  AND o.SubscriberKey = s.SubscriberKey
)
  • thank you David. Yes the way you rephrased my question is correct. I need to get the list of only subscribers that were sent job 22008 or 22007 and exclude anyone who opened one of these two emails. I tried your query but i'm getting invalid column name SubscriberKey. – Gen X Mar 1 '17 at 21:18
  • I had a typo in the second query. I've updated my answer above. – David Devoy Mar 2 '17 at 1:54

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.