Why Dataset Design Comes Before Territory Comparisons
A territory experience dataset is only useful when its records describe comparable risks over a clearly defined period. A policy might change territory during its term, cover several vehicles, or include more than one coverage. A claim may also have payments and reserve estimates recorded at different dates. If these details are flattened into a single row without a consistent rule, premiums, exposures, and losses can be assigned to the wrong place or counted more than once.
The task here is to specify and assemble the records needed for analysis, not to select territory factors. The essential design decision is to preserve enough detail to group records consistently by experience period, coverage, and territory. A hypothetical insurer could use policy-level, exposure-segment, premium-transaction, and claim-level tables linked by stable identifiers.
Choose the Record Grain and Required Fields
Do not treat “one row per policy” as a universal rule. It is workable only when a policy has one relevant coverage and one territory for the entire experience period. Where coverage or territory changes, the dataset needs additional segment records or linked transaction records. Keeping the source records at their natural grains makes those changes auditable.
| Record type | Useful fields | Purpose |
|---|---|---|
| Policy and coverage | Policy identifier, insured or risk-unit identifier, coverage code, effective date, expiration date, cancellation date, and relevant limits or deductibles | Defines the insured contract and the coverage being analyzed |
| Exposure segment | Policy identifier, risk-unit identifier, coverage code, segment start and end dates, territory code, and earned exposure | Records time at risk under a particular coverage and territory |
| Premium | Policy identifier, coverage code, transaction type, transaction effective date, written premium, and earned premium or earning inputs | Connects premium to the same coverage and period as exposure |
| Claim and financial transaction | Claim identifier, policy identifier, coverage code, date of loss, paid indemnity, paid allocated loss adjustment expense, case reserves, recoveries, and valuation date | Connects loss experience to the insured risk and preserves the basis and timing of recorded amounts |
Use a stable territory code and maintain its geographic definition and effective dates. If a risk changes territory, the dataset must identify when the new code applies. A territory label alone is not enough if its definition changes over time. Likewise, a claim should generally be associated with the territory applicable to the insured risk on the date of loss, not automatically with the policyholder’s current address or the territory on a later renewal.
The coverage definition must also be explicit. For example, a hypothetical personal vehicle program might analyze bodily injury liability separately from collision coverage. A policy-wide premium total cannot be matched meaningfully to one coverage’s exposure and claims unless that premium has been allocated by a documented rule. The same concern applies to claims: a claim identifier does not replace a coverage code when a claim contains payments under multiple coverages.
Period, Exposure, and Premium Consistency
State the experience period, such as a calendar year, and the date convention. A convenient convention is to treat a policy term as the half-open interval from its effective date up to, but not including, its expiration date. The number of days in the overlap with the experience period then determines the time-on-risk portion. A policy-year is one common exposure unit, but the appropriate exposure unit depends on the product and rating basis; a vehicle-year or another defined unit may be more suitable.
For a single unit of exposure and a flat premium earned uniformly over the policy term, let \(D\) be the total term days and \(d\) the days overlapping the experience period. Then earned exposure is \(d/D\) policy-years and earned premium is the term premium multiplied by \(d/D\). This is a time-based allocation, not a claim-cost formula.
The justification is direct: under the uniform earning assumption, each of the \(D\) covered days earns the same fraction of the term exposure and premium. The experience period contains \(d\) of those days, so its share is \(d/D\). The method does not apply unchanged if the exposure units, premium, or earning pattern vary during the term. In that case, split the record at each relevant change and apply the applicable rule to each segment. Do not silently use 365 days for every term: leap days and actual term dates can affect the denominator.
Worked Example: Earning a Partial Policy Term
A hypothetical policy covers one unit of a specified liability coverage from October 1, 2025, through October 1, 2026. Treat the term as including October 1, 2025, and excluding October 1, 2026. Its flat term premium is $1,200. Calculate its 2026 earned exposure and earned premium under pro rata daily earning.
Method. Find the overlap with calendar year 2026. The overlap is January 1 through September 30, 2026, or 273 days: \(31+28+31+30+31+30+31+31+30=273\). The full term has 365 days.
Exposure calculation. The covered fraction of the term is \(273/365=0.747945205\ldots\). One unit is therefore credited with \(1\times273/365=0.747945205\ldots\) policy-years.
Premium calculation. Earned premium is \(1{,}200\times273/365=\$897.534246\ldots\), which is $897.53 to the nearest cent. The remaining term premium is not earned in calendar year 2026 under this convention.
Check and trap avoided. The term spans 365 days, and the calendar-year overlap is 273 days, so the exposure fraction is less than one. Crediting the entire $1,200 as 2026 earned premium would confuse term or written premium with premium earned during the experience period.
Premium should follow the same period and coverage logic as exposure. If endorsements change coverage or premium partway through a term, retain the transaction effective date and apply the documented earning method. Do not add a policy’s full-term written premium to every calendar year it touches. Also retain the distinction between written premium and earned premium: the first records premium written under the applicable accounting convention; the second assigns premium to the period in which coverage is provided.
Worked Example: Splitting Exposure and Premium at a Territory Change
A hypothetical one-unit policy runs from July 1, 2025, to July 1, 2026, and has a flat term premium of $730 for one coverage. The territory changes from Cedar to Harbor effective October 1, 2025. Build the 2025 calendar-year exposure and earned-premium segments, using the date convention above and pro rata daily earning.
Method. Split the policy at both the territory-change date and the start of the experience year if needed. The 2025 covered days run from July 1 through December 31, a total of \(92+92=184\) days. July 1 to October 1 is 92 days in Cedar; October 1 to January 1 is 92 days in Harbor. The full policy term is 365 days.
Cedar segment. Earned exposure is \(92/365=0.252054795\ldots\) policy-years. Earned premium is \(730\times92/365=\$184.00\).
Harbor segment. Earned exposure is also \(92/365=0.252054795\ldots\) policy-years. Earned premium is also \(730\times92/365=\$184.00\).
Reconciliation. Total 2025 exposure is \(184/365=0.504109589\ldots\) policy-years. Total earned premium is \(\$184+\$184=\$368.00\), which also equals \(730\times184/365=\$368.00\).
Check and trap avoided. Cedar and Harbor cover 92 days each, so their exposures and earned premiums are equal, and the segments sum to the 2025 total. Assigning the entire year’s $368 of earned premium to the renewal or current territory would break the time and geography alignment. Keep calculations at full precision before rounding displayed amounts.
Connect Claims Without Duplicating Policy Records
A claim table often has several financial transactions for a single claim, while a policy table may have one record for an entire term. Joining these tables carelessly can duplicate policy premium or exposure once for every claim transaction. Preserve the distinct grains: use policy-coverage-period or segment records for exposure and premium, and claim-coverage records or claim transactions for losses. Aggregate each to a compatible level only after the keys and definitions are checked.
Loss fields need a clear valuation date and basis. A basic incurred amount at a valuation date may include paid amounts and case reserves, with specified recoveries treated consistently. The dataset should state whether the measure includes allocated loss adjustment expense, whether it is gross or net of reinsurance, and which recovery types are deducted. These are data definitions, not interchangeable conventions. If a claim has multiple coverages, allocate or record its amounts by coverage using the source-system detail and a documented rule; do not assign the whole claim to each coverage.
Worked Example: Building a Claim Record on a Stated Basis
A hypothetical claim arose on March 15, 2025, under the collision coverage of a policy in Maple territory on that date. At the stated December 31, 2025 valuation, the claim has $8,000 paid indemnity, a $2,000 case reserve for indemnity, $600 paid allocated loss adjustment expense, and a $500 salvage recovery. For this example, define incurred loss to include the stated indemnity and allocated expense amounts and subtract the salvage recovery; assume no other payments, reserves, or recoveries. Identify the claim’s territory and calculate the incurred amount on this basis.
Method. Use the date of loss to assign the territory and keep the claim linked to its policy and collision coverage. Combine paid indemnity and its case reserve: \(\$8{,}000+\$2{,}000=\$10{,}000\). Add paid allocated loss adjustment expense: \(\$10{,}000+\$600=\$10{,}600\). Subtract the stated salvage recovery: \(\$10{,}600-\$500=\$10{,}100\).
The claim record therefore has Maple as its loss-date territory, collision as its coverage, December 31, 2025 as its valuation date, and $10,100 of incurred loss under the defined basis. Its policy identifier and claim identifier should link it to the corresponding policy and claim records.
Check and trap avoided. The arithmetic includes the case reserve and the expense amount because the stated definition includes them, and subtracts salvage once. Using the territory at a later renewal, or adding this claim amount to every row created by joining claim transactions to policy segments, would assign or duplicate the loss incorrectly.
Validation Before Aggregation
Before producing territory summaries, check both field definitions and record relationships. A useful sequence is:
Record the experience-period dates, product, exposure unit, coverage definitions, loss basis, valuation date, and territory version.
Split exposure when the territory, coverage, risk unit, or another required field changes. Retain the effective dates that explain each segment.
Confirm that policy, coverage, and claim identifiers resolve; review missing or invalid dates and codes; and ensure each claim is attached to the intended policy and coverage.
Compare segmented exposure and earned premium with independently calculated policy-period totals. Check that claim transactions reconcile to claim-level amounts on the stated valuation basis.
A practical reconciliation is to verify that a policy’s territory segments cover the relevant days once, with no gaps or overlaps, and that their earned exposure sums to the policy’s period exposure. Similarly, segment premium should reconcile to the amount earned for the same policy, coverage, and period, subject to documented allocation rules and rounding. Keep unrounded values for aggregation; round displayed dollar amounts at the final reporting step.
These checks matter because later calculations cannot repair inconsistent source records. A territory comparison requires losses, premium, and exposure to refer to aligned risks, dates, coverages, and locations. A dataset that preserves those links makes subsequent analysis more interpretable and is aligned with the CAS Exam 5 (Basic Ratemaking and Estimated Claim Liabilities) syllabus.
Check Your Understanding
For each problem, state the key data rule or calculation before giving the result.
- A one-unit policy runs from April 1, 2026, to April 1, 2027, under a 365-day term. Using half-open dates and pro rata daily earning, find its calendar-year 2026 earned exposure. (Answer: \(275/365=0.7534\) policy-years, rounded to four decimals.)
- A 365-day policy has a $1,095 term premium. During the experience year, 120 covered days fall in Territory North and 60 fall in Territory South. Under pro rata earning, find each territory segment’s earned premium and the total period earned premium. (Answer: North $360.00; South $180.00; total $540.00.)
- A claim has $4,500 paid indemnity, a $1,500 case reserve, $300 paid allocated loss adjustment expense, and a $200 recovery. Under a defined incurred basis that includes all those items and subtracts the recovery, what amount belongs in the claim record? (Answer: $6,100.)
- A policy has two coverages and three claim transactions. A join creates six rows, each carrying the full policy premium. What is the data problem, and what should be preserved before aggregation? (Answer: The join duplicates premium across transaction rows; preserve the policy-coverage and claim-transaction grains, link by identifiers, and aggregate each measure without replicating it.)
- A policy’s territory changes on June 1, and a claim occurs on May 20. The policyholder’s address is updated in July. Which territory should be recorded for the claim, and what date rule should support that choice? (Answer: The territory effective for the insured risk on May 20, the date of loss; do not substitute the later address or renewal territory.)