Data quality and reconciliation: why your dashboards disagree

Agree what should match

Write down the totals being compared and the reason you expect them to agree. A CRM’s closed opportunities and a payment system’s collected transactions are not automatically the same measure. They may use different dates, include different adjustments and represent different stages of the commercial process.

Choose a specific period and a stable population before investigating. Avoid changing several filters until the numbers happen to look close. The UK Government Data Quality Framework identifies several dimensions of quality, including completeness and consistency. Those ideas are useful here, but the first task is simpler: decide what the comparison is actually supposed to prove.

Trace records instead of debating totals

Break the difference into identifiable groups. Which records appear in both systems? Which exist in only one? Which match but carry a different value or status? Use stable identifiers where possible, and document any matching based on less reliable fields such as names.

A total can conceal offsetting errors: one duplicate and one missing record may cancel numerically while leaving the process wrong. Record-level reconciliation exposes those cases. Keep the original values available and avoid silently overwriting one source with another. The business needs to understand the difference before deciding which system is authoritative for a particular field.

A hypothetical order-reporting mismatch

Imagine a retailer comparing an ecommerce order report with a payment report. Some orders were created late on the final day of the month, some payments arrived later, and a few orders were partially refunded. One report groups by order date while the other groups by transaction date. Their disagreement is partly a definition issue.

The same review also finds a duplicated import and an order with no matched payment reference. Those are different problems and need different actions. This hypothetical example shows why a single adjustment to make totals agree can hide operational exceptions. Classify the causes, then repair or explain each category deliberately.

Build a small exception workflow

Create an exception list with the record, type of difference, owner and proposed resolution. Distinguish expected timing differences from missing records, duplicates and invalid values. Where a delay is normal, define how long an item can remain unmatched before it requires attention.

Reporting freshness matters as well. Google Analytics documents processing intervals, illustrating a general point: systems may become complete at different times. Set an appropriate cutoff for comparison and show whether the period is still provisional. An automated alert should not repeatedly warn about a known, harmless delay while genuine missing records disappear into the same queue.

Run these checks before rebuilding reporting

Take a manageable sample and trace it from the original event through each system. Include a normal record, a cancellation, a correction and a delayed update. That exercise can reveal where assumptions diverge.

• Do both reports use the same business definition and population?
• Are dates, timezones and period cutoffs aligned?
• Are currencies and adjustments treated consistently?
• Are duplicates identified with an appropriate key?
• Can every reported record be traced to its source?
• Are missing and unmatched records visible rather than discarded?
• Does each exception have an owner and a resolution rule?
• Are freshness and provisional values clearly labelled?

Fix the process that creates the discrepancy

A one-time cleanup may restore a report but leave the same error waiting to return. If duplicates come from repeated imports, make the import safe to rerun. If sales stages are inconsistently used, improve the workflow and definitions. If a field is repeatedly missing, decide whether it should be required or whether the report expects information the team never reliably collects.

There is a tradeoff between exhaustive reconciliation and proportionate control. Start with commercially important measures and recurring failure patterns. Automate stable checks only after their meaning is agreed. A trustworthy dashboard is the visible result of reliable definitions, traceable records and clear ownership underneath it.