1

I've been tinkering with this for some time and cannot get it to work.

I have a data extension with the fields: [EmailName], [EmailAddress], [JobID], [SubscriberKey], [DeliveredTime], [OpenDate]

I have the SQL QUERY built (see below) with the end goal to pull all e-mails sent in a timeframe. I also want to include either if/when they opened the e-mail. The below query has valid syntax and runs, but doesn't work to update the DE. It only works if I remove the join that adds the _Open table.

Any ideas as to an error with the syntax below? Thank you!

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 JOIN _Open o ON se.JobID = o.JobID
WHERE se.EventDate >= GetDate()-9 AND se.EventDate <= GetDate() -7
GROUP BY j.JobID, j.EmailName, j.DeliveredTime, s.EmailAddress, s.SubscriberKey
0

In your output data extension, what are your field types and lengths?

Make sure a field like EmailName has a sufficient length e.g. 150.

Is your intention to pull all emails/opens or just the most recent open? i.e. whats your reasoning for using using max(o.eventdate)

  • My intention is just to pull the most recent open. I also want to see sends even if there is no open and just leave the [OpenDate] blank but I don't think exact target accepts FULL OUTER JOINs – arbitel Jan 9 '18 at 18:59
  • Left join should work. It would require the OpenDate field in the target data extension to allow NULL. – David Devoy Jan 9 '18 at 22:06
  • Do you have Triggered emails in your environment? The JobID on triggered sends only changes when the trigger is republished. This could potentially play with your results. – David Devoy Jan 9 '18 at 22:08
0

I'm not sure what fixed it - but I deleted the query activity and remade and it works. Frustrating to not know what caused the problem!

My only issue now is that null opens aren't displayed. I tried changing the 'LEFT JOIN' to 'LEFT OUTER JOIN' but no luck, it doesn't append any records to the DE. I'm going to post separate question

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.