1

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
)
2
  • What's not working? Are you getting an error or zero results or what? Hard to help without these kind of details. Commented Mar 1, 2017 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.
    – player1
    Commented Mar 1, 2017 at 19:15

1 Answer 1

4

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
)
2
  • 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.
    – player1
    Commented Mar 1, 2017 at 21:18
  • I had a typo in the second query. I've updated my answer above. Commented Mar 2, 2017 at 1:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .