SQL Query to find if email was delivered, opened, clicked and event-dates

 As an email marketer you will probably be in situations where you will be required to send a targeted marketing email to customers/prospects to achieve a desired outcome. And few days after the email is deployed you may be asked to provide a report to show how many emails were delivered/bounced, recipients who received the email, if they opened or clicked and when they performed this event.

For this example scenario, let us assume the email was sent to a data extension named – Event-Registration which has three columns

We are going to find the desired information by joining the send data extension (Event-Registration) with _Sent, _Open, _Click and _Bounce Data Views and Querying it.

SELECT de.Email, de.FirstName, de.LastName, s.EventDate as SendDate, o.EventDate as OpenDate, c.EventDate as ClickDate, b.EventDate as BounceDate FROM [Event-Registration] de LEFT JOIN _Sent s ON de.Email = s.SubscriberKey LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1 LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1 LEFT JOIN _Bounce b ON s.JobID = b.JobID  and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and c.IsUnique = 1 WHERE s.JobID = xxxxxxx

In your case replace {Event-Registration} with the name of the Data Extension the email was sent to and replace xxxxxxx with JobID of the send job for which you need to create the report.

This query if you run in Query Studio will provide the required results in following columns (See below). If there is a value for BounceDate it means the email bounced and not delivered.

You can create a DE with similar fields and export the results. Sometimes you may notice that there is no open date but click date. That means open event was not registered but click event was registered. This can happen in certain scenarios and is not an error. For example if the email is clipped and the recipient opens it in a webpage. In such a scenario, just consider open and click dates as same.