Mandrill – Transactional Emails Open Rate

Question

What is my Transactional Email Open Rate? How are my emails performing over time?

One of the first Transactional email metrics is the number of transactional emails sent during a day. Then we can calculate the transactional email open rate and how it changes over time. 

*Open Rate: The percentage of email recipients who open a given email.

Schema

Blendo will sync all of your transactional email Mandrill data into your data warehouse properly re-modeled for it and ready to be used for your analytics purposes.

Output

Open Rate for your Mandrill Transactional Emails

SQL Query

Number of opened emails per day

SELECT DISTINCT date, count(event) over (partition BY date)  
FROM  
  (SELECT event,
          date_trunc('day', to_timestamp(ts)) AS date
   FROM events_
   WHERE event='open') p;

Mandrill Open Rate

WITH sent AS  
  (SELECT DISTINCT date AS sent_date,
                   count(event) over (partition BY date)
   FROM
     (SELECT event,
             date_trunc('day', to_timestamp(ts)) AS date
      FROM events_
      WHERE event = 'send') t),
     opened AS
  (SELECT DISTINCT date AS open_date,
                   count(event) over (partition BY date)
   FROM
     (SELECT event,
             date_trunc('day', to_timestamp(ts)) AS date
      FROM events_
      WHERE event='open') p)
SELECT sent_date,  
       (opened.count::float / sent.count::float) * 100
FROM sent  
JOIN opened ON (sent_date = open_date)