CRM data quality: why duplicates come back after the clean-up

Scope: This article is about customer information in day-to-day CRM work, using Dynamics 365 Sales and Customer Service on Dataverse as the example. The principles apply to other CRM solutions too. If several systems need to share one authoritative version of each customer, that is an architecture question, which we cover in CRM is not a master data register.

Why doesn't a one-off clean-up last?

A clean-up is worthwhile. It merges duplicates, fills gaps and deactivates contacts who have left. But it does not change how information gets in. The next day a salesperson registers a company that already exists, a trade-show list is imported on top of existing contacts, and the web shop integration creates a new customer instead of updating the existing one.

Reality changes too. People change jobs, companies merge or close, and addresses change. Information then goes out of date without anyone having done anything wrong. Data quality therefore needs ongoing maintenance, much like the customer relationships themselves.

The table shows the four entry points, the errors they typically cause and measures that are proportionate to the risk.

For Norwegian business customers, the organisation number is the best key, because it identifies each legal entity uniquely. Branches and other operations at separate addresses are registered as sub-entities with their own organisation numbers. If you keep several accounts per legal entity, for example one per branch, the organisation number cannot be a unique key on its own. The Brønnøysund Register Centre offers an open API to the Central Coordinating Register for Legal Entities, so the name and organisation number can be looked up when a customer is registered instead of being typed in by hand, and changes in the register can be picked up later.

Be careful about making many fields required. Microsoft itself warns that people may enter incorrect data simply to save a record when they do not have the right information to hand. Instead, make the field required only when the situation calls for it. A business rule on the form can require the field once a particular value is set, for example when a prospect becomes a customer. If you use a business process flow, the field can be required at the stage before a quote.

Integrations can create duplicates without anyone noticing

When a user saves a record that resembles an existing one, Dynamics 365 shows a warning listing the possible duplicates. The user can then merge the records or save anyway. An integration gets no such warning. When records are created or updated through the Web API or the SDK for .NET, duplicate detection is suppressed by default. The integration has to request the check for it to apply.

The same goes for required fields. A column set to Business Required in Dataverse is enforced in the app, but not when the record is saved through the APIs. There the platform enforces only the columns the system itself requires.

The robust approach is to give each integration a stable key from the source system, such as the customer number in the ERP system or the organisation number. In Dataverse this is called an alternate key. It ensures that no two records can have the same key value, and it lets the integration use upsert: update the record if it exists, create it if it does not. There are two caveats: uniqueness is not enforced when the key column is empty, and keys containing certain special characters cannot be used for updates or upserts through the API.

Also agree which system maintains which fields. If both the ERP system and the CRM can change the invoice address, the integration will sooner or later overwrite a correct address with an old one. There is more on this in our guide to connecting ERP and CRM.

A hypothetical example

A wholesaler cleans up its customer database before the sales team starts using Copilot, merging a large number of duplicates. Three months later many of them are back. The cause is the web shop integration. It creates a new contact for every order placed by a customer who is not signed in, and neither duplicate detection nor a key stops it. What helps is having the integration look up an existing contact by email address before it creates a new one, and making one person responsible for merging whatever still gets through. Another clean-up on its own would have produced the same result three months later.

This example is hypothetical and put together to show the pattern. It does not describe any particular customer.

Decide which information has to be right

Not every field matters equally. Start with the information used for something where an error is costly: invoicing, consent and mailings, management reporting and customer follow-up. A field nobody uses needs no checks, and can often be removed from the form.

Copilot makes data quality more visible. Copilot in Dynamics 365 Sales only retrieves information from records and files the user has access to, and its answers are no better than the records they draw on. If a customer's history is split across two accounts, a summary of one of them may show only half the picture. Microsoft's guidance for Sales implementations lists data quality and measures against duplicates among the points to consider when preparing the solution for Copilot. What Copilot does in practice is covered in Copilot in Dynamics 365 Sales.

Who is responsible for keeping customer information up to date?

In Dynamics 365, account and contact records have an owner. That means the record is assigned to a user or a team, which among other things controls access. It is not the same as someone being responsible for the information being correct. Without that clarity, merging duplicates can easily become a task with no clear owner. Microsoft describes the same problem in a reference architecture for duplicate contacts in Dynamics 365 Sales.

A simple arrangement has three roles:

• The people who use the information every day, often salespeople or case handlers, correct errors when they spot them and flag possible duplicates.
• A named person with the time and access merges duplicates and works through the results of the scheduled duplicate detection jobs.
• The platform owner decides the rules: which fields are required and when, which duplicate detection rules apply, and which system maintains which fields.

In a smaller organisation, two of the roles may sit with the same person. What matters is that someone has been given the task and has time set aside for it. How the platform owner's responsibilities fit with the rest of governance is covered in Enterprise CRM with Dynamics 365.

When duplicates keep coming back, it is often because one of the entry points has no one responsible for it.

Cartagena, from work on CRM solutions in Dynamics 365

What Dynamics 365 and Dataverse provide

The tools are already in the platform. They support the way you work, but they do not replace it.

Duplicate detection rules catch the obvious cases, such as the same email address, the same organisation number or the same first characters of a name, depending on the criteria you set up. “Olsen Bygg AS” and “Olsen Bygg” can slip through. Scheduled jobs, and someone who works through the results, catch more of the rest.

Track a few metrics every month

You do not need a separate quality programme to know whether things are moving in the right direction. Choose three or four metrics linked to the information that matters most, and follow them from month to month:

• the share of active business customers without an organisation number
• the number of possible duplicates in the latest scheduled duplicate detection job
• the number of customers with open opportunities but no active contact
• the number of mailings returned because the address does not exist

The direction tells you more than the level. If the numbers start rising again after a clean-up, one of the entry points is still letting new errors in. The metrics can sit in a simple report alongside the sales figures. See Power BI + Dynamics 365 Sales.

When do you need more than good housekeeping in the CRM?

The measures above keep the CRM data in order for the people who use the CRM. If the ERP system, web shop, reporting and CRM are to share one authoritative version of each customer, you need a separate layer that matches and merges records across systems. That can be a master data tool or Customer Insights - Data. When that pays off, and why we think the CRM should rarely be that layer, is covered in CRM is not a master data register.

If you are about to change solutions, the migration is a good time to clean up, because the data has to be reviewed anyway. See our guide to migrating from Excel to Dynamics 365, and how we approach Dynamics 365 implementation.