Geographic Analysis

Geographic Analysis — Where Does the Money Go?

By joining provider NPIs to the NPI Registry, we can map spending, beneficiaries, and provider counts to the states where care is actually delivered. This reveals dramatic variation — some states spend 10x more per beneficiary than others for the same services.

Provenance

Methodological Guardrails

Total Medicaid Spending by State

SELECT * FROM medicaid.spending_by_state

Citation: by_state (source medicaid.spending_by_state).

Cost per Beneficiary by State

This is the metric that reveals the most about state-level variation. High cost-per-beneficiary can mean generous reimbursement (good for providers), high-acuity populations, or inefficiency. Low values can mean lean programs or underpayment that drives providers away.

SELECT * FROM medicaid.spending_by_state

Provider Capacity by State

The beneficiaries-per-provider ratio measures how stretched each state's provider network is. Higher values mean each provider is serving more people — longer wait times, shorter appointments, burnout risk.

ER Reliance by State

Where are people most reliant on ERs instead of primary care? States with a high ER share of combined ER + primary care spending have access problems.

SELECT * FROM medicaid.state_er_ratio

Citation: er_by_state (source medicaid.state_er_ratio).

Territories (Separate from State Rankings)

To keep state rankings interpretable, US territories are reported separately.

SELECT * FROM medicaid.spending_by_territory;
SELECT * FROM medicaid.territory_er_ratio;

Compare States

Pick two states to compare side-by-side across key metrics.

SELECT * FROM medicaid.state_monthly_trends
WHERE state IN ('${inputs.compare_a.value}', '${inputs.compare_b.value}')

SELECT * FROM medicaid.spending_by_state_category
WHERE state IN ('${inputs.compare_a.value}', '${inputs.compare_b.value}')
ORDER BY category, state

State Detail

CA — Spending by Category

SELECT * FROM medicaid.spending_by_state_category
WHERE state = '${inputs.selected_state.value}'
ORDER BY paid_billions DESC

State Spending Trends

CA — Monthly Spending Trend

Provider Specialties

CA — Top Provider Specialties by Spending

SELECT * FROM medicaid.state_provider_specialty
WHERE state = '${inputs.selected_state.value}'
ORDER BY paid_billions DESC
LIMIT 20

County-Level Analysis

Provider shortages don't happen at the state level — they happen in individual counties. This table shows Medicaid spending and provider capacity at the county level, mapped via NPI Registry ZIP codes to the Census Bureau's ZCTA-to-county crosswalk.

CA — Counties by Medicaid Spending

SELECT * FROM medicaid.spending_by_county
WHERE state_abbr = '${inputs.selected_state.value}'
ORDER BY paid_millions DESC

Provider Deserts

Counties where each Medicaid provider serves a disproportionately large number of beneficiaries. High beneficiaries-per-provider signals stretched capacity — longer waits, shorter appointments, and providers at risk of burnout or leaving Medicaid entirely.

County Attribution Sensitivity

County assignment can change results depending on attribution method. We publish both:

• Primary-county method (largest ZIP land-area overlap)
• Weighted method (allocate ZIP spend across all overlapping counties by land-area weight)

SELECT * FROM medicaid.county_attribution_sensitivity

Citation: county_sensitivity (source medicaid.county_attribution_sensitivity).

Reproduce This Page

cd dashboard
export EVIDENCE_SOURCE__medicaid__token="<your_motherduck_token>"
export EVIDENCE_SOURCE__medicaid__database="medicaid"
npm run sources
npm run build
npm run preview
# then open http://localhost:3000/geography