0

I have query below that only pulls e-mails sent that haven't been opened. I want it to pull all e-mails sent (within the where timeframe) and show the [OpenDate] whether it is null or not.

I'm using Exact Target (Marketing Cloud)'s SQL Query. Any ideas why the below doesn't work?

SELECT j.EmailName, s.EmailAddress, j.JobID, j.DeliveredTime, s.SubscriberKey, max(o.EventDate) as OpenDate FROM  _Job j
INNER JOIN _Sent se ON j.JobID = se.JobID
INNER JOIN _Subscribers s ON se.SubscriberKey = s.SubscriberKey
LEFT OUTER JOIN _Open o ON se.JobID = o.JobID
WHERE se.EventDate >= GetDate()-14 AND se.EventDate <= GetDate() -7
GROUP BY j.JobID, j.EmailName, j.DeliveredTime, s.EmailAddress, s.SubscriberKey
arbitel
  • 321
  • 6
  • 22
  • what is the output you are getting? Are you not receiving any output? – Luke Becker Jan 09 '18 at 20:16
  • Ah disregard previous comment if you read. The above returns nothing - 0 records. If I change LEFT OUTER JOIN to LEFT JOIN then I get all of the Sent emails with a value in o.EventDate (which means they opened the e-mail). I do not receive any records where o.EventDate=null – arbitel Jan 09 '18 at 20:26
  • try joining _Open to _Job instead of to _Sent. – Luke Becker Jan 10 '18 at 03:18

0 Answers0