Mandrill – Transactional Emails Clickthrough Rate

Question

What is my Transactional Emails Clickthrough Rate? Are my email recipients clicking my links inside my transactional emails?

A metric to give us an idea of the performance of our transactional emails are clicks. The simplest case is to get the click-through rate for the links inside an email.

*Clickthrough Rate: The percentage of email recipients who clicked on one or more links contained in 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

Clickthrough Rate for your Mandrill Transactional Emails

SQL Query

Mandrill Clickthrough 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),
     clicked AS
  (SELECT DISTINCT date AS clicked_date,
                   count(event) over (partition BY date)
   FROM
     (SELECT event,
             date_trunc('day', to_timestamp(ts)) AS date
      FROM events_
      WHERE event= 'click') p)
SELECT sent_date,  
       (clicked.count::float / sent.count::float) * 100
FROM sent  
JOIN clicked ON (sent_date = clicked_date)