Reconciling Optimizer Outputs to Policy Records

Why Reconciliation Is a Separate Control

An optimizer can return a feasible set of price decisions and still produce an implementation file that does not tie to the intended policy population. A policy may be absent, represented twice, matched to the wrong term, or priced using an outdated input. These are data and implementation failures, not necessarily optimization failures. The control objective is to show exactly which eligible policy records received which decisions, and to explain any difference between expected and observed counts or premium totals.

The first requirement is to define the population being reconciled. “Policies in the optimizer” is not enough: specify the renewal period, the eligibility rules, and the data snapshot date. A stable key should identify one decision unit, commonly a policy identifier combined with the renewal term or effective date. A policy identifier by itself may not be unique if the extract includes multiple terms, locations, or coverage records.

Definition: Let $E$ be the set of eligible policy-period records at the agreed snapshot, $O$ the optimizer decision records, and $R$ the records found in the policy system after the recommendations are mapped or loaded. A reconciliation matches records by a defined unique key, then checks (1) key coverage, (2) key multiplicity, and (3) the assigned premium or price for each matched record. A record count is meaningful only after the unit of record and eligibility rules are fixed.

A recommended price is not automatically a bound policy or a realized premium. Some eligible policies may not renew, may be withdrawn, or may require a separate operational decision. For a reconciliation of implementation, state whether $R$ means records receiving a loaded recommendation, records issued a renewal offer, or records actually bound. Do not compare an optimizer’s eligible population with a bound-policy extract and label the difference a missing-record error without accounting for that definition change.

Set Up the Reconciliation Before Comparing Totals

Create one row per decision unit in each file. Preserve the original key fields, eligibility status, input snapshot identifier, optimizer-selected price, and the corresponding policy-system price. Test uniqueness in each file before joining. A many-to-many join can multiply rows and inflate totals even when each source file appears individually plausible.

Freeze the population definition.
Record the renewal period, eligibility criteria, and snapshot date. Apply the same definition to the source, optimizer, and policy-system extracts.

Validate the key.
Choose fields that identify one policy decision, then count occurrences of each key in every file. Resolve duplicates before interpreting joined totals.

Compare coverage and amounts.
List keys present in one file but absent in another. For matched keys, compare selected and mapped prices record by record, then sum the differences.

Classify and document exceptions.
Identify whether each discrepancy is due to eligibility, a missing or extra record, a duplicate, a stale input, or a price-mapping issue. Retain the exception list and resolution.

Use a consistent premium basis. For example, compare annualized written premium with annualized written premium, not one file’s monthly installment amount with another file’s full-term premium. If premiums are stored at a more detailed level than the decision unit, aggregate them using a documented rule before comparing. Keep currency precision through the calculation; round only for presentation.

A Reconciliation Identity

Suppose each key is unique in both the optimizer output and the policy-system records. Let $p_O(k)$ be the optimizer price for key $k$, and $p_R(k)$ the mapped policy-system price. The common keys are $C=O\cap R$. The optimizer total minus the policy-system total can be split into unmatched optimizer records, unmatched policy records, and price differences on matched records.

\sum_{k\in O}p_O(k)-\sum_{k\in R}p_R(k) = \sum_{k\in O\setminus R}p_O(k) -\sum_{k\in R\setminus O}p_R(k) +\sum_{k\in C}\left[p_O(k)-p_R(k)\right].

To justify the identity, partition $O$ into $O\setminus R$ and $C$, and partition $R$ into $R\setminus O$ and $C$. Subtract the resulting two sums. The common-key terms can be paired, leaving precisely the three terms shown. Thus a total difference is not itself a diagnosis: the identity identifies the amount attributable to unmatched keys versus differences in mapped prices. It assumes unique keys and a common premium basis. If keys are duplicated, first report multiplicities and correct or explicitly account for them; a set-based join alone conceals duplicates.

Worked Example: Finding an Eligible Record Missing From the Output

A hypothetical renewal optimizer is intended to return one annual price for each of five eligible policy-period records. All amounts below are annual dollars. The eligible list and optimizer output are:

Key	Eligible?	Optimizer price	Present in loaded file?
A101 / 2027	Yes	$1,260	Yes
A102 / 2027	Yes	$945	Yes
A103 / 2027	Yes	$1,470	No
A104 / 2027	Yes	$840	Yes
A105 / 2027	Yes	$1,155	Yes

Method. Reconcile by the composite policy-period key, first checking the count and then the premium total. The expected eligible count is $5$. The loaded count is $4$, so the count difference is $5-4=1$.

The optimizer total is $1{,}260+945+1{,}470+840+1{,}155=5{,}670$ dollars. The loaded total is $1{,}260+945+840+1{,}155=4{,}200$ dollars. The difference is $5{,}670-4{,}200=1{,}470$ dollars.

The key comparison shows that A103 / 2027 is in the eligible optimizer population but absent from the loaded file. Its optimizer price is $1,470, exactly the observed premium shortfall. The record-level exception and the total reconciliation therefore agree. Common mistake avoided: treating a $1,470 total variance as a general pricing error before checking whether a whole record is missing.

Worked Example: Detecting a Duplicate Key That Inflates the Total

A hypothetical optimizer returns three annual prices, and the downstream policy extract is expected to contain the same three policy-period keys once each. The optimizer output is: 3001 / 2028 at $1,020; 3002 / 2028 at $960; and 3003 / 2028 at $1,150. The policy extract contains 3001 / 2028 twice, each at $1,020, plus 3002 / 2028 at $960 and 3003 / 2028 at $1,150.

Step 1: Compare row counts. The optimizer has $3$ rows and the policy extract has $4$. The policy extract has one excess row.

Step 2: Check key multiplicities. Key 3001 / 2028 occurs twice in the policy extract; each other key occurs once. This is a duplicate, not a fourth eligible policy.

Step 3: Compare totals. The optimizer total is $1{,}020+960+1{,}150=3{,}130$ dollars. The raw policy-extract total is $1{,}020+1{,}020+960+1{,}150=4{,}150$ dollars. The excess is $4{,}150-3{,}130=1{,}020$ dollars, equal to the duplicated record’s price.

After investigating the duplicate and retaining one valid row for each key, the corrected count is $3$ and the corrected total is $3{,}130$ dollars, matching the optimizer. Common mistake avoided: dropping duplicates automatically without confirming which row is valid. If duplicate rows have different prices or other conflicting fields, the correct resolution requires source-system investigation, not arbitrary deduplication.

Separate Stale Inputs From Mapping Errors

A stale-input discrepancy can have matching counts and keys. The optimizer may have used an older exposure, current premium, territory, or other input attribute, while the policy system has since changed. A key-only reconciliation will not identify this. Retain a snapshot date or input version and compare material decision inputs for each matched key, not just the final price.

Worked Example: Same Keys, Different Price Because an Input Was Stale

A hypothetical renewal batch contains three eligible keys. The optimizer’s recorded input base premium and selected price, alongside the current policy-system base and mapped price, are:

Key	Optimizer input base	Selected price	Current system base	Mapped price
Q11 / 2029	$1,000	$1,100	$1,000	$1,100
Q12 / 2029	$900	$990	$1,000	$1,100
Q13 / 2029	$800	$800	$800	$800

Step 1: Check counts and keys. Each file has $3$ rows, and all three policy-period keys match. There is no count or coverage discrepancy.

Step 2: Reconcile totals. The optimizer total is $1{,}100+990+800=2{,}890$ dollars. The mapped total is $1{,}100+1{,}100+800=3{,}000$ dollars. The optimizer-minus-system difference is $2{,}890-3{,}000=-110$ dollars.

Step 3: Compare inputs and prices by key. Q11 and Q13 match in both input base and selected or mapped price. For Q12, the optimizer input base is $900, whereas the current system base is $1,000; the selected and mapped prices differ by $990-1{,}100=-110$ dollars. This record fully explains the portfolio difference. The evidence identifies a stale input as a likely cause, but the approved response—refresh and rerun, or apply a documented operational correction—must follow the applicable process.

Common mistake avoided: interpreting a complete key match as proof that the optimizer used current data. The keys establish record coverage; input-version checks establish whether the decision was based on the intended snapshot.

Practical Controls and Exam Traps

A useful reconciliation has both a portfolio summary and a record-level exception file. The summary should show eligible, optimizer, and mapped record counts; unique-key counts; duplicate counts; unmatched-key counts in each direction; and totals on the same premium basis. The exception file should identify the key, the failed check, the relevant source values, and the disposition. A single “total variance” field is not sufficient evidence that the output is safe to implement.

Check eligibility changes explicitly. An ineligible record appearing in the optimizer output is not the same as an eligible record missing from it. Likewise, an eligible policy absent from a bound-policy extract may simply not have bound, if the control is intended to reconcile bound outcomes. Label the populations and timing before drawing conclusions.

Wrong key grain: Joining on policy number alone can match one renewal decision to another term. Include the term or other fields needed to make the key unique.
Many-to-many joins: A duplicate on either side can multiply joined rows and totals. Test key counts before joining.
Unmatched totals treated as price differences: Use key-level comparisons to distinguish a missing record from a price mismatch.
Stale attributes ignored: Equal row counts and matching keys do not prove that input values or snapshot versions agree.
Inconsistent premium basis: Confirm term, currency, and annualized-versus-installment basis before interpreting a total variance.

Key takeaway: Reconcile the defined population by a unique policy-period key, not by total premium alone. Check coverage and multiplicity first, then matched-record prices and input freshness; each discrepancy should have a record-level explanation.

Check Your Understanding

For each problem, state the relevant count or premium check and identify what the evidence supports.

A hypothetical optimizer has four eligible keys priced at $700, $900, $1,100, and $1,300. The mapped file has three matching keys priced at $700, $900, and $1,300. What are the count difference and optimizer-minus-mapped premium difference, and which missing key explains it? (Answer: The optimizer has 1 more record; the premium difference is $1,100; the $1,100 key is missing.)
An optimizer has keys U / 2030 and V / 2030 at $1,250 and $750. A policy extract has U / 2030 twice at $1,250 and V / 2030 once at $750. Find the raw extract count and total, then the corrected unique-key count and total. (Answer: Raw count 3 and total $3,250; corrected count 2 and total $2,000.)
Both files have the same three unique keys. For key M, the optimizer price is $840 and mapped price is $900; for N, they are $1,100 and $1,100; for P, they are $760 and $700. Find the optimizer-minus-mapped total difference and explain whether counts identify the cause. (Answer: $840+1{,}100+760-(900+1{,}100+700)=0$ dollars; counts match, but offsetting record-level price differences of $-$60$ and $+$60$ remain and must be investigated.)
A batch has five optimizer records and five policy records, all with matching unique keys. For one key the optimizer input exposure is 2.0 units and the current source has 2.5 units; its selected price is $1,080 and mapped price is $1,350. Other records match. What is the total optimizer-minus-mapped difference, and what is the principal diagnostic lead? (Answer: $$1,080-$1,350=-$270$; the count and key checks pass, while the changed exposure is evidence to investigate a stale or changed input.)
A source extract contains two rows for policy ID Z in the same renewal year, but one is a separate location. The optimizer output has one row for each location. Is policy ID alone a valid reconciliation key, and what control should be applied before comparing totals? (Answer: No. Use a key that also identifies the location, test uniqueness at that grain in both files, and only then compare counts and premiums.)

What You'll Learn