Mailchimp – Email Campaign Clickthrough Rate

Question

What is my email campaign Clickthrough Rate? Are my email recipients clicking my links inside the email campaign?

A metric to give us an idea on the performance of our email marketing campaign are clicks. The simplest case is to get the clickthrough 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 our Mailchimp data into your data warehouse properly re-modeled for it and ready to be used for our analytics purposes. Check this post about the resources Mailchimp exposes and should be replicated on our database, in case you want to dig in more.

create or replace email_reports_all as (  
SELECT  
id as campaign_id,  
campaign_title,  
send_time,  
emails_sent,  
clicks_click_rate,  
clicks_clicks_total,  
clicks_unique_clicks,  
clicks_unique_subscriber_clicks,  
opens_open_rate,  
opens_opens_total,  
opens_unique_opens,  
bounces_hard_bounces,  
bounces_soft_bounces,  
unsubscribed  
FROM mailchimp_reports  
);

SELECT campaign_title,  
       emails_sent,
       clicks_click_rate,
       send_time
FROM email_reports_all  
ORDER BY send_time ASC LIMIT 10;