Finding and merging duplicates: customer and product records

The most common cause is mundane: someone creates a new customer because they cannot find the existing one. Not because it is missing, but because it is spelled differently. The search box expects the beginning of the name, while the person searched for the second part of it. The legal form was written out once and abbreviated the other time. The street name appears once with a full stop and once spelled out. In that situation creating a new record is the sensible reaction, because the job has to move on. The duplicate therefore appears exactly where someone is doing their work, not where someone is being careless.

The second major source is data migration. At every system change, every merger of two databases and every import from a list, records meet that describe the same reality but are built differently. If the import contains no check, a few minutes create more duplicates than years of manual work. Anyone planning a legacy migration should therefore place the clean-up before the migration rather than after it, because in the target system the records are already linked to new transactions and are much harder to separate.

The third source is separate entry channels. A contact form on the website creates a prospect, the shop counter records the same person at the till, the field service keeps its own list. As long as these channels do not look at the same database, the duplicate is a property of the system and not a question of discipline. With product records there is an added twist: purchasing, warehouse and sales use different names for the same part, often for good reasons. A duplicate is rarely a typing mistake and almost always the result of a process without a checkpoint.

Every clean-up starts with a measurement, and it is simpler than it sounds. The goal is not a figure to two decimal places but an order of magnitude: are we talking about a few dozen cases or about a structural share of the database? That answer decides whether an afternoon is enough or whether a tool has to be built. Build groups based on unambiguous attributes and count how many records fall into each group. A group is not yet a duplicate list, it is a candidate list.

Take a small sample from each group and actually look at the records. You will quickly see which rules carry weight and which produce too many false hits. The same phone number is a strong attribute for private customers and a weak one for business customers behind a central switchboard. The same postcode with the same house number works well in rural districts and badly in office buildings. Write these observations down, because they are the basis for the matching rules that follow.

The matching itself does not compare whole records but individual fields, and it returns not a yes or no but a measure of how close two values are. Two names differing in a single letter are very similar; two names with swapped parts are similar as well, even though no character sits in the same position. One simple method counts how many character changes would be needed to turn one value into the other. Another compares how a name sounds, which helps with names taken down over the phone. A third splits both values into short character sequences and measures the overlap, which works well against swapped word order.

These field scores are then combined with weights. Not every field carries the same weight: a matching VAT identification number is a strong argument, a matching postcode on its own means almost nothing. Conversely, a single differing field must not discard a match immediately, because that is exactly where duplicates differ. What helps is a small written set of rules stating, for each field, how strictly it is compared and how much it counts. That set belongs in your process documentation, otherwise nobody remembers after the first run why a case is on the list.

Computationally, comparing every record against every other record is too expensive on larger databases. Grouping therefore comes first: only records that agree on a coarse attribute, for example the first letter of the normalised name together with the postcode, are compared at all. This pre-grouping cuts the effort considerably, but it has a price: choose a grouping attribute that differs within a duplicate pair, and you will never find it. In practice it helps to run two or three groupings one after another and combine the results.

With customers there is usually at least one strong attribute: an address, an e-mail address, an identification number. With products that is often missing. Two records for the same component differ in wording, in the order of attributes, in the unit and sometimes in the pack size. A description made of material, dimension, colour and standard can be written in many orders, and every one of them has been used somewhere in the business. Pure text matching therefore carries less far for products than for customers.

What does carry weight are external numbers: the manufacturer's part number, the supplier's number, a standard designation, a drawing number. These fields exist in many databases but are poorly maintained, because nobody needs them day to day. That is exactly why it pays to fill them in during the clean-up: they are both the best matching attribute and the best prevention against future duplicates. Where they are missing, only a combination of normalised description, unit and product group helps, and those results almost always belong in the review class.

A matching method can be very good at finding candidates and still be entirely unsuited to deciding on its own. The reason lies in the nature of the two possible errors. If a genuine duplicate is not found, the situation stays as it already was: inconvenient, but no new damage. If two different records are merged, a new and wrong state is created, and the transactions of two different customers now hang on one record. These two errors are not equally severe, so they must not be treated equally.

Particularly exposed are cases that look very similar to a matching routine but belong apart in business terms: two branches of the same company at the same address, two people with the same name in the same building, father and son in a family firm, a property company and an operating company sharing an address, a site address that temporarily coincides with a company address. In all of these the similarity is high and the right decision is still to keep them separate. Merging is a business decision, not a technical operation.

Start with the data that causes the greatest damage, which in most businesses means customer records, because invoices and communication hang on them. Work through the certain matches there first, because they are quick and produce a visible result. Only then move into the review cases, and only then into the product data. The vast majority of companies in Germany are small and medium-sized enterprises (Federal Statistical Office), and there is no data quality department there: an approach demanding more than a few hours per week will not be sustained.

Name an owner per data set. Not as an extra box on the organisation chart, but as a clear answer to the question of who decides, in case of doubt, whether two records are the same customer. Without that person review cases circulate and the list grows faster than it is worked through. For products the ownership is usually shared between purchasing and the warehouse; in that case an agreement is needed on who has the final say.

Finally, measure the same things you measured at the start: number of records, number of candidate groups, number of open review cases and the number of new records created per month. The last figure is the most interesting one, because if new records drop noticeably after the check is introduced, the duplicate problem really was a process problem. Record the baseline and the rules so that nobody starts from scratch next year. Where the clean-up is part of a wider tidying-up effort, it belongs inside a process analysis rather than in an isolated project of its own.