Blog · Data quality and reconciliation
A method for reconciling the CRM's account list and revenue to the finance ledger: the join on the account identifier, the five kinds of mismatch that come out, accounts in one and not the other, revenue with no owner, duplicates, timing, and identifier drift, what each one means, who fixes it, and the identity that says when the two systems agree.
The CRM says the company has 3,100 accounts. The ledger says 2,640 customers. Neither is wrong, and until they are joined nobody knows what the difference is made of. This guide sets out the join, the five kinds of mismatch, what each means, and the identity that says when the two agree.
CRM account ↔ ledger customer, on the account identifier, or on a maintained mapping table
Both ways: every CRM account looks for a ledger customer; every ledger customer looks for a CRM account.
| # | Mismatch | Meaning | Owner | Fix |
|---|---|---|---|---|
| 1 | Ledger customer, no CRM account | Revenue nobody owns; nobody is covering it | Sales ops | Create the account; assign an owner |
| 2 | CRM account, no ledger revenue | Prospect, or a duplicate of a buying account | Sales ops | Mark as prospect, or merge |
| 3 | Duplicates on either side | One customer split into two; shares and concentration wrong | Data owner | Merge, with a dated mapping |
| 4 | Timing | Closed-won in March, invoiced in April | Nobody; it is a bridge | Report bookings and revenue separately |
| 5 | Identifier drift | Account re-keyed, merged or migrated | Data owner | Map old to new, dated |
Identifiers only.
Σ ledger revenue attributed to CRM accounts = ledger revenue every ledger customer with revenue has exactly one CRM owner
When both hold, the CRM's coverage and the ledger's revenue describe the same book. Until they do, coverage is a share of the CRM and not of the business.
| Check | Count | Revenue | Type |
|---|---|---|---|
| Ledger customers with no CRM account | 210 | $1.9m | 1 |
| CRM accounts with no ledger revenue | 540 | 2: 480 prospects, 60 probable duplicates | |
| Probable duplicates, ledger side | 31 pairs | $2.4m affected | 3 |
| Closed-won not yet invoiced | 44 | $0.8m | 4 |
| Identifiers changed since last month | 12 | 5 |
Two hundred and ten customers worth almost two million dollars are invoicing and nobody in the CRM owns them. That is the first list, and it is a coverage finding before it is a data finding.
Type 1 first: it is revenue with no owner. Type 3 next: duplicates distort every ratio. Type 5 monthly, as it arrives. Type 2 as hygiene. Type 4 is not worked; it is explained by the bridge.
Reconciled once. The lists come back.
Names as the join. Acme, ACME Ltd and Acme Limited. Identifiers, or a mapping confirmed by a person.
Type 4 treated as an error. Bookings and revenue are different measures. Bridge them.
Type 1 treated as a data problem. It is unowned revenue. Assign first, tidy later.
Mapped once, the CRM export, the ledger and the mapping table produce the five lists and the identity check every month. Covirage builds this from the exports as they are. The metrics governance solution describes the setup, and the territory reconciliation guide covers the checks that follow once the two systems agree on the accounts.
Then a mapping table is the first deliverable: CRM account to ledger customer, one to one, dated. It is built once from names and addresses, confirmed by a person, and maintained. Without it there is no reconciliation, only two lists.
CRM-attributed revenue, meaning ledger revenue attributed to CRM accounts via the mapping, should equal the ledger exactly. CRM's own revenue figures, from closed-won opportunities, are bookings and will not match, and the bookings-to-revenue bridge explains why.
Every month, with the same checks, and the exception lists worked. The first reconciliation is a project; the twelfth is twenty minutes.