Why Audit Target Data Before Modeling?
A ratemaking model can calculate precisely from an inaccurate target. Missing exposure changes the weight assigned to a policy, unmatched claims can disappear from the loss total, and duplicate records can make a small group appear much lossier than it is. These are data-control problems, not modeling problems: fitting a more sophisticated model does not repair them.
An audit compares independently prepared totals and records before model fitting. The aim is not to force every number to agree. It is to find differences, trace them to a source or definition, and document whether they require correction. A suspicious value should be investigated, not silently deleted or replaced.
The target definition matters. For example, claim records may represent one row per claim at a valuation date, or multiple rows per payment transaction. Repeated claim identifiers are not automatically duplicates: several distinct payments on the same claim may be legitimate transaction records. First establish what one row is supposed to represent, then test whether the data follow that rule.
Build Controls Around the Record Grain
A useful audit starts with an expected set of policy-periods and an expected set of claims, both defined using the same eligibility rules as the model file. Let \(e_i\) denote the earned exposure for policy-period \(i\), \(N_i\) its claim count, and \(X_j\) the loss amount for claim record \(j\). A short set of controls can reveal substantial problems:
- Exposure completeness: Compare the expected policy-period keys and total exposure with the model file. Check for missing rows, duplicate keys, invalid exposure values, and unexplained differences by period or segment.
- Claim matching: Match claim records to eligible policy-periods using the appropriate identifiers and coverage period. Count unmatched claims and total their amounts; do not assume that a failed match means the claim is invalid.
- Uniqueness: Test the key that should identify one record at the declared grain. Review repeated keys against the underlying source to distinguish duplicate snapshots from valid transactions.
- Amount plausibility: Compare amounts with coverage limits, permitted signs, units, and source-system values. Flag exceptions for investigation rather than automatically capping them.
- Period reconciliation: Compare totals across claim, policy, accounting-period, and target extracts using the same loss basis and valuation date. If adjustments are applied, reconcile both before and after adjustment.
Here \(\mathcal{P}\) is the expected set of eligible policy-periods. The difference is a diagnostic, not a correction. A nonzero result may arise from missing records, duplicate records, different eligibility rules, or a mismatch in the definition of exposure. The audit must identify which explanation applies.
Worked Example: Missing Exposure and an Unmatched Claim
Worked Example: Missing Exposure and an Unmatched Claim
A hypothetical insurer expects four eligible policy-periods in a small commercial portfolio. Its policy control file lists exposures of 0.75, 0.50, 0.40, and 0.35 earned car-years. The prepared target file contains the first, second, and fourth records only. Four claim records are associated with the expected policy-periods, with losses of $1,200, $900, $700, and $300 respectively. Audit the exposure and claim totals, then compare the pure premium implied by the complete control data with the one implied by the incomplete target file.
Method. Reconcile policy-period keys and exposure first, then match claims to those keys. Assume the loss amounts are on the same valuation and coverage basis, and that each policy-period is eligible.
The control exposure is \(0.75+0.50+0.40+0.35=2.00\) car-years. The target exposure is \(0.75+0.50+0.35=1.60\) car-years, so the exposure difference is \(1.60-2.00=-0.40\) car-years. The missing key is the policy-period with 0.40 car-years.
The complete claim total is \($1,200+$900+$700+$300=$3,100\). The claim with a $700 loss belongs to the missing policy-period, so it does not match to the prepared target file. The matched-loss total is \($1,200+$900+$300=$2,400\); one claim, totaling $700, is unmatched.
For this check, pure premium is total loss divided by total exposure. The control value is \($3,100/2.00=$1,550\) per car-year. The incomplete target value is \($2,400/1.60=$1,500\) per car-year. The difference is not zero even though both numerator and denominator are understated.
Check. The exposure shortfall is exactly 0.40 car-years, the missing policy-period’s exposure, and the unmatched amount is exactly its $700 claim. The control and target loss totals plus their respective matched exposures reconcile to the listed records. The common mistake is to conclude that missing exposure and missing loss cancel because both are absent; the calculated values show that they need not.
Deriving a Useful Reconciliation Identity
A claim-level loss total and a policy-level loss total should agree when they use the same eligible records, loss basis, and period. To see why, let \(J_i\) be the set of claim records assigned to policy-period \(i\). If the sets \(J_i\) are disjoint and together contain every eligible claim record, then each claim amount appears exactly once when the policy totals are summed:
This is a partition identity: regrouping a complete set of amounts does not change their sum. If the totals differ, at least one condition needs examination. A claim may be unmatched, assigned more than once, excluded under a different rule, or valued on a different basis. The identity does not tell you which explanation is correct; record-level tracing does.
The same logic explains a common join problem. Suppose one policy has one exposure row but two legitimate claim rows. Joining the policy row to the claim table produces two joined rows. Summing exposure from the joined table counts the policy’s exposure twice. When exposure and claims have different record grains, aggregate or otherwise structure them so that a join cannot multiply one side’s amounts.
State whether a row represents a policy-period, claim snapshot, or payment transaction, and define the eligible population, valuation date, and loss basis.
Compare expected and prepared policy-period keys; test the appropriate uniqueness key for claim records; list missing, unmatched, and repeated keys.
Compare exposure and loss totals at a common grain and period. Break differences down by period or segment to make tracing manageable.
Use source records and coverage rules to resolve discrepancies. Preserve the reason for each correction, exclusion, or accepted difference.
Worked Example: Duplicate Records and an Implausible Amount
Worked Example: Duplicate Records and an Implausible Amount
A hypothetical target extract is defined as one row per claim, with net paid loss after policy limits. The applicable limit is $30,000 per claim. The extract contains these rows: C-31, $800; C-32, $1,200; C-33, $42,000; C-44, $600; and a second row for C-44, $600. A source payment record confirms that C-33’s net paid amount is $4,200, not $42,000. Audit the extract and calculate the corrected claim total.
Method. First test uniqueness at the stated one-row-per-claim grain. Then test the amount against the stated limit and trace the exception to its source. A repeated key should be removed only after confirming it is a duplicate snapshot, not a valid separate transaction.
The raw extract total is \($800+$1,200+$42,000+$600+$600=$45,200\). Claim C-44 occurs twice, so the second $600 row is a duplicate under the stated grain. Removing that confirmed duplicate gives \($45,200-$600=$44,600\).
The $42,000 amount for C-33 exceeds the $30,000 per-claim limit by \($42,000-$30,000=$12,000\). It is therefore implausible for a net-paid target under this definition and must be checked. The source record confirms $4,200, a difference of \($42,000-$4,200=$37,800\). Replacing the amount with the verified source value gives a corrected total of \($44,600-$37,800=$6,800\).
Check. Summing the distinct, verified claim amounts gives \($800+$1,200+$4,200+$600=$6,800\). The answer uses both the duplicate-key check and the limit/source check. The common mistake is to cap C-33 at $30,000 automatically; the source establishes $4,200, so applying a cap would still leave the target wrong.
Worked Example: Reconciling Period Totals and Adjustments
Worked Example: Reconciling Period Totals and Adjustments
A hypothetical insurer compares a claim-system extract with a prepared target file for two six-month periods. In the first period, the claim-system total is $3,000; in the second, it is $4,000. The target file reports $3,000 and $5,000 respectively before adjustment. The documented factors to a common period are 1.06 for the first period and 1.02 for the second. The expected exposures are 4.0 and 5.0 car-years. Find the period discrepancy, the correctly adjusted total, and the pure premium. Assume the factors apply once to the claim-system amounts.
Method. Reconcile the unadjusted period amounts first. Then apply each period’s given factor to its own verified amount and check the adjusted total against an independently calculated control.
The first-period discrepancy is \($3,000-$3,000=$0\). The second-period discrepancy, target less claim system, is \($5,000-$4,000=$1,000\). Thus, the prepared target file exceeds the claim-system total by $1,000 before adjustment, and the second-period difference needs tracing before the target can be accepted.
Using the verified claim-system amounts, the first-period adjusted total is \($3,000\times1.06=$3,180\). The second-period adjusted total is \($4,000\times1.02=$4,080\). The common-period total is \($3,180+$4,080=$7,260\). Total exposure is \(4.0+5.0=9.0\) car-years, so the resulting pure premium is \($7,260/9.0=$806.67\) per car-year, rounded to the nearest cent.
Check. The weighted adjusted total is $7,260, and the exposure denominator is 9.0 car-years. Applying the factors to the target-file period amounts instead would produce \($3,000\times1.06+$5,000\times1.02=$8,280\), which is $1,020 higher. That result does not resolve the original $1,000 discrepancy; it carries it into the adjusted target.
Common Audit Traps
- Summing exposure after a one-to-many join. Wrong calculation: if a policy has 1.0 exposure and two claim rows, summing joined exposure gives \(1.0+1.0=2.0\). Correction: exposure remains 1.0; reconcile or aggregate each data grain separately before combining results.
- Calling every repeated claim identifier a duplicate. A claim-level snapshot should normally have one row per claim, but a transaction file can legitimately contain several payments for one claim. Verify the row definition and source before removing records.
- Treating an unmatched claim as zero loss. An unmatched record is an exception whose policy, coverage, or period link is unresolved. Excluding it without investigation can understate losses.
- Automatically deleting or capping unusual amounts. A value outside an expected range is a flag, not proof of an error. Check units, coverage terms, recoveries, and source-system values before changing it.
- Comparing unlike totals. Paid, incurred, and ultimate amounts, or totals at different valuation dates, may differ legitimately. Reconcile only after stating the loss basis and period; otherwise explain the basis difference explicitly.
- Applying a period factor twice. Label whether each file is unadjusted or already adjusted. A total can look plausible while still having been trended twice or not at all.
Check Your Understanding
For each problem, identify the relevant control and calculate the requested result.
- A control file contains policy-period exposures of 0.60, 0.80, and 1.00 car-years. The target file contains only the first and third records. What are the control exposure, target exposure, and missing exposure? (Answer: 2.40, 1.60, and 0.80 car-years, respectively.)
- A claim-level file should have one row per claim and lists D-1 at $500, D-2 at $900, and D-2 again at $900. After source verification confirms the repeated D-2 row is a duplicate, what are the raw total and distinct-claim total? (Answer: $2,300 raw and $1,400 after removing the duplicate.)
- A net-paid target has a $15,000 per-claim limit. It lists three claims at $700, $18,000, and $2,500. Source verification confirms the middle amount should be $1,800. What is the verified total, and by how much did the original total exceed it? (Answer: $5,000 verified total; the original $21,200 total exceeded it by $16,200.)
- Two periods have verified losses of $2,000 and $3,000, adjustment factors of 1.05 and 1.02, and exposures of 2.0 and 4.0 car-years. What are the adjusted loss total and pure premium? (Answer: $5,160 adjusted loss and $860.00 per car-year.)
- A claims extract has three eligible claims totaling $4,200, but only two claims totaling $3,100 match to the policy-period file. How many claims and how much loss remain unmatched? (Answer: 1 claim and $1,100.)