DEV Community

Sales List Rescue
Sales List Rescue

Posted on Originally published at pureservices.store

Merging Mailchimp audiences? Dedupe your exports first (5 Google Sheets checks)

#mailchimp #emailmarketing #googlesheets #productivity

Mailchimp recommends one primary audience, but plenty of accounts end up with three or four. The catch: Mailchimp dedupes only inside an audience. In its own words, "If the same person's email is in two audiences, we see that as two people" (Mailchimp Help).

The built-in Combine Audiences tool is quick, but it moves only subscribed contacts and doesn't transfer tags, groups, opt-in timestamps, signup sources or GDPR permissions. The safer route is to export every audience, stack the CSVs, clean them, and import back into your primary audience one status at a time. Here are the checks to run on the stacked file in Google Sheets before you import.

0. Before you stack: add two columns to every export

A Mailchimp export can come as a ZIP with separate CSVs for subscribed, unsubscribed, non-subscribed and cleaned contacts. Before stacking, add:

  • Audience: the old audience's name
  • Status: subscribed, unsubscribed, non-subscribed or cleaned (from the file name)

Stack your primary audience first, so its rows win when you dedupe.

1. Clean up email case and spaces, then find same-email duplicates

Case and stray spaces hide duplicates. In a helper column:

=LOWER(TRIM(A2))
Enter fullscreen mode Exit fullscreen mode

Then count each cleaned email:

=COUNTIF($H$2:$H,H2)
Enter fullscreen mode Exit fullscreen mode

Anything above 1 is the same person in two audiences.

2. Catch consent conflicts before they become a compliance problem

The dangerous duplicate is someone subscribed in one audience and unsubscribed in another. Flag emails that appear with more than one status:

=IF(COUNTIFS($H$2:$H,H2,$B$2:$B,"<>"&B2)>0,"CONFLICT","")
Enter fullscreen mode Exit fullscreen mode

Resolve these by hand. Unsubscribed and cleaned addresses should go in as suppression, not as subscribers.

3. Don't lose tags when you merge rows

When two rows share an email but carry different TAGS or Audience values, decide which to keep before you delete the second row. A quick way to see them side by side:

=TEXTJOIN(" | ",TRUE,UNIQUE(FILTER($C$2:$C,$H$2:$H=H2)))
Enter fullscreen mode Exit fullscreen mode

4. Flag invalid and blank emails

Mailchimp won't import broken addresses. A rough format check:

=IF(REGEXMATCH(H2,"^[^@\s]+@[^@\s]+\.[^@\s]+$"),"","CHECK")
Enter fullscreen mode Exit fullscreen mode

This only checks the format. It doesn't verify that the mailbox exists.

5. Split by status before importing

Mailchimp imports one status at a time ("You can only import 1 status at a time"), and unmatched columns are dropped. Filter the cleaned sheet by Status, save each as its own UTF-8 CSV, and tick "Update any existing contacts" on import.

Two warnings from Mailchimp's docs

  • Combining is permanent: "This process is permanent and cannot be undone." Keep every export, including the primary audience's.
  • Pause welcome automations first, because the combine tool "will trigger the automation for all contacts moved from the original audience."

Rather not do it by hand? Stack your exports into one CSV and we'll clean it: same-email duplicates merged, possible duplicates and consent conflicts flagged, every change logged, your original file returned, back within 24h. Quick Clean, $25 for up to 200 rows. We never log in to your Mailchimp account. Full walkthrough of both merge routes: Mailchimp merge audiences guide. Or check your stacked file free in the browser first with the CSV Preflight Checker.

Top comments (0)