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 = ...