CASE STUDY

Making Sense of Messy Data Across Multiple Payers

How we helped a mid-sized Medicare Advantage provider unify fragmented claims data and see the full financial picture

Client Background

Client: A Medicare Advantage provider group managing ~30 clinics and 40,000–50,000 members

Challenge:
The client worked with four different payers, each providing monthly claims files—but formats varied, data quality was inconsistent, and documentation standards differed across plans.

As their operations lead described it:

They had been using Excel to patch together reports from the two payers they could semi-structure, but building a unified view of performance across all plans was impossible. They couldn’t answer a simple question: Are we hitting our financial targets?

“It feels like digging through a trash can, hoping to find lunch.”

Our Approach

From messy files to a meaningful financial view.

We stepped in to help them make sense of the chaos—building a scalable, repeatable analytics infrastructure from the ground up:

  • Data Schema & Mapping
    We worked with their team to understand the business logic, then designed a standardized schema that could accept all four payers’ data.

  • Pipeline Development
    We created automated ingestion and transformation pipelines to normalize claims files, clean inconsistencies, and map everything into one structured model.

  • Dashboard Design
    We developed a unified medical expense dashboard, where all payer data flows into one interactive view—with filters by payer, category, provider, and patient.

  • KPI Definition
    We helped define key metrics like PMPM by category, enabling the team to track financial targets and drill into what’s driving over- or under-performance.

Results That Matter

Clarity

The team could finally see total medical spend across all payers—and compare it to their internal benchmarks.

Prioritization

They could drill down by provider or patient cohort to identify high-cost drivers and outliers.

Scalability

The framework was built in a modular way—starting with one payer and adding others incrementally without rework.

Adoption & Improvement

Users loved the first version—and immediately provided feedback that we used to refine usability and expand features.

Why It Worked

We didn’t just clean data—we built a system that grows with their business.

  1. We listened to users, understood their Excel workflows, and designed around their decision-making needs.

  2. We provided hands-on support across data engineering, modeling, and visualization—roles they otherwise would’ve needed multiple hires to fill.

  3. We focused on delivery—the working dashboard went live in just two months, even while balancing other projects in parallel.

“This is the first time we’ve had a clean view of all our spend. I can finally stop digging through spreadsheets.”

— Director of Medical Economics, Medicare Advantage Provider Group