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?