Analysis of U.S. Medicaid drug spending data published by the Centers for Medicare & Medicaid Services (CMS), covering five years of spending trends across drugs, manufacturers, and claim types.
Built as a healthcare data analyst portfolio project targeting roles in hospital procurement and health system analytics.
| Tool | Purpose |
|---|---|
| MySQL | Database design, data loading, analytical queries |
| Power BI | Interactive reports and dashboards (Power BI Desktop / Service) |
| Python (Jupyter) | Exploratory data analysis, visualizations |
| GitHub | Version control and portfolio hosting |
Source: Centers for Medicare & Medicaid Services (CMS) File: DSD_MCD_RY25_P06_V20_D23_BGM.csv Download: https://data.cms.gov/summary-statistics-on-use-and-payments/medicare-medicaid-spending-by-drug/medicaid-spending-by-drug
| Detail | Value |
|---|---|
| Rows | 16,938 |
| Columns | 36 |
| Years Covered | 2019 – 2023 |
| Unit of Analysis | Drug × Manufacturer |
The raw CSV is not included in this repository. Download it from the link above and place it in the
/data/folder before running any scripts.
medicaid-spending-analysis/
│
├── sql/
│ ├── 01_create_table.sql # Database and table schema
│ ├── 02_load_data.sql # Load CSV into MySQL
│ └── 03_analysis_queries.sql # 5 analytical queries
│
├── python/
│ └── eda_notebook.ipynb # Exploratory data analysis
│
├── power_bi/
│ └── report_files/ # Power BI Desktop files and exported report screenshots
│
├── data/
│ └── README.md # Data source instructions
│
└── README.md
The raw CMS file contains 36 columns describing drug-level Medicaid spending and utilization for 2019–2023. The table below maps each column to a business definition, an expected data type, and a representative example value.
| Column | Business definition | Data type | Example value |
|---|---|---|---|
| Brnd_Name | Brand-name drug name | string | Acyclovir |
| Gnrc_Name | Generic drug name | string | Acyclovir |
| Tot_Mftr | Total number of manufacturers associated with the drug entry | integer | 1 |
| Mftr_Name | Manufacturer or labeler name | string | Teva Pharmaceuticals |
| Tot_Spndng_2019 | Total Medicaid spending for 2019 | decimal | 1250000.00 |
| Tot_Dsg_Unts_2019 | Total dosage units dispensed in 2019 | decimal | 150000.00 |
| Tot_Clms_2019 | Total claims submitted in 2019 | decimal | 25000.00 |
| Avg_Spnd_Per_Dsg_Unt_Wghtd_2019 | Weighted average spend per dosage unit in 2019 | decimal | 8.32 |
| Avg_Spnd_Per_Clm_2019 | Average spend per claim in 2019 | decimal | 50.00 |
| Outlier_Flag_2019 | Indicator for unusually high or atypical values in 2019 | integer | 0 |
| Tot_Spndng_2020 | Total Medicaid spending for 2020 | decimal | 1325000.00 |
| Tot_Dsg_Unts_2020 | Total dosage units dispensed in 2020 | decimal | 155000.00 |
| Tot_Clms_2020 | Total claims submitted in 2020 | decimal | 26000.00 |
| Avg_Spnd_Per_Dsg_Unt_Wghtd_2020 | Weighted average spend per dosage unit in 2020 | decimal | 8.55 |
| Avg_Spnd_Per_Clm_2020 | Average spend per claim in 2020 | decimal | 50.96 |
| Outlier_Flag_2020 | Indicator for unusually high or atypical values in 2020 | integer | 0 |
| Tot_Spndng_2021 | Total Medicaid spending for 2021 | decimal | 1380000.00 |
| Tot_Dsg_Unts_2021 | Total dosage units dispensed in 2021 | decimal | 160000.00 |
| Tot_Clms_2021 | Total claims submitted in 2021 | decimal | 27000.00 |
| Avg_Spnd_Per_Dsg_Unt_Wghtd_2021 | Weighted average spend per dosage unit in 2021 | decimal | 8.63 |
| Avg_Spnd_Per_Clm_2021 | Average spend per claim in 2021 | decimal | 51.11 |
| Outlier_Flag_2021 | Indicator for unusually high or atypical values in 2021 | integer | 0 |
| Tot_Spndng_2022 | Total Medicaid spending for 2022 | decimal | 1450000.00 |
| Tot_Dsg_Unts_2022 | Total dosage units dispensed in 2022 | decimal | 165000.00 |
| Tot_Clms_2022 | Total claims submitted in 2022 | decimal | 28000.00 |
| Avg_Spnd_Per_Dsg_Unt_Wghtd_2022 | Weighted average spend per dosage unit in 2022 | decimal | 8.79 |
| Avg_Spnd_Per_Clm_2022 | Average spend per claim in 2022 | decimal | 51.79 |
| Outlier_Flag_2022 | Indicator for unusually high or atypical values in 2022 | integer | 0 |
| Tot_Spndng_2023 | Total Medicaid spending for 2023 | decimal | 1520000.00 |
| Tot_Dsg_Unts_2023 | Total dosage units dispensed in 2023 | decimal | 170000.00 |
| Tot_Clms_2023 | Total claims submitted in 2023 | decimal | 29000.00 |
| Avg_Spnd_Per_Dsg_Unt_Wghtd_2023 | Weighted average spend per dosage unit in 2023 | decimal | 8.94 |
| Avg_Spnd_Per_Clm_2023 | Average spend per claim in 2023 | decimal | 52.41 |
| Outlier_Flag_2023 | Indicator for unusually high or atypical values in 2023 | integer | 0 |
| Chg_Avg_Spnd_Per_Dsg_Unt_22_23 | Percentage change in average spend per dosage unit from 2022 to 2023 | decimal | 12.50 |
| CAGR_Avg_Spnd_Per_Dsg_Unt_19_23 | Compound annual growth rate for average spend per dosage unit from 2019 to 2023 | decimal | 0.18 |
Five queries were written to answer procurement-relevant business questions:
| # | Query | Business Question |
|---|---|---|
| 1 | Top 10 drugs by total spending | Which drugs consume the most Medicaid budget? |
| 2 | Total spending by year (2019–2023) | How has overall Medicaid drug spending trended? |
| 3 | Top 5 manufacturers by revenue | Which suppliers dominate Medicaid drug supply? |
| 4 | Brand vs generic cost per prescription | How much more do brand drugs cost vs generics? |
| 5 | Drugs with 50%+ spending increase YoY | Which drugs represent rising procurement risk? |
See /sql/03_analysis_queries.sql for full query code.
- Total Medicaid drug spending increased significantly from 2019 to 2023, driven by a small number of high-cost brand-name drugs.
- The top 10 drugs account for a disproportionate share of total program spending.
- Brand-name drugs cost substantially more per prescription than their generic equivalents.
- Several drugs showed year-over-year spending increases exceeding 50%, signaling procurement risk areas.
- A small group of manufacturers generate the majority of Medicaid drug revenue.
Note: Specific figures will be updated after SQL queries are executed against the full dataset.
Built using Power BI Desktop connected to the MySQL database (or a prepared dataset). The report is organized across 3 pages mirroring the original analysis:
Page 1 — Spending Overview
- Total spending KPI cards by year
- Year-over-year spending trend line chart
- Top 10 drugs by 2023 spending (bar chart)
Page 2 — Manufacturer Analysis
- Top manufacturers by total revenue (bar chart)
- Drug count per manufacturer (table)
- Manufacturer market share (donut chart)
Page 3 — Cost & Risk Analysis
- Brand vs generic average cost per claim (clustered bar)
- High-risk drugs: 50%+ YoY spending increase (table)
- CAGR distribution by drug type (histogram)
You can publish the report to Power BI Service for sharing, or export screenshots to assets/ for portfolio display.
The Jupyter notebook (/python/eda_notebook.ipynb) covers:
- Dataset shape, data types, and null value summary
- Distribution of spending per dosage unit (histogram)
- Total spending vs total claims scatter plot (2023)
- Top 10 drugs by spending bar chart
- Year-over-year spending trend line chart
- Download the CSV from the link in
/data/README.md - Place it in your MySQL
secure_file_privfolder - Run
sql/01_create_table.sqlto create the database and table - Update the file path in
sql/02_load_data.sqland run it - Run
sql/03_analysis_queries.sqlto execute the analysis
pip install pandas matplotlib seaborn jupyter
jupyter notebook python/eda_notebook.ipynb- Install Power BI Desktop (or use Power BI Service)
- Connect to MySQL using the MySQL connector or import a prepared dataset
- Build the three report pages described above
- Publish to Power BI Service to share, or export report pages/screenshots to
/assets/for portfolio use
[Fadi Amir] SQL Developer | Database Design
This project is for educational and portfolio purposes. Data is publicly available from CMS and is not redistributed here.