The four-level tree that every sales measure is summed through, the difference between a level and a dimension, the identity that must hold at each level, and the mapping decisions that make one hierarchy serve finance, sales operations and the salesperson at once.
Every sales measure on this site, coverage, share, penetration, gaps, is computed at a row and summed up a tree. The tree is the same in every industry: region, team, person, account, with the words changed. This guide sets out the tree, the difference between a level and a dimension, and the identity that makes the whole thing trustworthy.
| Level | In a bank | In a distributor | In a law firm |
|---|---|---|---|
| Region | Region | Depot | Office |
| Team | Desk | Route | Practice group |
| Person | Salesperson | Rep | Partner |
| Account | Client | Store | Client |
Each level nests inside the one above. A person belongs to one team; a team to one region. An account belongs to one person at a time.
Product, date, channel, segment. They describe a row but do not nest. A product appears under many accounts and an account buys many products, so product cannot be a level of the tree. The same row can be summed by product and by account, and both sums equal the total, but "each product sits under one account" is not a property anyone expects to hold.
The practical rule: levels are what the tree check applies to; dimensions are what the roll-up can also be sliced by.
total = Σ regions = Σ teams = Σ people = Σ accounts
And, for each dimension:
total = Σ products = Σ periods
Every level is computed from the same rows, so the identity holds by construction. When it fails, one of five things has happened, and each leaves its mark at a specific level:
| Failure | Where it shows |
|---|---|
| A value that is not a number | Grand total short or missing |
| A row with a blank level | That level sums to less than the total |
| A member under two parents | A level sums to more than its parent |
| Duplicate rows | Every level agrees, all above finance |
| Levels from different sources | An unexplained variance at one level |
The five reconciliation checks go through each.
The tree has to be declared once per source, and three decisions recur.
Dated ownership. An account that moved from one person to another belongs to the first before the date and the second after. Without the date it is under both, and people sum to more than the team.
Primary versus joint. Two people on one account is real. Name the primary for the roll-up and keep the secondary as an attribute, so the tree stays a tree.
Group structures. A parent with subsidiaries. Decide the level the relationship is managed at and roll the subsidiaries to it, keeping them as a dimension for slicing.
Three regions, six people, eighteen accounts, four products. Figures in millions.
| Level | Members | Total |
|---|---|---|
| Company | 1 | 111.70 |
| Region | 3 | 111.70 |
| Person | 6 | 111.70 |
| Account | 18 | 111.70 |
| Product, a dimension | 4 | 111.70 |
Five sums, one number. That table is what "reconciled" looks like, and it is what a validation report should show first.
Product treated as a level. The tree check warns on every product. Make it a dimension.
Region derived from the person. A region column filled by looking up the person's region makes region and person the same information twice. Carry region on the row from the source, and assert that people sit under one region.
Blank levels hidden. Rows with no team quietly dropped from the team level and kept in the total. Show them as "unassigned".
Different periods per level. The team report is monthly, the account list is quarterly. One row file, one period.
Covirage's data map is exactly this tree: region, team, person and account as levels, product and date as dimensions, the value that rolls up, and the identity asserted on every file. The data map draws it, and the glossary entry on roll-up has the short version.
Because the tree property fails: a product sits under many accounts, and an account buys many products. Levels nest; dimensions cross. Treat product as a level and the tree check will warn that every product sits under more than one account, which is true and meaningless.
Add them: region, country, office, team, person, account is fine. The identity holds at every level, and each is asserted against the one above. What matters is that each level nests inside the one above it.
At the row. Every level is computed from the same row-level file, so the identity holds by construction. A roll-up assembled from separate reports per level is two roll-ups pretending to be one.