Question

What is my Transactional Emails 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 your transactional email Mandrill data into your data warehouse properly re-modeled for it and ready to be used for your analytics purposes.

Mandrill Transactional Email Events
The Mandrill events table will have the following structure.

Mandrill Data ModelOutput

Transactional Emails bounce rate with Mandrill

Bounce Rate of your Mandrill Transactional Emails

SQL Query

Mandrill Bounce 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),
     bounced AS
  (SELECT DISTINCT date AS bounce_date,
                   count(event) over (partition BY date)
   FROM
     (SELECT event,
             date_trunc('day', to_timestamp(ts)) AS date
      FROM events_
      WHERE event='soft_bounce'
        OR event='hard_bounce') p)
SELECT sent_date,  
       (bounced.count::float / sent.count::float) * 100
FROM sent  
JOIN bounced ON (sent_date = bounce_date)

in Transactional Emails