The Mailchimp Email KPIs You Can Start Using Today

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
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
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
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
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
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
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 open and click
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 open and click
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 campaign
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)
average open rate
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
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.

CTR
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.

bounce per 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
bounce by campaign
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.