There are over 10 million search results when searching for posts about email marketing optimization. There are even more resources on helping you to succeed this optimization. But how can you see that it is working or not?
You can’t manage what you can’t measure
In order to measure the effectiveness of our email marketing efforts, we first have to agree and calculate the email campaign KPIs and metrics to chart. Then we may track them onto a dashboard or a BI tool or even excel. While it’s possible to see some of these data from inside an email marketing software and its reports, it does not provide the whole picture. Imagine the amount of information that resides in email marketing tools combined with data from sales or other transactional data. in order to take advantage of it, we will need to use a bit of SQL and some dashboards to see our email campaign metrics.
The most important factor is to set it up correctly from the beginning. We need an easy way to consistently get email marketing data into a database like Amazon Redshift or PostgreSQL. Then create and monitor our KPIs and eventually be able to generate explanatory and predictive models that will help optimize our email marketing.
This post will provide an initial setup and data modeling to chart some important email campaign metrics. In the future, I plan on working with more details and new KPIs and analysis.
We want to reach to a point to have a dashboard like the picture bellow made with Mode. Let’s see it in more detail.
Email Marketing Analytics in 3 steps
We need three main elements to be up and running with email marketing analytics.
- The email marketing campaign data and we are going to use data from our favorite Mailchimp.
- A database to move our data to and a Data Model to work with. Blendo will help us in both.
- A Dashboard. for this step we are going to use on of our go-to tools for the job, Mode.
1. Email Marketing Campaign Data
We start from our campaign data. It is really important to note here that someone needs to have an idea on some of the differences between Email Marketing data and Transactional Email data and what kind of data we expect to get from an email marketing service. Email marketing services usually offer email marketing analytics too, but one size never fits all.
The key to building our dashboards and further analyze our email marketing campaign data is to get it in one place first.
Luckily most of these services offer an API that can make our life a bit easier. We are are going to use the Mailchimp API to get our email campaign data.
Tools like Blendo are our friends here. Blendo connects to popular SaaS tools and consistently will sync your data into databases like PostgreSQL, MS SQL or Amazon Redshift. Blendo will help us connect in seconds and start pulling our data from MailChimp. Then it will make sure to sync our email marketing data with a database of our own choice. In this post, we will send our data from Mailchimp to PostgreSQL in Heroku. – I could also send our data from Mailchimp to Amazon Redshift but I resisted the rage :).
Blendo will make sure to have all your data fresh and synced. Blendo syncs the email marketing data from Mailchimp into our PostgreSQL instance. It will automatically create the necessary needs so what is only needed from you are the credential to setup the connection to Mailchimp and your PostgreSQL.
And what about the data model? Blendo has your back too.
2. The Data Model
Mailchimp offers a rich dataset that we can use to perform our own analysis to better understand our customers and to optimize our marketing campaigns. Richness comes with a price, Mailchimp’s data model is quite complex and that would require effort to model it for the database (i.e PostgreSQL). Not in our case!
As we said above, Blendo will sync all of our Mailchimp data into one PostgreSQL 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.
For the purpose of this post we are going to use data from tables:
Reports
The Mailchimp API the reports table contains information about the performance of our campaign and it has the following structure.
In our PostgreSQL, Blendo has created a table called mailchimp_reports. As we do not need all of its columns, we will create an SQL VIEW with the data we are going to need. Out of the mailchimp_reports table, we are going to use the campaign_title, send_time, emails_sent, clicks_click_rate, opens_open_rate, opens_unique_opens, bounces_hard_bounces, bounces_soft_bounces and unsubscribed.
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 );
List Members
In Mailchimp API the members table is a way of grouping together recipients for fine tuning your email campaigns and it is a sub-resource of lists table.
In our PostgreSQL, Blendo has created a table called mailchimp_list_members. We are mainly interested for the email_address, timestamp_opt and status.
3. Dashboard & Email Marketing KPIs
The number of services to build custom dashboards or BI tools to track metrics is huge. For our SQL and charting purposes, we use the tool from our friends at Mode. Mode is a data analysis platform that combines a powerful, web-based SQL editor with charting and sharing tools. After having connected our Mailchimp data to Blendo, and sync them with PostgreSQL, Mode was great for querying and charting our campaign data.
There are a lot of email campaign metrics that someone may track. Which to choose it depends on the company’s goals. Some of the most important and those we are going to see bellow are:
Open Rate
One of the first email campaign metrics is the number of emails sent during an email campaign, the relevant open rate and how it changes over time. If we want to see a trend of the email marketing performance over time, we could write a small query like the following.
SELECT campaign_title,
emails_sent,
opens_unique_opens,
opens_open_rate,
send_time
FROM email_reports_all
ORDER BY send_time ASC LIMIT 10 ;
Let’s see it in the email marketing analytics dashboard at Mode.
Clickthrough Rate
Another 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. Again with the data we have from Mailchimp, a really simple query is:
SELECT campaign_title,
emails_sent,
clicks_click_rate,
send_time
FROM email_reports_all
ORDER BY send_time ASC LIMIT 10;
Let’s see it in our email marketing dashboard at Mode.
Email List Growth
A lot of businesses consider their email list growth as an important metric. As this is not a metric directly derived from Mailchimp, a way to measure the cumulative and monthly growth of our list is the following.
select sub.*, sum(count) OVER (ORDER BY sub.timestamp_opt)
from (
SELECT date_trunc('month', timestamp_opt) AS timestamp_opt, count(*) AS count
FROM mailchimp_list_members
WHERE (status = 'subscribed'
AND CAST(extract(year from timestamp_opt) AS integer) = CAST(extract(year from now()) AS integer))
GROUP BY date_trunc('month', timestamp_opt)
ORDER BY date_trunc('month', timestamp_opt) ASC ) sub
Let’s see it in the email marketing analytics dashboard at Mode.
Bounce Rate (Hard and 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.
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;
Let’s see it in our email marketing dashboard at Mode.
Unsubscribes
Another thing that we may watch are unsubscribes. A short query is the following.
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;
Let’s see it in the email marketing analytics dashboard at Mode.
Building your email marketing analytics for your business
My intention with this post was just to share some ideas on how to measure some of the important email marketing metrics from our Mailchimp data. To wrap up, you will need:
1. A way to get your Mailchimp data into PostgreSQL.
2. Your data model will be optimized and your data ready to be analyzed.
3. Analyze your data with your favorite tool.
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 pull your email campaign data from your favourite email marketing tool, give Blendo a try! Reach out to us and we’ll show you around!
