Blog · Data quality and reconciliation
A customer master is the file that says what each account is, and most companies have three that disagree. This guide sets out the eight fields a master needs for coverage and share-of-wallet analysis, identifier, parent, segment fields, size, owner, status and dates, where each comes from, the fields that seem useful and are not, the rule that one system is the source for each field, and the monthly check that keeps the master true.
Every company has a customer master, usually three: the ERP's, the CRM's and a spreadsheet in finance. None of them agrees on how many customers there are. This guide sets out the eight fields an analytics master needs, where each comes from, what to leave out, and the monthly check.
| Field | What it is | Source system | Used for |
|---|---|---|---|
| Identifier | Stable, opaque, one per customer | ERP or billing | Everything; the join key |
| Parent identifier | The entity the decision is made at | ERP hierarchy, or a maintained mapping | Roll-up to the decision level |
| Segment field 1: sector | What the customer does | ERP or a register | Norms |
| Segment field 2: size band | How big the customer is | Register, stated figure, or list; not sales | Norms |
| Segment field 3: type or channel | How the company sells to it | ERP | Norms; coverage model |
| Owner | The rep or team responsible | CRM, dated | Coverage; crediting |
| Status | Active, dormant, lost, prospect | Computed from the ledger, or set | Denominators |
| Effective dates | When the record and the owner took effect | Each source, dated | History that does not restate |
Eight fields. One source each. The master is the join of them on the identifier.
| Field | Why not |
|---|---|
| Name | Personal or identifying; the lookup stays in the source |
| Address, contacts, phone | Same |
| Notes | Free text; highest risk, no analytic use |
| Revenue with the company | Belongs in the ledger; in the master it makes the norm circular |
| Rep's health colour | Opinion; replaced by a computed score |
Owner comes from the CRM. Sector comes from the register. Size comes from the stated figure or the list. If two systems disagree on a field, the named source wins and the other is corrected, not averaged.
| Check | Rule | Failure listed as |
|---|---|---|
| Completeness | Every ledger customer with revenue is in the master | Unmastered revenue |
| Uniqueness | One row per identifier | Duplicate identifiers |
| Hierarchy | Every parent identifier exists; no child under two parents | Orphans; double parents |
| Segments | Segment fields populated, or flagged unknown | Unsegmented, with count |
| Owner | Every active customer has exactly one owner on the date | Unowned; double-owned |
| Step | Result |
|---|---|
| Ledger customers with revenue, trailing 24 months | 2,640 identifiers |
| CRM accounts matched on identifier | 2,410 matched; 230 ledger customers not in CRM |
| Sector from the register | 2,380 populated; 260 unknown, flagged |
| Size band from the stated figure or list | 2,100 populated; 540 unknown, flagged |
| Parent from the ERP hierarchy | 1,900 top-level; 740 children; 12 orphans listed |
| Owner from CRM, dated | 2,400 owned; 240 unowned, listed |
A master of 2,640 rows, with its gaps counted rather than hidden. The 230 unmastered CRM gaps and the 240 unowned customers are the first two lists, and they are coverage findings before they are data ones.
The CRM as the master. Prospects, duplicates and typed fields.
Sales revenue as the size field. The norm is circular.
Overwrites instead of dated rows. Last year's roll-up changes.
Gaps hidden. Unknown segment defaulted to "other"; the norm for "other" means nothing.
Mapped once, the ledger, the ERP hierarchy, the register and the CRM produce the master and the five checks every month, with the gaps as lists. Covirage builds this from the exports as they are. The metrics governance solution describes the setup, and the segmentation guide covers what the three segment fields are for.
Because the CRM's list has prospects, duplicates and accounts created for a single quote, and its fields are whatever a rep typed. The master is the subset that exists in the ledger, with fields owned by named systems. The CRM feeds the owner field; it does not own the master.
The thing the norm scales by: employees, sites, revenue band, beds, rigs, seats. One field, from a source that is not the company's own sales to the customer, because the norm must not be circular. A public register, the customer's stated figure, or a purchased list, labelled.
With a parent field on every record, pointing to another record or to itself for a top-level entity, dated. A child under two parents, or a parent that does not exist, fails the monthly check. Acquisitions and disposals are new parent rows with dates, never overwrites.