I Found £5.19M Missing. Then I Had to Figure Out Why.

Finding a problem in data is satisfying.

Finding £5.19 million worth of discrepancies is even more satisfying.

But then comes the slightly less convenient question: Why?

That was the question behind my latest portfolio project - a cross-system data quality and revenue reconciliation investigation using SQL and Power BI.

And what started as a fairly straightforward reconciliation exercise turned into something much more interesting.

Because I found the problem.

I found where most of it was happening.

I tested several possible explanations.

And in the end, I still couldn't prove one definitive root cause.

Strangely enough, that became my favourite part of the entire project.

The Scenario

The project was built around five operational datasets representing different parts of the same commercial process:

• 1,800 customer records

• 42,000 orders

• 40,986 payments

• 2,197 refunds

• 41,880 CRM records

  
  

Individually, each dataset looked like a fairly normal source of operational information.

But businesses rarely make decisions based on one isolated system.

An order can exist in one system, its payment in another, its refund somewhere else, and the associated sales value in a CRM.

So the question I wanted to answer was not simply:

What does each dataset contain?

It was:

Do these systems tell the same story?

As it turned out, not quite.

Starting with the Basics: Can I Trust the Data?

Before analysing revenue, I first validated the data itself.

I checked table volumes, data types, status distributions, relationships between records and the financial fields that would eventually be used in reconciliation.

This part is not particularly glamorous.

There are no impressive dashboards yet.

But I increasingly think it is one of the most important parts of analysis.

There is little value in calculating sophisticated KPIs if the underlying records are incomplete, duplicated or inconsistent.

Once the basic validation was complete, I could move on to the real investigation.

39,999 Completed Payments. But Did They Actually Match the Orders?

For every order, I calculated the expected value using:

Order Value = Quantity × Unit Price × (1 − Discount)

I then compared this value with the corresponding completed payment.

Because this involved financial data, I allowed a tolerance of ±£0.01.

The result was immediately interesting:

of 39,999 completed payments:

• 36,014 (90.04%) matched the calculated order value

• 3,985 (9.96%) did not

• no material overpayments were identified

  
  

And those 3,985 mismatches represented approximately:

£5.19 million in underpayment variance

At this point, finding the discrepancy was no longer the main question.

Now I wanted to understand it.

First Question: Where Are the Mismatches?

I grouped the underpayments by order status.

The results were:

Order Status	Underpayments
Completed	3,769
Returned	132
Pending	53
Cancelled	31

That meant approximately 94.6% of all identified underpayments were associated with orders marked as Completed.

That finding mattered to me beyond the number itself.

A status called Completed sounds reassuring.

From a reporting perspective, it would be very easy to assume that a completed order represents a successfully completed commercial transaction.

But here, operational status and financial reconciliation were clearly not the same thing.

“Completed” did not necessarily mean that the expected payment value had actually been received.

That is exactly the kind of assumption that can quietly become a reporting problem.

Could Discounts Explain It?

This was one of the first explanations I wanted to test.

Perhaps discounted orders were being calculated differently somewhere in the process.

It sounded plausible.

So I compared underpayment rates.

For orders with a discount, the underpayment rate was: 10.08%

For orders without a discount: 9.82%

Almost identical.

So although discounts existed in the data, there was no evidence that they were the primary driver of the mismatch.

Hypothesis tested. Evidence didn't support it. Move on.

And I actually enjoyed that part.

Not every query needs to produce a dramatic finding.

Sometimes its value is simply eliminating one possible explanation.

Maybe There Was a Pattern in How Much Customers Paid?

Next, I looked at the relationship between payment value and calculated order value.

For each underpayment, I calculated what percentage of the expected order value had actually been paid and grouped the results into bands.

Payment Ratio	Underpayments
0%	39
0–25%	83
25–50%	1,549
50–75%	1,564
75–90%	489
90–100%	261

This produced another interesting result.

3,113 mismatches - 78.1% of all underpayments - involved payments covering between 25% and 75% of the calculated order value.

That was clearly a pattern.

But it still wasn't a root cause.

There wasn't one obvious percentage appearing repeatedly.

It wasn't a simple case of every affected payment being 50% of the expected value, for example.

The data was telling me something was happening.

It just wasn't telling me exactly why.

And Then the CRM Had Its Own Surprise

Payment reconciliation wasn't the only data quality issue.

When I investigated the CRM export, I found:

373 duplicated order IDs

Looking specifically at Closed Won records, the duplicates affected 356 orders.

The result?

Approximately:

£1.08 million in duplicated reported deal value

This is a different type of problem from the payment mismatch, but potentially just as important from a reporting perspective.

If those CRM records were simply aggregated without duplicate validation, reported commercial performance could be overstated.

Again, the individual records existed.

The numbers looked like numbers.

A dashboard could happily sum them.

And that is exactly the problem.

A technically correct calculation can still produce a misleading business result if the underlying data is wrong.

So What Was the Root Cause?

This is where the project became particularly interesting to me.

I had now investigated:

• order status

• discount usage

• payment-to-order value ratios

• refund activity

• CRM duplication

  
  

I knew the scale of the payment problem.

I knew its financial impact.

I knew where the majority of mismatches appeared.

I had identified patterns.

I had eliminated at least one plausible explanation.

But I still could not prove one definitive root cause.

And I could have stopped there and invented a nice explanation.

Something like:

"The analysis demonstrates that incorrect discount processing caused the underpayments."

It would have made the project sound wonderfully complete.

It also wouldn't have been supported by the data.

So I didn't.

Sometimes “I Don't Know Yet” Is an Analytical Result

This was probably the biggest lesson from the project.

When I first started building portfolio projects, I think I subconsciously expected every analysis to end like a detective film.

You investigate.

You find a pattern.

You reveal the culprit.

Case closed.

Real analysis isn't always that cooperative.

Sometimes the evidence allows you to say:

This is the problem.

This is how large it is.

This is where it occurs.

These are the explanations we tested.

These explanations are not sufficient.

And this is what we need to investigate next.

That is not an incomplete analysis.

It is being honest about the boundary between evidence and assumption.

In this case, the next step would be to investigate upstream payment-processing logic - particularly how payment amounts are generated, transformed and transferred between operational systems.

That information simply wasn't present in the available datasets.

SQL cannot magically query data that does not exist.

Even when I really want it to.

Turning the Investigation into Business Actions

Finding discrepancies is useful only if the analysis leads somewhere.

Based on the evidence, I proposed five actions.

1. Strengthen payment reconciliation controls

Calculated order values should be automatically compared with completed payment values, with discrepancies flagged as exceptions.

2. Investigate upstream payment-processing logic

The next stage should examine how payment amounts are created and transferred between systems.

3. Introduce CRM duplicate validation

Duplicate order IDs should be detected before Closed Won values are included in revenue reporting.

4. Review completed orders with incomplete payment coverage

A Completed order status should not automatically be treated as confirmation that the expected revenue has been received.

5. Continue the root-cause investigation

The current analysis establishes the scale, impact and patterns of the issue. Additional system-level information is required to determine the underlying cause.

Building the Power BI Report

Once the SQL investigation was complete, I built a two-page Power BI report.

The Executive Summary focuses on the scale of the issue:

• 39,999 completed payments

• 3,985 payment mismatches

• £5.19M underpayment variance

• £1.08M duplicated Closed Won CRM value

• mismatch distribution by order status

• reconciliation results

• payment-ratio patterns

  
  

  

The second page, Investigation Findings, deliberately moves away from charts.

Instead, it communicates:

• key findings

• business impact

• recommended actions

• what the analysis can establish

• what still requires investigation

I wanted the report to do more than visualise numbers.

The first page answers:

What happened?

The second answers:

So what do we do about it?

What I Used

The project brought together:

• SQL Server and SSMS for database validation, reconciliation and investigation

• SQL for cross-system analysis and hypothesis testing

• Power Query for preparing report data

• DAX for measures and analytical calculations

• Power BI for executive reporting and communicating the investigation

• GitHub for documenting the complete project

  
  

But the most useful skill involved wasn't really a particular tool.

It was knowing when to keep investigating - and when the evidence no longer justified another conclusion.

What This Project Taught Me

This project started with data quality and revenue reconciliation.

But I think it ended up teaching me something broader about the type of analyst I want to become.

I don't want to be someone who simply receives a dataset, calculates a few KPIs and builds a polished dashboard.

I want to be the person who looks at a number and asks:

Does this actually make sense?

If it doesn't:

Where did it come from?

Does another system agree?

How large is the discrepancy?

What could explain it?

Can I actually prove that?

And perhaps most importantly:

Where does the evidence stop?

Because sometimes the most professional conclusion isn't a clever explanation.

Sometimes it's:

“I found the problem. I tested what the available data allowed me to test. Here's what we know, here's what we don't know yet, and here's what I would investigate next.”

For me, that was probably more valuable than finding a perfectly neat answer.

Explore the Full Project

The complete project - including the SQL scripts, Power BI report, investigation methodology and findings is available on GitHub