Blog · Data quality and reconciliation
The five checks that make a sales roll-up agree with the reported number: value is numeric, every level is filled, the hierarchy is a tree, no duplicates, and level totals equal the grand total. With the variance each one produces when it fails.
Every sales operations team has lived the meeting where the territory report says one number and the finance report says another, and the next hour is spent finding out why. The reasons are always the same five, and each one leaves a fingerprint at a specific level of the roll-up. This article lists them, in the order they should be checked, with the variance each one produces.
A sales roll-up is a tree: region contains teams, teams contain people, people contain accounts, and every row has a value. For the tree to be trusted, one identity must hold:
total = Σ regions = Σ teams = Σ people = Σ accounts
If any level sums to something else, the report is wrong somewhere, and the level where it first diverges tells you where.
The failure. A value column with "TBC", "n/a", a blank, or "£1,200" in a system that does not strip currency symbols. Depending on the tool, the row is dropped silently, treated as zero, or turns the whole total into an error.
The fingerprint. The grand total is lower than finance by exactly the sum of the rows that failed to parse, or the total is missing altogether.
The fix. Parse the value column explicitly, count the rows that do not parse, and report them by row number before any total is computed. Strip currency and thousands separators deliberately. Never let a spreadsheet's guess stand in for a decision.
The failure. A row with an account and a value but no region, because the account was created last week and nobody set the territory. Or a rep who left and whose accounts have not been reassigned.
The fingerprint. The grand total is right, but the regions sum to less than it. The missing amount is exactly the value of the rows with a blank region.
The fix. Count blank cells per level column. Report the rows. Decide, per level, whether a blank is an error or an "unassigned" bucket that the report should show explicitly. Hiding it inside the total is how it stays unfixed.
The failure. An account that sits under two people, because coverage changed mid-period or two reps both log against it. A person who appears in two teams after a reorganisation.
The fingerprint. People sum to more than the teams, or accounts sum to more than the people. The excess is the value of the rows under the second parent.
The fix. For each level, list the members that have more than one parent. Almost always the answer is a date: before the reorganisation this account belonged to A, after it to B. Apply the mapping by date, or pick the parent as of period end and say so.
The failure. The same invoice exported twice, the same week's file appended to itself, a report that includes both the order and the shipment.
The fingerprint. Every level agrees with every other level, and all of them are higher than finance by the value of the duplicates. This is the one that the level identity does not catch, because duplicates are consistent all the way down.
The fix. Define what makes a row unique, usually the combination of every mapped column, and count rows that repeat it. Report the count and the value. Where the duplicates are legitimate, for example two identical orders on the same day, the file needs a column that tells them apart, and that is a source change.
The failure. Everything above passes and the levels still disagree, because the roll-up was built from different sources for different levels: regions from one report, people from another, and the two reports have different cut-off dates.
The fingerprint. A variance at one level with no rows to explain it.
The fix. Build every level from the same rows. A roll-up that takes its region totals from a regional report and its rep totals from a CRM export is two roll-ups pretending to be one. Compute all levels from a single row-level file and the identity holds by construction.
Run the checks in this order because each depends on the ones before it. A non-numeric value corrupts every level; a blank region makes the tree check meaningless for that row; duplicates inflate everything consistently, so they are only visible once the levels agree with each other and disagree with finance.
| Check | What it catches | Where the variance shows |
|---|---|---|
| 1. Numeric | Text in the value column | Grand total short, or missing |
| 2. Filled | Blank level cells | A level sums to less than the total |
| 3. Tree | Members under two parents | A level sums to more than its parent |
| 4. Duplicates | Repeated rows | All levels agree, all above finance |
| 5. Agreement | Mixed sources | A level with an unexplained variance |
Reconciled means every check passed, or every failure is a named line in the report: "3 rows with no region, £4,300, listed below". The number is trusted because the exceptions are visible, not because they were absent.
This is why the checks belong in the tool, not in a person. Run on every file, before any screen is drawn, they cost nothing and end the meeting before it starts. Covirage runs exactly these five on every upload and shows the report first. You can upload a sample and watch it run on your own rows, in your browser, without an account.
To the penny, for the same period and the same definition of revenue. Where the definitions differ, for example gross bookings against net revenue, the difference should be a named, explained line, not an unexplained variance. Zero variance after a named adjustment is the standard.
The numeric check on the value column, because every later check depends on it. A single 'TBC' in a revenue column turns a total into nothing, and every level below it inherits the problem.
On every file, every time. The cost is milliseconds. The alternative is finding out in the meeting.