Carrier statement intake readiness worksheet

Why mapping matters

Every carrier sends commission statements in a different format. Different column names, different policy number formats, different date conventions, different product code structures. Aetna might send a XLSX with a policy number in column B. UnitedHealthcare sends a pipe-delimited text file with the member ID in a different position. Humana changes their column headers every year without notice.

Without a documented mapping for each carrier, every import cycle requires manual interpretation — someone reads the file, figures out what column is the policy number this month, and hopes it is the same next month. This is where import errors come from. A policy number read from the wrong column creates a reconciliation failure that is nearly impossible to trace back to the source.

Carrier intake mapping is not glamorous work. But it is the foundation that everything else in commission operations depends on. A well-documented mapping turns a 3-hour monthly import task into a 20-minute validation run.

Carrier profile

For each carrier in your book, document the following before setting up the intake process:

• Carrier name and NAIC code — the NAIC code is the carrier's unique regulatory identifier. Using it as the primary carrier key rather than the name prevents issues when carriers rebrand or rename products.
• Lines of business — which product lines this carrier writes through your agency. This determines which comp plan types you need configured.
• Statement frequency — monthly, bi-monthly, or quarterly. Some carriers issue mid-month corrections. Document whether correction statements replace or supplement the original.
• Statement delivery method — carrier portal login required, email attachment, or API. Document the URL and credential storage location for portals.
• Primary contact — the carrier's commission operations contact for discrepancy inquiries. Commission questions routed to the wrong department can take weeks to resolve.
• Contract reference — the document name or ID of the executed compensation agreement. This is the source of truth for rate disputes.

File format and delivery

Carrier statement files come in a surprisingly wide variety of formats. Documenting the format before you start importing prevents mid-cycle surprises.

• File type — XLSX, XLS, CSV, pipe-delimited text, fixed-width text, PDF. If the carrier only provides PDF, you will need a manual extraction step or OCR processing.
• Encoding — most files are UTF-8, but legacy carrier systems sometimes produce CP1252 or Latin-1 encoded files. Encoding mismatches cause import errors on names with special characters.
• Header row location — some files have header rows on row 3 after summary rows. Others have no header at all and rely on column position.
• Multi-sheet files — some carriers send XLSX files with one sheet per product line. Document which sheet or sheets contain the commission data.
• Totals rows — many files include subtotals and grand totals that should not be imported as commission rows. Document where these appear so import validation can skip them.
• Date format — MM/DD/YYYY, YYYY-MM-DD, M/D/YY. Date format inconsistencies cause silent errors in effective date processing.

Required field mapping

For each carrier, document which column in their statement file corresponds to each required field in your commission system. The minimum required fields for reconciliation are:

• Policy number — the carrier's policy identifier. This is the key that matches the statement row to your internal policy record. Confirm whether the carrier uses the same policy number as appears on the policy document, or an internal processing number.
• Member/insured name — not required for matching, but useful for manual verification when policy numbers do not match exactly.
• Product code — the carrier's internal code for the product line. This maps to your product registry and determines which comp plan applies.
• Effective date — the policy effective date. Used in graded schedule calculations to determine policy year.
• Premium amount — the modal or annualized premium used to calculate the commission. Confirm whether the carrier reports modal or annualized premium and which payment frequency they assume.
• Commission amount paid — the actual commission amount the carrier is reporting as paid. This is the credit side of your carrier ledger.
• Revenue type — the category of commission (first-year, renewal, override, bonus). Some carriers combine these; others break them out.
• Agent/producer code — the agent identifier in the carrier's system. This may differ from your internal agent ID.

Product code registry

The product code registry maps each carrier's internal product codes to your internal product definitions and their associated comp plans. This mapping is the single most important reference file in your commission intake process.

For each carrier, create a mapping table with at minimum:

• Carrier product code (exactly as it appears on the statement)
• Carrier product name
• Your internal product name
• Your internal comp plan ID
• Effective date range for this mapping (some carriers change codes annually)
• Notes on any exceptions or special handling

Review the product code registry before each statement cycle for carriers that issue product updates on January 1 or July 1. Add new codes before the first statement that uses them arrives, not after.

Track unmapped product codes explicitly. When an import produces a row with a product code not in your registry, it must go to an exception — not be silently dropped. Silent drops are the primary source of invisible leakage.

Known data quality issues

Every carrier has known, recurring data quality issues that affect reconciliation. Document them before they surface as unexplained exceptions. Common patterns include:

• Policy number format changes — carrier migrated systems and old policies now have a different number prefix. Map old and new formats to the same internal record.
• Missing renewal rows in specific months — carrier's statement extract logic drops renewal rows for policies that renew on certain dates. Document the pattern and expect those rows to come in the following month's statement.
• Mixed-period statements — carrier sends one statement covering multiple periods with a date column that determines which month each row belongs to. Import logic must split by period.
• Bonus rows without policy references — carrier sends contingent bonus payments as aggregate rows without policy-level detail. These require manual allocation or special exception handling.
• Premium amount in thousands — carrier reports premium in thousands (e.g., 1.250 means $1,250). Import logic must apply the correct multiplier.

For each known issue, document: how to identify it in the file, what the correct import behavior should be, and whether any manual step is required after import.

Intake readiness checklist

Before adding a new carrier to your commission system, confirm you have documented:

Run a test import using a prior period's statement before relying on the mapping in a live cycle. Validate that: the row count matches the statement, policy numbers match your internal records on a sample basis, the commission amounts imported correctly, and no product codes are unmatched.