Medicare Advantage, Part D, Medicaid

Vlada Plan Value v19d parent shelf + ranking (Method Lock 1.0; who cut, who added; new contracts rolled into their parent), national / state / county x product type x parent

medicare_semantic.plan_value_shelf_v19d·live·mart

Rows

80,002

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_v19d 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 the landscape parent: that parent itself when it is already a resolved parent (a new contract of a known parent rolls into it, as v18b), else 'NEW CONTRACT: ' + the name. /ma reads v19d after the flip.
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,002 rows = 80,002 distinct keys, measured 2026-10-16 in Athena - national 390, state 1,896, county 77,716; 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(21)
`basis`	varchar(543)

Query it

describe_data_source(source_fqn="medicare_semantic.plan_value_shelf_v19d")     # the card, live
query_substrate(sql="SELECT * FROM medicare_semantic.plan_value_shelf_v19d 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_v19d")`
Raw door	Athena (read-only via query_substrate): medicare_semantic.plan_value_shelf_v19d
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_v19d.md · every page in one file: /llms-full.txt