Designing a governed commercial performance data model
Orders, invoices, targets and customer context do not become a KPI merely because they can be joined.
The case in brief
Current reality
Orders, invoices, targets and customer context arrive at different grains, from different systems, at different times and under different correction rules—so the same performance question gets a different answer in every report.
What must become true
Every commercial metric is reproducible from governed facts whose meaning, grain, identity, period, version, authority, correction behaviour, currency treatment and freshness are explicit—so two consumers asking the same business question receive the same answer.
Design question
What does each commercial fact mean, at what grain and period—and how do we make every downstream metric reproduce the same business meaning?
01The reality · current state
What the data does today.
A commercial organization sells through several of its own companies, each with its own ERP and currency. Commercial teams want one view of performance per customer, product line and period. Today each region exports ERP data into spreadsheets, joins it to the CRM by customer name, phases annual targets its own way and converts currency at its own rate. The problem looks like a dashboard problem. It is a semantic-model problem.
- Order lines and invoice lines are joined directly, so partial deliveries are counted twice
- “Actual” means ordered in one report and invoiced in another
- Customers are matched by name; groups buying through several legal entities split or collide
- Annual targets are compared with year-to-date actuals, and mid-year revisions overwrite past months
- Credit notes and cancellations are removed or ignored depending on who built the report
- Each region converts currency with its own rate, and a corrected rate silently rewrites last year
- Nobody can say which version of a number a decision was taken on
02The semantic problem
Which meanings are mixed today.
Most disagreements were not calculation errors. They were different meanings travelling under the same word.
- Ordered vs invoicedA commitment and a recognised sale were both called “actual”.
- Annual vs phased targetA full-year number was compared with part of a year.
- Signed target vs planning figureThe commercial commitment and the finance budget shared one column.
- Open order vs realised revenueWhat is still to be delivered was added to what has been invoiced—or not, depending on the report.
- Original vs reporting amountConverted numbers replaced the amount that was actually invoiced.
- Correction vs editCredit notes were netted, deleted or ignored instead of being facts of their own.
03Key dimension · Grain, semantics, time & currency
Every fact is a contract before it is a number
Each fact keeps its native meaning; the contract says what it means, at which grain, who may change it, which time it belongs to, how it is corrected, how currency is treated, how fresh it is—and what it is used for.
Select a fact to read its contract. Arrow keys move between facts.
01Orders
- Meaning
- Commitment to buy
- Grain
- Order line × version
- Authority
- ERP
- Time semantic
- Order date
- Correction rule
- New version; cancelled is a status
- Currency
- Original + reporting
- Freshness
- Daily
- Used for
- Open orders, pipeline realism
02Invoices
- Meaning
- Recognised sale
- Grain
- Posted invoice line
- Authority
- ERP (finance)
- Time semantic
- Posting date
- Correction rule
- Credit-note line, never an edit
- Currency
- Original + reporting
- Freshness
- Daily
- Used for
- Realised revenue
03Targets
- Meaning
- Signed commercial commitment
- Grain
- Account × product line × month
- Authority
- Commercial planning
- Time semantic
- Target period, phased
- Correction rule
- New version from an effective date
- Currency
- Reporting, at the planning rate
- Freshness
- On publication
- Used for
- Performance to target
04Customer context
- Meaning
- Governed commercial account
- Grain
- Account × validity period
- Authority
- CRM & master data
- Time semantic
- Valid from – to
- Correction rule
- Merge keeps the surviving key
- Currency
- —
- Freshness
- Hourly
- Used for
- Where facts meet
05Derived performance
- Meaning
- Actual against target, year to date
- Grain
- Account × product line × month
- Authority
- Data platform (derived)
- Time semantic
- Reporting period, as of
- Correction rule
- Recomputed; published by version
- Currency
- Reporting, declared basis
- Freshness
- Daily; final at close
- Used for
- Segments, reviews, signals
Compare all 5 in one table
| Orders | Invoices | Targets | Customer context | Derived performance | |
|---|---|---|---|---|---|
| Meaning | Commitment to buy | Recognised sale | Signed commercial commitment | Governed commercial account | Actual against target, year to date |
| Grain | Order line × version | Posted invoice line | Account × product line × month | Account × validity period | Account × product line × month |
| Authority | ERP | ERP (finance) | Commercial planning | CRM & master data | Data platform (derived) |
| Time semantic | Order date | Posting date | Target period, phased | Valid from – to | Reporting period, as of |
| Correction rule | New version; cancelled is a status | Credit-note line, never an edit | New version from an effective date | Merge keeps the surviving key | Recomputed; published by version |
| Currency | Original + reporting | Original + reporting | Reporting, at the planning rate | — | Reporting, declared basis |
| Freshness | Daily | Daily | On publication | Hourly | Daily; final at close |
| Used for | Open orders, pipeline realism | Realised revenue | Performance to target | Where facts meet | Segments, reviews, signals |
- Orders
- Meaning
- Commitment to buy
- Grain
- Order line × version
- Authority
- ERP
- Time semantic
- Order date
- Correction rule
- New version; cancelled is a status
- Currency
- Original + reporting
- Freshness
- Daily
- Used for
- Open orders, pipeline realism
- Invoices
- Meaning
- Recognised sale
- Grain
- Posted invoice line
- Authority
- ERP (finance)
- Time semantic
- Posting date
- Correction rule
- Credit-note line, never an edit
- Currency
- Original + reporting
- Freshness
- Daily
- Used for
- Realised revenue
- Targets
- Meaning
- Signed commercial commitment
- Grain
- Account × product line × month
- Authority
- Commercial planning
- Time semantic
- Target period, phased
- Correction rule
- New version from an effective date
- Currency
- Reporting, at the planning rate
- Freshness
- On publication
- Used for
- Performance to target
- Customer context
- Meaning
- Governed commercial account
- Grain
- Account × validity period
- Authority
- CRM & master data
- Time semantic
- Valid from – to
- Correction rule
- Merge keeps the surviving key
- Currency
- —
- Freshness
- Hourly
- Used for
- Where facts meet
- Derived performance
- Meaning
- Actual against target, year to date
- Grain
- Account × product line × month
- Authority
- Data platform (derived)
- Time semantic
- Reporting period, as of
- Correction rule
- Recomputed; published by version
- Currency
- Reporting, declared basis
- Freshness
- Daily; final at close
- Used for
- Segments, reviews, signals
- 01Source factsKept at native grain, every version
- 02Conformed modelDeclared grain, governed identity, time and currency
- 03MetricOne definition, one owner, one version
- 04ConsumerDecision views and CRM signals, stamped with an as-of date
A metric is not a formula. It is a semantic contract—and a number without grain, time, owner and version is not governed information.
04Grain & contracts
What one row or message means.
Meaning, grain, key, freshness and owner—written down before anything is mapped.
Order line
A commercial commitment; changes until delivered or cancelled.
- Grain
- One row per order line per version
- Key
Company + order + line + version- Freshness
- Daily, before the working day
- Owner
- ERP data owner
Invoice line
A recognised sale. Corrected by credit notes, never edited.
- Grain
- One row per posted invoice line
- Key
Company + invoice + line- Freshness
- Daily, before the working day
- Owner
- Finance
Target
A published plan, phased by month; partial-year targets start at their effective month.
- Grain
- Account × product line × month × version
- Key
Target version + account + line + month- Freshness
- On publication
- Owner
- Commercial planning
Exchange rate
A rate of a declared type for a date or month.
- Grain
- Currency pair × rate type × date × version
- Key
Pair + type + date + version- Freshness
- Monthly; corrections versioned
- Owner
- Treasury
Customer dimension
One commercial account as it was on each date, linked to its legal entities and ERP customers.
- Grain
- One row per account per validity period
- Key
Analytical customer key- Freshness
- Hourly from CRM events
- Owner
- CRM data owner
- Chosen grain
- Account × product line × month, with the version of every input recorded beside it.
- Too fine
- Line-level joins multiply volume and invite double counting: a partial delivery becomes two sales.
- Too coarse
- Totals by region or quarter cannot be traced to a customer, segmented or corrected.
06Time, version & currency
A number without a time semantic is incomplete.
Time, targets and currency are not report settings. They are part of the model—and every one of them can change the answer.
Six clocks, kept apart
| Time | What it means | Rule |
|---|---|---|
| Transaction date | When the order was placed | Drives open orders and order intake |
| Posting date | When the invoice was posted | Drives realised revenue and the rate date for invoices |
| Target period | The month a phased target belongs to | Performance is measured against the version in force that month |
| Reporting period | The period a report answers for | Calendar month; year to date is derived, never stored |
| As-of date | When the figure was computed | Shown beside every published value |
| Publication version | Which run and definitions produced it | Past decisions stay reproducible |
A target is data plus governance
The target model is part of this model, not a separate one: one grain, one owner, a declared phasing and a lifecycle in which every change is a version.
| State | Who | Rule |
|---|---|---|
| Proposed | Sales leadership | Top-down and account sums compared; gaps explained |
| Signed | Sales leadership with finance | The commercial commitment—kept apart from the finance budget |
| Published | Commercial planning | Versioned, phased and effective-dated; read everywhere, edited nowhere |
| Frozen | Automatic, per period | Closed months cannot change |
| Revised | Sales leadership, approved by finance | A new version from an effective date; past months keep theirs |
The original amount is a fact; the reporting amount is a view
Currency is one semantic dimension of the model. Each converted amount carries what is needed to reproduce it.
| Question | Decision |
|---|---|
| Transaction or reporting currency? | Both: the original amount and currency are the fact; the reporting amount is derived |
| Which rate date? | Invoices at posting date, orders at order date |
| Which rate type? | Monthly average for actuals; the planning rate when comparing with targets |
| Where is it converted? | Once, in the data platform—never in the CRM or a report |
| What if a rate is corrected? | A new rate version recomputes forward; published reports keep the version they used |
07Candidate architectures
Credible options, judged against these premises.
Copy transactions into the CRM
Few transactions, one company, one currency
Cost: CRM volume, no point-in-time history, every team recomputesReport straight from each source
One source per question
Cost: Different grains, times and rates give different answers to the same questionA governed semantic model in the data platform, metrics computed once
Several sources, companies and currencies; shared definitions
Cost: Needs fact contracts, a target lifecycle, a rate policy and a freshness promiseDesign decision
Conform operational facts in the analytical platform at a declared business grain, preserve original facts and versions, govern time and currency semantics centrally, and compute commercial measures from that model once—rather than rebuilding them independently in CRM reports or dashboards.
08Resulting architecture
Where the data comes from, where it goes—and how.
Every hand-over is named with its mode. The mode follows the business tolerance for waiting, inconsistency and loss.
ERPPlanningCRMData platform
- 01 · ERPERP ordersFeeds by Batch
Order lines per company, every version kept.
- 02 · ERPERP invoicesFeeds by Batch
Posted invoice lines, including credit notes, in original currency.
- 03 · PlanningTargetsFeeds by Batch
Published target versions with their phasing.
- 04 · Rate serviceExchange ratesFeeds by Batch
Rates by type and date, versioned.
- 05 · CRMCRM accountsFeeds by Event
Commercial accounts and their governed identity links.
- 06 · Data platformConformed modelHands over by Batch
Facts at the declared grain, with time, version and currency semantics.
- 07 · Data platformMetric computationHands over by Batch
Performance to target, coverage and period status—computed once.
- 08 · Analytics · CRMConsumers
Decision views and read-only CRM signals, each stamped with an as-of date.
09Failure & quality
Design the failure path before the happy path.
- Invoices arrive a day after ordersCompleteness per company and dayMark the month provisional; recompute when they arriveData platform team
- A credit note corrects a closed monthPosting date after the period closeBook it in the open period; the closed report keeps its versionFinance
- An ERP customer maps to no accountUnmapped-customer check on every loadPark under “unmapped” and route to master data—never drop the revenueMaster data
- A target is republished mid-yearNew target versionRecompute from its effective month; earlier months keep their versionCommercial planning
- No rate exists yet for a monthMissing rate for pair and monthHold the amounts as provisional; never convert at a guessed rateTreasury
Data quality is a property of the contract
| Dimension | Owner | Detected by | When it fails |
|---|---|---|---|
| Completeness | Data platform team | Expected loads per company and day | Period stays provisional |
| Identity | Master data | Unmapped or merged customers on each load | Parked, never dropped |
| Consistency | Finance | Invoiced totals against the ERP ledger | Publication held |
| Freshness | Data platform team | As-of date against the promise | Values shown as stale |
| Semantic validity | Sales operations | Definition and version checks per metric | Metric not published |
10Governance
Who keeps the meaning stable over time.
| What | Owner | Cadence | Evidence |
|---|---|---|---|
| Metric definitions | Sales operations | Versioned on every change | Metrics published per definition version |
| Target publication & freeze | Commercial planning | Monthly freeze; revisions quarterly | Versions in force per month |
| Rate policy | Finance & treasury | Annual; corrections versioned | Rate versions used by published reports |
| Grain & identity contracts | Data platform owner | With every new source | Unmapped facts and contract changes |
11The second layer
Questions that change the design.
Meaning & grain
- Does “actual” mean ordered or invoiced—and who decided?
- What is the grain of each fact, and where do they meet without double counting?
- Is a missing target a zero, or no target at all?
Time & version
- Which target version was in force for that month?
- When is a period final?
- Can last year’s report be reproduced exactly?
Currency & corrections
- Which currency is the fact, and which is a view?
- Which rate compares targets with actuals?
- How are credit notes and late invoices represented?
12Decisions & outputs
What the work produces.
- 01Fact contracts
- 02Declared grain
- 03Time & version semantics
- 04Target lifecycle & phasing rules
- 05Currency policy & amount model
- 06Metric contracts
- 07Data-quality rules