Stop wasting hours cleaning CRM data: build a monthly hygiene cycle

You do not have a data cleaning problem. You have a repeatability problem.

If duplicates, inconsistent fields and broken imports keep coming back, it usually means the business has grown beyond manual hygiene. The fix is not a bigger spreadsheet. It is a small set of standards, a way to measure drift, and automations that apply the same rules every time.

The short version

• Treat data cleaning as a repeatable system: standards, scoring, automated fixes, and a monthly review.
• Start with a small set of high impact hygiene rules: unique keys, normalised fields, and safe merge rules.
• Build your cleanse workflow to be idempotent, and add dedupe memory so retries do not create new duplicates.
• Stop importing blind: validate and standardise in a staging sheet or base, then write to the CRM using stable identifiers.
• Outsource the monthly cycle when you need reliability, auditability, and someone to own failures and edge cases.

Why does this keep happening even after you dedupe HubSpot?

HubSpot can help, but it cannot save you from inconsistent processes.

A few common failure modes we see:

• Imports create new records when your file does not contain the right unique identifier. HubSpot can deduplicate contacts by email address, and can also use Record ID in imports, but if you import without the identifier you intended to use, you can still end up creating new records instead of updating existing ones. See HubSpot’s notes on how it deduplicates during import and the warning about relying on Record IDs without checking for existing records first in Deduplicate records in HubSpot and the identifier mapping details in Understand the import tool.

• Your “duplicate” definition is not the CRM’s definition. HubSpot’s duplicate management focuses on specific objects and matching logic, and it is not the same as “we consider a company unique by Companies House number” or “a person is unique by phone number if email is missing”. You still need an explicit standard your team agrees on.

• Retries and replays create duplicates. If an automation run fails halfway through and you manually re-run it, you can accidentally create a second copy unless the workflow is designed to be idempotent (safe to run twice).

• Sheets and Airtable hide differences. Google Sheets’ `UNIQUE` will treat values as different if there are trailing spaces or other hidden characters, which makes “it looks the same” duplicates persist. Google calls this out directly in the UNIQUE function documentation.

The pattern is consistent: dedupe tools are useful, but they are downstream. You need upstream standards and a monthly control loop.

The minimum standards that prevent most duplicates

You do not need a perfect data dictionary. You need a small number of rules that stop the worst churn.

1) Decide your unique keys per system

Pick one “primary key” per object that your integrations must respect.

Typical examples:

• HubSpot contacts: email (when present), else a custom external ID you control.
• HubSpot companies: domain name is common, but many B2B teams have legitimate multi company per domain cases. If that is you, define an alternative key (for example, Companies House number, or a combination key you generate).
• Airtable tables: a dedicated unique field, not the record name.
• Google Sheets staging: a column called something like `external_id` that never changes.

HubSpot’s docs are clear that imports can use identifiers like email and Record ID. If your file does not contain the Record ID, HubSpot will create a new record rather than updating the existing one, which is how “one bad import” becomes an ongoing mess. The mechanics are in Understand the import tool and reinforced in HubSpot’s own import troubleshooting guidance such as Review and troubleshoot record import errors.

2) Define canonical formatting for a few high value fields

Pick the fields that break reporting and routing when inconsistent:

• `phone` (E.164 formatting, or at least “digits only plus leading +44”)
• `country` (ISO codes or a strict list)
• `lifecycle_stage` and `lead_status` (fixed allowed values)
• `company_name` (capitalisation rules, suffix handling)

Do not try to standardise everything at once. Standardise the fields that drive assignments, campaigns, lists, and revenue reporting.

3) Define safe merge rules (and when not to merge)

Merging is destructive. If you merge the wrong two records, you will be cleaning up audit trails and sales activity for weeks.

Write down rules like:

• Only auto merge when there is a strong match: same email, or same external ID.
• Never auto merge when two records have different owners, or active deals.
• Prefer “survivor” records with the most recent activity.

HubSpot’s duplicate manager helps you review and manage potential duplicates, but it is still your job to decide what “correct” means for your sales process. Start with HubSpot’s overview pages: Deduplicate records in HubSpot and Review and manage duplicate records.

How to score your current data in one afternoon

Before you automate anything, get a baseline. Otherwise you will build a cleanse workflow and still argue about whether it helped.

A simple scorecard works well because it forces you to pick measurable rules.

Here is a practical structure we use:

If you are starting from spreadsheets, run a quick audit in a staging Google Sheet.

• Use `UNIQUE` to spot duplicates, but remember hidden characters can prevent matches, as Google notes in its UNIQUE documentation.
• Add helper columns for normalisation (trim spaces, lower case emails, standardise phone formats).

The output you want is not a perfect dataset. It is a list of the top three hygiene failures by business impact.

What does an automated cleanse workflow actually look like?

A useful cleanse workflow has three properties:

1. It is repeatable (same inputs, same outputs).
2. It is idempotent (safe to run twice).
3. It produces an audit trail (so you can explain changes).

Below is a realistic pattern that works across HubSpot, Google Sheets, Airtable, and the automation tools you mentioned.

Step 1: Stage and normalise (Sheets or Airtable)

Do not write straight into your CRM from random sources.

Create a staging table or sheet that represents the canonical record shape, including:

• `external_id`
• `email_normalised`
• `phone_normalised`
• `company_key` (domain, Companies House number, or your chosen unique key)
• `source_system` and `source_record_id`
• `last_seen_at`

If you are in Airtable, the built in Dedupe extension is useful for reviewing duplicate sets in a controlled way before you do destructive changes.

Step 2: Dedupe memory so replays do not create new records

This is where most “no code” advice falls over. It shows how to remove duplicates within a single run, not how to make the process safe over weeks.

Options by tool:

• n8n: Use the Remove Duplicates node with deduplication history, including storing duplication data across executions and clearing history when appropriate. The mechanics of scope and clearing history are documented in n8n’s Remove Duplicates node docs on GitHub: n8n Remove Duplicates node documentation.

• Make: Use a Data Store as persistent key value storage for dedupe keys and lookups. Make documents data stores in Data stores, and also provides an array function called remove duplicates in Make Functions. The combination gives you both in run dedupe and cross run state.

• Zapier: Use Storage by Zapier to persist small pieces of state between runs (for example, “last processed timestamp” or a set of processed IDs). Zapier’s Storage is documented in Save and retrieve data from Zap workflows using Storage by Zapier.

Step 3: Write to HubSpot using stable identifiers

If you rely on “name match” or fuzzy matching, you will eventually merge the wrong thing.

A safer approach:

• Use HubSpot Record IDs when you already have them.
• Use email for contacts when present.
• If you have records without email, you must use a separate external ID strategy and keep it consistent from day one.

HubSpot’s import tooling and deduplication behaviour is documented in Deduplicate records in HubSpot and Understand the import tool. Those pages also explain why a file with missing identifiers can create new records rather than updating existing ones.

Step 4: Build error handling for rate limits and partial failures

This is the part most teams skip, then they lose trust in the automation.

Make is explicit about storing failed runs as incomplete executions and retrying temporary failures. The relevant documentation is:

• Manage incomplete executions
• Error handlers
• Exponential backoff

The engineering point: you want a workflow where a transient API error does not cause duplicate records, and does not require someone to babysit every run.

A worked example: a monthly hygiene cycle that does not need heroics

Here is a monthly cycle that fits most small and mid sized teams and works with HubSpot plus Sheets and Airtable.

Week 1: Score and agree the top rules

• Run the scorecard.
• Pick three rules to improve this month.
• Decide who owns each rule, including what happens when the rule is violated.

Examples of high impact rules:

• “Every contact must have exactly one external ID once created.”
• “Phone numbers must be normalised before they enter HubSpot.”
• “Companies must have a defined company key. If domain is not unique for us, we use a different key.”

Week 2: Automate the simplest fixes

Start with fixes that are deterministic:

• trim whitespace
• lower case emails
• normalise country values
• remove trailing spaces that break dedupe logic

In Google Sheets, do not rely on what looks identical. Google explicitly warns about hidden trailing spaces affecting duplicates in the UNIQUE function documentation.

Week 3: Handle duplicates with a review queue

Automating merges is where teams get burned.

• Use HubSpot’s duplicate manager for an initial pass. Use Review and manage duplicate records to review pairs in a structured way.
• For anything that requires business judgement, push records into a “review queue” sheet or Airtable view with side by side fields.

If you are using Airtable for review, the Dedupe extension gives you a controlled review experience without writing custom code on day one.

Week 4: Report, and prevent regression

You need two kinds of reporting:

1. Outcome metrics: how many duplicates removed, how many invalid values fixed, how many import errors avoided.
2. Leading indicators: how many new violations entered the system and from which source.

If you already run a lot of automations, this is where a run ledger helps. Swarm Labs’ Time Hive exists for tracking automation runs and hours saved, which makes it easier to show the operational value of hygiene work without guessing.

What should you automate, and what should you keep manual?

A good rule: automate anything deterministic, review anything judgement based.

Automate:

• normalisation (trim, lower case, standard formats)
• identifier assignment (generate external IDs)
• validation (allowed lists, required fields)
• dedupe detection (finding candidate sets)
• safe updates (upsert by stable ID)

Keep manual or semi manual:

• merges where two records contain conflicting truth
• resolving company matching where domain is shared
• deciding the “survivor” record when there are active deals and ownership implications

Your automation should produce a clear queue of “needs decision” items, not try to pretend those decisions do not exist.

A simple build vs buy decision for data hygiene

Many teams start with Zapier or Make, then hit edge cases: retries, idempotency, audit trails, and rate limits.

Use this decision check:

• If you have one source and one destination and the logic is simple, Zapier and Make are usually enough.
• If you need cross run dedupe memory, consider tools with persistent state built in (Make Data Stores, n8n plus a database, or Zapier Storage for smaller workloads).
• If the workflow is business critical, invest in proper error handling. Make’s incomplete executions and retry strategy is documented in Manage incomplete executions, and it is worth using.
• If your data rules are going to change often, treat hygiene as a product: version the rules, log changes, and report on drift.

If you want a deeper tool comparison, we already covered the constraints and trade offs in Zapier vs Make vs n8n: which automation tool fits your business?.

A managed monthly cleanse is usually cheaper than a weekly panic

If you are spending hours each week manually fixing imports and duplicates, you are already paying. You are just paying in senior time, and the work is inconsistent.

A managed monthly cycle makes sense when:

• several people import and edit data, so standards drift
• your CRM feeds billing, forecasting, or renewals work
• you have multiple systems (HubSpot plus Sheets and Airtable) and no single owner

Swarm Labs is a UK software studio in Manchester. We build internal tools and integrations with n8n, Make, Zapier and custom code, and we can set up data hygiene automation plus a monthly cleansing cycle that includes scoring, reporting, and a review queue where merges need human judgement. If you want this run as a system rather than a recurring fire drill, talk to us about your integration.

Sources

1. HubSpot Knowledge Base: Deduplicate records in HubSpot
2. HubSpot Knowledge Base: Understand the import tool
3. HubSpot Knowledge Base: Review and manage duplicate records
4. Google Docs Editors Help: UNIQUE function
5. Airtable Support: Dedupe extension
6. n8n Docs (GitHub): Remove Duplicates node documentation
7. Make Help Center: Data stores
8. Make Apps Documentation: Make Functions
9. Make Help Center: Manage incomplete executions
10. Zapier Help Center: Save and retrieve data from Zaps using Storage by Zapier