Automating Commission Statement Reconciliation Across Insurers

That commission reconciliation is necessary, and painful, is settled ground. Our operations playbook on broker commission disclosure and reconciliation covers the reconciliation duty and what a broker must disclose to a client. This post asks the narrower question it does not: why is this so hard to hand to a machine, and how do you build the machine anyway.

Reconciliation is a record linkage problem, and record linkage is only easy when both sides share a stable primary key. Your placement register and the insurer's statement do not. They came from different systems, from different masters, with no contract between them about identity.

What arrives on a broker's shared drive each cycle:

• Native PDFs with a machine-readable text layer, from the insurer's policy admin system.
• Scanned PDFs, produced when a branch prints, signs, and rescans. No text layer at all.
• Excel workbooks with merged header cells, or a totals row sitting in the middle of the data.
• Portal exports as CSV, where the column set changes without notice when the insurer ships a release.
• Fixed-width text files from an older extract, where field boundaries live in a specification nobody at either firm has read this decade.

A broker placing with fifteen insurers is not building one reconciliation but fifteen, and the count grows when an insurer emits one format for motor and another for commercial fire.

The second structural problem is the policy number. It is the obvious key, and it is not a key. The same policy appears in your register as P/141234/11/2026/000123 and on the statement as 141234112026000123, or with leading zeros stripped by Excel, the branch prefix omitted, or an endorsement suffix appended that your register holds on a separate row. Nothing enforces a canonical form: no regulation specifies one, and no interchange standard binds an Indian insurer to its brokers. Non-life commission expense ran to roughly INR 47,266 crore in FY2024-25, and every rupee of it moves through files shaped like the ones above.

Getting rows out of a statement is a distinct problem from deciding what they mean. Three approaches exist, and the mistake is to pick one and apply it everywhere.

Template per insurer

You write an explicit rule set per insurer and format: this column is the policy number, this one the commission amount, the header occupies rows one to four. Templates are exact, cheap to debug, and give a controller a rule she can read. They are brittle in a specific way. They do not fail loudly when the insurer inserts a column; they fail quietly, mapping GST into the commission field, and the totals still foot to a wrong number. Every template needs a shape assertion that halts the run when the header signature changes, rather than a best-effort fallback.

Layout-based extraction

Rather than naming columns, you infer table structure from geometry: x-coordinates of text runs, ruling lines, whitespace gutters. This survives cosmetic changes and generalises to formats you have never seen, but it struggles precisely where insurer statements are hardest. Multi-page tables are the standard failure. A statement that continues a policy's instalment rows onto page seven without restating the policy number produces orphan rows unless the parser carries forward the last non-empty key, and that carry-forward is a heuristic that will eventually be wrong.

Learned extraction

A model that reads the document and emits structured fields handles scanned statements, unpredictable layouts, and the long tail of one-off formats through a single code path. This is where machine learning earns its place: a scanned branch statement is close to intractable with rules and quite tractable with OCR plus a model that understands the page.

So the honest architecture is a cascade. Route to a template where you have one, because that path is faster, cheaper, and auditable, and send the unrecognised and the scanned down the learned path. Measure the split, because the volume falling to the expensive path tells you whether template coverage is decaying.

Once rows exist, engineers reach for a join. The join is where the design lives, because the relationship between a statement line and a placement is not one to one.

Exact match on a normalised key

Start here, and expect it to carry most of the volume. Normalisation is the work: strip separators, upper-case, left-pad the numeric block, split the endorsement suffix into its own attribute rather than concatenating it into the key. Keep it a pure function, version it, and record which version produced a given match, because changing it changes the meaning of every historical match.

Fuzzy match on the attribute tuple

What remains has a broken, missing, or ambiguous policy number, and must be matched on insured name, premium, and inception date together. Never on one alone. Name comparison needs edit distance plus Indian canonicalisation: Pvt Ltd, Private Limited and (P) Ltd are the same suffix, and M/s is not part of the name. Premium is a strong signal, but only once you decide whether you compare gross premium, premium net of GST, or the commissionable base, and the statement may not say which. Blocking matters for cost: generating candidates within insurer plus inception month shrinks the comparison space before you score anything.

The cardinalities that break naive joins

1. One placement, many statement lines. Instalment premium: each instalment raises its own commission line, and a join assuming uniqueness will either fan out your register rows or silently keep the first.
2. Many placements, one statement line. A bulk payout settling a batch of small motor policies as one credit, with the constituent policies named only in an attachment, or not at all.
3. Unmatched on the statement side. The insurer has paid commission for which you hold no placement: often an endorsement you never booked, a co-broked share, or another firm's business misallocated to your code.
4. Unmatched on the register side. You expect commission that has not arrived. This is the direction that costs money, and the direction a system built to reconcile statements will structurally under-report, because nothing in the input file tells you a row is missing.

Every candidate match carries a score. The design decision is what the system may do at each score, and that is a policy question wearing engineering clothes.

Three bands work. Above the upper threshold the system posts with no human involvement. Below the lower threshold it does not propose at all, because a bad proposal costs a reviewer more attention than silence. Between the two it proposes, ranks, and asks, and the reviewer accepts, rejects, or reassigns.

Two properties matter more than where you first set the lines. Thresholds must be tunable without a deploy, every change recorded with an actor and a timestamp, because you will move them after an insurer changes its format and your scores shift underneath you. And the reviewer's decision is training signal: a rejection is a labelled negative pair, and a reassignment is a labelled positive for a pair your scorer ranked below the winner, the most informative label available. A system that discards these and asks the same question next cycle wastes its most expensive input.

Design around the asymmetry: a false accept posts money against the wrong policy and surfaces late, usually when a client queries a remuneration figure, while a false reject costs ninety seconds. Never let an auto-accept-rate dashboard drive the upper threshold higher. That metric improves fastest when the system is getting things wrong.

The instinct is to optimise the match rate. What decides whether the system survives contact with a finance team is the handling of what does not match.

If 95 percent match cleanly, the residual 5 percent carries the economic case. Those items skew toward endorsement reversals, co-broked splits, bulk payouts, and policies cancelled then reinstated: high value, and exactly the cases a naive matcher cannot see. The exception queue is not an error log. It is the primary interface, and it should be designed first.

A queue that works has four properties:

• Every exception carries a reason code from a fixed vocabulary: no candidate, ambiguous candidates, amount variance beyond tolerance, key present but insurer unknown, duplicate line. Free text is for the human; the reason code is for the aggregate, and the aggregate is how you find a systemic cause.
• Every exception has a named owner and an age. An unowned exception is a write-off with a delay.
• The queue is grouped, not listed. Forty exceptions from one insurer sharing a reason code are one problem, and rendering them as forty rows guarantees a reviewer works forty problems.
• Resolution writes back. Whatever the reviewer did, a key alias, a tolerance adjustment, a manual link, becomes a rule the next run applies by itself. A queue whose contents recur cycle after cycle is one nobody opens by month four.

Watch composition, not length. A queue holding steady in size while its reason codes rotate is healthy; one holding steady with the same codes has quietly become a manual process with extra steps.

Insurers restate. A statement is reissued because a branch corrected an entry, because a reversal landed against a prior period, or because the first file was wrong. You will receive the same policy, for the same period, twice, with different numbers. The matcher must absorb that without double-counting, and the property that gets you there is idempotency: running the same input twice leaves the same state as running it once.

The design treats the commission ledger as append-only and derives the current view:

1. Give every ingested file a content hash and a statement identity of insurer, period, and sequence. Re-ingesting an identical hash is a logged no-op.
2. Never update a posted commission row in place. A restatement writes a reversal of the prior row plus a new row, both stamped with the statement identity that produced them. A controller can then see what changed and why; an in-place update destroys exactly that.
3. Make match decisions first-class rows, not attributes. A match is an event with a timestamp, a scorer version, a score, and an actor, system or named reviewer. Superseding it writes a new event rather than overwriting the old one.
4. Keep the pipeline replayable. Retain the raw file and the parsed intermediate, so that when you fix the normaliser you can re-derive six months of matches from stored inputs and diff them against what was posted.

Once a model sits in the pipeline there is a temptation to let it do more. Resist it at a specific boundary. Machine learning is the right tool for extraction, and for scoring whether two records describe the same placement. It is the wrong tool for the money.

Once a line is matched to a placement, the arithmetic is deterministic and must be exact. Rate applied to commissionable base, rounded to the paise under one stated convention, GST separated on a fixed rule, reversal netted against the original line. No model, no tolerance, no approximation. If your expected commission and the insurer's differ by two paise, that is a rounding convention mismatch with a discoverable cause, not a variance to absorb. Money reconciliation is not a probabilistic task. The probabilistic part ends at identification, and that boundary must be visible in the code rather than blurred across it.

That boundary is what makes the trail explainable. Every automated match should reconstruct on demand, without an engineer, as a chain a controller can read:

• The source file, its hash, and when it landed.
• The extraction path taken, template or learned, and its version.
• The candidate set considered, the winner's score, and the runner-up's score.
• The threshold in force at that moment and who last set it.
• The arithmetic, with rate and base shown, not just the result.
• Any later reversal or supersession, with its cause.

The runner-up score is the element teams omit and auditors want: a match at 0.94 where the next candidate scored 0.31 is a different fact from one where the next scored 0.93, and only the second warrants a look.

None of this is only engineering hygiene. The IRDAI (Payment of Commission) Regulations, 2023 already require a broker to hold the basis for commission on each placement, and that chain is what holding the basis looks like once a machine produced the number.