[REST Basic Auth] Mailchimp API

This guide provides a step-by-step process to display Mailchimp data in your Confluence using the Mailchimp API with REST Basic Auth.
At the end, you can use the query to display metrics for your Mailchimp campaign.

Preview

Title	Total Deliveries	Open Rate	Click Rate	Bounce Rate	Unsubscribe Rate	Total Opens	Total Clicks	Last Opened	Last Clicked
Newsletter March	2419	45.8%	3.5%	3.7%	1.5%	1207	140	Mar 23, 2025	Mar 19, 2025
Newsletter April	2643	52.9%	4.1%	0.8%	1.8%	1470	350	Apr 25, 2025	Apr 18, 2025

Step 1: Create Mailchimp API Key

Go to Mailchimp - Your API Keys
Create a new API Key or use an existing one

Step 2: Create a Datasource in PocketQuery

Field	Value
Name	`Mailchimp`
Type	`REST Basic Auth`
Base URL	`https://<dc>.api.mailchimp.com/3.0`
Password	your API Key you created in the previous step

The base url for the API is https://<dc>.api.mailchimp.com/3.0/. The <dc> part of the URL corresponds to the data center for your account. For example, if the data center for your account is us6, all API endpoints for your account are available relative to https://us6.api.mailchimp.com/3.0/.

To test whether the connection is working, you can enter /ping in the Test path field and then use the Test connection function to check the connection.

Step 3: Create a Converter in PocketQuery

Field

Value

Name

Mailchimp Campaign Report

Content

CODE

function convert(json) {
  const data = JSON.parse(json);

  const formatDate = (dateString) => {
    if (!dateString) return '';
    const date = new Date(dateString);
    return date.toLocaleDateString('en-US', {
      month: 'short',
      day: 'numeric',
      year: 'numeric',
    });
  };

  const toPercent = (value) => (value * 100).toFixed(1) + '%';

  const table = data.reports.map(report => {
    const { 
      campaign_title,
      emails_sent,
      unsubscribed,
      opens,
      clicks,
      bounces 
    } = report;

    const bounceTotal = bounces.hard_bounces + bounces.soft_bounces + bounces.syntax_errors;

    return {
      'Title': campaign_title,
      'Total Deliveries': emails_sent,
      'Open Rate': toPercent(parseFloat(opens.open_rate)),
      'Click Rate': toPercent(parseFloat(clicks.click_rate)),
      'Bounce Rate': toPercent(bounceTotal / emails_sent),
      'Unsubscribe Rate': toPercent(unsubscribed / emails_sent),
      'Total Opens': opens.opens_total,
      'Total Clicks': clicks.clicks_total,
      'Last Opened': formatDate(opens.last_open),
      'Last Clicked': formatDate(clicks.last_click),
    };
  });

  return table;
}

Step 4: Create a Query in PocketQuery

Field	Value
Name	`Mailchimp Campaign Report`
Datasource	`Mailchimp`
Converter	`Mailchimp Campaign Report`
REST URL	`/reports`

You can find detailed documentation of the Mailchimp API here.