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.
events
table will have the following structure.
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)