A planning guide from Data & AI Lab. All numerical examples below are hypothetical.
Two dashboards can both calculate “revenue” correctly and still disagree because they answer different questions. One may use invoice dates, another payment dates; one may include tax while another excludes it. A metric contract makes those choices explicit before the visual design begins.
1. Write a one-page metric contract
Choose a business owner who can settle interpretation questions. The analyst can implement the definition, but should not quietly choose which financial or operational events count. Give the metric a precise name rather than an attractive but ambiguous label.
| Field | What to record |
|---|---|
| Name and decision | What is measured, who uses it, and what decision it supports. |
| Grain | One row per order, order line, invoice, customer or another explicit entity. |
| Formula | The calculation, including numerator and denominator for a rate. |
| Included events | Eligible statuses, exclusions, refunds, cancellations and adjustments. |
| Time | Date field, timezone, reporting period and treatment of late records. |
| Units | Currency, exchange-rate date, rounding and tax treatment. |
| Source and freshness | System of record, tables or exports, and expected update time. |
| Ownership and change | Business approver, technical owner, definition version and effective date. |
If the team cannot fill in a field, keep it visible as an open decision. Building a dashboard around an unresolved definition usually makes the disagreement harder to unwind because people become attached to the first number they see.
2. Work a small example before a large query
Consider a hypothetical internal metric called “net paid order value.” For this example only, it is the sum of paid order amounts excluding tax, less refunds recorded during the reporting period. Cancelled orders are excluded. This is an operational example, not an accounting revenue-recognition rule.
| Event | Amount | Included contribution |
|---|---|---|
| Paid order A | 1,000 | +1,000 |
| Paid order B | 600 | +600 |
| Cancelled order C | 400 | 0 |
| Refund recorded this period | 200 | −200 |
| Net paid order value | 1,400 |
Now change one assumption: what if the refund belongs to an order paid last month? Under this example’s definition it still reduces this period’s value because the refund date controls inclusion. A different report might restate the original order period. Both approaches need a clear label and agreement; they should not share an unexplained “revenue” heading.
Add edge cases before implementation: partial refunds, zero amounts, missing currency, an event exactly at midnight and an order whose status changes after the daily refresh. Write the expected result for each case so acceptance is repeatable.
3. Check grain before adding dimensions
Suppose order A has three lines. Joining its header amount of 1,000 to all three lines and summing produces 3,000. Check row counts and sums before and after each join. When a lookup is expected to have one match, test that expectation. A duplicate customer or product record can inflate results even when the join syntax is valid.
Rates introduce another trap. One branch converts 1 of 2 enquiries and another converts 9 of 100. Averaging their rates gives 29.5%; combining their counts gives 10 out of 102, approximately 9.8%. Decide whether the question concerns the average branch rate or the overall enquiry conversion rate. The metric contract should say which weighting is intended.
4. Reconcile using identical boundaries
Choose a small reporting period that the business owner can inspect. Compare the output to the agreed system of record using the same timezone, statuses and rounding. Reconcile both the total and the underlying identifiers; matching totals can hide two offsetting errors.
- List records present in the source but absent from the model.
- List records present in the model but excluded by the source definition.
- Compare amounts for identifiers present on both sides.
- Record the refresh timestamp so late source changes are not mistaken for calculation errors.
Set any tolerance deliberately. A rounding tolerance is different from permission to ignore missing transactions. Preserve the exceptions and get the appropriate owner to approve them.
5. Design the dashboard around the next action
Once the number is agreed, decide how it helps a user act. An operations dashboard might start with exceptions requiring attention, while a monthly management view might begin with trend and context. A page with many charts is not necessarily more informative.
Put the metric definition within reach, display the reporting period and last successful refresh, and distinguish a real zero from missing data. Use descriptive labels and consistent units. Do not use colour as the only signal of a problem: include a label or symbol that remains understandable without it.
6. Version the definition and test the handover
Ask a stakeholder to answer a real question using the dashboard without coaching. Check whether they choose the correct period, interpret the unit and understand the freshness state. Confusion at this point is design feedback, not user failure.
When the definition changes, record what changed, who approved it and whether historical periods are recalculated. Otherwise a trend can appear to move because the measurement changed rather than the business. If freshness and reruns remain unreliable, use the pipeline readiness checklist to work backward to the source.