Cases · Reconciliation
Reconciliation protocol: store orders vs payment processor
Self-initiated case study using synthetic data: a store's order export and a payment processor's export.
Goal
A store wanted to know whether its order export and its payment processor's export for 1 August to 1 September 2026 agree: every order that should be paid has a payment for the same amount, and nothing was paid that is not an order.
Challenge
1,200 orders and 1,035 payments. Cancelled orders should have no payment, some orders were charged twice, one payment came in another currency, and some payments have no order at all. A cent of rounding must not count as a difference, and a real difference must not hide inside a total.
Solution
Rules were written down before counting: order_id is the key; the 1,034 paid and refunded orders should have a payment, the 166 cancelled ones none; the order total must equal the payment amount in the same currency, within 0.01. Every order was set against its payments and every payment against its order, and a table was built from the store's total to the processor's.
Outcome
1,022 of 1,034 orders reconcile, 3 of them a cent apart. 15 records do not: 4 paid orders with no payment, 5 amounts that differ, 2 orders charged twice, 1 payment in another currency and 3 payments with no order. The table goes from EUR 249,511.39 of paid and refunded orders to EUR 249,278.77 of EUR payments, line by line. Not checked: whether refunds reached customers (refunds are in neither file), the processor's fees, and whether either file is right on its own.
From the case
The protocol, 1 page (PDF)
Service
Reconciliation for your own files
The same method on your exports: definitions in writing first, then an independent recount.