Reconciling the order lifecycle against settlement

Reconciling the order lifecycle against settlement

Most sellers reconcile at the payout level. The marketplace deposits a figure, finance ties it to the settlement summary, the two agree, and the month closes.

That check proves the deposit arrived. It proves nothing about individual orders.

An order can ship, deliver, and then never appear in any settlement file. The payout still balances, because the missing order was never expected in the total that finance was comparing against. Nobody notices. Ninety days later the claim window has shut.

Where order data and settlement data part ways

Order data is keyed by order ID. Settlement data is keyed by settlement cycle. The two were never designed to sit next to each other, and the joins break in predictable ways.

One order can span three cycles. The sale lands in the first. The return credit lands three weeks later in the second. A reimbursement, if you chase it, lands in a third. Read any one cycle in isolation and the order looks wrong.

One settlement line can cover many orders, or none. Reserves, holds, adjustments, and fee lines move money between cycles without carrying an order ID at all. Most spreadsheet reconciliations quietly drop these rows because they will not join. The rows are usually small. The habit of dropping unmatched rows is not.

And some orders simply never arrive. Lost in transit, cancelled after invoicing, delivered but unpaid. Absence is the hardest thing to spot in a report that only shows what is present.

Build a spine keyed by the order

The fix is structural. Every order gets one row that persists across its whole life, and every settlement line joins onto that row.

Each order carries a state. Created, shipped, delivered, return initiated, return received, refunded, settled, part settled, or unsettled past the expected window. The state is derived, not typed by anyone. It updates each time new data lands.

Two rules make this hold up:

Absence is a state. An order with no settlement line and a delivery date 40 days old is not a missing record. It is an exception with an age. The system has to be able to assert that something should have arrived and has not.

Unmatched lines go somewhere. Settlement rows that carry no order ID are counted into their own bucket and reported. They are never dropped. If that bucket grows, you have a data problem worth knowing about before it becomes a money problem.

Fig. 1. Order lifecycle, scheduled pull to closed row.

Ingesting settlement without fighting the file format

On Amazon, use the Date Range report as the ingestion source rather than the V2 settlement report.

The V2 report is bound to a settlement cycle ID. Backfilling means stitching cycles together in order, and any order that straddles a cycle boundary arrives split across two files you then have to reassemble. Reruns are awkward, because the unit of the file is the cycle rather than a window you choose.

The Date Range report lets you pull a fixed window and pull it again later. That matters because late credits are normal. A return processed in week six against an order from week two will not exist the first time you read that window. It will exist the second time.

Two more things worth getting right at ingestion:

Make it idempotent. Reading the same window twice should produce the same result, not duplicate rows. Key on transaction identity, not on file position or row order.

Keep a rolling reopen window. Once a period is closed, late data still needs a path in. If reopening a month is a manual exercise, someone will skip it, and the skipped credits become permanent.

The reports this runs on

No single report answers the question. The order spine, the money, and the physical unit movement live in different places, and each has its own refresh cadence. This is the set we ingest.

A few points about why the list looks like this.

The spine comes from the all orders report, not from settlement. It covers every channel, so orders that never reach a settlement line still exist as rows you can count. Build the spine from anything settlement shaped and the missing orders stay invisible, which is the failure this whole exercise exists to prevent.

The reimbursements report is the one people skip, and skipping it is expensive in the other direction. Without it you file claims for orders Amazon has already credited. Those claims get rejected, and a queue full of rejections is how finance teams lose faith in the system after week three.

Returns and replacements explain short settlements. A settlement value below the order value is not automatically a claim. It is a claim only when no return was received and no replacement was issued. Ingesting both turns a large pile of variances into a small pile of real exceptions.

Refresh cadence sets the shape of the pipeline. Two sources update near real time, four update daily, and the Date Range report is pulled on request. So the reconciliation run is daily, and the Date Range pull is scheduled around it rather than triggered by hand. The 18 month limit on ledger history is the one hard boundary. Anything older than that cannot be rebuilt later, which is a good reason to start storing it now rather than after you need it.

The output is a work queue, not a report

A reconciliation that ends in a spreadsheet of variances ends with a person deciding which lines are worth chasing. That decision is where most of the time goes, and it is the part that quietly gets abandoned in a busy week.

So the output is a queue. Each exception is a claim in a defined category:

• Delivered, not settled, past the expected window
• Returned, not credited
• Refund issued with no matching return receipt
• Settlement value short of the expected order value with no return recorded
• Order absent from settlement entirely

Every claim arrives with its evidence pack already assembled. The order record, the invoice, the shipment, the delivery confirmation, the return record if one exists, the settlement lines that did land, the lines that were expected and did not, and the age of the exception in days. One screen. The person filing the claim reads and files rather than hunting across four exports.

To be clear about scope, the question here is whether each order was settled and for what. It is not a fee audit. Those are different problems with different data and they should not be bolted together.

What actually changed

Month end close on marketplace reconciliation went from three weeks to twenty minutes. Finance effort on the process dropped by 67 percent.

Those are the numbers people quote, but they are not the point. The claim window is the point.

Marketplaces cap how long after an event you can raise a claim. When your close takes three weeks, every exception is already three weeks old before a human sees it. Add the internal queue and the time to gather evidence, and a meaningful share of exceptions expire before anyone files them. Money you cannot claim is money you have given away, and it never appears as a loss anywhere. It shows up as revenue that was simply lower than you expected.

Cutting the close to twenty minutes does not just save finance hours. It moves exceptions into the claimable window while they are still worth something.

Where to start

You do not need a system to run the first check. You need one number.

Pick a 30 day window that closed at least 60 days ago. Count the orders. Count how many of those orders you can trace to a settlement line. The gap is the number that starts the conversation, and in our experience it is never zero.

Then decide the expected settlement window for each marketplace and payment method, because without that number you cannot call anything late. Then build ingestion properly before anyone asks for a dashboard. A dashboard over unreliable joins is worse than no dashboard, because people believe it.

Problem first. Process second. Software last.

We build order to settlement reconciliation systems for marketplace sellers, including ingestion, exception queues, and claim evidence packs. If your close runs in weeks and your claims run late, [get in touch](/contact).