Blog · Data quality and reconciliation
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.
A hospital supplier's CRM has forty accounts for one health system. A franchise group's supplier has one account for forty stores that each buy on their own. Both are hierarchies read at the wrong level. This guide sets out the levels, the decision level, the dated parent mapping, the identity, and the two mistakes.
| Level | Example | Usually |
|---|---|---|
| Group | The parent company, the health system, the trust | Where concentration and total relationship are read |
| Legal entity | A subsidiary, a hospital, an academy | Where contracts are often signed |
| Site | A plant, a branch, a store, a campus | Where delivery and coverage happen |
| Department or ship-to | A ward, a dock, a cost centre | Where orders originate; a dimension |
The level at which the contract is signed and the renewal decided, per customer and per product
Measures are computed there: share of wallet, renewal watch, coverage, concentration. Levels beneath are kept as dimensions. Levels above are the roll-up.
| Customer | Product | Decision level | Why |
|---|---|---|---|
| Health system on a system agreement | Contracted category | System | Signed and renewed centrally |
| Same system, capital equipment | Capital | Hospital | Each hospital's budget |
| Franchise group | Consumables | Store | Stores buy independently |
| Corporate with central procurement | Everything | Group | Central |
Every record: identifier, parent identifier, effective from, effective to. A top-level entity is its own parent. An acquisition is a new row. A disposal closes a row and opens another.
Σ revenue at group = Σ at entity = Σ at site = Σ at ship-to = ledger, for the period, using the parents in force in the period
And: every record has exactly one parent on any date. An entity under two groups, which happens after an acquisition when both rows stay open, fails it and is listed.
Measured too low. Forty ship-tos as forty customers. Concentration understated; share of wallet computed forty times on forty partial wallets; renewal watch showing forty small renewals where there is one. The fix is the parent mapping and the decision level.
Measured too high. Forty independent stores as one customer. One share of wallet that describes none of them; one coverage figure for forty buying decisions; a gap list with one row where there should be forty. The fix is the decision level recorded per agreement.
| Identifier | Parent | Level | Decision level for contracted supplies | Effective from |
|---|---|---|---|---|
| SYS-01 | SYS-01 | System | Yes | 2019 |
| HOSP-14 | SYS-01 | Hospital | No; rolls up | 2019 |
| HOSP-22 | SYS-01 | Hospital | No; acquired | 2025-04-01 |
| HOSP-22 | SYS-07 | Hospital | 2019 to 2025-03-31 | |
| ST-3300 | HOSP-14 | Ship-to | No; dimension | 2019 |
Hospital 22 moved systems in April. Reports for March use the old parent; reports for May use the new. Concentration for last year did not change when the row was added.
Overwrites. History restated; last year's concentration moves.
One decision level for all products. Capital measured at the system; consumables at the hospital.
Ship-tos as customers. Forty of one.
Groups as customers when the entities buy alone. One of forty.
Mapped once and dated, the ERP hierarchy, the contract register and the ledger produce the roll-up at every level, the decision level per agreement and the identity every month. Covirage builds this from the exports as they are. The metrics governance solution describes the setup, and the facility identifiers guide covers the hierarchy in one industry's vocabulary.
By where the contract is signed and the renewal decided. For a health system on a system-wide agreement, the system; for a franchise group whose stores buy independently, the store; for a corporate with central procurement, the group. It can differ by product at the same customer, and the hierarchy records the level per agreement.
The acquired entity gets a new parent row with an effective date. Before the date, it rolls up to its old parent; after, to the new. Reports for any period use the parent in force then. An overwrite restates history and moves last year's concentration figure.
Because the decision level explains the number and the site level explains the decision. A trust below norm on a programme is the finding; the four schools that do not use it are the reason. Measured at the trust, cut by school.