Sign in

Blog · Data quality and reconciliation

Control total and identity: the check against the world, and the check against itself

The difference between a control total, which tests an uploaded file against the figure the source system showed, and an identity, which tests the computed tables against each other, why both are needed and neither replaces the other, the failure each catches that the other cannot, a filtered export against a duplicated account, the order they run in, and what a report says when one of them is unavailable.

By CoviragePublished 16 September 20262 min read
The short answerA control total tests the file against the world: the revenue in the export equals the revenue the finance screen showed for the same period. An identity tests the tables against themselves: region equals team equals person equals account equals the file's total. A filtered export passes every identity and fails the control total; a duplicated account passes the control total and fails the identity. Both run on every upload, control total first, and a report whose control total is unavailable says unreconciled beside every figure that depends on it.

Two checks decide whether an uploaded file can be trusted, and they answer different questions. The control total asks whether the file is the right one. The identity asks whether the tables built from it agree with each other. This guide sets out both, the failure each catches alone, the order, and what the report says when one is missing.

The two definitions

Control total Identity
Tests The file against the source system The tables against each other
Question Is this the right file, complete? Do the roll-ups sum?
Reference A figure noted from the source before exporting The file's own total
Catches Filtered, truncated, wrong-period, duplicated-at-export files Duplicates, missing mappings, wrong period cuts between files
Cannot catch A duplicate inside a complete file A complete-looking file that is half the month

The failure each catches alone

File Control total Identity Reading
Export filtered to one region Fails: $2.1m vs $4.18m Passes: the region's teams sum to the region The file is wrong; the tables are consistent
Account under two reps Passes: $4.18m = $4.18m Fails: Σ reps $4.22m ≠ $4.18m The file is right; the assignment file is wrong
Both Fails Fails Fix the export first

The order

  1. Control total. If it fails, stop; re-export.
  2. Identities. If any fails, list the rows; reconcile at source.
  3. Measures.

The rows you need

  • The figure from the source screen, noted before export: revenue for the period, row count, customer count.
  • The file.
  • The mapping files the identities depend on.

A worked validation

Check Result Action
Control total, revenue $4,182,400 = $4,182,400 Pass
Control total, rows 48,211 = 48,211 Pass
Identity, roll-up Σ reps $4,223,400 ≠ $4,182,400 Fail: 2 accounts under two reps, $41,000; listed
Identity, product Σ lines = $4,182,400 Pass

The file is right; the assignment file is not. Fix the assignment; re-run.

When the control total is unavailable

Line Shown as
Revenue by region $4,182,400, unreconciled to source
Every dependent figure Carries the label

Honest, visible, and a task for next month.

Where it goes wrong

Identity only. The half-month export, perfectly consistent.

Control total only. The duplicate, hidden inside a complete file.

Order reversed. Reconciling tables built on the wrong file.

Unreconciled shown as a pass. The silent gap.

Every upload, both checks, in order

Covirage runs the control total first and the identities second on every upload, and labels every figure unreconciled where no control total was given. The validation report guide covers where both sit among the six lines, and the export preparation guide covers noting the control total before exporting.

Questions people ask

Why control total first?

Because if the file is not the file, nothing computed on it is worth checking. A half-month export can roll up perfectly at every level and describe half a month. The control total is the one check that says the file is the right one and complete, and it takes a minute to note before exporting.

What if there is no control total?

The report says unreconciled beside the total and every figure downstream inherits the label. It is honest, it is visible, and it is the reason to get the control total next month. A silent pass is worse than a labelled gap.

Can the identity catch a filtered export?

Only by accident, if the filter removed a level unevenly. A filter that removed one region entirely leaves the remaining regions summing to the remaining total, and the identity holds. That is why the two checks are different and both are run.

Related reading

Data quality and reconciliation

A CRM data quality scorecard: five fields, five checks, one number per rep

How to score CRM data quality from the export rather than from complaints: five fields that every coverage and pipeline measure depends on, owner, close date, stage, activity date and account identifier, the check for each, the completeness and validity rate per rep, why the score is reported beside the measures that depend on it, and the rule that a measure computed on data under a stated score is shown greyed.

16 Sept 20263 min readData quality and reconciliation

Account hierarchy: parents, subsidiaries and the level you actually sell to

How to build and use an account hierarchy for sales analytics: the levels that exist in most customers, group, legal entity, site, department, the one level where the buying decision is made and every measure is computed, the levels beneath kept as dimensions, the dated parent mapping that survives acquisitions, the identity that revenue sums identically at every level, and the two mistakes that produce forty customers where there is one and one where there are forty.

16 Sept 20263 min readData quality and reconciliation

B2B customer segmentation from the ledger, not from personas

How to segment a B2B customer base from data the company holds: the two or three fields that make a segment useful for norms, size, sector and channel, why behavioural fields like order frequency and product mix belong in the measures rather than the segment definition, the minimum segment size that makes a norm mean something, the test that a segmentation is good, and why personas do not survive contact with a ledger.

16 Sept 20263 min readData quality and reconciliation

Baseline and norm: what this account usually does, and what accounts like it do

The difference between a baseline, an account's own typical level from its own history, and a norm, what similar accounts do when the relationship is full, why the two answer different questions, has this account changed and how far is it from where it could be, which measures use which, surge and dormancy against baseline, gap and share against norm, the case where an account is at its baseline and far from the norm, and the rule that both are stated on the line that uses them.

16 Sept 20263 min read