Medicare Advantage, Part D, Medicaid

Vlada Plan Value v18 parent shelf + ranking (who cut, who added), national / state / county x product type x parent

medicare_semantic.plan_value_shelf_v18·live·mart

Rows

80,160

Columns

History

N/A

Card

nocolumns only

Typed tools

What it is

1Every parent in a place, for one product type, on two bases side by side.
2SAME MEMBERS (wtd_plan_value_2026 / _2027 / wtd_change, members_followed, members_valued_both_years): the signed same-member numbers of vw_plan_value_pairs_v18 at plan_type ALL, equal on every row.
3PLANS ON THE SHELF (avg_plan_value_2026 / _2027 / avg_change, avg_xpd_* without Part D): the simple average over the distinct plan-segments the parent offers in the geography each contract year (CMS service area, vw_pbp_plan_county_service_area; regional PPOs over every county of their CMS region; employer plans 800-899 excluded; valued plans only), with plans_offered / new / dropped / valued counts and ranks (1 = biggest increase among parents, ties by members_2026).
4State and national averages are over the DISTINCT plan-segments offered anywhere in the state / nation.
5Parent of a 2027 plan = parent_organization_2027, else the contract's resolved parent (vw_ma_parent_resolved_v1), else 'NEW CONTRACT: ' + the landscape parent.
6Built by ops/scripts/plan_value_shelf_sql.py; gates ops/scripts/plan_value_shelf_check.py.
7Owner source: register w3 vlada_w3_benvat.

Grain

Key	Grain
N/A	1One row per geography_level x state x county_ssa x product_type x parent_organization, parent ALL = the market (80,160 rows = 80,160 distinct keys, measured 2026-10-09 in Athena - national 408, state 1,905, county 77,847; nine product types, never pooled).

Columns (30)

Column	Type	Meaning
`geography_level`	varchar(8)
`state`	string
`county_ssa`	string
`county_name`	string
`product_type`	string
`parent_organization`	string
`members_2026`	double
`members_followed`	double
`members_valued_both_years`	double
`wtd_plan_value_2026`	double
`wtd_plan_value_2027`	double
`wtd_change`	double
`plans_offered_2026`	bigint
`plans_offered_2027`	bigint
`plans_new_2027`	bigint
`plans_dropped_2027`	bigint
`plans_valued_2027`	bigint
`plans_valued_xpd_2027`	bigint
`avg_plan_value_2026`	double
`avg_plan_value_2027`	double
`avg_change`	double
`avg_xpd_2026`	double
`avg_xpd_2027`	double
`avg_xpd_change`	double
`rank_wtd_change`	bigint
`rank_avg_change`	bigint
`rank_avg_xpd_change`	bigint
`n_parents_in_market`	int
`build`	varchar(20)
`basis`	varchar(536)

Query it

describe_data_source(source_fqn="medicare_semantic.plan_value_shelf_v18")     # the card, live
query_substrate(sql="SELECT * FROM medicare_semantic.plan_value_shelf_v18 WHERE <filter> LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`geography_level,state,county_ssa,product_type,parent_organization`
Join	`how_to_join(source_fqn="medicare_semantic.plan_value_shelf_v18")`
Raw door	Athena (read-only via query_substrate): medicare_semantic.plan_value_shelf_v18
Company names	`parent_organization` resolve to a permanent Vlada ID: `resolve_entity(text=<name>)`; the company pages hold owners and holdings

This page as markdown: /docs-md/data/medicare_semantic_plan_value_shelf_v18.md · every page in one file: /llms-full.txt