Work · 9 of 17 · Deep dive · 8 minutes
Healthcare administration / Case study
While testing claim files I noticed some never reached the audit table. I extended the analysis to three years, 20 million claims and 50 GB of data, found 300,000 missing claims worth about $10M, and put a daily reconciliation audit in place.
Evernorth (Cigna) · 2018–2022 · from my work, written for public reading
01
1 / 8 · Executive brief & flow
Find the gap between received and processed.
missing claims identified
in unprocessed claim value
Historical reconciliation finding · expanded 20-million-claim analysis
Select a process step for a short overview, then open its details. The comparison is illustrative; the historical totals above describe the investigation.
The incoming streams include four COBA files and eight files from Emdeon. COBA identifies the CMS claim-file stream; Emdeon processes VA claims. Received-file metadata provides the starting point for the reconciliation control.
Process explanation (illustrative guidance): Keep received files visible even when a later processing step pauses or fails. The four comparison rows in this figure are illustrative records, not a historical batch.
The delivered audit table captures EDI 837 metadata: file name, unique ID, total claims and claim ICN numbers. Adjudicated claims retain the source file’s unique ID, allowing the received inventory to be compared with the processed inventory.
Process explanation (illustrative guidance): Use the same source-file identifier on both sides of the comparison. This file ID is distinct from the claim ID used for image retrieval in the separate Paper-to-EDI project.
The daily audit compares claims received with claims processed, using the source-file identifier retained on adjudicated claims. A paused upstream file-processing step can leave an entire file unprocessed. The illustrated missing row demonstrates that distinction; it does not depict a measured failure rate.
Process explanation (illustrative guidance): Investigate an unmatched record before treating it as an error or a completed claim. After recovery, confirm the processing record rather than closing the item merely because a file was received.
A Tableau dashboard provides visibility into claims fallout from the received-versus-processed comparison. The visual here is a simplified explanation of the control, not a screenshot of a production dashboard.
Process explanation (illustrative guidance): Keep unresolved items visible for investigation. Detection and recovery are separate events; a report finding does not itself process a claim.
The expanded 20-million-claim investigation identified 300,000 missing claims worth about $10M in unprocessed claim value. The business processed the delta. These are historical investigation totals, not monthly volume, ongoing fallout rates or savings.
Process explanation (illustrative guidance): Confirm recovery against processing records. The before/after switch shows an illustrative unmatched file gaining a processed match, not a new quantified result.
The investigation led to a daily audit comparing received and processed claims. The audit table, source-file identifier and Tableau reporting make future fallout visible for investigation.
Process explanation (illustrative guidance): Recheck unresolved work after a repair and keep the received and processed inventories aligned. This control is separate from the Microsoft BizTalk replacement and the claims-engine replacement.
A daily reconciliation audit I put in place made gaps visible for investigation.
Files arrived, but nothing kept score.
No recordin the audit tables
I noticed it while testing claim files.
Missing claimsSome never reached the audit table
I started with a bounded sample.
Part of the expected claims unaccounted for
Two filestoo specific to be noise
I proposed extending the analysis.
20 million claims50 GB of files
300,000missing claimsabout $10M in unprocessed claims
I compared claims received with claims processed.
Completed-work reportingIt could not show what was missing
Recovery was not the whole product.
Every dayaudit received against processed
I set completeness to claims received.
Each claim names its source file.
Every weekfallout surfaced for reprocessing
I built the audit table and dashboard.
Caught by the daily auditFuture gaps visible through the daily audit
A permanent daily reconciliation audit.
Dedicated ownerassigned by operations
I helped make it visible for leadership.
What I learned
Completed-work reporting could not show what was missing.
Claims received checked against claims processed, every day.
The $10M is the value of claims found unprocessed. It is not a saving.
Product workflow, shown through the decisions and handoffs.
Ask what never enters the success report. If the denominator is defined by completed work, the product looks healthy while people go unserved.
From my original account
02
Recover the delta, and then build the thing that would have caught it. I built an audit table capturing file metadata (file name, unique identifier, claim totals, claim control numbers), changed adjudication to stamp each claim with its source file’s identifier, and delivered a dashboard with live fallout visibility.
| Option | What it fixes | Decision |
|---|---|---|
| Reprocess the found claims only | Recovers the missing claims | Necessary, not sufficient |
| Replace the intake platform | Future capacity | Premature before the failure was understood |
| Completeness definition plus daily audit (chosen) | A daily audit compares received and processed claims and surfaces fallout for investigation. | Built and adopted |
I noticed it the way you notice most real problems: something small that should not have been there. Claims files were erroring out of the intake pipeline at a supplemental-benefits business — and the audit tables had no record of the failures. Files were arriving. Some of what was in them was not being processed. And nothing in the system was keeping score.
I started with a bounded sample. The number of claims expected and the number accounted for did not agree. That gap was too specific to dismiss as noise. I proposed extending the analysis and wrote SQL to isolate the fallout, extraction tooling to inspect failed electronic files, and a cross-reference against what had actually been processed.
The investigation identified claims and provider payments that needed resolution. Timely payment was a business concern. The important finding was that completed-work reporting had not made the missing population visible.
Recovering the claims addressed the immediate issue. But recovery was not the whole product. I helped make received-versus-processed reconciliation visible through an audit view and reporting in Tableau, with operational ownership and leadership review. The next gap needed to be easier to find than the first.
The investigation exposed gaps between received and processed claims. I kept the work focused on source-file identity, fallout visibility and business recovery. The separate EDI shredding replacement addressed processing capability.
I think of this as the chapter where I learned that a missing audit trail is a finding, not a gap. If a system cannot tell you what it dropped, assume it dropped something.
Tools in this chapter: SQL, EDI 837 parsing, extraction tooling, source-file identifiers, reconciliation logic and Tableau.
03
At Evernorth, while testing EDI 837 claim files I saw that some files never reached the audit table, so their claims errored out without a trace. For a business that receives and acknowledges claims that is more than a data-quality nuisance. There was no reconciliation between claims received and claims adjudicated.
04
I proposed extending the analysis: 20 million claims across 50 GB of files.
300,000 missing claims worth about $10M in unprocessed claims were identified; the business processed the delta, and the find led to a permanent daily reconciliation audit. The daily reconciliation surfaced fallout for reprocessing every week. Operations assigned a dedicated owner and the report joined the weekly leadership readout.