ExactTarget - SQL Query that Pulls All Sent Emails with Open stats

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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)