Why one customer shows up under several names in your reports

Take a hardware wholesaler called Hartley Traders. One clerk types Hartley Traders Ltd. Another types Hartley Trdrs. A third adds the Leeds branch code. Last year’s account, closed after a migration, still holds a balance. A well-run sales ledger can still hold forty versions of one buyer after ten years of branch openings, mergers, rebrands and counter staff typing from memory.

To the software these are five parties. Each has its own ledger, credit limit and history. No report is wrong, because each one is faithfully adding up what it was given.

The damage shows up one level higher, where you ask who your biggest customer is or how much a group owes you in total. Ask for the top twenty customers and the answer changes with the grouping: customer code, tax number or the name typed on the invoice.

Credit control suffers first. A buyer with a limit set per account can run up an overdue balance on each of four codes while every one stays inside its own limit. Total exposure is invisible until someone adds it by hand. A credit controller who sees four slightly overdue accounts treats them as small problems. The single combined balance would trigger an immediate stop on new orders.

Top customer lists lie in the opposite direction. A group that buys heavily through several names ranks lower than a single mid-sized buyer, so your best relationship may sit on page two.

Receivables ageing, discount slabs and rebate calculations also split. A volume rebate earned on the combined total is never triggered on any single row. Rebate schemes, annual price agreements and tiered discount slabs all assume one buyer, so every split quietly costs you either margin or goodwill.

Start with exact keys. Tax registration number, phone, email domain and bank account are strong signals. Two records sharing a registration number are the same legal party until proven otherwise. Two records that share a company registration number, an IBAN or a bank sort code almost always describe the same legal party, whatever the names say.

Then add fuzzy matching on names. Strip punctuation and suffixes such as Inc, Ltd and Co, lower-case everything, and compare what remains using edit distance. Score each pair and send mid-confidence pairs to a person. Library tools such as Python’s rapidfuzz, or the matching functions in most database engines, can score thousands of name pairs in seconds and rank the likely duplicates first.

Never merge automatically on a name match alone. Two firms called Apex Supplies can sit in different cities and owe you different amounts.

A customer master holds one row per real party with a permanent ID. Branches, ship-to addresses and old account codes hang beneath it as children. Every report rolls up to the parent. Where a group has real subsidiaries, model them as separate legal parties linked to one ultimate parent, so you can report both the single entity and the group exposure.

Keep a crosswalk table that maps each old code to its master ID. This preserves history, so last year’s invoices still resolve after the merge.

Close the door at source. Make the order screen search the master before it lets anyone create a new customer, and require a registration number for new entries. The same master should feed the order screen, the invoice template, the receivables report and any dealer portal, so a correction made once appears everywhere.

Export the customer list with total sales and open balance for the last two years. Sort by normalised name and scan the top two hundred by hand. You will spot clusters quickly.

Merge the sure ones, queue the doubtful ones, and rerun the top customer and ageing reports. Compare before and after. Keep the merge log, listing the old code, the new ID, the person who approved it and the date, because an auditor will eventually ask how a balance moved.

Then assign one person to approve every new customer record. That small gate stops most of the splitting.