The whole method, written out. Including the parts where it refuses to answer.
Reconciliation is not hard. Reconciliation that you can trust in a meeting is, and the difference is almost entirely in the boring parts: the key, the exclusions, and knowing when the files can't be compared at all.
Two lines by email · no obligation · answered by the person who would do the work
The key, and why it decides everything
Two files can only be compared through something they share — an order reference, a SKU, a shipment ID, an invoice line. In practice that shared thing is never written the same way twice: #1041, 1041 and SO-1041 are one order in three costumes.
So the first job is normalisation: strip prefixes and punctuation, align case, handle the leading-zero conventions, and — the one that caught us out on real data — recognise when a numeric field has lost its decimal point on one side.
Then we measure how well the two sides join, and print that number before any findings. If the match rate is below 90%, the run stops. No findings, no summary, no chart.
Why the refusal matters more than the finding.
A broken join produces the same output shape as a catastrophic leak: thousands of records on one side with no partner on the other. One of those means fix your process; the other means fix your spreadsheet. A tool that doesn't distinguish them will eventually hand somebody a very confident, very wrong number in front of their CEO.
This exists because of a real dataset: before SKU normalisation, one warehouse's own files reported 27% "ordered but not picked" and 24% "picked but not ordered" at the same time. The case, in full →
What normalisation actually does to a key
Seven transformations, applied in order, each one from a real file that broke without it.
| Transformation | Before | After | Why it exists |
|---|---|---|---|
| Strip order prefixes and suffixes | #1041 · SO-1041 · 1041-A | 1041 | Store, ERP and 3PL each stamp their own |
| Drop punctuation in SKUs | TWL-BLU-SET · TWL_BLUE_SET | twlbluset | Connectors never matched them; stock walked apart |
| Recover a lost decimal | 105 · 10.5 | 10.5 | 34% of lines in one real WMS export |
| Normalise case and spacing | Blue Set · BLUE SET | blue set | Manual entry on one side, system on the other |
| Handle leading zeros | 0004512 · 4512 | 4512 | One side stores the reference as text, the other as a number |
| Split composite references | 1041/2 · 1041-SPLIT2 | 1041 | Split shipments otherwise read as missing orders |
| Trim marketplace channel tags | AMZ-1041-FBA | 1041 | Multichannel brands stamp the channel into the reference |
What it looks like when this is skipped. On a real footwear warehouse export, the same two files reported 27.3% “ordered but not picked” and 23.9% “picked but not ordered” at the same time. Both sides broken at once is the signature of a wrong key.
After normalisation the key matched at 97% and the picture inverted into something believable: 4.58% ordered and never picked against 0.01% picked and never ordered. A warehouse does not pick things nobody ordered — that asymmetry is how you know the key is right. The full dataset →
Collisions are checked in both directions: after normalisation, two genuinely different products must not collapse into one key. In that dataset the collision count inside each side was zero, which is the test that makes the 97% trustworthy rather than convenient.
What gets excluded, and why each exclusion exists
| Excluded | Reason | What happens if you don't |
|---|---|---|
| Orders too recent to judge | They haven't had time to ship | Yesterday's normal backlog is reported as leakage |
| Cancelled orders | Nothing should have shipped | The headline number roughly triples, and the finding is worthless |
| Returned and refunded lines | Already reconciled through another path | Double counting against the same unit |
| Test SKUs and internal orders | Not customer demand | Small but persistent noise that undermines trust in the rest |
| Pre-migration periods | The system changed underneath the data | A migration artefact presented as an operational problem |
| Mixed currencies, unconverted | Sums across currencies are meaningless | A total that is confidently wrong in a specific direction |
Every exclusion is reported as a count, not applied silently. If 812 orders were dropped as too recent, the output says so — you're entitled to disagree with the rule and see the number with it removed.
Compare events, not totals
Totals tell you a gap exists. Events tell you when it opened. Any reconciliation that stops at "you're 1,284 units short" has produced the same information as the count that prompted it.
So the matching runs at line level with timestamps attached, which lets the output say something actionable: this gap opened on 3 February, in receipts, on purchase orders closed more than a day after goods arrived, and it has recurred on every late-closed PO since.
That sentence is the deliverable. The number is just its headline.
Group by cause, rank by money
Findings are clustered by mechanism — double receipt, missing return posting, one-sided transfer, rate above contract — and each cluster carries a value, so the list is a work queue rather than a wall of exceptions.
Most stacks have three or four causes producing eighty per cent of the gap. Naming them is what turns a recurring investigation into a process fix that somebody can actually own.
Where a cluster can't be explained from the data alone, it's labelled as needing a human at your end rather than given a confident story.
What we need, what we don't, and what never moves
A note on our own fragility. Our scripts break on a renamed column exactly like your connector does. We're not claiming to be more robust than the tools you already have — the difference is that watching and repairing this is the job we're paid for, not a side effect of somebody else's week.
The checks, by seam
| Seam | Two sides | Typical findings |
|---|---|---|
| Orders | Store or marketplace orders · 3PL or WMS shipments | Paid never shipped · SKU never matched · cancelled upstream only · returns never posted back |
| Inventory | WMS movement log · ERP or store stock ledger | Double receipts · one-sided transfers · adjustments without reason codes |
| Invoices | 3PL, freight and customs invoices · rate card · shipment log | Rate above contract · storage on emptied positions · accessorials with no event · duplicates |
| Deductions | Remittances with codes · ASN, routing guide, POD | Late that wasn't late · compliance charged wrongly · shortages matching the BOL · duplicate POs |
| Reporting | Everything above, plus finance | Two definitions of one metric · a mapping that never got the new channel |
About the method itself
Why not just build this in-house?
You can, and some teams should — the logic isn't secret, which is why it's written out on this page. What makes it work isn't the code, it's someone looking at the output every morning and chasing what it says. That's the part that quietly stops happening in-house, usually around week three.
Can we see the code?
The browser version is JavaScript sitting in the page you already loaded — view source. For the paid work, the logic that runs on your files is documented in the handover, and we'd rather you understand it than trust it.
What if our data can't answer the question?
Then you get told that, early, with the reason — usually a missing timestamp or a key that doesn't survive normalisation. We'd rather lose the engagement in week one than deliver something unreliable in week three.
Do you need a live connection to our systems?
Not for the one-off work. For the daily version, scheduled exports are enough — most platforms can drop a file to storage or email on a timer. Direct API access is possible if you want it, but it's never a requirement to start.
Where to go from here
Two lines by email. No form, no calendar link.
Which two systems, roughly what size, what you have already tried. You get a straight answer the same working day — including “this is not something we would help with”, when that is the honest one.
Two lines by email · no obligation · answered by the person who would do the work