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?

  • Is this a one-off campaign where Email 1 is only sent once? – Macca May 4 '17 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 – FelixSanchez May 4 '17 at 10:28
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
)
  • 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 – FelixSanchez May 3 '17 at 13:43
  • Yes! Sorry, I have now edited to correctly syntax the SQL. – Gortonington May 3 '17 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? – FelixSanchez May 4 '17 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. – Gortonington May 4 '17 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 – FelixSanchez May 5 '17 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.