Despite the differences between transactional emails and email marketing, when it comes to analytics the fundamental metrics and KPIs that we should be tracking are quite similar. The main difference is the data we will have to work with in order to calculate these metrics and KPIs. The purpose of this post is to:
- Introduce you to the data that usually generated by transactional emails
- Explore the data and the queries needed
- Provide a hands-on view, to a small set of fundamental KPIs and Metrics that you should be tracking to understand the performance of your actions.
- Build a sample dashboard that we can use to constantly monitor the metrics and KPIs.
The Fundamental KPIs and Metrics of Transactional Emails
In a previous post, we have created a set of basic KPIs and Metrics for email marketing. In a summary, these are the following:
- Open Rate
- Clickthrough Rate
- Email List Growth
- Bounce Rate
- Unsubscribes
For the case of transactional emails, we will omit the Email List Growth and the Unsubscribes. Both do not make much sense in transactional emails, but we will add the Total Number of Sent Emails. That makes more sense in the context of transactional emails as each sent is triggered by an interaction of a user with our product, so this is something that we would like to monitor.
Transactional Email Analytics: The prerequisites
To effectively perform analytics on the data that are generated by our transactional emails, we need the following:
- The raw Transactional Email data (or metadata), in this post we will assume that our data are generated by Mandrill.
- A database to send and store our data. Blendo will help us in this.
- A Dashboard. For this step, we are going to use Re:Dash.
Getting your Transactional Email Data from Mandrill
The (meta) data generated by Mandrill as we create and send transactional emails are quite simple in structure. Transactional Email providers like Mandrill are more or less exposing SMTP as a service through a REST API. The data we get access to, can be used for analytics and are similar to log data. Every time an event is triggered on the transactional email platform, it is also logged by it, and thus we can access it.
Every time an event is triggered on the transactional email platform, it is also logged by it, and thus we can access it.
We will use this data to measure our KPIs and metrics. For a detailed view of the data generated by Mandrill, you can check the documentation here.
How to Access and Store Mandrill data
The first issue we have to deal with is how to access this data and how to store it into a place where we can perform queries on it. To do that we will be using Blendo and the Webhooks functionality that Mandrill offers.
Step:1 – Connect a data warehouse
Store Mandrill data
First, we need to say to Blendo where to send the data. This is easily done by defining an output source at Blendo. If it is the first time you log in to Blendo you will see the screen bellow. Click on the box with Postgres.
There we select which database we’d like to use, e.g. PostgreSQL, and provide the credentials for it. If you want some more help on how to send your data from any data source to PostgreSQL check our documentation.
Step:2 – Connect Mandrill
Create a Mandrill Integration at Blendo
We will create an in integration through Webhook on Blendo which is a trivial process. If you want you may read our documentation about setting up our Mandrill integration. But actually it is something like this…
Setup Mandrill Webhook
When we create a new Webhook on Blendo, a URL is given back to us which can be used to post data to. This will be used on Mandrill to set up a Webhook connection between the two systems.
Make sure that you include all the different events on the setup screen of Mandrill for the Webhook.
The Data Model of your Mandrill Transactional Email Data
As we have mentioned already, the data generated by transactional emails on Mandrill are quite simple and if we take them together they look a lot like an event log. Storing this data in a relational database requires just one table in the simplest scenario. By using Blendo, this table will have the following structure.
Relational Database Data Model for Mandrill events
Transactional Email KPIs & Dashboard
The number of services to build custom dashboards or BI tools to track metrics is huge. For our SQL and charting purposes, we will use Re:Dash. Re:Dash is a web-based open source client that can connect to services like Redshift, PostgreSQL and more, share queries, create dashboards etc. After having connected Mandrill with Blendo, and push our transactional email data to PostgreSQL, we’ll be using Re:Dash for querying and charting our campaign data. A sample Dashboard could be this.
Total Number of Mandrill Transactional Emails Sent (per day)
Now this is a quite trivial question to ask but as it is actually linked to events triggered by our users on our product, it makes sense to keep track of it. To do that we may execute the following query, which will give us the number of emails sent per day.
SELECT DISTINCT date, count(event) over (partition BY date)
FROM
(SELECT event,
date_trunc('day', to_timestamp(ts)) AS date
FROM events_
WHERE event = 'send') t;
Total Number of Mandrill Transactional Emails Sent (per day)
Open Rate for your Mandrill Transactional Emails
Sending the emails is one thing, having our customers open it is another and a very important one. So ideally we’d like to track how many emails were opened by our recipients. To do that, we need to execute the following query.
SELECT DISTINCT date, count(event) over (partition BY date)
FROM
(SELECT event,
date_trunc('day', to_timestamp(ts)) AS date
FROM events_
WHERE event='open') p;
Now that we have our query to get the number of opened emails per day, we can combine it with the results of the previous query to get the transactional email open 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),
opened AS
(SELECT DISTINCT date AS open_date,
count(event) over (partition BY date)
FROM
(SELECT event,
date_trunc('day', to_timestamp(ts)) AS date
FROM events_
WHERE event='open') p)
SELECT sent_date,
(opened.count::float / sent.count::float) * 100
FROM sent
JOIN opened ON (sent_date = open_date)
Open Rate for your Mandrill Transactional Emails
Click Through Rate for your Mandrill Transactional Emails
Most often, when we send an email to someone we expect an action from him. Usually, this action has the form of a URL that should be clicked. So the next step is to measure how many recipients from those who have opened the emails have also clicked to the included URL. This is the click through rate KPI that we will measure with the following query.
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)
Click Through Rate for your Mandrill Transactional Emails
Bounce Rate of your Mandrill Transactional Emails
For many different reasons, the emails we sent might never reach our recipients. This is a bounce event and there are two types, soft and hard. In order to calculate the bounce rate, we need to add both soft and hard bounces to our queries. The following query does just that.
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)
Bounce Rate of your Mandrill Transactional Emails
Tracking the Performance of your Transactional Emails
The intention of this post was to create a small set of the fundamental KPIs and metrics that you should monitor for your transactional emails. These KPIs will give you a high-level view of how your transactional emails perform and are just the bare minimum for that purpose. Getting your data from a transactional email platform, like Mandrill, onto your database is just a matter of a few minutes with Blendo. Then you are free to explore your data and figure out all the different questions that will help you to better understand your transactional emails.
Blendo is here to help you! You are welcome to try it out for free.
Thank you for reading.
If you want an easy way to sync your transactional email data, give Blendo a try! Reach out to us and we’ll show you around!
