Matching orders to transactions to payouts

The word matching suggests pairs. The relationships in payment reconciliation are not pairs, and treating them as pairs is why matching logic that looks correct produces results that are not.

Getting the shapes right first makes the rest straightforward.

The actual relationships

An order has one or more transactions. An authorisation and a capture are two. A retried payment is several. A partial refund adds another. A customer paying in two parts adds more. A one-to-one assumption breaks on the first order that was authorised and captured separately.

A transaction belongs to exactly one payout, or to none yet. This is the clean relationship in the chain and the one to anchor on.

A payout contains many transactions, plus fee, refund, chargeback and reserve movements that may relate to transactions from earlier periods.

A payout maps to one bank credit. Usually. Some providers combine payouts into one transfer, and some split a large one, so this needs verifying with your provider rather than assuming.

So the chain is one-to-many, many-to-one, one-to-one — and each hop needs its own logic. A design that models it as order equals payment equals deposit will work on a clean test set and fail on real data in ways that are hard to trace.

Matching in three tiers

Ordered by reliability. Always exhaust a tier before falling to the next, and always record which tier produced a match.

Tier one: the shared reference. Your order reference present in the gateway's transaction record, or the gateway's reference stored on your order. Deterministic, unambiguous, and the only tier that scales. Where this exists, most of your volume matches here and nobody looks at it — see the gateway reference that joins everything.

Tier two: composite matching. Amount plus date plus payment method plus, where available, the last four digits of the card. Reliable when the combination is unique in a narrow window, and this is the point to be careful: if two candidates fit, do not pick one. Two customers buying the same item on the same day is not an edge case, it is Tuesday.

Tier three: a person. Everything else. The goal is not to eliminate this tier but to keep it small and to make each item a quick decision rather than an investigation.

Record the tier on every match. This is a small implementation detail with disproportionate value: the share of volume matching at tier one is the health measure of the whole integration. A drop means a reference has stopped flowing, which is a configuration change somewhere, and you want to know within a day rather than at month end.

The rules that prevent confident nonsense

Four constraints. Each one exists because its absence produces a specific class of wrong answer that looks right.

Never match one-to-many by guess. If two orders could explain one transaction, neither is matched. An ambiguous match resolved by picking the closest is worse than no match, because it removes the item from the exception queue and puts a wrong allocation into the books.

Never allow a transaction to match two orders. Enforce it structurally rather than trusting the logic. Duplicate matching is the mechanism by which revenue gets counted twice, and it is invisible afterwards because both entries look legitimate.

Handle partial amounts explicitly. A transaction that is part of an order's value is a partial payment, not a failed match on a different order. Modelling it as its own state prevents a search for the order that would explain the whole amount, which does not exist.

Age before investigating. A payment recorded today may settle in three days. An order with no transaction is only an exception once it is older than your provider's normal settlement lag. Investigating same-day differences wastes most of the effort spent on reconciliation.

The date problem, which is bigger than it looks

Three dates apply to the same money and they are routinely different.

The order date at your store. The transaction date at the gateway, which can differ by a timezone or by a capture happening later. The payout date, days after both.

Two consequences.

Matching within a fixed window fails at boundaries. A payment at eleven at night, recorded at your store on one date and at the gateway on the next, will not match a same-day rule. The window has to be wide enough to absorb timezone and capture differences, and narrow enough that composite matching stays unique.

Periods do not align. Sales in one month settle in the next, so a month-end cut always has transactions in flight. This is not an error to be resolved but a state to be represented — see reconciling across a period boundary.

Establish which timezone each source reports in, once, in writing. It is a five-minute question and it explains a whole category of mysterious near-misses.

What good looks like in numbers

Four measures, tracked over time rather than inspected at month end.

Share matched at tier one. Should be high and stable. Movement is the signal.

Share needing a person. Should be low and falling as rules improve.

Age of the oldest unmatched item. Should be days, not weeks. A month-old unmatched transaction is a problem that has been decided against rather than resolved.

Value unexplained. The total of everything not yet matched, which should be small and clearing on a rhythm — see what 98% automated reconciliation means.

The reason to track rather than inspect is that all four degrade gradually. A reference that stops flowing does not break anything; it moves volume from tier one to tier two, and the books still close, with more people involved every month.

Common questions

Why is one order not equal to one payment?

Because an order can carry several transactions: an authorisation and a separate capture, retried attempts, a customer paying in two parts, or a partial refund. Matching logic that assumes one payment per order works on clean test data and fails on the first order that was authorised and captured separately.

How should ambiguous matches be handled?

By leaving them unmatched. If two orders could explain one transaction, picking the closest removes the item from the exception queue and writes a wrong allocation into the books, where it is far harder to find later. Two customers buying the same item on the same day is routine rather than exceptional, so the ambiguity has to be resolved by evidence or by a person.

Why record which matching method succeeded?

Because the share of volume matching on a shared reference is the health measure of the whole integration. If a reference stops flowing after a configuration change, nothing breaks — volume simply shifts to composite matching and more items reach a person each month. Tracking the tier surfaces that within a day instead of at month end.

Why do dates not line up between store, gateway and bank?

Three different dates apply to the same money: the order date at the store, the transaction date at the gateway, which can differ by timezone or by a later capture, and the payout date days after both. A same-day matching rule therefore fails on evening transactions, and the matching window has to absorb those differences while staying narrow enough for composite matching to remain unique.

Related: decomposing a payout line by line · the three-way match a Malaysian store needs · the exception queue and how to size it