Blog · Data quality and reconciliation
The difference between an identity, a sum that must be true by construction, region equals team equals person equals account equals the ledger, and a reconciliation, the work of finding why it is not and fixing the source, why the identity is checked on every upload and the reconciliation is done on the exceptions it lists, the three causes that account for most failures, and why a number is never adjusted to make an identity hold.
An identity is a sentence with an equals sign that has to be true. A reconciliation is what happens when it is not. Companies that confuse them either adjust totals until they match, or spend a week a month making them match by hand. This guide sets out both, the rule between them, and the three causes behind most failures.
| Identity | Reconciliation | |
|---|---|---|
| Is | A sum that must hold by construction | The work of finding why it does not |
| When | Every upload, automatically, in seconds | On the exception list the identity produces |
| Produces | Pass, or a list of rows | Fixed source data |
| Never | Adjusted to pass | Done by changing the total |
| Kind | Example |
|---|---|
| Roll-up | Σ region = Σ team = Σ person = Σ account = ledger |
| Decomposition | bookings = Σ billings = Σ revenue over the term; open pipeline = clean + flagged; obligations = mapped + in progress + unmapped |
| Cause | Looks like | Fix at source |
|---|---|---|
| A duplicate | Account under two reps; a customer twice under two identifiers; a line on two credit splits | Dated mapping; confirmed merge; split corrected |
| A missing mapping | Revenue with no owner; a ship-to with no facility; a SKU with no line | The mapping row, dated |
| A period cut wrong | Ledger on the month, activities on 30 days; invoice date against ship date | Re-export on the same cut |
The identity is checked. The source is fixed. The number is never adjusted.
| Identity | Result |
|---|---|
| Σ reps' revenue = ledger | $12.04m vs $12.00m: fail by $41,000 |
| Row | Cause |
|---|---|
| Account 2207 under R-04 and R-11 from 1 July | Duplicate: the move record has no end date on R-04's assignment |
| Three invoice lines on 2207 counted under both | $41,000 |
The reconciliation: end R-04's assignment on 30 June in the assignment file. Re-upload. The identity holds. Coverage, concentration and credit for 2207 are all right now, because the row was fixed, not the total.
If the $41,000 had been subtracted from the total, the revenue identity would have passed, and 2207 would still be double-counted in R-04's coverage, in the top-ten share, and in the commission run. The adjusted total is the most expensive fix there is.
Totals adjusted. The row stays wrong everywhere else.
Identity run monthly, by hand. A week of reconciliation, every month.
Exception list without owners. Known, unfixed.
Decomposition identities skipped. Bookings called revenue for a year.
Covirage checks the identities on every upload, lists the failing rows with the probable cause, and never adjusts a number. The roll-up hierarchy guide covers the first kind of identity, and the identity by industry hub covers what has to sum on each desk.
Because an adjusted total hides a row that is wrong, and the row is wrong in every other table too. The account under two reps is double-counted in coverage, in concentration and in credit; adjusting the revenue total fixes none of them. The identity's failure is the finding; the adjustment erases it.
The first time, days, because the failures have accumulated for years. After that, minutes, because the identity catches each new failure the month it appears and the exception list has one or two rows. A reconciliation that takes a week every month is one where the identity is not being run on every upload.
Two kinds. Roll-up identities: the same total at every level of a hierarchy. Decomposition identities: a total equals the sum of named parts, bookings equal billings equal revenue over a term, or open pipeline equals clean plus flagged. Both are checked the same way and fail the same way.