When the Numbers are all Wrong

The phone rings, and with the usual reluctance of a software engineer on technical support, you answer “Hello, you’ve reached Blueshift, what can I do for you?”. Your heart sinks as the voice on the other end says “Hi, we just upgraded the system to the latest version yesterday and when I went in this morning the forecast has changed, and I think the numbers are all wrong.”

If your life was a choose your own adventure book, the first two options would be:

• Turn to page 32 to calmly say “Sorry, I think you’ve called the wrong Blueshift, we sell stationary products” and hang up the phone.
• Turn to page 56 to weep in despair.

Undoubtedly this call, or any similar variant is not one that anyone wants to take, but if/when you do, let me share a third option you can pencil into your book: “Use a systematic approach to identify the root cause of the problem and solve it, then bask in the glow of client satisfaction”.

This method (with adaptations for your circumstances) can be used for pretty much any kind of data validation or investigation:

Step 1 – Establish what right looks like

This method (with adaptations for your circumstances) can be used for pretty much any kind of data validation or investigation:

Step 2 – Verify there actually is an issue (and check your assumptions!)

Before getting into the detail, run the simplest top level query you can run to compare the data to your “right” benchmark. This will help to both validate your perception of “right” and prevent you wasting time on investigation of the client was mistaken and there is no real issue.

Step 3 – Slice down to isolate a smallest possible set of “wrong” data

Starting with your top level query, introduce grouping at a moderate level of aggregation, then choose one group that shows the problem and filter to that “slice” of the data. Rinse and repeat until you get to a level granular enough to make the next step practical. For example, if you are investigating an issue in forecast data for a food manufacturing business, you might start at total forecast level, then drill down by brand, then drill down further by product in a single brand, then drill down further by sales channel for a single product, then by region until you get to a very small set of “wrong” records.

Step 4 – Trace back the origin of this data to find the root cause

Having isolated a small set of “wrong” records, you should be able to trace the exact path of how these records were generated to identify the point at which “right” became “wrong”. If you can’t find an issue, you may need to go back and check your assumptions again!

Step 5 – Repeat Steps 3 and 4 with different slices

I find it’s generally useful to check my work by spot checking a few other “wrong” slices of data, to confirm my reasoning of the root cause and to identify any other possible root causes. I try to find a slice as different as possible to the first one to maximize my chances of finding a different issue.

Step 6 – Confirm you can explain all “wrong” records

Based on the work you have done, you should now be able to collate a list of unique root causes and run some queries to confirm that these account for the entire discrepancy. Depending on the situation, you may skip this step if you are confident you can fully explain the discrepancy, or if it is more practical to test your theory by just fixing the root cause of the problem then checking again.

Step 7 – Communicate with client, and implement a fix

This step is pretty straightforward – explain the root cause to the client in language that is as non-technical as possible, then with their approval, implement a fix.

Step 8 – Validate your fix

This step is very important and gives you a final safeguard. It should be simple to repeat your queries from Step 2 to confirm if the issue is resolved.

Step 9 – Bask in client satisfaction

As promised, you can finish the process by reading the inevitable email from the client thanking you for your excellent work. For bonus points, picture the party they would surely like to throw you to celebrate your remarkable investigative skills.