Query NOT IN _Open from JobID .. incl. EmailAddress

Question

In a 2 email marketing campaign, I want to send mail 2 to those who did not open mail 1. I believe I can do this by having an automation query for those who did not open mail 1 and then send them mail 1.

I have this query (thanks to this great answer)

select distinct s.subscriberKey
from _sent s
where s.JobID = 4791255
and s.SubscriberKey NOT IN (
  SELECT o.SubscriberKey 
  FROM _Open o  
  WHERE o.JobID = s.JobID
  AND o.SubscriberKey = s.SubscriberKey
)

But to send mail 2 I have to have my query populate my Data Extension with the EmailAddress of each subscriber. I think I can do this by referencing to the EmailAddress column in the _Subscribers Data View, but I don't know how to tell the EmailAddress based on the SubscriberKey from the _Open Data view

How should I alter my query to add the EmailAddress of each subscriber?

Answer 1

Assuming that the SubscriberKey in your account is not the default of email address, you would need to also query the _Subscribers dataview.

You would just need to add the EmailAddress field to your DE and use the below query instead.

Something like the below should work:

select distinct s.subscriberKey, sub.EmailAddress
from _sent s
INNER JOIN _Subscribers sub
ON s.SubscriberKey = sub.SubscriberKey
where s.JobID = 4791255
and s.SubscriberKey IS NOT NULL
and s.SubscriberKey NOT IN (
  SELECT o.SubscriberKey 
  FROM _Open o  
  WHERE o.JobID = s.JobID
  AND o.SubscriberKey = s.SubscriberKey
)