Cleaning your CRM data: duplicates, fields and rules

Cleaning your CRM data becomes urgent the day a salesperson finds three “Dupont” records, half the phone numbers missing and a status nobody updates. They go back to their spreadsheet, and the CRM degrades a little more: a CRM is worth only the trust the team places in it.

This guide follows the order we recommend, from the figures that size the problem to the checks that make the result last. If you have not yet chosen your tool, our guide to choosing between Twenty, Dolibarr and Odoo starts from your processes. The guides on migration and operation are in our CRM and customer data section.

Why does a poorly kept CRM cost so much?

Adoption. Data that people doubt is no longer consulted, so no longer updated, so even less reliable. That circle dooms a CRM more surely than a poor choice of software.

Automations and AI. Every automation reads the CRM: a follow-up goes to the wrong contact, an invoice is created twice, a report counts the same customer twice. An AI plugged into it does worse, because it answers with confidence: asked who a customer’s contact is, it picks one of the duplicates without warning. Data quality is, incidentally, one of the reasons why the return on AI in business is slow to arrive.

The GDPR. Article 5 of the regulation requires data to be accurate and, where necessary, kept up to date, and that every reasonable step be taken to erase or rectify inaccurate data without delay. It also requires data to be limited to what is necessary (data minimisation), and kept no longer than necessary. A CRM that is never cleaned drifts a little further from these principles every year.

Step 1: measure before you touch anything

You do not clean blind, and you delete nothing without a safety net.

• A full backup before any bulk change, and a restore that has been tested.
• Reversible operations: a bulk correction is first run as a simulation, with a way of rolling back; as long as any doubt remains, you archive rather than delete.
• A measured audit of the current state, on your own data: number of records per object, share of records with neither email nor phone, records with no owner, number of different values in each list (a freely typed “Sector” field quickly reveals its spelling variants), records with no activity for a long time. These figures will then be used to measure progress.

One last prerequisite, a definition:

Step 2: deal with duplicates

Where they come from

Duplicates always have a way in: repeated imports, a website form that creates a record on every submission, two salespeople entering the same company, and all the spelling variants (“Dupont SARL”, “SARL Dupont” and “Dupont”; a personal email and a work email; a website written with or without “www”). Finding the way in matters as much as the clean-up: otherwise, they come back.

Finding them

Two levels of search:

In Twenty, the email and the domain are unique by default, and the documentation on uniqueness constraints explains how to make other fields unique in the data model settings, for example a customer number taken from your invoicing software.

Resolving them

For each group, you choose the record to keep (the most complete one, or the one carrying the deals in progress), bring over what is useful from the others (notes, phone number, links to deals), then delete the others. Depending on the tool, this goes through a merge function, through the API or by hand: check what yours offers before starting the job.

Three behaviours of Twenty are worth knowing, and the same documentation describes them. Before an import, duplicates inside the file are highlighted, and you can fix or remove those rows before launching the import. An imported row whose email or domain already exists updates the existing record instead of creating a second one: handy for filling gaps, dangerous if the file contains an older version of the data. The third bears directly on the clean-up.

Step 3: fields that mean only one thing

A good part of the mess comes from the model, not from data entry.

• Lists rather than free text for anything used to sort or count (sector, source, status, size): a few clean values give usable reports, free text gives variants.
• Fewer fields. Every field nobody fills in is noise, and one more reason not to open the record. It is also what the minimisation principle of Article 5 asks for: keep only what is useful.
• Required fields, but few. The bare minimum of the usable record. Too many requirements produce fake values (“to be completed”, “0000”), worse than an empty field because they look real.
• One value, one meaning. Any field used to make decisions (source, status, owner) can be checked with one question: does each value refer to a single reality? A “Website” source that mixes quote requests and newsletter sign-ups makes the two indistinguishable the day you want to know where sales come from.

Step 4: what there is no longer a reason to keep

Cleaning also means deleting. In France, the CNIL proposes reference retention periods for customer and prospect files, summarised in its questions and answers on the reference framework for managing commercial activities:

At the end of the period, you can contact the prospect again to ask whether they wish to keep receiving solicitations. Without a positive and explicit answer, their data is deleted, or archived in a database separate from the active data if an obligation justifies it (accounting or tax, for example).

Two clarifications on the same page matter for the clean-up. These frameworks are not binding: you can set other periods, provided you can justify the choice. And a person who has objected to prospecting must not come back through the next import: the CNIL recommends keeping what allows their objection to be respected for at least three years, in an exclusion list used for nothing else.

In practice: a “last contact” field kept up to date, ideally by an automation, and a regular list of the records that have exceeded the set period.

Step 5: rules to make it last

• One person responsible for the data, named, with a deputy. Not “the team”.
• Clear permissions: who creates, who edits, who deletes. The tool’s roles and permissions exist for that.
• Entry points under control: a website form first checks whether the email exists before creating a record, and an import always goes through a file that has been reviewed, deduplicated and compared against the exclusion list.
• A monthly review: the list of probable duplicates, of records with no owner and of records past the retention period. It stays short when the automatic checks have prepared the work.

Step 6: automatic checks

An automation can re-read the CRM every night and produce a short list: new probable duplicates, records created without a required field, values outside the list, records past the retention period. The list goes to the responsible person, through a channel they actually read. These are the principles of monitoring automations applied to data: an alert, a list to work through, no silent correction.

A check is above all there to say what it cannot settle.

A machine that merges on its own what it poorly understands does more damage than the duplicates themselves.

What cleaning does not fix

• A fuzzy process. If nobody knows who follows up a prospect or when to update a status, the data degrades again. The answer is organisational, not software.
• Legitimate similarities. A person who changes company, a group and its subsidiaries sharing a domain: not everything that looks alike is a duplicate.
• What was never entered. Cleaning makes what exists reliable; it does not reconstruct what is missing.

Where to start

Start with the audit of step 1, on your own figures: it tells you the scale of the job before you spend time on it, and it will serve as a point of comparison. If a change of CRM is on the horizon, do the big clean-up then and set the rules from the first day of the new tool: our guide to migrating from HubSpot to an open-source CRM shows where the clean-up fits into the migration.

If you would rather have support, in our CRM offer your existing data is imported, deduplicated and checked: nothing doubtful goes in as it is, and the team starts on a clean base. And if your CRM is Twenty, our report on running Twenty in production says what operating it day to day demands.

Frequently asked questions

Sources

1. Article 5 of the regulation (new tab)cnil.fr
2. Documentation on uniqueness constraints (new tab)docs.twenty.com
3. Questions and answers on the reference framework for managing commercial activities (new tab)cnil.fr