Can a better reporting tool fix this?
No. A reporting tool reads whatever it is pointed at, so pointing a better one at two disagreeing systems produces a third figure. The fix sits underneath the reporting layer.
Why the numbers from two systems never match.
Sales reports one revenue figure. Finance reports another. Both are defensible, and the meeting stops being about the business and starts being about the spreadsheet.
It is almost never arithmetic
When two reports disagree, the first instinct is that somebody has made a mistake. That is rarely it. Both systems are usually calculating correctly. They are calculating different things.
Three causes produce most of the gap. The systems use different dates for the same event. They use different definitions of the same word. And they hold different sets of records because one of them never received everything.
Different dates for the same event
A sale has several dates attached to it: when the opportunity closed, when the order was raised, when the goods shipped, when the invoice was issued, when the payment cleared. The CRM usually reports on the first. Finance usually reports on the fifth.
For a month with normal trading that produces a small difference. For a month ending near a weekend, or a quarter with a push at the end of it, the difference gets large enough to argue about. Nobody is wrong. The reports were never measuring the same moment.
Different definitions of the same word
Revenue is the obvious one. Gross or net of discount, including or excluding shipping, before or after credit notes, recognised on invoice or spread across a contract term. Each of those is a legitimate definition and each produces a different number.
The same applies to a customer, which might mean an account, a billing entity, or a trading site. It applies to an order, which might or might not include cancelled lines. Most businesses have never written these definitions down, which means each system encodes whichever one its implementer assumed.
Different sets of records
The third cause is the one that keeps growing. Something did not arrive. A record was created in one system and the interface that copies it failed quietly, or ran before the record existed, or skipped it because a required field was empty.
This is the cause worth finding first, because unlike the other two it gets worse over time and it corrupts everything downstream. A definition mismatch produces a stable, explainable gap. A missing-records problem produces a gap that drifts.
How to tell which one you have
Take one period and one metric. Pull the underlying records from both systems, not the summary figures, and compare them row by row.
If both systems hold the same rows and the totals differ, the problem is definition or dates, which is a decision to be made rather than an engineering job. If one system is missing rows the other has, the problem is the interface between them, and no amount of agreeing definitions will fix it.
What to write down before anything gets built
Checklist
- Which system owns each field, so there is one place a value is created
- The exact definition of every headline metric, agreed by the people who present it
- Which date each report measures against
- What happens to a record that fails validation, rather than letting it disappear
- Who is told when an interface fails, and how quickly
Questions we get asked.
Is a data warehouse the answer?
It is the answer to the definition problem, because a warehouse is where a single definition can live and be reused. It is not the answer to the missing records problem, which is an integration fix.
How long does it take to resolve?
Agreeing definitions is usually days of meetings rather than weeks of engineering. Tracing missing records depends on how many interfaces are involved, and it is often the faster half.
Start with the problem.
Describe what is slowing the operation down. We will come back with the approach, the scope, and what it costs.
Get in touch