Mailchimp – Email Campaign Bounce Rate (Hard and Soft Bounce Rate)

Question

What is my email campaign Bounce Rate? Which is the Hard Bounce Rate? Which is the Soft Bounce Rate?

There are two types of bounces, “hard” and “soft” bounces. Soft bounces are usually temporary problems with email addresses which are valid. Hard bounces are from invalid email address and have to be cleaned from our email list.

*Bounce Rate: The percentage of the total emails sent that could not be successfully delivered to a recipient.

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,  
       (bounces_hard_bounces + bounces_soft_bounces) AS total_bounced,
       (bounces_hard_bounces::decimal / emails_sent::decimal) AS hard_bounce_rate,
       (bounces_soft_bounces::decimal / emails_sent::decimal) AS soft_bounce_rate,
       ((bounces_hard_bounces + bounces_soft_bounces)::decimal / emails_sent::decimal) AS total_bounce_rate,
       send_time
FROM email_reports_all  
WHERE emails_sent <> 0  
ORDER BY send_time ASC LIMIT 10;