# Vlada Plan Value v18 parent shelf + ranking, v18b (who cut, who added; new contracts rolled into their parent), national / state / county x product type x parent

`medicare_semantic.plan_value_shelf_v18b` · Medicare Advantage, Part D, Medicaid · status live · 80,012 rows

Every parent in a place, for one product type, on two bases side by side. SAME 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. PLANS 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). State and national averages are over the DISTINCT plan-segments offered anywhere in the state / nation. Parent 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 (v18b: a new contract of a known parent rolls into it), else 'NEW CONTRACT: ' + the name. /ma reads v18b. Built by ops/scripts/plan_value_shelf_sql.py; gates ops/scripts/plan_value_shelf_check.py. Owner source: register w3 vlada_w3_benvat.

**Grain.** One row per geography_level x state x county_ssa x product_type x parent_organization, parent ALL = the market (80,012 rows = 80,012 distinct keys, measured 2026-10-09 in Athena - national 389, state 1,895, county 77,728; nine product types, never pooled).

| 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(536) |  |

Query it: `query_substrate` with `SELECT ... FROM medicare_semantic.plan_value_shelf_v18b WHERE ... LIMIT 100`; read `describe_data_source("medicare_semantic.plan_value_shelf_v18b")` first.