Why the Meaning of One Row Matters
A model is fitted to rows, but the business question is usually about something more specific: a customer, a policy, a claim, or a transaction. If those are not distinguished, a dataset can appear to contain thousands of prediction cases when it actually contains repeated records for a much smaller number of entities. The resulting model may answer a different question from the one decision-makers intended.
For example, a hypothetical insurer may have one record per payment transaction, several transactions for a claim, and several claims associated with a customer. A transaction-level dataset is not automatically a claim-level dataset merely because every transaction has a claim identifier. The first design task is to name the entity to be predicted; the next is to state exactly what one row represents.
A practical description should be specific enough that two analysts would agree about whether a proposed row belongs in the dataset. “One row per claim” is a grain statement. “One prediction for each claim being considered for triage” is a prediction-unit statement. “Claim data” is not specific enough: it could mean one row per claim, one row per transaction associated with a claim, or one row per policy containing a claim.
Distinguishing Entities and Records
The same operational system can contain several linked types of entities. A customer can hold multiple policies; a policy can have multiple claims; a claim can have multiple transactions. The row count therefore depends on the table’s grain, not just on the number of customers or claims mentioned in the data.
| Possible row grain | What one row represents | Typical identifier |
|---|---|---|
| Customer | One customer | Customer ID |
| Policy | One policy | Policy ID |
| Claim | One claim | Claim ID |
| Transaction | One transaction, such as a payment or adjustment | Transaction ID |
The identifier should be unique at the stated grain. If a table is described as one row per transaction, a transaction ID should distinguish its rows. A claim ID may repeat in that table because a claim can have several transactions. Conversely, if the task is to predict something for each claim, repeated claim IDs indicate that the table is not yet at one-row-per-claim grain.
The distinction does not mean that one grain is always preferable. If the intended output is one prediction per transaction, transaction rows may be appropriate. If the intended output is one prediction per customer, a table with one row per transaction does not directly provide one row per customer. The question is whether the data structure represents the intended prediction unit, not whether one particular grain is universally best.
Worked Example: Customer Predictions From Policy Records
A hypothetical insurer wants to prepare one renewal outreach decision for each customer in a small selected group. The group contains customers C01, C02, C03, and C04. The policy extract has two policies for C01, one for C02, three for C03, and none for C04. Determine the number of customer prediction units, the number of policy rows, and whether the extract itself has one row per prediction unit.
Step 1: Count the intended prediction units. The stated task calls for one decision for each of the four customers. The prediction-unit count is therefore 4.
Step 2: Count the policy rows. The policy counts are 2 for C01, 1 for C02, 3 for C03, and 0 for C04. Thus the extract contains \(2+1+3+0=6\) policy rows.
Step 3: Count represented customers. C01, C02, and C03 appear in at least one policy row. C04 does not appear. Therefore, the six policy rows represent 3 distinct customers, not 6.
Step 4: Compare the grain with the task. The prediction unit is the customer, while the extract grain is the policy. It also omits C04, even though C04 is in the stated group. As supplied, it is neither one row per customer nor a complete record of the four intended units.
Check and common mistake. The arithmetic \(2+1+3=6\) correctly counts policies, but calling those six rows “six customers” would confuse policy records with customer units. The correct counts are 4 intended customer units, 6 policy rows, and 3 represented customers. This example is aligned with the SOA Exam PA (Predictive Analytics) syllabus.
Counting Rows and Distinct Units
When a dataset has repeated unit identifiers, report both the number of rows and the number of distinct identifiers for the intended unit. They answer different questions. The row count tells how many records are present. The distinct-ID count tells how many different identified entities are represented, assuming the identifier is valid for that entity.
The relationship can be shown by grouping rows by their unit identifier. Let there be \(K\) distinct claim identifiers and let \(m_j\) be the number of rows for claim \(j\). Then the total number of rows \(N\) is the sum of the rows in each claim group:
This follows because every row belongs to exactly one of the \(K\) groups, and each group contributes its own \(m_j\) rows once to the total. If every claim has exactly one row, then every \(m_j=1\), so \(N=K\). If any claim has multiple rows, its \(m_j\) exceeds 1 and the row count can exceed the number of represented claims. A claim with no row in the extract contributes neither a row nor a distinct identifier to that extract.
Worked Example: Claim IDs Repeated in a Transaction Extract
A hypothetical case system lists claims R1 through R6. A transaction extract contains five rows with claim IDs R1, R2, R2, R4, and R6. State the number of claims in the case system, the number of distinct claims represented in the extract, the transaction-row count, and the claims absent from the extract.
Step 1: Count the case-system claims. The listed IDs are R1, R2, R3, R4, R5, and R6, so the case system contains 6 claims.
Step 2: Count the transaction rows. The extract lists R1 once, R2 twice, R4 once, and R6 once. Counting every listed row gives \(1+2+1+1=5\) transaction rows.
Step 3: Count distinct claims represented. The distinct claim IDs are R1, R2, R4, and R6. There are 4 distinct claims represented, even though R2 appears twice.
Step 4: Identify absent claims. Comparing the case-system list with the distinct extract IDs, R3 and R5 are absent. There are \(6-4=2\) case-system claims not represented.
Check and common mistake. The counts reconcile: the four represented claims contribute row multiplicities \(1,2,1,1\), which sum to 5 rows. Counting five rows as five represented claims is wrong because the repeated R2 row is still the same claim. The correct answer is 6 claims in the case system, 4 distinct claims represented, and 5 transaction rows.
Match the Row Grain to the Prediction Task
A sound design begins by stating the prediction unit in plain language, then checking that the available records can represent those units. This is not just a database description. It determines what one fitted observation means. If the task is to score claims for claim triage, a table with one row per claim is the direct match. If the task is to score payment transactions, one row per transaction may be the direct match. A model built on transaction rows should not be described as producing one independent prediction for each claim merely because claims appear in an ID column.
Likewise, a customer-level task and a policy-level table are not interchangeable. One customer may appear on several policy rows, while a customer with no policy in the extract may not appear at all. It is important to distinguish the intended population from the entities that happen to have records in a particular extract. The row count alone cannot establish whether all intended units are present.
Write “one prediction for each customer,” “one for each claim,” or another equally precise statement. Avoid labels such as “account data” unless the entity is defined.
Describe one row as one customer, policy, claim, transaction, or other specific record. State which identifier should distinguish rows at that grain.
Count rows and distinct identifiers separately. Compare the distinct units represented with the units expected for the stated task.
If a row can correspond to a different entity from the prediction unit, state that clearly. Do not interpret a row count as a prediction-unit count without verifying the identifiers.
Worked Example: Claim Rows Are Not Policy Rows
A hypothetical portfolio contains five policies: P1, P2, P3, P4, and P5. A claim extract lists four claims: C101 and C102 are associated with P2, C103 with P3, and C104 with P5. The proposed task is to prepare one claim-triage prediction for each claim in the extract. Determine the prediction-unit count, claim-row count, number of distinct policies represented by those claim rows, and whether the extract represents all five policies.
Step 1: Identify the prediction unit. The task asks for one prediction per claim, so the prediction unit is a claim.
Step 2: Count claim rows. The extract lists C101, C102, C103, and C104. There are 4 claim rows and 4 distinct claim IDs, so the extract is at claim grain for these records.
Step 3: Count represented policies. The policy IDs attached to the claims are P2, P2, P3, and P5. Removing the repeated P2 leaves P2, P3, and P5: 3 distinct policies are represented.
Step 4: Compare with the portfolio. The portfolio has 5 policies, but the claim extract represents only 3 of them. P1 and P4 have no claim row in this extract. That does not contradict the claim count: this extract is organized around claims, not all policies.
Check and common mistake. The result is 4 claim prediction units in the extract, 4 claim rows, and 3 represented policies. It would be incorrect to conclude that there are only 3 claims because three policies appear, or that the four claim rows represent four different policies. C101 and C102 are separate claims on the same policy.
Common Mistakes and Practical Checks
- Equating rows with people or accounts. A row is whatever the stated grain says it is. If the grain is transaction, multiple rows may refer to one claim or customer.
- Counting repeated IDs as distinct units. Count distinct identifiers for the unit of interest. A repeated claim ID adds a row, not another claim.
- Assuming that a missing ID means the entity does not exist. An entity absent from one extract may still be in the intended population or another system. State what population the available records cover.
- Using an ambiguous grain description. “One row per account” is unclear if account might mean a customer, policy, or billing record. Name the entity and identifier.
- Confusing the prediction unit with the decision destination. A person may make a decision at customer level while the source table is at policy level. State both clearly rather than assuming the table determines the task.
These checks matter because a model’s reported number of observations and its intended number of predictions may differ. A transaction extract can contain many rows for a smaller number of claims; a policy extract can contain several rows for one customer; and an extract at claim grain can omit policies with no claims. Clear unit and grain statements make those distinctions visible before modeling begins.
Check Your Understanding
For each situation, distinguish the prediction unit, row grain, and relevant counts. Check each answer against the identifiers rather than the row total alone.
- A hypothetical customer table has rows with customer IDs A, B, B, C, and D. The task is one outreach prediction per represented customer. How many rows and distinct customer prediction units are present? (Answer: 5 rows and 4 distinct customer units.)
- A claim inventory contains claims Q1 through Q5. A payment extract has claim IDs Q1, Q1, Q3, and Q5. How many claims are in the inventory, how many distinct claims are represented, how many payment rows are present, and which claims are absent? (Answer: 5 inventory claims, 3 represented claims, 4 payment rows, and Q2 and Q4 are absent.)
- A policy table contains P10 and P11 for customer M, P12 for N, and P13 and P14 for O. If the table has one row per policy, how many policy rows and distinct customers does it represent? If the intended task is one prediction per customer, does the row grain match? (Answer: 5 policy rows, 3 distinct customers, and no—the table is at policy grain, not customer grain.)
- A hypothetical transaction extract has transaction IDs T1, T2, T3, T4, and T5, linked respectively to claims C7, C7, C8, C9, and C9. The task is one triage prediction per claim represented in the extract. How many transaction rows and distinct claim units are there? (Answer: 5 transaction rows and 3 distinct claim units.)
- A system lists six policy IDs, S1 through S6. A policy-level extract contains S1, S2, S2, S4, and S6. State the row count, the number of distinct policies represented, and the number of listed policies absent. (Answer: 5 rows, 4 distinct policies represented, and 2 policies absent: S3 and S5.)