Extracting Claim Status History From an Insurance Claims System Export

A claims export tells you what status a claim is in now, and when each change happened. Nobody wants either of those. They want to know how long claims sit in review, and that number has to be built.

What the export actually contains

Claims systems export status history as an event log: one row per transition, carrying a claim identifier, the new status, a timestamp, usually the previous status, and an actor. Some exports add a note or reason code. The shape is stable across vendors even where the column names are not.

Two variants complicate the picture. The first is a denormalised export — one row per claim with a current status and a handful of milestone dates — which contains no history at all and cannot be made to yield dwell times. Recognising that early saves a lot of work. The second is the mixed case, where structured transitions exist but some of what happened is only in a free-text adjuster note (“placed on hold pending medical records 14/3, released 21/3”). That is the one place in this pipeline where a language model earns its keep: the structured columns should be parsed deterministically, and only the note text needs a model to turn prose into candidate events, flagged as derived and kept separate from the system’s own transitions.

Capture the export’s own as-of timestamp from the header or filename. It defines the right-hand edge of every open interval and without it the last status of every claim is uncomputable.

Deriving dwell time

Sort each claim’s events by time, then the dwell in the status set by event i is the gap to event i+1:

events for claim X (as_of = 2026-05-01T00:00Z)

  2026-03-02T09:14Z  ->  received
  2026-03-02T09:20Z  ->  triage
  2026-03-05T16:02Z  ->  investigation
  2026-04-10T11:47Z  ->  pending_documents
  2026-04-28T08:05Z  ->  investigation

dwell(received)          = 09:20 - 09:14           = 6 minutes
dwell(triage)            = 03-05 16:02 - 03-02 09:20 = 3d 06h 42m
dwell(investigation, 1)  = 04-10 11:47 - 03-05 16:02 = 35d 19h 45m
dwell(pending_documents) = 04-28 08:05 - 04-10 11:47 = 17d 20h 18m
dwell(investigation, 2)  = as_of - 04-28 08:05       = 2d 15h 55m  [censored]

The last interval is the one that breaks naive analysis. It has no closing event, so its duration is a lower bound rather than a measurement — the claim is still in that status. Mark it censored and carry the flag through every aggregate, because averaging censored and completed intervals together systematically under-reports how long the slow statuses take. Any cohort of recent claims is mostly censored intervals.

Note also the six-minute first interval. Automated intake often writes two events seconds apart, and a status whose median dwell is under a minute across the corpus is a system artefact rather than a stage of work. Detecting those and excluding them from operational reporting is worth doing once, at extraction time, with a rule you can state.

Timestamps that do not cooperate

• Ties. Two events with an identical timestamp are common when a batch job writes several transitions at once. Sorting by time alone is then non-deterministic and can produce a negative dwell. Break ties on a sequence number or a row identifier, and if neither exists, on the previous-status column, which chains the events into their real order.
• Mixed time zones. Exports frequently render timestamps in a server-local zone with no offset. Two claims handled in different offices can then be an hour apart for no reason, and around a daylight-saving transition an hour can repeat or vanish entirely — producing a genuine negative interval in correctly recorded data. Normalise to UTC on ingest and store the original string; the wider set of traps is in the date field validation rule.
• Date-only values. Some systems record a date with no time. Every event on that day then collides, and dwell times within a day collapse to zero. Round to a day granularity for those claims rather than mixing precisions in one aggregate.
• Backdated corrections. Better systems carry two times: when the event occurred and when it was recorded. Use the event time for dwell and the recorded time for audit, and keep both — a large gap between them is itself a data quality signal.
• Duplicate consecutive statuses. A transition from investigation to investigation is a no-op the system wrote for its own reasons. Collapse runs of the same status into one interval, or your per-visit counts inflate.

Reopened claims and repeat visits

Claims revisit statuses. The worked example above enters investigation twice, and closed claims get reopened, sometimes years later. Two consequences follow, and both are easy to get wrong.

Total time in a status is the sum over visits, not the difference between the first and last time the claim was in it. And time to closure is not last event minus first event on a reopened claim; that figure includes the entire dormant period between the first closure and the reopening. Compute both a first-closure duration and a final duration and keep them as separate fields, because they answer different operational questions and only one of them is what somebody means by “how long did this claim take”.

Model the output as one row per interval — claim, status, visit number, start, end, duration, censored flag — rather than one row per status per claim. Aggregations of every kind fall out of the interval table, and none of them can be recovered from a pre-aggregated one. That is the same argument for storing raw extracted rows next to derived ones that applies across document storage schema design.

The actor column is not all people

Every transition names who made it, and the column mixes at least three populations: named users, service accounts belonging to integrations, and the system itself performing scheduled work. A status change written by a batch process at 02:00 is not an adjuster working overnight, and a report on adjuster productivity that counts it says something false.

Classify actors on ingest into user, integration and system, from the identifier pattern plus the timing distribution, and store the raw identifier alongside the classification. Then two derived quantities become meaningful: handoff count, being the number of times the acting user changes across a claim’s history, which is a better proxy for friction than dwell time alone; and after-hours activity, which is only interpretable once system actors are excluded.

Actor identifiers are personal data in most jurisdictions even when they look like usernames, and claim exports also carry claimant names and often injury details. Decide what needs to leave your infrastructure before any of it reaches a hosted model — for the free-text note parsing described above, the note usually needs the dates and not the names.