The sales team says the company has 412 active customers. Finance, looking at invoices, says 358. Both numbers are honest, and neither is wrong in a way anyone can point to. The CRM holds people who were quoted and never bought. The invoicing system holds a customer under a legal name that sales knows by its brand. A few companies exist twice in each.
This is not a software problem, and it is not an argument about which system is better. It is a reconciliation problem: two lists describe the same world, and nobody has compared them line by line. This article is about the comparison itself. If you are still deciding which CRM to use, our piece on CRM that works like a glorified spreadsheet covers that question. Here we assume you have both systems and need them to agree.
Why the two lists drift apart
Each system is built for a different job. The CRM is for the conversation: contacts, deals, notes. The invoicing system is for the obligation: legal name, billing address, tax number, payment terms. A customer is created in one at a different moment and by a different person than in the other, often by retyping.
Over time small differences pile up. A company changes its address and only sales hears about it. Someone adds "Ltd" in one place and not the other. A person leaves the customer and a new contact is added, but the invoice still goes to the old email. None of these is dramatic. Together they are why a simple question like "how many customers do we have?" takes an afternoon.
Step 1: Choose a match key
Names are a poor way to match records. "Northwind Trading", "Northwind Trading s.r.o." and "NORTHWIND" are one company to a human and three to a spreadsheet.
Look for a field that both systems hold and that does not change by accident. The best candidates are a company registration number or a tax or VAT number. A customer ID copied from one system into the other also works, if it was entered consistently. Check first how many records in each system actually have the key filled in. If a third of the CRM records have no registration number, the first job is filling those in, and that is useful work in its own right.
Match on the key first. Only for the records left over, compare names and email domains and mark each pair as a probable match for a person to confirm.
Step 2: Export and compare
Export both lists to a spreadsheet with the same columns: key, name, billing address, main contact email, status. Then do a join on the key. You will end up with three piles: records found in both, records only in the CRM, and records only in invoicing.
Do not fix anything yet. The goal of this step is only to see the size of the problem. Write down the counts. A reconciliation that starts with "we found 46 differences in 358 customers" is a manageable project; one that starts with a feeling that the data is a mess is not.
Step 3: Sort the differences
Every difference falls into one of four groups, and each group has a different remedy.
- Missing on one side. A customer invoiced but absent from the CRM usually means an account created by finance for a one-off job. A CRM record with no invoice is often a prospect, and that is fine. Decide which of these should exist on both sides.
- Duplicates. The same company appears twice in one system. Merge them, keeping the record with the longer history, and note which one survived.
- Conflicting values. The key matches, but the address, contact or payment terms differ. This group needs the ownership rule below.
- Different on purpose. The CRM may track a brand while invoicing needs the legal entity. A customer may have three billing entities and one sales relationship. These are not errors. Write them down so that nobody "fixes" them next quarter.
The fourth group matters more than it seems. If you treat every difference as a mistake, you will damage records that were correct.
Step 4: Decide who owns what
The reason conflicts come back is that either system can be edited by anyone. Settle it with a simple table: for each field, one system is the owner.
| Field | Owner | The other system |
|---|---|---|
| Legal name, tax number, billing address | Invoicing | Receives a copy |
| Payment terms, currency | Invoicing | Receives a copy |
| Contacts, roles, notes | CRM | Receives main contact only |
| Account owner, deal status | CRM | Not needed |
The exact table will differ in your business. What matters is that it exists and that each field has one name next to it. When a conflict appears, the owning system wins, and the other is corrected from it. If you have an integration, it should copy in one direction per field, not both ways. Our article on connecting an AI agent to CRM, email and documents makes the same point about integrations: one system at a time, with a clear direction.
An example, with invented numbers
The numbers below are made up to show the method. They do not describe a real client.
A services company with 358 invoiced customers exports both lists. Matching on tax number finds 301 records in both systems. Another 44 records are in the CRM only and 57 are in invoicing only. After a closer look:
- 30 of the CRM-only records are prospects and stay as they are.
- 14 CRM-only records are real customers whose invoices were issued under a different legal name; they are linked by key.
- 41 invoicing-only records are one-off clients and are added to the CRM as inactive; 16 are old accounts that are archived.
- 9 duplicates are found in the CRM and merged.
- 23 of the 301 matched records have a different address; invoicing is the owner, so the CRM is updated.
The result is one agreed number of active customers and a list of 12 items that need a decision from a person. The work took one person about three days.
Common mistakes
- Matching on names. It works for the easy 70% and hides the rest. Use a key.
- Fixing in both systems at once. Without an owner you recreate the conflict the next time someone edits one side.
- Merging blindly. Merging two records deletes history. Check before you merge, and keep a note of what was merged.
- Treating it as a one-off clean-up. The systems drift apart again. Put a short repeat on the calendar, for example once a quarter, and track the number of differences. If it grows, find out which process creates them.
- Leaving finance or sales out. One side knows which differences are intentional. Ask them before changing anything.
What to do this week
On Monday, choose the match key and count how many records in each system have it. On Tuesday, export both lists and join them. On Wednesday, sort the differences into the four groups. On Thursday, agree the ownership table with the heads of sales and finance. On Friday, make the corrections the table allows and write down the open items with a named person for each. Put the next reconciliation in the calendar before you finish. If your wider aim is to find out where such manual rework costs you the most, measuring a manual process in a week is a good companion.
Sources
- Our related articles linked above.
- The worked example is invented to show the method and does not describe a real client, dataset or result.
