Claim Count Data Checks

Why Claim-Count Checks Matter

A claim frequency calculation is only meaningful when its numerator and denominator describe the same risks, coverage, and time period. A system extract may contain payment transactions rather than claim events, counts may use different definitions across years, or a claim may be present without the exposure needed to interpret it. These issues can create an apparent change in frequency even when the underlying insurance experience has not changed.

This tutorial focuses on validating the data before using claim counts. The examples are hypothetical and use deterministic record checks; no interest rate, mortality assumption, or claim-count probability distribution is assumed.

Definition: A claim count, denoted here by \(N\), is the number of events that meet a stated claim definition for a stated population and period. Exposure, denoted by \(E\), measures the insured risk over that period using a specified basis, such as earned vehicle-years or policy-years. When \(E>0\), the observed frequency is \(N/E\). A claim record is not necessarily one claim event: one event may generate several records, and one policy may have several distinct claim events.

Before calculating frequency, specify the unit being counted. For example, a database may contain one row per payment, coverage feature, claim transaction, or claim event. Those units are not interchangeable. The count definition should also state the coverage, event or loss date basis, reporting window, and treatment of reopened claims, cancellations, and claims that are later withdrawn.

A Practical Data-Check Sequence

A useful audit proceeds from definitions to records and then to aggregate patterns. The point is not to delete every unusual value. It is to identify which values are errors, which are valid observations, and which need investigation before they can be used.

State the count unit.
Write down what qualifies as one count, the population covered, and the period assigned to the count. Decide whether a reopened claim remains one event and whether distinct coverage features are counted separately.

Check record identity.
Search for repeated claim identifiers, repeated transaction identifiers, and near-duplicates. Confirm whether a repeated row is an accidental duplicate, a legitimate additional event, or an administrative update.

Match exposure to the count.
Confirm that claim counts and exposure refer to the same policies, coverage, dates, and segment. Investigate missing, negative, or zero exposure attached to a positive count.

Review zeros and aggregate patterns.
Check whether zero-count observations represent complete, valid reporting. Compare counts with exposure across periods and segments; a larger count may simply reflect more exposure, while a sharp rate change needs explanation.

Duplicate Records and the Claim Unit

An exact duplicate can inflate the count, but similarity alone does not establish duplication. Two claims may share a policy number, loss date, or claimant while representing separate events. Conversely, two records with different transaction numbers may refer to the same claim if one is a reopened or corrected transaction. The audit must use the chosen claim unit and the system's record relationships, not just a single field.

Worked Example: Removing Confirmed Duplicate Event Rows

A hypothetical commercial auto extract contains 126 claim-event rows for a period and 1,200.5 earned vehicle-years. Review identifies three rows as exact duplicate event records, each repeating an existing claim ID and event date. Two other rows share a policy and date with other claims but have distinct event IDs; claim-file review confirms that they are separate events. Determine the adjusted claim count and frequency. Name the audit method and a mistake it avoids.

Method: deduplicate confirmed event IDs, then divide by matched exposure. The three confirmed duplicate rows are removed. The two similar-looking records are retained because they represent distinct events.

The adjusted count is:

N_{\text{adjusted}}=126-3=123.

Using the full matched exposure of 1,200.5 vehicle-years:

\frac{N_{\text{adjusted}}}{E} =\frac{123}{1{,}200.5} =0.102457\ldots \approx 0.1025

The adjusted observed frequency is 0.1025 claims per vehicle-year, rounded to four decimal places. As a check, multiplying the unrounded frequency by exposure gives \(0.102457\ldots \times 1{,}200.5=123\) claims. The common mistake is to remove every record that looks similar; that would also discard the two verified distinct events.

Consistent Definitions Across Periods

A frequency comparison requires a stable numerator definition. Suppose one year counts unique claim events while another counts coverage-feature records. A single event involving two coverages could then contribute one count in the first year and two in the second. The difference would be partly a change in data convention, not necessarily a change in claim incidence. If definitions cannot be made consistent, the comparison should be qualified or the data should be restated to a common basis where possible.

Worked Example: Reconciling a Change in Claim Records

A hypothetical insurer reports 420 unique claim events and 8,400 earned policy-years in Year 1. In Year 2, an extract has 520 rows and 8,800 earned policy-years. Audit shows that the Year 2 rows comprise 440 unique claim-event IDs, 60 additional coverage-feature rows already attached to those events, and 20 reopened-claim rows that repeat existing event IDs. The target definition is one count per unique event. Calculate comparable frequencies and identify the misleading calculation.

Method: restate both periods to unique claim events. Year 1 already follows the target definition:

f_1=\frac{420}{8{,}400}=0.0500.

For Year 2, the 60 feature rows and 20 reopened rows are not new events under the stated definition. The audited event count is therefore 440, not 520:

f_2=\frac{440}{8{,}800}=0.0500.

Both comparable frequencies are 0.0500 claims per policy-year. If the raw Year 2 row count were incorrectly used, the result would be \(520/8{,}800=0.05909\ldots\), or 0.0591 to four decimal places. That apparent increase is caused by counting rows under a different convention, not by the reconciled event counts. The check is that the Year 2 unique-event count, 440, agrees with the audited list of event IDs.

Exposure Gaps and Unusual Zeros

A missing exposure is not the same as zero exposure. If exposure is missing, the denominator is unknown; if exposure is truly zero, a positive claim count generally indicates a mismatch, such as a date-basis error or exposure omitted from the extract. Neither case should be silently converted into a usable frequency. A zero claim count with positive exposure is possible, but it should be checked against reporting completeness and the intended period.

A zero can also arise because a segment was not loaded, a claim-reporting feed failed, or a filtering rule removed records. Thus, “no claims recorded” and “complete observation with no claims” are different data states. Retain that distinction in the audit trail.

Worked Example: Separating Missing Exposure From a Valid Zero

A hypothetical book has four groups for the same coverage and period:

Group A: exposure \(E=400\), count \(N=36\).
Group B: exposure \(E=250\), count \(N=0\); the exposure and reporting feed have been verified complete.
Group C: exposure is missing, count \(N=12\).
Group D: recorded exposure \(E=0\), count \(N=3\).

Calculate the frequency for the currently usable groups, state what happens to Groups C and D, and calculate the overall frequency if later investigation confirms that Group C had exposure 150 and Group D had exposure 30.

Method: isolate unresolved records and calculate only on reconciled exposure. Groups A and B have valid exposure. Their combined count and exposure are:

N_{A+B}=36+0=36,\qquad E_{A+B}=400+250=650.

Thus, the provisional frequency for those two verified groups is \(36/650=0.0553846\ldots\), or 0.0554 claims per exposure unit to four decimal places. Group B remains in the calculation: its zero count is valid because its exposure and reporting completeness were checked.

Group C cannot yet contribute a frequency because its denominator is missing. Group D must be investigated because three claims are paired with zero recorded exposure. Do not divide by zero or add those claims to the numerator while omitting their associated exposure.

Under the stated later corrections, total exposure is \(400+250+150+30=830\), and total count is \(36+0+12+3=51\). The corrected overall frequency is:

\frac{51}{830}=0.0614457\ldots \approx 0.0614.

The answer is 0.0614 claims per exposure unit, rounded to four decimal places. As a check, the total count includes all four groups exactly once, and the total exposure includes each group's confirmed exposure. The common mistake is to treat missing exposure as zero or to interpret Group D's recorded zero as proof that the claims should be discarded.

Checking Count-to-Exposure Patterns

Counts should be interpreted relative to exposure. If exposure doubles and the observed frequency is stable, a count near twice as large is unsurprising. Conversely, an unusually high count relative to exposure may reflect a valid difference in risk, but it can also reveal duplicate counting, a mismatched period, a shifted segment definition, or an exposure omission. A pattern check is a diagnostic signal, not a basis for automatic adjustment.

For positive exposures \(E_j\) and corresponding counts \(N_j\), let \(f_j=N_j/E_j\). The pooled frequency is an exposure-weighted average of the individual frequencies:

f_{\text{pooled}} =\frac{\sum_j N_j}{\sum_j E_j} =\frac{\sum_j E_j f_j}{\sum_j E_j}.

The second equality follows by substituting \(N_j=E_j f_j\) for each group. It shows why each group's rate is weighted by its exposure when forming the overall rate. This identity assumes that the counts and exposures use the same claim definition and compatible periods; it does not correct inconsistent data.

Worked Example: Flagging a Count-to-Exposure Discontinuity

A hypothetical program has these quarterly totals, all intended to use the same event definition:

Quarter 1: \(E=500\), \(N=25\).
Quarter 2: \(E=1{,}000\), \(N=50\).
Quarter 3: \(E=1{,}500\), \(N=75\).
Quarter 4: \(E=1{,}000\), \(N=95\).

Calculate each quarter's frequency and the pooled frequency. State what the pattern suggests, and show how the pooled result would change if investigation confirmed that 45 Quarter 4 rows were duplicate events.

Method: normalize each count by its exposure, then inspect the sequence. The quarterly rates are:

f_1=\frac{25}{500}=0.0500,\quad f_2=\frac{50}{1{,}000}=0.0500,\quad f_3=\frac{75}{1{,}500}=0.0500,\quad f_4=\frac{95}{1{,}000}=0.0950.

The first three quarters have the same frequency even though their counts rise with exposure. Quarter 4 has a substantially higher count relative to its exposure and merits a record-level and definition check. This is a flag for investigation, not proof that the Quarter 4 count is wrong.

Using the reported counts, total count is \(25+50+75+95=245\), and total exposure is \(500+1{,}000+1{,}500+1{,}000=4{,}000\). Therefore:

f_{\text{pooled}}=\frac{245}{4{,}000}=0.06125.

If 45 Quarter 4 rows are confirmed duplicates, its corrected count is \(95-45=50\). The corrected total count is \(245-45=200\), so the corrected pooled frequency is \(200/4{,}000=0.0500\). The check is that each of the first three quarters already has rate 0.0500, and the corrected fourth quarter does too. The common mistake is to compare raw counts alone or to remove rows merely because the rate is high; the correction is justified only by confirmed duplicate evidence.

Common Audit Traps

Counting records instead of events. Rows, transactions, coverage features, and claim events can be different units. State the count definition and reconcile to it.
Deleting all apparent duplicates. A shared policy or loss date does not prove two rows represent the same claim. Verify event identity and retain legitimate separate events.
Mixing count and exposure bases. A count for one coverage or period cannot be divided by exposure for a different coverage or period without a justified reconciliation.
Treating every zero alike. A verified zero count with positive exposure is usable; a missing feed, missing exposure, or positive count with zero exposure needs investigation.
Calling an unusual rate an error. A pattern can identify where to investigate, but a high or low rate alone does not establish a data defect.

Key takeaway: Before interpreting claim frequency, confirm the event unit, reconcile duplicate and changing records, validate exposure, and investigate zeros and discontinuities. Use only counts and exposure that are aligned and sufficiently verified.

Check Your Understanding

For each hypothetical situation, identify the appropriate check and calculate the requested quantity.

A property extract has 214 event rows and 2,000 earned location-years. Audit confirms that 6 rows repeat existing event IDs; 3 other rows have the same address but distinct confirmed events. What is the adjusted frequency? (Answer: \(208/2{,}000=0.1040\) claims per location-year.)
In Period A, there are 300 unique events and 6,000 earned policy-years. In Period B, 360 rows comprise 320 unique events and 40 feature rows attached to those events; exposure is 6,400 policy-years. Under a one-event-per-count definition, what are the two period frequencies? (Answer: Period A \(=0.0500\); Period B \(=320/6{,}400=0.0500\) claims per policy-year.)
A verified segment has 180 exposure units and zero claims. Another segment has 40 claims but its exposure is missing. Which segment can enter a frequency calculation now, and what is the frequency for the verified segment? (Answer: The zero-claim segment can enter and has frequency \(0/180=0\); the 40-claim segment cannot enter until exposure is resolved.)
Two periods have \(E_1=800, N_1=32\) and \(E_2=1{,}200, N_2=84\). Calculate each rate and the pooled rate, then state which period deserves a data check. (Answer: \(f_1=0.0400\), \(f_2=0.0700\), pooled \(=116/2{,}000=0.0580\); Period 2 merits investigation.)
Three segments have exposures 100, 300, and 600, with counts 4, 18, and 48. Calculate the pooled frequency and explain why the arithmetic mean of the three segment rates is not the pooled rate. (Answer: pooled frequency \(=70/1{,}000=0.0700\); the segment rates are 0.0400, 0.0600, and 0.0800, and their unweighted mean is 0.0600 rather than the exposure-weighted pooled rate.)

What You'll Learn