The Mailchimp Email KPIs You Can Start Using Today
Posted by on August 17, 2017 Data, Data Analytics, Marketing, Marketing Analytics
Undoubtedly, Mailchimp offers robust KPIs and analytics within their own platform and even compare your numbers to industry standards. This is a great starting point. But, it can be to your advantage to track those Mailchimp KPIs outside of the platform and alongside your other marketing KPIs (like numbers from your Google Analytics or Marketing Automation) to get the full picture.
Just getting started in SQL? No problem. In this blog post, we’ve written out all the SQL code for tracking email subscriptions and campaign performance, so you can start gleaning insights today. All you have to do start using Blendo and Chartio, create a dashboard and copy and paste the SQL.
In this blog post, I’ll cover the top KPIs for Mailchimp and provide a guide to implementing each one into your own dashboards.
Email Subscriptions
New Subscribers per Mailing List
This metric allows you to track the monthly or daily number of new subscribers over a selected period for each of your mailing lists.
The more the subscribers, the further your content may reach, increasing your chances for converting new customers. Furthermore, the continuous increase of your subscribers indicates well structured content being sent that attracts more and more eager subscribers.
Monthly: New Subscribers per List ID
SELECT left(date_trunc('month', timestamp_opt::date)::text, 7),
count(*)
FROM mailchimp_list_members
WHERE date_trunc('month', timestamp_opt) >= {CALENDAR.START}
AND date_trunc('month', timestamp_opt) <= {CALENDAR.END}
AND {LISTID.IN('"list_id"')}
GROUP BY left(date_trunc('month', timestamp_opt::date)::text, 7)
ORDER BY left(date_trunc('month', timestamp_opt::date)::text, 7)
Daily: New Subscribers per List ID
SELECT left(date_trunc('month', TIMESTAMP::date)::text, 7), count(*) AS unsubscribers
FROM mailchimp_unsubscribes
WHERE {LISTID.IN('"list_id"')}
AND date_trunc('month', TIMESTAMP) >= {CALENDAR.START}
AND date_trunc('month', TIMESTAMP) <= {CALENDAR.END}
GROUP BY left(date_trunc('month', TIMESTAMP::date)::text, 7)
ORDER BY left(date_trunc('month', TIMESTAMP::date)::text, 7)
Unsubscribers per mailing list
While it may not be the most favorable thing to track, monitoring the number of recipients who have hit ‘unsubscribe’ from your emails is also an important metric when it comes to an email campaigns’ performance. An increase in unsubscribers over time indicates a potential mismatch between the content and its relevancy with the recipient.
Monthly: Unsubscribers per List ID
SELECT left(date_trunc('day', timestamp_opt::date)::text, 10),
count(*)
FROM mailchimp_list_members
WHERE date_trunc('day', timestamp_opt) >= {CALENDAR.START} and date_trunc('day', timestamp_opt) <= {CALENDAR.END}
and {LISTID.IN('"list_id"')}
GROUP BY left(date_trunc('day', timestamp_opt::date)::text, 10)
ORDER BY left(date_trunc('day', timestamp_opt::date)::text, 10)
Daily: Unsubscribers per List ID
SELECT left(date_trunc('day', TIMESTAMP::date)::text, 10),
count(*)
FROM mailchimp_unsubscribes
WHERE {LISTID.IN('"list_id"')}
AND date_trunc('day', TIMESTAMP) >= {CALENDAR.START}
AND date_trunc('day', TIMESTAMP) <= {CALENDAR.END}
GROUP BY left(date_trunc('day', TIMESTAMP::date)::text, 10)
ORDER BY left(date_trunc('day', TIMESTAMP::date)::text, 10)
Current Subscribers:
SELECT count(*)
FROM mailchimp_list_members
WHERE status = 'subscribed'
This Month: New Subscribers
SELECT count(CASE
WHEN date_trunc('month', timestamp_opt)= date_trunc('month', now()) THEN 1
ELSE NULL
END) AS curr_subs,
count(CASE
WHEN date_trunc('month', timestamp_opt)= date_trunc('month', now()- interval '1' MONTH) THEN 1
ELSE NULL
END) AS pr_subs
FROM mailchimp_list_members
This Month: Unsubscribers
SELECT count(CASE
WHEN date_trunc('month', TIMESTAMP)= date_trunc('month', now()) THEN 1
ELSE NULL
END) AS curr_unsubs,
count(CASE
WHEN date_trunc('month', TIMESTAMP)= date_trunc('month', now()- interval '1' MONTH) THEN 1
ELSE NULL
END) AS pr_unsubs
FROM mailchimp_unsubscribes
List Growth
Inevitably, customers will come and go over time and so it is important not to stick to the same mailing list but instead expand it continuously. List growth monitors the difference between the new subscribers and the unsubscribers over a given period of time. Maintaining a positive and continuously increasing list growth reflects an effective email marketing campaign.
List Growth:
SELECT MONTH,
(subs-unsubs) AS growth
FROM
(SELECT left(date_trunc('month', timestamp_opt::date)::text, 7) AS MONTH,
count(*) AS subs
FROM mailchimp_list_members
WHERE date_trunc('month', timestamp_opt) >= {CALENDAR.START}
AND date_trunc('month', timestamp_opt) <= {CALENDAR.END}
AND {LISTID.IN('"list_id"')}
GROUP BY left(date_trunc('month', timestamp_opt::date)::text, 7)
ORDER BY left(date_trunc('month', timestamp_opt::date)::text, 7))subs,
(SELECT left(date_trunc('month', TIMESTAMP::date)::text, 7),
count(*) AS unsubs
FROM mailchimp_unsubscribes
WHERE date_trunc('month', TIMESTAMP) >= {CALENDAR.START}
AND date_trunc('month', TIMESTAMP) <= {CALENDAR.END}
AND {LISTID.IN('"list_id"')}
GROUP BY left(date_trunc('month', TIMESTAMP::date)::text, 7)
ORDER BY left(date_trunc('month', TIMESTAMP::date)::text, 7))unsubs
WHERE subs.month = unsubs.left
Campaign Performance
Marketers can only be as efficient and effective as their campaigns. While Marketing has been seen as a creative function of the organization, marketers do need to back up their efforts with campaign performance measurement. This section includes the most important metrics regarding engagement rate.
Average Open Rate per List or Campaign
The email open rate is calculated as the division of the number of unique opens by the amount of emails sent, minus the number of bounces. Simply put, given the number of recipients who actually received your email, your open rate is the percentage of people who opened that email. Conveniently, Mailchimp has already calculated your average email open rates for you.
Average Click Rate per List
In alignment with average open rate, average click rate is calculated as the division of the number of unique clicks by the amount of emails sent, minus the number of bounces. Again, Mailchimp has done the work for you and has already calculated your average click rate.
Both, average open rate and average click rate, can be monitored either on a monthly or a daily basis depending on the demands of the analysis you are conducting. Furthermore, you can also choose to monitor this metrics for one or more mailing lists or mailing campaigns.
Monthly: Average Open and Click Rate per List ID
SELECT left(date_trunc('month', send_time::date)::text, 7) AS "Month",
avg(report_summary_click_rate) AS "Average Click Rate",
avg(report_summary_open_rate) AS "Average Open Rate"
FROM mailchimp_campaigns
WHERE date_trunc('month', send_time) >= {CALENDAR.START}
AND date_trunc('month', send_time) <= {CALENDAR.END}
AND {LISTID.IN('"recipients_list_id"')}
GROUP BY left(date_trunc('month', send_time::date)::text, 7)
ORDER BY left(date_trunc('month', send_time::date)::text, 7)
Daily: Average Open and Click Rate per List ID
SELECT left(date_trunc('day', send_time::date)::text, 10) AS "Month",
avg(report_summary_click_rate) AS "Average Click Rate",
avg(report_summary_open_rate) AS "Average Open Rate"
FROM mailchimp_campaigns
WHERE date_trunc('day', send_time) >= {CALENDAR.START}
AND date_trunc('day', send_time) <= {CALENDAR.END}
AND {LISTID.IN('"recipients_list_id"')}
GROUP BY left(date_trunc('day', send_time::date)::text, 10)
ORDER BY left(date_trunc('day', send_time::date)::text, 10)
Daily: Average Open and Click Rate per Campaign
SELECT left(date_trunc('day', send_time::date)::text, 10) AS "Day",
avg(report_summary_click_rate) AS "Average Click Rate",
avg(report_summary_open_rate) AS "Average Open Rate"
FROM mailchimp_campaigns
WHERE date_trunc('day', send_time) >= {CALENDAR.START}
AND date_trunc('day', send_time) <= {CALENDAR.END}
AND {CAMPAIGN.IN('"id"')}
GROUP BY left(date_trunc('day', send_time::date)::text, 10)
ORDER BY left(date_trunc('day', send_time::date)::text, 10)
This Month: Average Open Rate
SELECT avg(CASE
WHEN date_trunc('month', send_time)= date_trunc('month', now()) THEN report_summary_open_rate
ELSE NULL
END) AS curr_open,
avg(CASE
WHEN date_trunc('month', send_time)= date_trunc('month', now()- interval '1' MONTH) THEN report_summary_open_rate
ELSE NULL
END)+0.15 AS pr_open
FROM mailchimp_campaigns
This Month: Average Click Rate
SELECT avg(CASE
WHEN date_trunc('month', send_time)= date_trunc('month', now()) THEN report_summary_click_rate
ELSE NULL
END) AS curr_click,
avg(CASE
WHEN date_trunc('month', send_time)= date_trunc('month', now()- interval '1' MONTH) THEN report_summary_click_rate
ELSE NULL
END) AS pr_click
FROM mailchimp_campaigns
This Month: Emails Sent
SELECT count(CASE
WHEN date_trunc('month', send_time)= date_trunc('month', now()) THEN emails_sent
ELSE NULL
END) AS curr_sent,
count(CASE
WHEN date_trunc('month', send_time)= date_trunc('month', now()- interval '1' MONTH) THEN emails_sent
ELSE NULL
END) AS pr_sent
FROM mailchimp_campaigns
Engagement Funnel
An engagement funnel including information about open and click rates can give you a better overview regarding the steps your recipients take after receiving your emails. More importantly, you can compare the number of those who opened your email to those who showed significant interest in your content by clicking-through.
Engagement Funnel:
SELECT action,
count(*)
FROM mailchimp_report_email_activity
WHERE action != 'bounce'
AND {LISTID.IN('"list_id"')}
AND TIMESTAMP >= {CALENDAR.START}
AND TIMESTAMP <= {CALENDAR.END}
GROUP BY action
Click-through Rate (CTR)
Simply put, it’s the percentage of times people saw your content and clicked-through. CTR is an important KPI for understanding at what extent the ads you publish drive traffic to your website or blog.
You can also gain insight regarding the type of content your subscribers may prefer and tune your campaigns accordingly in the future. For example, noticing that a certain campaign yields high CTR, you can assume that your audience is willing to receive more content on that topic in the future.
Click Through Rate:
SELECT date_trunc('day',send_time),
sum(COUNT)*1.0/sum(emails_sent) AS CTR
FROM
(SELECT campaign_id,
send_time,
emails_sent,
count(*)
FROM mailchimp_report_email_activity
LEFT JOIN mailchimp_campaigns ON mailchimp_report_email_activity.campaign_id=mailchimp_campaigns.id
WHERE action = 'click'
AND emails_sent IS NOT NULL
GROUP BY campaign_id,
emails_sent,
send_time
ORDER BY campaign_id)tmp
WHERE send_time >= {CALENDAR.START} and send_time <={CALENDAR.END}
GROUP BY date_trunc('day',send_time)
ORDER BY date_trunc('day',send_time)
Bounces per List ID
Bounces refer to undeliverable emails and can be either soft or hard. A bounce is considered as soft when it happens temporarily due to a full mailbox or server problem and as hard when it happens repeatedly in cases of wrong or inactive email.
That being said, it is important to track the hard bounces for each of your lists and remove those emails that seem to be inactive in order to maintain the quality of your list.
Bounces per List ID:
SELECT
left(date_trunc('month', timestamp)::text, 7)as month,
count(*)
FROM mailchimp_report_email_activity
where action = 'bounce' and left(date_trunc('month', timestamp)::text, 7) is not null and timestamp >= {CALENDAR.START} and timestamp <= {CALENDAR.END} and {LISTID.IN('"list_id"')}
group by month
order by month
Top 10 Bouncing Campaigns:
SELECT campaign_id AS "Campaign ID",
left(date_trunc('month', TIMESTAMP)::text, 7) AS "Month Launched",
count(*) AS "Number of Bounces"
FROM mailchimp_report_email_activity
WHERE action = 'bounce'
AND left(date_trunc('month', TIMESTAMP)::text, 7) IS NOT NULL
GROUP BY "Campaign ID",
"Month Launched"
ORDER BY "Number of Bounces" DESC
LIMIT 10
This guest blog post was written by Kostas Pardalis, co-Founder of Blendo. Blendo is an integration-as-a-service platform that enables companies to extract their cloud-based data sources, integrate it and load it into a data warehouse for analysis.
