Integrating Mailchimp and Airtable for Campaign Reporting

Comparison matrix

ROI metrics

Automating data delivery from Mailchimp to Airtable removes 6 hours weekly of manual analyst data aggregation, recovering implementation costs within one month.

Step-by-step setup guide

1. 1.Provision API Credentials

   Generate an API key in Mailchimp under Account > Extras > API Keys, noting your datacenter prefix (e.g., us19). In Airtable, create a Personal Access Token with data.records:write scope and access targeted to your reporting base.

   export MC_API_KEY="your_mc_key-us19"
   export MC_DC="us19"
   export AT_PAT="patXXXXXXXXXXXXXX.YYYYYYYY"
   export AT_BASE_ID="appXXXXXXXXXXXXXX"

2. 2.Define the Airtable Reporting Schema

   Create an Airtable table named 'Campaigns' with fields: CampaignID (Single line text), Title (Single line text), EmailsSent (Number), OpenRate (Percent), ClickRate (Percent), and SendTime (Date).

   curl -X POST "https://api.airtable.com/v0/meta/bases/$AT_BASE_ID/tables" \
     -H "Authorization: Bearer $AT_PAT" \
     -H "Content-Type: application/json" \
     -d '{"name":"Campaigns","fields":[{"name":"CampaignID","type":"singleLineText"},{"name":"EmailsSent","type":"number","options":{"precision":0}},{"name":"OpenRate","type":"percent","options":{"precision":2}}]}'

3. 3.Query Campaign Report Data from Mailchimp

   Extract performance metrics for sent campaigns using the Mailchimp Reports API endpoint. Filter by status 'sent' and sort by send time.

   curl -X GET "https://${MC_DC}.api.mailchimp.com/3.0/reports?count=10&status=sent" \
     --user "anystring:${MC_API_KEY}"

4. 4.Transform and Ingest Metrics into Airtable

   Format the payload returned by Mailchimp into Airtable's nested 'fields' structure, batching writes in blocks of up to 10 records per payload.

   curl -X POST "https://api.airtable.com/v0/$AT_BASE_ID/Campaigns" \
     -H "Authorization: Bearer $AT_PAT" \
     -H "Content-Type: application/json" \
     -d '{"records":[{"fields":{"CampaignID":"c182b819f2","EmailsSent":12500,"OpenRate":0.284}}]}'

5. 5.Configure Webhook Ingestion for Live Delivery Updates

   Deploy a serverless listener endpoint and register it in Mailchimp under Audience Settings > Webhooks to stream open and click events immediately as they occur.

   curl -X POST "https://${MC_DC}.api.mailchimp.com/3.0/lists/a12b34c56d/webhooks" \
     --user "anystring:${MC_API_KEY}" \
     -H "Content-Type: application/json" \
     -d '{"url":"https://api.yourdomain.com/webhooks/mailchimp","events":{"subscribe":true,"campaign":true},"sources":{"user":true,"admin":true,"api":true}}'

Webhook payload example

{
  "type": "subscribe",
  "fired_at": "2023-10-24 14:32:00",
  "data": {
    "id": "8a73b4512e",
    "list_id": "a12b34c56d",
    "email": "operations@example.com",
    "email_type": "html",
    "status": "subscribed",
    "ip_opt": "198.51.100.42",
    "web_id": 4819203,
    "merges": {
      "EMAIL": "operations@example.com",
      "FNAME": "Alex",
      "LNAME": "Morgan"
    }
  }
}

FAQ