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

3 Answers 3

5

You should modify a few things about this to gain efficiency as it's likely timing out. You'll want to join the Subscribers data view to the Sent view on SubscriberID (SubscriberKey is technically fine but this should run faster). When joining Sent to Open I would keep it a left join if you want to see all sent as well as know if they opened (an Inner would only return those that opened). In order to optimize this, I'd join Sent and Open on SubscriberID, JobID, ListID, and BatchID fields. This should make this run MUCH faster. With the current join only on JobID, you are returning multiple rows for every subscriber.

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

-1

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)

3
  • 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, 2018 at 18:59
  • Left join should work. It would require the OpenDate field in the target data extension to allow NULL. Jan 9, 2018 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. Jan 9, 2018 at 22:08

You must log in to answer this question.

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