Reconciling revenue across three sources: an 8.4% gap explained down to 0.33%

The same word for two different amounts

The whole 8.4% gap on the site came from cancelled and partially refunded orders. The owner's sheet records the amount at the moment of ordering. The CRM shows the money left after cancellations and refunds. Both figures are correct, and they answer different questions: how much was sold, and how much money stayed. The 0.33% that remains comes from orders placed near midnight.

Along the way I fixed a gap in the CRM itself. It already excluded cancelled and fully refunded orders from revenue. A partially refunded order, though, was counted in full: 7 orders in 30 days, 0.47% of the total. The refund is now subtracted and shown on a separate line, so managers who see the original order amount in their own table can see why the numbers differ.

The warehouse book: the check now runs every day

On the first day I matched the warehouse book against the CRM once and saved the result in a temporary folder. By the next day it was gone. The new module makes it permanent. It reads the book by exporting a range of cells, calculates revenue and cost for each sales channel, and compares them line by line with Shopify, using the same code the CRM runs. The result is saved to a file. CRM pages read that file, because reading the book directly would mean exporting 16,000 rows every time a page opens.

The job runs once a day on a timer. The threshold is 3%, while the measured difference was 0.6%: that leaves room for orders near midnight and manual edits. When a discrepancy is found, the run still finishes normally and prints the difference. The job fails in one case only, when the check couldn't be done at all. Then an alert goes to the team chat.

The books are the merchandiser's working tool, so the module only reads them. Two tests scan the module's source code and confirm it has no function that writes.

It took four rounds to find the causes

The first live run showed a +0.62% difference for the site and marked each store's line in red. Every cause had to be found on specific matched orders:

• the book stores the price before the discount, and Shopify stores the amount paid; the book is now compared with "paid plus discount", and the match was checked order by order
• some orders have a row with no price in the book, for free accessories; such an order is left out of the comparison on both sides, and the size of that gap is saved as a separate field
• "manual" orders exist only in the CRM; in the book they sit under the regular store, so the comparison puts them back under their store
• the book writes one store's order numbers with a leading zero; before that was fixed, 100 orders out of 158 looked missing, and after it 153 of 158 matched

After four rounds the numbers settled: −0.81% for one store, −0.12% and 0.00% for the marketplaces, against a 3% threshold. On one marketplace all 62 orders out of 62 matched, with none lost on either side.

AI agents looked for errors in the new code

Soon after launch, AI agents went through the module with a single task: find what's wrong with it. The result was 68 findings, of which 42 were confirmed and 11 turned out to be real defects in the new code. I reproduced each defect before fixing it and covered it with a test. Each test was checked by putting the bug back and making sure the test fails.

• orders were requested by UTC time but filtered by the store's local date: 154 orders out of 959, 15% of the money, fell near midnight, and a one-day check showed a 15% gap even though the book was fine. Orders are now fetched with an extra day on each side and then filtered by the requested dates
• a write-off line for defective stock was counted as a sale because the text was compared as a whole, which turned a 50.0% margin into 59.3%; the category is now read from the start of the line
• the sales channel was compared case-sensitively, so one lowercase letter in a column filled in by hand showed "mismatch" for a correct book
• the book's line price was compared with an order total that includes tax and shipping; the comparison now uses the sum of the items
• the module said "matched" even when Shopify returned nothing: the book had money, Shopify didn't answer, and the list of mismatches was empty. "Matched" now requires at least one checked line and no unchecked ones
• the verdict ignored how much was actually compared: the test data (101 orders, 100 of them with an unpriced row) gave "matched" when only 0.25% of the money had been checked. The share of money compared is now saved with the result, and below 70% there is no verdict
• the result showed "money outside the comparison" as the value of the affected orders, five times the real sum of the differences; that is now three separate fields
• margin was calculated on a mix of rows; it now uses only rows where both the sale price and the purchase price were read

The module now has 33 tests. On launch day it had 15, and after the rounds of looking for causes it had 24. The test values are taken from real cells of the book.

The result made it onto the screen

The review also found a problem in how the work was organised: the result went into a file that nobody opened. A warning nobody sees protects nothing. A "Warehouse books" card on the CRM's integrations page now shows, for each line, the book amount, the Shopify amount, the difference and the verdict, along with the share of money compared and the margin based on purchase prices.

Both figures are correct, and now they have different names

The 8.4% site gap is explained: it is cancelled orders and partial refunds, with 0.33% left over from orders near midnight. The warehouse book differed from the CRM by 0.6%. One marketplace matched exactly, and on the other all three sources agree within 3.5%.

I made one caveat clear from the start. Rows reach the warehouse book from Shopify by hand and through my own automatic transfer of orders into the warehouse sheets. So the 0.6% match confirms that the transfer is accurate: no order lost, no amount counted twice. The only data the book adds on its own is the purchase price and logistics.

The 12.84% "decline" turned out to be growth of about 30%

In the sheet, this year's August row added up 19 days, while last year's row held the whole month. So 19 days were being compared with 31. Over the same dates, 1–19 August of both years, site revenue grew by 29%, and by 30% with the marketplaces included.

The owner received a six-section summary. It marks separately what came from his own reports and what was checked against the raw data. I also withdrew one of my own claims in that summary. The first version said the sheet had no spend for one channel. When I read all 37 columns, the spend was there, and it differed from the platform's bill by just 0.04%. Before saying data is missing, you have to be sure you've read all of it.

The check now runs every day

After four rounds the store and marketplace lines settled at −0.81% / −0.12% / 0.00% against a 3% threshold. A live run a week after launch covered 701 rows, with differences of +2.18% / −0.02% / +0.00% and the verdict "matched". The result opens instantly on the CRM's integrations page, together with the share of money compared and the margin based on purchase prices.

A verdict only when enough was compared

The main rule of this work: "no differences found" and "couldn't compare" are two separate answers. "Matched" now means four things: no line differs, at least one line was actually checked, nothing is left unchecked, and the comparison covers at least 70% of the money. Below that there is no verdict, and the screen says "not checked". If Shopify doesn't respond, that is shown as a failure. If there is nothing to compare with, no percentage is calculated and the screen shows a dash.