Case study: finding coverage gaps a healthcare client kept missing in spreadsheets

A healthcare operations lead emailed us last spring with a sentence we've heard a dozen variations of: "We know we're leaving money on the table, we just can't prove where." Her team was running coverage-gap analysis out of three spreadsheets, a shared Dropbox, and one analyst's memory. This is the story of what we built and what changed. Names and numbers are blurred to protect the client.

What they had

The team supported a regional provider group reconciling patient coverage across commercial plans, Medicare Advantage, and a handful of managed Medicaid contracts. Their process looked like this:

- Pull eligibility files weekly from four payer portals (manual login, CSV export). - Cross-reference against the EHR's patient roster in Excel. - Flag mismatches by hand. Re-verify by phone. - Email the list to billing every Friday.

One analyst owned the whole thing. When she took PTO, the process stopped. They estimated 15 to 20 hours a week of pure clerical work, plus an unknown number of gaps that simply slipped through because no one had time to chase them.

This is the kind of work Healthcare Dive recently described as the operational underbelly of payer-provider coordination: claims, prior auth, eligibility, coverage analysis. Boring on the surface. Expensive when it breaks.

What we built

We scoped it small on purpose. No "platform." One app, one job: surface coverage gaps every morning, ranked by likely revenue impact, with a one-click path to verification.

The build, over about six weeks:

- Scheduled pulls from each payer source. Where there was no API, we automated the portal logins with a headless browser and stored the files in their cloud. - A normalization layer that maps each payer's field names to a single internal schema. This was 60% of the actual work. - A reconciliation engine that compares eligibility against the EHR roster and flags four gap types: lapsed coverage, plan switch, secondary not on file, and demographic mismatch. - A simple web UI for the analyst. Filter, sort, mark verified, export to billing. - Managed hosting on our infrastructure. They don't have an IT team. They shouldn't have to.

No AI in the loop yet, by the way. We discussed it. The reconciliation logic is deterministic and the team wanted to trust the output before adding probabilistic anything. We'll revisit later.

What changed

In the first month: the weekly 15-to-20-hour task dropped to roughly two hours of review. More importantly, the team caught a category of gap (secondary coverage not on file for dual-eligible patients) they had not been systematically tracking. We won't quote a dollar figure here. The client has one. It was enough to fund the build several times over in the first quarter.

The lead now spends her Fridays on appeals instead of CSVs. That's the part that matters.

The takeaway

Coverage-gap analysis isn't a unique problem. Every provider group and payer-adjacent team has some version of it. But the solution should fit the team that has to live with it. Small app, narrow job, runs every morning, doesn't ask for attention. That's the bar.