Customer data cleansing and deduplication: how to

Why does a customer database get messy?

A customer database almost always gets messy, even if you handle it carefully. The causes are usually the same:

• Customers come in through different routes: web form, phone, online shop, accounting.
• People enter their details slightly differently each time: ‘Jansen BV’, ‘Jansen B.V.’ and ‘jansen bv’.
• Data sits in several systems that don't talk to each other.
• People move house, change jobs or email addresses, and nobody updates it.
• Colleagues create a new customer because they couldn't find the existing one.

What does poor data quality cost you?

Duplicate and incorrect data seem harmless, but they cost a little every day. You spend longer looking for the right customer. You send a newsletter twice to the same person. A quote goes to an old address. Your overview of revenue per customer is wrong, because one customer appears under three names.

And if you use AI, for example to answer customer questions or create reports, that AI simply takes over the errors. So cleaning up is often the first step before you automate further.

Customer data cleansing in six steps

• Step 1: make a backup. Export everything before you change anything, so you can go back if something goes wrong.
• Step 2: decide what you need. Which fields do you really use? Fields nobody uses can often be dropped.
• Step 3: standardise the formatting. Same notation for phone numbers, postcodes, countries and legal forms. Tidy capitals and spaces.
• Step 4: find duplicate records. First the easy ones (exactly the same email address or KvK number), then the tricky ones (similar-looking).
• Step 5: merge according to fixed rules. Decide in advance which record ‘wins’, for example the most recent or the most complete.
• Step 6: clear out what needs to go. Customers you are no longer allowed or required to keep, you delete or anonymise.

Tip: don't start with the whole database at once. First take a small part, for example all customers from the past year, and test your rules on that. If the result is right, apply the same rules to the rest. Write your rules down, so you can use them again for the next clean-up.

How do you find duplicate customers?

Deduplication is the hardest part. You are looking for records about the same person or company, even if they are not exactly the same. Useful attributes to compare on:

• Email address: often the best key for people.
• Chamber of Commerce (KvK) number or VAT number: the best key for companies, if you have it.
• Phone number, after standardising the notation.
• Postcode with house number, together with part of the name.
• Company name without legal form and punctuation.

In Excel, sorting, conditional formatting and the duplicate values function get you a long way with exact duplicates. Many CRM packages also have a built-in function to find and merge duplicate records. Still working a lot in separate spreadsheets? Then also read replacing Excel with software.

Deduplication with AI and smart matching

With ‘almost identical’ records, a simple formula falls short. That is where fuzzy matching helps: software that calculates how closely two names or addresses resemble each other. AI can also help with tricky cases, such as a company that appears under both a trading name and an official name, or an address written in two ways.

A sensible approach: let the software suggest candidates with a score, merge the certain cases automatically and have a person review the doubtful ones. That way you avoid two different customers accidentally becoming one. Do watch where your data goes. Don't just paste a customer database into a free AI tool.

Customer data and GDPR

Cleaning up is also good for your privacy policy. GDPR (AVG in Dutch) requires, among other things, that you don't collect more data than necessary, that data is accurate and that you don't keep it longer than necessary. A few practical points:

• Agree on retention periods and stick to them. Statutory periods often apply to your financial records.
• Delete data of people who are no longer customers and whom you no longer need to keep.
• Make sure you can quickly find someone's data if that person asks for access or deletion. With one clean database that is much easier.

This is not legal advice. If you are unsure about periods or rules, ask a lawyer or check the Dutch Data Protection Authority (Autoriteit Persoonsgegevens). You can read more background in AI and GDPR.

How to stop it becoming a mess again

• Choose one system as the source for customer data and let other systems read from it.
• Connect your systems, so you don't retype data. For example your CRM with your accounting; see connecting your accounting.
• Validate data on entry: a valid email address, a postcode in the right format, required fields.
• Have the system warn you when a customer seems to exist already.
• Schedule a regular check, for example a short check for new duplicates every quarter.

Help with cleaning up your customer data

A one-off clean-up of a small file is often something you can do yourself. If it involves thousands of records, several systems, or you want it to stay right from now on, we can help through AI & automation. We clean up, merge using clear rules and build integrations and checks so it stays clean. Sometimes a custom CRM is the logical next step. A project starts from €1,799 excl. VAT (€2,177 incl. VAT), one-off.

Want to know how your customer data is doing? Request a free quickscan. In 30 minutes, online or in person, we discuss your systems. Afterwards you get short written advice with a price indication, with no obligation.

Frequently asked questions