CRM

Customer data deduplication: challenges, tools and tutorial

Deduplication means spotting the data present in several files of the information system and merging it into a single one. Above 5% duplicate data, users complain and reports lead to false analyses. The classic process chains data normalization, similarity scores, an overall score, then record merging.

Maxime Ben Bouaziz
CEO Salesdorado
October 1, 2024, updated November 10, 2026, 8 min read
Meuble de classement en bois aux dizaines de tiroirs étiquetés
★ CE QU'IL FAUT RETENIR
  • A duplicate record appears several times in the same database; duplicated data appears in several databases or files of the information system.
  • Entries made without checking, import tools, integrations that do not query the CRM, bugs: duplicates have several sources.
  • 2% duplicate data is not a disaster. Above 5%, things go wrong.
  • Normalize the data, calculate similarity scores (Levenshtein, Soundex…), combine them into an overall score, then merge.
  • In SQL, plan unmerging rules: merging two contacts that are not identical is one of the biggest risks.

The performance of your marketing & CRM actions depends largely on the quality of your customer databases. You need to clean them regularly. Among other things, that means deduplicating your contacts. What is it? Why does it matter? How does it work? Which tools? That is what this article covers. At the end, as a bonus, we show you a short tutorial for deduplicating your contacts in SQL.

Why is deduplication so important?

Deduplication means identifying the data that appears in several files of the information system and keeping it in a single file by merging. In database management, it mostly means deduplicating contacts.

A CRM-type information system, for example, inevitably tends to generate duplicate contacts in several different places in the system.

The causes of data duplication

For at least 4 reasons:

  • The people who manage the CRM sometimes add contacts or create accounts without checking whether they are already recorded in the system. Even if your CRM sends notifications when it detects a duplicate, not everyone pays attention to them, and the notifications do not always display well on mobile.
  • Data import tools do not always identify duplicates very well.
  • Integrations with external data sources such as website forms, partner portals or email brokers do not always query the CRM data before importing new data.
  • Many human errors or software bugs (bugs in the CRM or in the associated applications / tools) can easily generate hundreds or thousands of duplicates.

The impact of duplicate data

Having 2% duplicate data is not a disaster, as long as it is short-lived data and your tools and processes can detect and fix it. Above 5% duplicates, things go wrong.

From that threshold on, users start complaining and reports lead to false analyses.

Detecting and fixing duplicate data takes time and requires strong methodological rigor.

You need to choose the right duplicate detection tools, use them well, use them to identify the main reasons why your information system generates duplicate data, and, based on that understanding, dry up the main sources of duplicates.

The benefits of data deduplication

An information system with as little duplicate data as possible lets you:

  • Have a cleaner database: deduplication is a key step in database cleaning.
  • Reduce your system’s maintenance and storage costs.
  • Reduce the sending costs of your campaigns and, more broadly, all marketing costs.
  • Improve the customer experience > Nothing is more annoying for a customer than being targeted several times by the same action, campaign or marketing scenario. Deduplication avoids the damage to brand image that comes from sending duplicate messages.
  • Know your customers better by limiting the scattering of data across several tables.
  • Improve the reliability of reports and of the decisions based on them.

The growing number of data sources and touchpoints tends to encourage the creation of duplicate data. When you use dozens of tools, databases and channels, the risk of a customer being recorded in several places in the system is far from negligible. Hence the importance of tackling the issue!

Deduplication vs removing duplicates

Deduplication and removing duplicates are often confused. They are actually two fairly different things.

We speak of a « duplicate record » when a piece of information or data is present several times in the same database or the same file.

We speak of « duplicated data » when the same data is present in several databases or files of the information system. Duplicated data is a major issue in prospecting in particular. When you use several different prospecting files, a contact can easily end up in several of them. In that case, data deduplication keeps you from contacting the same prospect several times.

That said, this distinction should not be taken as absolute. What duplication and duplicate records have in common is that both result in two identical contacts/data in the information system. In a unified information system with a single customer view, removing duplicates and deduplicating are fairly similar operations.

What are the classic approaches to deduplication?

Duplicate data often comes from syntactic differences resulting from typing errors, inversions, the use of abbreviations, or the natural evolution of data over time (moving house, name change, etc.). To handle these issues effectively at scale, you need automation mechanisms.

Here are the main steps of a classic deduplication process:

1. Data normalization and validation

The first step is to restructure and normalize the data to make it easier to compare. This includes, for example:

  • RNVP processing (Restructuring, Normalization, Postal Validation) of addresses
  • Normalization of phone numbers
  • Converting strings to upper or lower case
  • Removing accents and special characters

The goal, as you will have gathered, is to harmonize the data format as much as possible to limit syntactic variations.

2. Calculating similarity scores

The second step assesses the degree of similarity between records, for each type of data (last name, first name, address, email, phone…).

Two approaches are possible:

  • String similarity analysis, using distance algorithms such as Levenshtein or Hamming. These algorithms measure the number of edits required to go from one string to another.
  • Phonetic similarity analysis, which compares data by how it sounds rather than how it is written. Algorithms such as Soundex, Metaphone or Double Metaphone are often used in this case.

These similarity scores identify records that, despite syntactic differences, are highly likely to refer to the same entity.

3. Calculating an overall similarity score

The last step combines the different similarity scores (last name, first name, address…) to get an overall score for each pair of records. This score reflects the probability that two records are duplicates.

Machine learning models, such as random forests, are often used at this stage to analyze the similarity scores and decide the threshold above which two records should be considered duplicates.

Once the duplicates are identified, the next step is merging the records. This requires defining rules to determine, in case of conflict, which data source takes precedence.

Which tool should you use to deduplicate your customer data?

We will now present 2 very good tools for deduplicating your customer data.

DQE Software

Homepage of DQE Software, a Data Quality vendor: “Master your data, boost your web forms”

Founded in 2008, DQE Software is a software vendor specializing in Data Quality management and in setting up single customer repositories. DQE has developed several smart technologies to optimize customer data quality, covering the whole chain from data collection to marketing activation.

DQE’s solutions are available in the cloud (SaaS) or as versions hosted on the company’s own servers. They are designed to interface easily with CRM and ERP tools. With more than 150,000 users and annual growth of +20%, DQE Software is a reference on the market.

Amabis

Amabis homepage: “Evolve your data strategy”

Based in France and Morocco, Amabis is a French player that has been active in the customer data management market for more than 20 years. The company has nearly 500 clients.

Historically, Amabis specialized in the managed hosting of CRM & marketing databases (AmaBase), which gave it strong expertise in customer data management and made it a leading player in Data Quality Management. Since 2015, Amabis has offered a new CRM, AmaCRM, with built-in Data Quality management.

Which service provider should you choose for help with data deduplication?

Customer data deduplication is a strategic issue for companies, but it is also a complex project that requires sharp skills, both technical and business.

It often makes sense to bring in an external provider to carry out this kind of project.

Here are a few criteria to consider when choosing a good provider:

  • Experience and references. Favor a provider that has already run many deduplication projects, ideally in your industry. That is the basics. Do not hesitate to ask for references and client cases similar to your company/project.
  • Methodology and tools. The provider must be able to explain its methodology and the tools it uses clearly. So check that its approach is structured, scalable and suited to your data volumes.
  • Support and skills transfer. Beyond the technical service, it really matters that the provider helps you get to grips with the solution and transfers skills to your teams. A good partner should make you autonomous. That is the end goal.
  • Knowledge of your tool ecosystem. Ideally, the provider should know the tools you use well (CRM, marketing automation tools, data solutions, etc.).
  • The ability to address related issues. A deduplication project is often part of a broader data quality effort. In fact, there are no providers focused solely on deduplication. The providers you will target are data providers, who can address a whole range of data issues of which deduplication is just one component.

What if you learned to deduplicate yourself in SQL?

Did you know you can deduplicate the customer data in your relational database with a « simple » SQL query? We took a few screenshots to quickly explain the logic. The query we are about to show you turns a contacts table into a single customer view (SCV). Here is the result:

Contacts table before and after the SQL query: six Galopin contacts reduced to five rows, contacts 3 and 6 merged on the same email, contact 1 kept separate by its unmerge rule

The first screenshot shows the table before running the SQL query, the second the table after running it.

As you can see, this query takes unmerging rules into account, so that two contacts wrongly associated can be separated afterwards. It is very handy. In this case, it keeps two members of the same family from being merged because they share a last name.

Remember that deduplication is the process of merging the identical contacts in your databases. In this process, one of the biggest risks is merging contacts that are not identical. Unmerging rules act as safeguards.

If you decide to deduplicate your contacts in SQL, we strongly recommend setting up these rules.

Also readDiscover our complete guide to the art of identifying anonymous website visitors in B2B.

The other advantage of this SQL query is that it manages a fuzzy join on the first name by treating accented / unaccented first names and first names with / without a capital letter as the same > Clément = clement. Remember that spelling / syntactic differences are the main source of duplicates.

Here is the query:

SQL deduplication query: three WITH blocks (A, B, C), then the union of merged and unmerged contacts

We took the time to detail each of its parts to help you better understand the logic.

The same SQL query annotated in four parts: partitioning contacts by email and last name, pre-computing the contacts not to merge, merging the others, reconciling merged and unmerged contacts

If you know SQL and relational database management, you can deduplicate your customer data in SQL. That is what we wanted to show you with this short example.

The questions we get over and over.

What is the difference between deduplication and removing duplicates?

A duplicate record is data present several times in the same database or file, while duplicated data is the same data present in several databases or files of the information system. In a unified information system with a single customer view, the two operations are fairly similar.

Why does a CRM generate duplicate data?

The people who manage the CRM add contacts without checking whether they already exist, import tools identify duplicates poorly, integrations with external sources (forms, partner portals, email brokers) do not always query the CRM before importing, and human errors or bugs can create thousands of duplicates.

At what rate of duplicate data should you act?

Having 2% duplicate data is not a disaster, as long as it is short-lived data that your tools and processes can detect and fix. Above 5%, users start complaining and reports lead to false analyses.

What are the steps of a classic deduplication process?

Normalize and validate the data (RNVP for addresses, phone numbers, case, accents), calculate similarity scores per data type, combine them into an overall score for each pair of records, then merge the duplicates according to rules that say which source takes precedence.

Can you deduplicate your contacts in SQL?

Yes, an SQL query on a relational database can turn a contacts table into a single customer view. It is strongly recommended to include unmerging rules, which act as safeguards against merging contacts that are not identical.

★ The conversation, 0 comments

What the community says.

Basic Markdown, moderated within 24 h
Loading…

    Read next