Revenue reconciliation across three sources: an 8.4% gap explained down to 0.33%

Two quantities under one word

The 8.4% site gap consisted exactly of cancelled and partially refunded orders. The owner's book records the amount at the moment of ordering; the CRM shows the money left after cancellations and refunds. Both figures are correct and answer different questions: how much was sold, and how much money stayed. The 0.33% remainder sits on orders at the day boundary. A gap in the CRM itself was closed along the way: the rule "a cancelled or fully refunded order is no revenue" worked, while a partially refunded order counted in full — 7 orders in 30 days, 0.47% of the total. The refund is now subtracted and printed as a separate line, so managers who see the original order amount in their own table get an explanation.

The warehouse book: reconciliation became a daily job

On day one the warehouse book was matched with the CRM by hand, into a file in a temporary folder, and by the next day the check was gone. The reconciliation module makes it permanent: it reads the book through a range export, computes revenue and cost per sales source, compares line by line with Shopify using the same code the CRM runs, and writes a snapshot to a file. CRM pages read the snapshot: reading the book directly would cost a 16-thousand-row export on every page open. The job runs on a daily timer. The 3% threshold, against a measured 0.6%, leaves room for the day boundary and manual edits. A discrepancy ends the run normally and prints the difference; the service fails in exactly one case — when the reconciliation did not happen — and an alert goes to the team chat.

The books are the merchandiser's working tool, so the module is read-only: two tests read the module's source text and confirm it contains no write function.

Four rounds of root-cause analysis

The first live run put the site total at +0.62% and painted the per-store lines red. Each cause had to be found on matched orders:

• the book stores the price before discount, Shopify stores the amount paid; the book is now compared with "paid plus discount", and the match was verified order by order
• some orders carry a row with no price in the book — free accessories; such an order is removed from the comparison on both sides, and the size of the gap is written to the snapshot as a separate field
• "manual" orders are a CRM concept; in the book they sit under the regular store, so the comparison folds them back into their store
• the book writes one store's order numbers with a leading zero; before normalization 100 orders out of 158 looked missing, after it 153 of 158 matched

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

An adversarial audit of day-old code

A few days later independent reviewer agents went through the module with one brief: find what is wrong. The tally — 68 findings, 42 confirmed, 11 defects in code written the previous day. Each was reproduced before the fix and closed with a test, and each test was checked by putting the bug back: the test goes red.

• the window was requested in UTC and filtered by the store's local date: 154 orders out of 959 fell on the day boundary, 15% of the money; a one-day window showed a 15% gap against a perfectly good book. The window is now fetched with a day of margin on both sides and filtered by the requested dates
• a write-off line for defective stock went into sales because of an exact string match, turning a 50.0% margin into 59.3%; the category is now read by prefix
• the sales source was matched case-sensitively: one lowercase letter in a column filled in by hand produced "mismatch" against a healthy book
• the book's line price was compared with an order total that includes tax and shipping; the comparison now uses the subtotal
• the module answered "matched" when its own side failed completely: the book had money, Shopify did not respond, the list of mismatches was empty. "Matched" now requires at least one verified line and no unverified ones
• the verdict ignored coverage: the audit's synthetic data — 101 orders, 100 of them with an unpriced row — produced "matched" on 0.25% of the money. Coverage is now printed in the snapshot, and below 70% no verdict is issued
• the snapshot printed "money outside the comparison" as the value of affected orders — five times the real sum of differences; that is now three separate fields
• margin was computed on mixed sets of rows; it now uses only rows where both the price and the purchase cost were read

The module now has 33 tests: 15 on launch day, 24 after the root-cause rounds. Test values are taken from live book cells.

The snapshot made it to the screen

The audit also flagged an organizational defect: the snapshot went into a file nobody opened. A signal nobody sees protects nothing. A "Warehouse books" card on the CRM integrations page now shows, per line, the book amount, the Shopify amount, the difference and the verdict, with comparison coverage and purchase-price margin alongside.

Both figures are correct, and now they have different names

The 8.4% site gap is fully explained: cancelled orders plus partial refunds, with a 0.33% remainder on the day boundary. The warehouse book agreed with the CRM within 0.6%; one marketplace matched exactly, and on the other all three sources agree within 3.5%. One caveat was stated out loud: rows reach the warehouse book from Shopify — by hand and through our own orders-to-warehouse-sheets pipeline — so the 0.6% match confirms the accuracy of the transfer: no order lost, no amount doubled. The book's independent part is purchase price and logistics, which exist nowhere else.

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

In the sheet, this year's August row summed 19 dates while last year's row held the full month, so 19 days were divided by 31. On equal windows, 1–19 August of both years, site revenue grew 29%, and 30% with the marketplaces included. The owner received a six-section summary that marks what came from his own reports and what was verified against primary data. One of my own claims was withdrawn inside that summary: the first version said spend on one channel was missing from the sheet; reading all 37 columns found it, matching the platform's billing within 0.04%. A conclusion drawn from absence requires proof that the reading was complete.

Reconciliation became a daily job

After four rounds of root-cause analysis the strict lines settled: −0.81% / −0.12% / 0.00% against a 3% threshold. A live run a week after launch: 701 rows, differences of +2.18% / −0.02% / +0.00%, verdict "matched". The snapshot opens instantly on the CRM integrations page, together with comparison coverage and purchase-price margin.

A verdict only with enough coverage

The rule that will outlive this project: a measured zero differs from an inability to measure. "Matched" now means no line diverged, at least one line was actually verified, nothing is left unverified, and the comparison covers at least 70% of the money. Below that there is no verdict, and the screen says "not verified". A complete failure on our side reads as a failure, and an empty denominator prints a dash.