Two reports disagree. How do I find out which one is wrong?
Usually neither is wrong; they are answering different questions. Work through four causes in order: date basis, filters, definition, then source system. Pick one specific record that appears in one report and not the other and follow it through both. A single example resolves the disagreement faster than comparing totals, because a total only tells you the size of the gap, never the reason for it.
Compare records, not totals
The instinct is to stare at two numbers and try to reason about the difference. That almost never works, because a single gap can be produced by a dozen offsetting rules. Export both reports at record level, diff the identifiers, and look at what falls out.
The rows present in one report and absent from the other are the rule you are looking for. Ten of them are usually enough to name the cause out loud.
If record-level export is not possible, narrow by dimension instead. Compare both reports by location, then by job type, then by week. The difference will usually concentrate in one cell, and that cell names the rule almost as directly as an individual record would.
The four causes, in the order they usually apply
- Date basis. Created, booked, scheduled, completed, invoiced and paid are six different dates on the same job. Two reports using different ones will never agree in a month with any growth in it.
- Filters. Excluded job types, canceled work, warranty calls, internal or test records, and locations added part way through the period.
- Definition. A lead can be a call over a duration threshold, a form submission, a CRM record, or a booked job. Same word, four populations.
- Source system. The ad platform, the call platform, the CRM and the field service system each own a different slice of reality and none of them sees the whole customer.
Period boundaries deserve their own check
Timezone is the quiet one. A platform reporting in one timezone and an operational system reporting in local time will disagree on every day boundary, and the error concentrates in evening activity. Weeks starting Sunday versus Monday, and fiscal months that do not match calendar months, produce the same class of mismatch.
These differences are structural. They will not go away, so they belong in documentation rather than in a recurring argument. Joining the systems properly is integration work, not a spreadsheet task.
Daylight saving transitions deserve a specific mention. Twice a year one day has twenty-three hours and another has twenty-five, which is enough to shift a boundary record and to break any calculation that assumes fixed-length days.
Write the answer where the number lives
Once you know why two reports differ, record it as a note attached to the metric itself: this tile counts completed jobs by completion date in local time, excludes warranty and internal work, and will not match the ad platform's conversion count.
Resolutions that live in someone's memory get rediscovered every quarter by a new person. Resolutions attached to the dashboard get read by whoever is about to be confused.
Topics: reconciliation · diagnostics · definitions · data audit
Have a version of this question about your own business?
The useful answer usually depends on which systems you run and how they're connected. That's a conversation, not a blog post.