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