1

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?

2
  • Is this a one-off campaign where Email 1 is only sent once?
    – Macca
    May 4, 2017 at 3:22
  • @AndrewMcLeod - Actually, what I want to achieve is to free my email marketeers hours during weekends. At the moment we do this manually by moving not-opened in tracking into a list and sending to these in weekend evenings.. I. e. a mail regarding free freight. So I'd like a setup where I can change the mails to be sent and the JobID, so I can re-use it. Does it make sense? Thank you May 4, 2017 at 10:28

1 Answer 1

2

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
)
5
  • Thank you, this makes perfect sense, this makes me understand how to join data from different Data Views. Very valuable! One question: When trying this in a query, I get this error The query syntax is invalid Incorrect syntax near the keyword 'INNER' Any idea what this is about? Thank you May 3, 2017 at 13:43
  • Yes! Sorry, I have now edited to correctly syntax the SQL. May 3, 2017 at 14:01
  • Yes, it works. THANK YOU I have another question though regarding performance. When I query JobID 4792224 (with only 9 subscribers) my query works, and subscribers who have not opened are populated in my Data Extension. When I query JobID 4788106 (with +95K subscribers) my data extension is never populated. I can't tell if it times out, so: 1) I need to find out if the query times out (something like alerts in javascript) 2) I need to find out if the queries I have are optimized. Would this be a topic for an entirely new question or can we discuss it here? May 4, 2017 at 8:35
  • for #1 - put it into an automation. This will tell you if the query errors/times out as the automation will fail. for #2 - that would likely be better as a new question - as you would get a fresher set of eyes on it. May 4, 2017 at 12:27
  • for #1 thank you, I did not know this. I'll test the performance right away and for #2 based on the result of #1 I'll create a new question on how this might be able to achieve, maybe by splitting the query into smaller queries May 5, 2017 at 6:23

You must log in to answer this question.

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