Claims and premium extract requirements
Medical portfolio analysis is most often delayed by a small and predictable set of missing columns. This guide sets out the fields required for analysis, the fields that improve it where they are available, and the fields that should be excluded from the extract entirely.
Last updated 12 September 2026
Two files are required
Analysis requires a premium register and a claims extract as separate files. The premium register establishes the population covered, the duration of cover and the premium charged. The claims extract establishes the corresponding cost.
A single combined file is not sufficient. Such a file generally contains one of the two datasets with the other aggregated into it, and the aggregation cannot be reversed once it has been applied.
Both files should be standard exports from the existing policy administration or claims system. Where a new report would have to be written, the standard export should be requested first: it is generally sufficient for analysis and is available sooner.
The premium register
Exposure and earned premium cannot be computed without the following fields:
- Policy start and end dates, and member start and end dates where members join or leave during the term. These determine premium earned pro rata by day and exposure measured in member-years.
- Premium, gross and net. The net figure is the more important of the two, as a gross loss ratio understates the position of a portfolio that cedes its poorest risk.
- A member identifier, and a principal identifier where dependants are recorded against a policyholder.
- Date of birth or age, and gender. Where a file contains both a date of birth and an age, the date of birth is used, as it is exact at the report date.
The following fields improve the analysis where they are available:
- Group, client entity, policy number, policy type and underwriting year, which determine the segments to which movement can be attributed.
- Network, segment or line of business, and broker or sales channel.
- Nationality, relation and marital status, which support demographic exposure analysis.
The claims extract
- The member identifier used in the premium register, recorded identically. This field forms the join between the two files and is the most frequent reason an extract cannot be analysed.
- Date of treatment, and date of reception where the system records it. The interval between them constitutes the reporting lag, from which a reserve for late-reported claims is estimated.
- Claimed and final amounts, and the currency where the portfolio is written in more than one.
- Claim status, distinguishing settled claims from those reserved. Incurred claims comprise paid and outstanding amounts; a file containing only paid claims understates the portfolio.
- Benefit or medical category, and the inpatient and outpatient marker where it is held as a separate field.
Provider, diagnosis and diagnosis code, medical act, billing type and city are also used where they are present.
Columns to exclude
Names, passport and national identification numbers, addresses, telephone numbers and email addresses are not required for any measure and should be excluded from the extract.
Where an export cannot exclude them, this does not prevent analysis. Identifying columns are deleted from each record before mapping and before any other code reads the row. An extract that never contained them is nonetheless simpler to approve. Data handling.
Format requirements
Excel or CSV, with one row per member or per claim line. Column names need not follow any convention: they are matched against a defined vocabulary, and any that cannot be resolved with confidence are presented for confirmation before the file is loaded. Dates may be supplied in most formats, including inconsistent formats within a single file, and amounts may include thousands separators and currency symbols.
Four conditions do apply: a single header row, no merged cells, no totals row at the foot of the file, and a consistent member identifier across both files. These account for the majority of extracts that require a second attempt.