Orders and stock reconciled between two systems
The problem
Orders and stock go missing between systems — the store, the warehouse, the ERP. Everyone says so; almost nobody shows the number, because the two files that prove it are rarely compared line by line.
What I built
- A Python tool that matches two exports — orders against shipments, or stock against stock — and lists every difference with its value.
- Key normalisation, so
#1041,1041andSO-1041are one order, and two spellings of a product code can still be paired. - It refuses to guess: when the keys match too poorly, it prints a warning instead of a number — a broken match looks exactly like a big discrepancy.
- Cancelled, returned and recent orders excluded before anything counts as missing.
The code is public: github.com/o-podpriatov/shopify-3pl-reconciliation.
What it found on public data
- Orders against shipments, 50,231 orders: 0.64% paid for and never shipped. In the worst hour, 42 of 43 orders were lost, across 42 different products — a connection failure, not a stockout.
- Picking against orders, 122,370 order lines: compared as exported, the files said 27.3% "ordered, not picked" — because one
system wrote a shoe size as
105and the other as10.5. After normalising the key, 97% matched; 4.58% were ordered and never picked. - Purchasing with three-way matching in SAP: 4.51% of received items had no invoice, and 4.06% were invoiced more than once.
One key fix — 122,370 order lines
The same shoe size, written two ways by two systems.
System A
10510.5
System B10.5
Each dataset changed the tool: product-code normalisation, the refusal threshold, and the exclusions all came from a real file that broke the first version.
Relevant if your role mentions
Reconciling data across systemsDiscrepanciesData validation Python / pandasInventory accuracyOrder dataTwo systems that should agree, and don't? Tell me which two — two lines are enough.
Email me← 16× faster reportsNext: a data platform for an energy lab →