[SalesForce] Query NOT IN _Open from JobID .. incl. EmailAddress

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?

Best Answer

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
)
Related Topic