MEDICARE DRUG
SPEND (2019-2023)

• Python was used for data profiling, cleaning, and validation, including handling missing values, normalizing manufacturer names, checking distributional outliers, and validating brand vs. generic classifications.
  ​​

• SQL was used to aggregate, shape, and calculate core metrics such as total spend, average price per dose, manufacturer market share, and compound annual growth rates (CAGR).
   

• Tableau Public was used to deliver an executive-ready, interactive dashboard with clear KPIs and dynamic exploration, while intentionally avoiding over-engineering.
  ​

The final output is a stable, portfolio-quality dashboard that balances analytical rigor with clear communication, demonstrating how complex healthcare cost data can be translated into actionable insight for policy, strategy, and analytics stakeholders.

Business Question

​

Where are Medicare drug costs rising fastest, and which products and manufacturers are driving overall spending and price growth?

Data

​

• Source: CMS Medicare Drug Spending Data (2019–2023)
   

• Grain: Drug / Manufacturer / Year
   

• Preparation: SQL-based aggregation and shaping
   

• Profiling & Validation: Python
   

Visualization: Tableau Public (Chromebook environment)
 

Key Fields Used

​

• Total drug spending
   

• Average price per dose (weighted)
   

• Brand vs. generic classification
   

• Manufacturer market share
   

Compound Annual Growth Rate (CAGR) of price per dose

Approach
 

1. Python-Based Data Profiling & Validation
 

Before building any metrics, Python was used to:

• Profile distributions of spending and price-per-dose
   

• Identify missing values and zero-division risks
   

• Validate year coverage and record counts
   

• Spot extreme outliers prior to aggregation
   

This ensured the downstream SQL metrics and Tableau KPIs were statistically sound and reliable.

 

2. SQL-Driven Data Modeling
 

• Transformed wide CMS extracts into analysis-ready aggregates
   

• Calculated:
   

  • Total spend by year
     

  • Brand vs. generic spend share
     

  • Manufacturer market share
     

  • Price-per-dose CAGR (2019–2023)
     

• Ensured clean, reproducible logic outside the visualization layer
   

3. Dashboard Design Principles
 

• No joins or relationships in Tableau (each worksheet powered by one SQL output)
   

• Static KPIs for clarity and trust
   

• Interactive charts via highlight actions and parameters
   

• Dark theme with restrained color usage for executive readability
   

Dashboard Highlights
 

KPI Summary
 

• $2.21T total Medicare drug spend (2019–2023)
   

• $176 average price per dose (2023)
   

• ~90% of spending on brand-name drugs
   

• HHI = 0.256, indicating a highly concentrated manufacturer market
   

Spending Trend

• A steady increase in total Medicare drug spending over five years, reinforcing long-term cost pressure on the program.

Product-Level Insights
 

• A small number of drugs dominate total spend
   

• A different set of drugs leads price growth (CAGR), highlighting emerging cost risks
   

• Interactive controls allow users to explore top drivers without overwhelming the view

Key Insights
 

• Medicare drug spending is highly concentrated among a small number of manufacturers.
   

• Brand-name drugs continue to dominate total spend despite generic availability.
   

• High-spend drugs are not always the fastest-growing — price growth risk and budget impact are driven by different products.
   

• Early Python profiling helped prevent misleading aggregates and KPI errors.

Tools & Skills
 

• Python – data profiling, validation, exploratory analysis
   

• SQL – data shaping, aggregation, metric calculation
   

• Tableau Public – dashboard design, interactivity, KPI presentation
   

• Healthcare Analytics – pricing, market concentration (HHI), growth analysis
   

• Data Storytelling – translating technical metrics into executive insights
   

Why This Project Matters
 

• This project demonstrates the ability to:

• Use Python for early-stage data quality and profiling
   

• Build SQL-first, reproducible analytics pipelines
   

• Design stable, production-ready dashboards
   

• Communicate complex healthcare cost dynamics clearly to non-technical stakeholders