Decide what “duplicate” means

Exact stored strings, domain-case normalization, provider-specific aliases, shared family mailboxes, and changed account addresses are not interchangeable. Domain names are case-insensitive, but the local part’s treatment can depend on the receiving system. Do not strip plus tags or dots globally based on one provider’s behavior.

Define a stable internal contact/person identifier where the product truly knows identity, and a separately normalized address lookup policy. Preserve the original address values and sources. Record collisions for review instead of silently choosing one row.

Convert states into events

Represent signup, confirmation, unsubscribe, complaint, bounce, re-subscribe, address change, import, and deletion as timestamped scoped events with provenance. Then calculate current send eligibility from those events. A last-write-wins contact spreadsheet loses the reason and can let an older import override a newer unsubscribe.

When combining rows, retain:

  • every applicable suppression and complaint;
  • consent purpose, brand, notice version, and confirmation;
  • acquisition source and time;
  • provider event identifiers;
  • account-address history where policy allows;
  • conflict and review status.

Do not broaden one list’s consent to another because the address matches.

Stage the merge

Run a read-only report first. Count exact matches, normalization-only matches, conflicting consent, suppressed-versus-active pairs, shared-account indicators, and records requiring human review. Sample each category with restricted access.

Create a reversible mapping from old IDs to the proposed canonical record. Rebuild audience eligibility in staging and assert that no applicable suppression is lost. Ensure downstream providers receive idempotent updates and old audience exports cannot reintroduce duplicates.

Verify after cutover

Compare subscriber and suppressed counts by list, send eligibility diffs, unsubscribe endpoints, complaint imports, and account email-change workflows. Monitor support contacts and provider events. Keep a rollback path that restores mappings without resending campaigns.

Set retention for old identifiers and merge evidence. The project succeeds when duplicate delivery falls while permission history remains explainable. A smaller database is not success if the cleanup turns “we do not know” into consent or revives an address that had said no.