Connect Mailchimp to PostgreSQL
Pull campaign performance, audience summaries and subscriber lists out of Mailchimp on a schedule. Opens, clicks, bounces and unsubscribes land as flat columns, ready to chart or join against revenue.
What you can pull from Mailchimp
Mailchimp exposes 3 objects you can pull, each as its own job. Every run pulls the records into a flat table, so nested fields arrive as ordinary columns that PostgreSQL can sort, filter and total without further work.
Campaign reports
One row per sent campaign, with the opens, clicks, bounces and forwards blocks flattened into columns.
| Column | Type | Notes |
|---|---|---|
| id | string | The stable upsert key |
| campaign_title | string | — |
| subject_line | string | — |
| send_time | datetime | — |
| emails_sent | integer | — |
| open_rate | float | A fraction, not a percentage: 42% arrives as 0.42 |
| unique_opens | integer | — |
| clicks_total | integer | — |
| hard_bounces | integer | — |
| soft_bounces | integer | — |
| unsubscribed | integer | — |
Audience summaries
One row per audience - size, growth and engagement. Omit list_id for every audience at once.
Subscribers
Members of one audience, filtered by status. list_id is required.
Options you can set
- resource
- Which Mailchimp data to pull — defaults to
campaign_reports - since_send_time
- Only campaigns sent at or after this
- member_status
- subscribed, unsubscribed, cleaned, pending, transactional or all — defaults to
subscribed - max_members
- Cap on subscribers per run, up to 50,000 — defaults to
5000
How it lands in PostgreSQL
PostgreSQL accepts append, replace, upsert writes. Upsert is the one that matters on a schedule: it merges on a key you choose, so matching rows are updated in place and only genuinely new records are created. Append instead, and a daily job multiplies your data.
Upsert uses key_columns as the conflict target.
- Rows are written in chunks of 1,000 by default, configurable up to 50,000.
- Replace truncates the target table before writing.
Authentication
You connect Mailchimp once through OAuth and the credential is stored server-side, never pasted into a cell or a formula. Only the scopes the connector actually calls are requested — the table below lists each one and what it is for.
Keeping runs incremental
Yes. since_send_time for campaigns, since_last_changed for subscribers, so a run fetches only what moved.
Pair a narrowed read with an upsert write and a re-run costs almost nothing: the rows it already knows are updated, and nothing is duplicated. That combination is what makes a frequent schedule affordable.
Limits and pacing
These are the provider-side ceilings that shape a schedule, not ours. Knowing them up front is the difference between a job that runs quietly every morning and one that starts failing the week your data grows.
| Limit | Value | Applies to |
|---|---|---|
| Pages per run A safety net; each resource also stops on a short page. | 200 | Mailchimp |
Common gotchas
Most of what goes wrong with a scheduled sync is not a bug — it is a detail of how one side behaves that nobody wrote down. These are the ones that come up for this pair.
open_rate is a fraction
A 42% open rate arrives as 0.42. Store it as a percent field and let the destination format it, or multiply by 100 - but not both, or a client dashboard ends up reporting 4,200%.
Campaign stats keep moving after send
Opens and clicks trickle in for roughly 72 hours. Upsert on the campaign id so the row updates, rather than appending a new snapshot every run.
Some report blocks are deliberately excluded
industry_stats, list_stats, ab_split, ecommerce and delivery_status are outside the reporting scope, and timeseries/timewarp are lists of dicts that would break a tabular shape outright.
Set either table or query, never both
The config rejects both being set, and rejects neither - caught when the job is saved rather than on its first run.
Set it up in four steps
- 1
Connect Mailchimp
Connect your Mailchimp account once. Mailchimp's API is per-account - each sits on its own datacenter endpoint - and the connection resolves that for you.
- 2
Connect PostgreSQL
Host, port, database, user and password, stored encrypted.
- 3
Shape the data
Select the columns you want, filter rows, cast types and add computed fields. Everything else is dropped before it reaches the destination.
- 4
Schedule it
Run once, or on a cron. Every run refreshes PostgreSQL with the latest Mailchimp data.
Do I need an add-on or extension for this?
No. The job runs server-side and writes into PostgreSQL through its API, so there is nothing installed in the destination itself. It keeps running when nobody has the file open, and a copy of the file does not need anything installed to work.
How often does the data refresh?
On whatever schedule you set with a cron expression — hourly, daily, or a specific time on specific days. Each run pulls the latest from Mailchimp.
Do I need to write any code?
No. You connect both sides, map the fields in a wizard and set a schedule. Computed fields accept small expressions — abs, round, min, max, len — but there is nothing to host or maintain.
Why is my open rate showing as 0.42?
Mailchimp returns rates as fractions. Format the destination column as a percent, or multiply by 100 - but not both, or the number comes out as 4,200%.
Can I pull more than one audience at once?
For audience summaries, yes - omit the list ID and you get one row per audience. Subscriber lists are per-audience and need a list ID.
Can I run my own SQL rather than reading a table?
Yes. Set a query instead of a table and aggregate in the database, which is usually far cheaper than moving raw rows and aggregating downstream. One statement only - multi-statement queries are rejected.
Does it need a superuser?
No. A user with SELECT on what you are reading is enough for a source; a destination needs INSERT and, for upsert, UPDATE on the target table.
Is the Mailchimp connector free to use?
You can connect Mailchimp and start syncing on the free plan.
Other sources into PostgreSQL
Related reading
Airtable upsert: stop creating duplicate records
Appending on every run turns a tidy base into six copies of every row. Here is how upsert works in Airtable, and how to choose a merge key that holds up.
Get data into Google Sheets without an add-on
Every major Sheets data tool is an add-on you install, authorise and keep updated inside the spreadsheet. Here is how to schedule the same imports without one.
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.