· Simple Data Foundry Team · Tutorials · 4 min read
Mailchimp campaign reports in Airtable
Mailchimp's reporting lives one campaign at a time. Here is how to get opens, clicks and bounces for every campaign into Airtable, updating on a schedule.
Short answer: Mailchimp shows you one campaign’s report at a time, which makes comparing thirty sends genuinely tedious. Pulling campaign_reports into Airtable gives you one row per campaign — opens, clicks, bounces, unsubscribes — that you can sort, filter and link to the rest of your marketing records.
Why Airtable rather than a spreadsheet
If the numbers are only being read, a spreadsheet is fine. Airtable earns its place when the campaign row needs to become a record — linked to the client it was sent for, assigned an owner, tagged with the offer it promoted, annotated with why the open rate dropped in July.
That is also the gap in most tooling here. Plenty of products will sync Mailchimp into Google Sheets. Far fewer treat Airtable as a real destination, and the ones that do generally live inside a single Airtable base as an extension.
What you can pull
Three resources, each its own job:
campaign_reports
One row per sent campaign. The columns that matter:
| Group | Columns |
|---|---|
| Identity | id, campaign_title, subject_line, type, send_time |
| Audience | list_id, list_name, emails_sent |
| Opens | opens_total, unique_opens, open_rate, last_open |
| Clicks | clicks_total and the rest of the clicks block |
| Problems | hard_bounces, soft_bounces, syntax_errors, unsubscribed, abuse_reports |
The nested blocks Mailchimp returns — bounces, forwards, opens, clicks — are flattened into ordinary columns, so opens.open_rate arrives as open_rate and behaves like a number.
Filter with since_send_time and before_send_time, or pull a single campaign with campaign_id.
Deliberately not included: industry_stats, list_stats, ab_split, ecommerce, delivery_status, and the timeseries/timewarp blocks. The last two are lists of dicts and would break a tabular shape outright.
list_summary
One row per audience — size, growth, engagement. Omit list_id to get every audience at once.
list_members
Subscribers for one audience. list_id is required. Filter by member_status (subscribed, unsubscribed, cleaned, pending, transactional, or all) and by since_last_changed to pull only what moved.
max_members defaults to 5,000 and caps at 50,000 — a deliberate guard against pointing a job at a large audience and waiting.
Setting it up
- Connect Mailchimp. OAuth, once. Mailchimp’s API is per-account — every account sits on its own datacenter endpoint — and the connection resolves that for you.
- Pick
campaign_reportsand setsince_send_timeto however far back you care about. Twelve months is usually plenty. - Choose Airtable, and the base and table.
- Set the write mode to upsert, keyed on the campaign
id.
That fourth step is the one people skip. Mailchimp campaign stats keep moving after send — opens and clicks trickle in for days. In append mode a daily job gives you seven rows per campaign, each a different snapshot. Upsert on id updates the row in place, so the table always holds the current figure.
The full reasoning is in Airtable upsert: stop creating duplicate records.
Field types
Airtable is typed, and this data has a specific trap: open_rate is a fraction, not a percentage. A 42% open rate arrives as 0.42. Store it as a percent field and let Airtable format it, or multiply by 100 as a computed field — but pick one, because doing both is how a campaign ends up reporting a 4,200% open rate on a client dashboard.
send_time should be a date field, not text, or the table will not sort chronologically. If you are unsure about the rest, paste a sample into the Airtable field type mapper.
Scheduling
Daily is right. Campaign stats settle over roughly 72 hours, so an hourly job mostly rewrites the same numbers and spends quota doing it.
If you send weekly, a daily pull with since_send_time set to the last 90 days keeps the table current and the run small. The cron generator will read your schedule back in plain English.
Where else it can go
- Mailchimp to Airtable →
- Mailchimp to Google Sheets → — for charting and pivots
- Mailchimp to Postgres → — for joining campaign performance against revenue in SQL
The last one is the interesting one: campaign reporting only becomes a business question when you can join it to what those subscribers went on to spend.
