Qestion

What is my email campaign Unsubscribe Rate?

Unsubscribe Rate is the percentage of email recipients who open 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.

Reports
The Mailchimp API reports table contains information about the performance of our campaign and it has the following structure.

Mailchimp Reports TableOutput

mailchimp email campaign unsubscribe rateSQL Query

email_reports_all VIEW

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  
);

Unsubscribe Rate

SELECT campaign_title, emails_sent,  
       (unsubscribed::decimal / emails_sent::decimal) AS unsubscribed_rate,
       unsubscribed,
       send_time
FROM email_reports_all  
WHERE emails_sent <> 0  
ORDER BY send_time ASC LIMIT 10;

in Email Marketing