Healthcare Cost Driver & Outlier Demo

Overview

This project is a small healthcare analytics demo designed to show how raw healthcare expenditure and utilization data can be transformed into curated, business-ready analytical layers.

The goal was not to build a complex machine learning product. The goal was to demonstrate how a centralized analytics platform can turn complex raw source data into reusable reporting outputs that help stakeholders understand:

• cost drivers
• utilization patterns
• segment-level differences
• high-cost populations

This project was built as a local prototype using public MEPS data and a bronze / silver / gold workflow.

Business Problem

Benefits and analytics stakeholders often struggle to answer basic questions quickly when healthcare data is raw, fragmented, or difficult to interpret.

Questions such as these are common:

• Which groups are driving the highest spend?
• How does utilization change over time?
• Is spend concentrated in a relatively small high-cost population?
• How much easier would this analysis be if the data were already shaped into curated business-ready tables?

This demo shows how a centralized analytics workflow can reduce the gap between raw source data and useful stakeholder insight.

Objective

Build a small, finished analytics demo that:

• ingests raw public healthcare data
• standardizes it into reusable layers
• creates curated business-ready gold tables
• surfaces simple but useful business insights through charts and summary tables

Data Source

This demo uses publicly available MEPS (Medical Expenditure Panel Survey) data from AHRQ.

Files used:

• h251.xlsx — MEPS Full-Year Consolidated file (2023)
• h248g.xlsx — MEPS Office-Based Medical Provider Visits file (2023)

These files were used because they provide a manageable but realistic healthcare analytics dataset with:

• person-level demographic and insurance information
• person-level annual expenditure and visit totals
• visit-level office-based utilization and expenditure details

Architecture

This project follows a simple bronze / silver / gold data-layer pattern.

Bronze

Raw source data staged as CSVs after initial local ingestion from MEPS Excel files.

Outputs:

• bronze_meps_person_raw.csv
• bronze_meps_office_visits_raw.csv

Silver

Cleaned and standardized analytical base tables.

Outputs:

• silver_member.csv
• silver_office_visit.csv

Transformations included:

• renaming technical source fields to clearer names
• converting identifiers to string
• preserving raw coded fields
• adding friendly label columns
• deriving age_band
• creating a usable visit-level date field

Gold

Curated, business-ready analytical tables for reporting and decision support.

Outputs:

• gold_member_annual_spend.csv
• gold_monthly_utilization.csv
• gold_spend_by_segment.csv
• gold_high_cost_members.csv