Commission Reconciliation Is a Data Problem, Not a Staffing Problem

An FMO supporting 6,000 agents across 23 carriers was spending 180 staff-hours per reconciliation cycle - four people working full-time for over a week every month. Leadership assumed the problem was staffing. It was not. Adding a fifth person would have reduced the cycle from 180 hours to 150 hours and introduced a new coordination overhead. The actual problem was that 23 carriers sent commission data in 23 different formats, and four people were manually translating all of them into a single spreadsheet.

The fix was not more staff. It was a normalization pipeline that ingested all 23 feeds, mapped them to a canonical schema, ran automated matching, and surfaced only the exceptions that required human judgment. The reconciliation cycle dropped from 180 hours to 12.

Why reconciliation is hard

Commission reconciliation is a specific instance of a general data problem: multiple source systems, no shared schema, no single source of truth.

Schema fragmentation. Carrier A sends a CSV with columns named "AgentID, GrossComm, PolicyNum." Carrier B sends an Excel file with "Producer_Code, Commission_Amount, Contract_Number." Carrier C sends a fixed-width text file with positional fields documented in a PDF from 2019. Same data, three completely different representations.

Every column name mapping, every date format conversion, every agent identifier crosswalk is a translation rule that someone has to maintain. In a spreadsheet, those rules live in the heads of the people who built the spreadsheet. When those people leave, the institutional knowledge leaves with them.

Manual reconciliation means a person looks at each ambiguous record, cross-references multiple sources, and makes a judgment call. That judgment call is usually correct - but it is undocumented, unreproducible, and lost when the person stops doing it.

Error compounding. When a reconciliation error is not caught in period 1, it compounds in period 2. An unmapped agent produces a growing balance of unreconciled commissions. A misclassified product code creates a systematic offset that grows every cycle. By the time someone notices, the correction requires tracing back through multiple periods of accumulated errors.

The engineering approach

The solution has three components: ingest, normalize, and match.

Ingest handles the physical receipt of commission data. Some carriers post files to an SFTP server. Others email Excel attachments. A few have APIs. The ingest layer standardizes this - every carrier feed, regardless of delivery mechanism, produces a raw file in a staging location with consistent naming and timestamp metadata.

Normalize transforms every carrier's raw format into a canonical schema. The canonical schema defines exactly what a commission record looks like internally: agent ID (mapped to internal identifiers), policy number (normalized format), commission amount (decimal, USD), commission type (new business, renewal, override, bonus), period (YYYY-MM), and carrier code.

Each carrier gets a mapping configuration - a set of rules that translate from the carrier's column names, formats, and identifiers to the canonical schema. These rules are maintained as data, not code. When a carrier changes their report format, only the mapping configuration changes - not the pipeline.

Match compares normalized commission records against expected commissions from the AMS. The matching engine applies rules in priority order: exact match on policy number and agent, fuzzy match on agent name when ID crosswalk fails, amount-based match for split commissions, and period-adjacent match for timing differences.

Records that match cleanly are marked as reconciled. Records that do not match are classified by exception type - unknown agent, amount discrepancy, missing policy, new product code - and surfaced in an exception queue for human review. The queue shows the exception, the relevant context from both systems, and a suggested resolution.

What changes operationally

The team that was spending 180 hours on reconciliation now spends 12 - mostly reviewing the 8 - 12% of records that the system flags as exceptions. Exception resolution is faster because the system presents the context: here is the carrier record, here is the closest AMS match, here is why the match failed.

More importantly, reconciliation shifts from a monthly crisis to a continuous process. Instead of accumulating a month of data and reconciling in batch, the pipeline runs daily. Exceptions surface within 24 hours of the carrier file arriving, not 30 days later. Error compounding stops because problems are caught in the period they occur.

The ROI is not just the staff-hours recovered. It is the elimination of the reconciliation backlog, the reduction in aged receivables, and the recovery of commissions that were previously written off because nobody had time to chase the discrepancy.

What it takes to build

The critical path is not the engineering. It is the data mapping work - obtaining sample files from every carrier, documenting every field, identifying every format variation, and building the crosswalk between carrier agent identifiers and internal records.

For the first five carriers by volume, this mapping work takes 2 - 3 weeks. The engineering to build the ingest-normalize-match pipeline takes another 3 - 4 weeks. Each subsequent carrier adds 3 - 5 days of mapping and configuration work, because the pipeline and canonical schema already exist.

A team that has built commission reconciliation systems before has a library of carrier-specific mapping patterns. Common carriers - the top 20 in life, health, and P&C - have well-known format quirks that do not need to be rediscovered. This is where domain-specific engineering experience produces the largest cost reduction.