One Number or Many? Sales Analytics at MetroGrocer
MetroGrocer, a grocery chain with 340 stores in three regions, is building a sales analytics platform. The CFO wants a single 'daily net sales' number by 06:00 local time. Region A closes at 23:00 and uploads a batch at 01:00. Region B's POS streams near-real-time but includes provisional refunds for two hours. Region C's franchise stores report manually by 09:00. The data team proposes a central warehouse with one KPI definition, refreshed nightly by 07:30. Regional managers want local dashboards with their own definitions and same-day data. The CFO's bonus is tied to the corporate number. A pilot showed the central number differs from Region B's dashboard by 3.4% because of refund timing. The steering committee asks whether to force one definition or allow regional variants.
1. Identify the core concepts and principles in tension and explain why a purely technical 'single source of truth' decision will not resolve the conflict.
Read the worked answer
Principles in tension
- Consistency vs. timeliness: the CFO needs one reconciled number by 06:00; regional managers need same-day operational data. A single nightly refresh cannot satisfy both without being explicit about provisional versus final data.
- Centralization vs. federation: one corporate definition supports comparability and accountability; local definitions support operational decisions and regional ownership.
- Single source of truth vs. multiple valid views: a single source of truth is about definition, lineage, and certification, not about forcing every user to see one physical table or one timestamp.
- Data ownership and incentives: the CFO's bonus is tied to the corporate number, so definition changes are not neutral. Regional managers may distrust a central number that moves their performance.
Why technology alone fails: the pilot's 3.4% variance is a semantic and timing problem, not a database problem. Region B includes provisional refunds for two hours; the central warehouse may include them or not. If the definition does not state finality, users will invent local definitions and the organization will have multiple truths. Forcing one definition without resolving refund timing and Region C's 09:00 manual reporting will produce a technically consistent number that nobody trusts.
Reasoning: the design must separate the certified financial measure from operational measures, publish the lineage and finality status, and assign a KPI owner. Otherwise the steering committee is choosing between false precision and uncontrolled variants.
2. Evaluate at least two operating models and recommend one with conditions. Include how to handle the 3.4% variance and the 09:00 manual region.
Read the worked answer
Options
- Option A - Central-only certified number. One definition, one refresh. Pros: strong comparability and control. Cons: misses the 06:00 need for Region C, and regional managers lose operational data; likely to spawn shadow spreadsheets.
- Option B - Federated local definitions. Each region publishes its own dashboard. Pros: fast and locally relevant. Cons: the CFO cannot compare regions; definition drift and disputes increase.
- Option C - Certified core plus governed views. A central semantic layer owns the canonical 'final net sales' definition, lineage, and reconciliation. Regions may publish operational views, but those views must declare whether they are provisional and must reconcile to the canonical measure.
Recommendation: Option C, conditional on explicit finality rules.
- Publish provisional net sales near-real-time and final net sales after reconciliation, each labeled. The CFO's 06:00 number should be final or clearly marked provisional with a defined finalization time, for example 10:00.
- Define refund treatment: include refunds when they are posted, or accrue them consistently. Publish the rule and the expected variance.
- For Region C, require manual submission by 08:30 or flag the region as incomplete in the 06:00 report. Do not silently impute franchise data.
- Add a reconciliation report that explains variance by driver, including the 3.4% refund-timing difference.
Conditions: a named KPI owner, a semantic layer, published lineage, a variance threshold with sign-off, and a change-control process for definitions. Without those, Option C becomes Option B in practice.
3. What governance and measurement would you put in place to prevent a repeat of the pilot's 3.4% discrepancy?
Read the worked answer
Governance
- Establish a KPI definition board with the CFO's delegate, regional finance leads, data stewardship, and the analytics owner. The board approves definition changes and resolves reconciliation disputes.
- Require every dashboard to show the measure definition version, data freshness, finality status, and known exclusions.
- Link regional data submissions to a service-level expectation; repeated late or incomplete submissions trigger escalation, not silent adjustment.
Measurement
- Track reconciliation variance by region and driver, with thresholds. A variance above the threshold requires an explanation and sign-off before publication.
- Monitor data-quality dimensions: completeness, timeliness, validity, and consistency against source systems.
- Track trust indicators: number of shadow reports, definition disputes, and time to resolve reconciliation issues.
Reasoning: the 3.4% discrepancy is a symptom of unowned definitions and unstated timing rules. Governance makes the rule explicit; measurement makes deviations visible before they reach the CFO's bonus conversation.