Why the Link Between Records Matters
Insurance data commonly describes the same business activity in several tables. A customer record describes a customer; a policy record describes a contract or policy term; a claim record describes a reported claim. A payment record may describe one payment transaction for a claim. These are different entities, even when their records share identifiers or appear together in a report.
The analytical question determines which entity is the unit of analysis. A customer-level prediction might use one row per customer, while a policy-level analysis might use one row per policy term. Neither choice makes a claim record a customer or a policy. To connect records responsibly, trace their identifiers and state the time period each record describes. This is aligned with the SOA Exam PA (Predictive Analytics) syllabus.
An identifier is not itself the entity, and two records with different identifiers do not necessarily describe unrelated activity. One customer may hold several policies; one policy may have several claims; and one claim may have several payment transactions. The relationship between entities must be understood in the context of the record definitions and dates.
Trace the Entity and the Time Period
For each table or record type, ask two questions: What does one row represent? and For what time or event is it valid? For example, a customer row might describe a person at a specified snapshot date. A policy row might describe a policy term from its effective date through its expiration date. A claim row might describe a claim arising from an incident on a loss date, even if the claim is reported later.
Identifiers should be traced across those record types rather than assumed to mean the same thing. A claim ID identifies a claim; its policy ID links it to a policy; and that policy record identifies the associated customer for the relevant period. If a customer’s relationship to a policy changes, the correct customer may depend on the date and the business rule for attribution.
| Record type | Typical entity represented | Time question to resolve |
|---|---|---|
| Customer | A person or organization | At what date or period is the customer information applicable? |
| Policy | A contract, policy, or policy term | Which effective and expiration dates define the term? |
| Claim | A claim arising from an insured event | Is the relevant date the loss date, report date, or another defined event date? |
| Payment | A transaction associated with a claim | Is the row one payment, one adjustment, or a cumulative amount? |
A relationship such as “one customer can have multiple policies” describes how entities may relate; it does not mean that each policy row is a separate customer. Similarly, several payment rows for one claim do not represent several claims. Keeping the entity and period explicit prevents accidental changes in the question being answered.
Worked Example: Tracing Claims to Customers
A hypothetical insurer is studying claims arising during the calendar year. Its policy records show P11 and P12 assigned to customer C01, and P13 assigned to customer C02. All three policies were active for the stated claim dates. The claim records are K21 on P11 dated February 4, K22 on P11 dated November 9, K23 on P12 dated August 12, and K24 on P13 dated December 3. Find the number of distinct claims by policy and by customer.
Method. Treat each distinct claim ID as one claim, use its policy ID to identify the policy, and then use the policy record to identify the customer. The stated claim dates fall within the relevant policy periods.
By policy. P11 has K21 and K22, so its count is \(2\). P12 has K23, so its count is \(1\). P13 has K24, so its count is \(1\). The policy total is \(2+1+1=4\) claims.
By customer. C01 has policies P11 and P12, so the customer’s count is \(2+1=3\). C02 has P13, so the customer’s count is \(1\). The customer total is \(3+1=4\) claims.
Check. There are four distinct claim IDs in the stated period, and the totals at both levels are four. The common mistake to avoid is counting the three policy records as three claims, or treating the two policies held by C01 as two customers. Policies, claims, and customers are different entities.
Counts Require a Clear Attribution Rule
Suppose each claim in the study is assigned to exactly one policy. Let \(C\) be the set of distinct claims in the period, let \(P\) be the set of policies, and let \(g(c)\) identify the policy assigned to claim \(c\). The count for policy \(p\) is the number of claims assigned to it:
where \(I\{\cdot\}\) equals 1 when the condition inside the braces is true and 0 otherwise. If every claim is assigned to exactly one policy, the policy-level counts add to the total number of distinct claims:
The justification is that each claim contributes 1 to the count for its assigned policy and 0 to every other policy. Thus, when the policy counts are added, every claim contributes exactly once. This equality does not apply automatically if claims are duplicated, omitted, or intentionally assigned to multiple policies. In those situations, the attribution rule must be specified before interpreting the total.
The same reasoning applies when aggregating to customers: a claim is counted for the customer associated with its assigned policy under the chosen attribution rule. If a customer holds multiple policies, their claim count is the sum of the claims attributed to those policies, not the number of policies they hold.
Worked Example: Policy Ownership Changes During a Term
A hypothetical policy P88 is active from January 1 through December 31. The named customer is C10 for dates before July 1 and C20 starting July 1. Use the loss date—not the claim report date—to assign claims to customers. Claim Q31 has a loss date of June 30 and a report date of July 5. Q32 has a loss date and report date of July 1. Q33 has a loss date of August 10 and a report date of August 12. Find the customer claim counts.
Method. Apply the stated loss-date rule and the stated ownership boundary. Dates before July 1 map to C10; July 1 and later map to C20.
Q31. Its loss date is June 30, which is before July 1, so it is attributed to C10. The later report date does not change the attribution under the given rule.
Q32. Its loss date is July 1, the first date of C20’s period, so it is attributed to C20.
Q33. Its loss date is August 10, which is also in C20’s period, so it is attributed to C20.
Totals and check. C10 has 1 claim; C20 has 2 claims. Their total is \(1+2=3\), matching the three distinct claim IDs. If report dates were used instead, Q31 would be incorrectly assigned to C20, giving C10 zero and C20 three. That is a different attribution rule, not a harmless date substitution.
Claims and Transactions Are Not Interchangeable
A claim may have multiple payment records, such as separate payments at different dates. When the target is claim count, count distinct claim IDs. When the target is payment activity, count or sum payment transactions according to the stated definition. Substituting one for the other changes the outcome being measured.
The following example separates claim-level counting from transaction-level counting. The transaction amount is used only to make the distinction concrete; it does not redefine the claim record.
Worked Example: Counting Claims Rather Than Payments
In a hypothetical six-month study period, customer C40 holds policies P40 and P41, while customer C50 holds P50. Claims A1 and A2 are assigned to P40, and claim B1 is assigned to P41. Claim C1 is assigned to P50. The payment records are: A1 has two payments of $300 and $200; A2 has one payment of $450; B1 has no payment recorded by the study cutoff; and C1 has two payments of $100 and $250. Find the distinct claim count by policy and customer, the number of payment records, and the sum of recorded payments.
Method. Count distinct claim IDs for claim counts. Count each stated payment entry once for payment-record count, and add payment amounts for the recorded-payment total.
Claims by policy. P40 has A1 and A2, giving \(2\) claims. P41 has B1, giving \(1\) claim. P50 has C1, giving \(1\) claim. The total is \(2+1+1=4\) distinct claims.
Claims by customer. C40’s policies P40 and P41 have \(2+1=3\) claims. C50’s policy P50 has \(1\) claim. The customer total is \(3+1=4\), consistent with the policy total.
Payment records and amount. A1 contributes 2 payment records; A2 contributes 1; B1 contributes 0; and C1 contributes 2. Thus there are \(2+1+0+2=5\) payment records. The recorded amount is \($300+$200+$450+$100+$250=$1{,}300\).
Check. The claim count is 4, while the payment-record count is 5 and the recorded payment total is $1,300. These values answer different questions. Counting payment rows as claims would report 5 claims, incorrectly including two rows for A1 and two for C1 as separate claims. B1 remains a claim even though no payment is recorded by the cutoff.
Common Mistakes and a Practical Check
The most reliable check is to write down the record’s entity and time period before calculating anything. Then trace the identifier needed for the question: claim ID for distinct claims, policy ID for policy attribution, or customer ID for customer aggregation. Do not infer a link from names alone when a defined identifier is available.
- Counting rows instead of entities. A table with payment-level rows can contain several rows for one claim. Count distinct claim IDs when the outcome is number of claims.
- Using the wrong date. A claim’s report date and loss date can differ. Use the event date specified by the analytical question and apply the ownership or coverage rule for that date.
- Assuming a customer and policy are one-to-one. One customer may have multiple policies, so policy counts cannot be interpreted as customer counts without tracing the customer identifier.
- Adding totals without checking attribution. Policy-level counts sum to the distinct claim total only when each in-scope claim is assigned exactly once and duplicates or omissions have been addressed.
Check Your Understanding
For each problem, identify the relevant entity and apply the stated date and attribution rules.
- A customer C70 has policies P70 and P71. Claims D1 and D2 are assigned to P70; D3 is assigned to P71. How many distinct claims are attributed to C70? (Answer: 3 claims.)
- A policy is assigned to customer C80 through March 31 and customer C81 starting April 1. Claim E1 has a March 31 loss date and April 4 report date; E2 has an April 1 loss date. If loss date governs attribution, give the count for each customer. (Answer: C80 has 1 claim; C81 has 1 claim.)
- Policy P90 has two claims. Claim F1 has three payment records and F2 has no payment record by the cutoff. What are the claim count and payment-record count? (Answer: 2 claims and 3 payment records.)
- Customer C91 has policies P91 and P92. P91 has 1 distinct claim and P92 has 2 distinct claims; each claim is assigned to exactly one policy. What is C91’s claim count, and what check can you use? (Answer: 3 claims; the policy counts sum to the same total.)
- Three distinct claims are assigned as follows: G1 and G2 to P93, and G3 to P94. A data extract has four rows because G1 appears twice. What is the distinct claim total, and why does the row total differ? (Answer: 3 claims; one claim is duplicated in the extract, so the four rows are not four distinct claims.)