# PBP otc (cleaned view)

`medicare_semantic.vw_pbp_otc` · Medicare Advantage, Part D, Medicaid · status live

Cleaned source-faithful PBP otc rows: era-normalized, CMS codes decoded, with offered_status/numeric_amount_observability/null_amount_reason/annualization_policy/combined_limit_policy/confidence_tier. The agent reasoning surface for otc (vs

**Grain.** One row per MA plan-segment per PBP report year per intra-year release snapshot (quarter) per OTC sub-benefit component. Verified: COUNT(*) = 503,753 exactly equals COUNT(DISTINCT contract_id, plan_id, segment_id, report_year, quarter, otc_sub_benefit), and a GROUP BY on those 6 columns yields max 1 row per group across all 503,753 groups. A plan-snapshot fans out into up to 4 component rows (otc_list_coverage, otc_items_allowance, otc_nicotine_replacement_therapy, otc_naloxone). version is constant within each grain group (not a key). Only otc_items_allowance carries a dollar amount; the other three components are indicator-only.

**How to use it.** Get current-year OTC allowance generosity per MA plan: SELECT contract_id, plan_id, segment_id, benefit_amount, benefit_amount_period_label, benefit_amount_annualized, benefit_amount_pmpm FROM vw_pbp_otc WHERE report_year=2026 AND quarter=(SELECT MAX(quarter) FROM vw_pbp_otc WHERE report_year=2026) AND otc_sub_benefit='otc_items_allowance' AND benefit_amount IS NOT NULL ORDER BY benefit_amount_annualized DESC — answers 'which 2026 Medicare Advantage plans offer the richest OTC dollar allowance, normalized to an annual figure.' Filtering to MAX(quarter) avoids snapshot double-counting; restricting to otc_items_allowance is required because only that component holds dollars.

**Traps.**
- Grain is 6 columns including quarter and otc_sub_benefit. (contract_id, plan_id, segment_id) is NOT unique (only 92,166 plan-year keys vs 503,753 rows). Each plan-snapshot fans into up to 4 sub-benefit rows; do not treat one plan as one row.
- There is NO is_current/snapshot flag. 'quarter' is an intra-year CMS PUF republication, and snapshot years (2018, 2019, 2023 = quarters 1-4; 2024 = 1,3,4; 2020 = 1,2,4) repeat the SAME plan data each quarter (amounts are stable across quarters, max distinct amount per plan-year = 1). Counting all quarters 2-4x inflates plan counts. To get one current row per plan-year, filter to MAX(quarter) within each report_year.
- Dollars live on ONE sub-benefit only: benefit_amount / _annualized / _pmpm are non-null exclusively on otc_sub_benefit='otc_items_allowance' (and even there only ~47%). The other three components (otc_list_coverage, otc_nicotine_replacement_therapy, otc_naloxone) are indicator-only and carry $0/null amounts by design — never SUM benefit_amount across sub-benefits or read OTC spend off the list/NRT/naloxone rows.
- A null benefit_amount usually does NOT mean missing data — read null_amount_reason: 'in_kind_or_vendor_administered' (250,819 rows; OTC delivered via vendor card so no filed $), 'unlimited', 'not_offered', or 'unknown_requires_crosswalk'. offered_status='offered_unknown' (160k rows) also means the offered flag is genuinely indeterminate, not absent.
- Sub-benefit availability is year-gated: otc_nicotine_replacement_therapy rows exist only report_year>=2019, otc_naloxone only 2025-2026. Their indicator columns (otc_nrt_indicator, otc_naloxone_indicator) are null for ~85-98% of the table because those rows simply don't exist in earlier years — not because the value is missing.
- Several columns are degenerate/empty and carry no signal in this view: benefit_category (always 'otc'), network_type (always 'in_network'), confidence_tier (always 'source_faithful'), consumer_document_reconciliation_status (always 'not_checked'), and lmi_authorities (always ''). Don't filter on them expecting variation.
- Use report_year as the time axis, NOT quarter — the profiler mislabels 'quarter' as the time column. Period labels like 'Every three months' refer to the allowance refill period, not a calendar quarter; annualize via benefit_amount_annualized to compare plans.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS MA contract id, e.g. H0104, H3347, E5088 (E-prefix = employer/EGWP). 1,680 distinct. Part of grain. |
| plan_id | string | Plan id within the contract, e.g. 001, 007, 027 (zero-padded string). 656 distinct values; only unique together with contract_id+segment_id. Part of grain. |
| segment_id | string | Plan benefit segment, mostly '0'; 26 distinct. Part of grain — identifies the PBP segment when a plan is segmented by geography. |
| version | double | PBP submission/bid version number (double, 1-26). Constant within a grain group (one version per plan-snapshot); NOT a grain key. Reflects which bid revision the row came from. |
| report_year | double | Contract/benefit year the PBP applies to (2010-2026). THE real history axis of this table (17 years). Use this, not 'quarter', for time-series. |
| quarter | double | Intra-year CMS PUF release snapshot (1-4), NOT a separate benefit period. Older years have only quarter=1; snapshot years (2018/2019/2023 have 1-4, 2020/2024 have 3) republish the SAME plan-year data each quarter. A trap: counting all quarters 2-4x-inflates plans. Dedup by MAX(quarter) per plan-year to get one current snapshot. |
| benefit_category | string | Always 'otc' — this view is the OTC slice only. Degenerate single-value column. |
| cost_share_string | string | Raw cost-share text as filed. Null in nearly all rows (OTC is usually $0 cost-share / allowance-based). |
| copay_amount | double | Generic per-visit copay from the PBP cost-share block. Largely null for OTC; the OTC-specific copay lives in otc_copay_amount_min/max. |
| coinsurance_pct | double | Generic coinsurance percent. Largely null for OTC. |
| limit_text | string | Raw free-text limit description from the bid. Largely null. |
| auth_required | boolean | Boolean: prior authorization required. Largely null for OTC. |
| referral_required | boolean | Boolean: referral required. Largely null for OTC. |
| network_type | varchar(10) | Always 'in_network' — degenerate single-value column for this view (OTC is in-network only). |
| max_oop_applies | boolean | Boolean: does this benefit count toward the plan MOOP. Largely null. |
| limit_quantity | double | Numeric quantity limit on the benefit (with limit_unit/limit_period). Sparsely populated. |
| limit_unit | string | Unit for limit_quantity (e.g. items/dollars). Sparsely populated. |
| limit_period_code | string | CMS code for the limit period (e.g. per month/quarter/year). Raw code paired with limit_period_label. |
| limit_period_label | string | Human label for limit_period_code, e.g. 'Every three months'. |
| limit_quantity_per_year | double | limit_quantity normalized to an annual figure. Sparsely populated. |
| benefit_amount | double | The OTC dollar allowance AS FILED for its period (e.g. 950.0 = $950 'Every three months'). Populated ONLY on otc_items_allowance rows, and there only ~47% (83,457 of 179,479) — null elsewhere. Range $0-$3,900; median ~$57. THE core cost figure. |
| benefit_amount_kind | varchar(14) | Classifies the amount: 'otc_allowance' (179,479 rows = the otc_items_allowance component, may still have null amount) vs 'indicator_only' (324,274 rows = the 3 non-dollar components). Tells you whether a dollar value is even possible for the row. |
| benefit_amount_period_code | string | CMS code for the allowance period. Raw code paired with the label below. |
| benefit_amount_period_label | string | Period the benefit_amount covers: 'Every three months' (most common), 'Every month', 'Every year', 'Every six months', 'Every three years'. Drives annualization. |
| benefit_amount_annualized | double | benefit_amount normalized to a yearly dollar figure (e.g. $950/quarter -> $3,800/yr). Range $0-$5,136. Use THIS to compare allowances across plans on equal footing. |
| benefit_amount_pmpm | double | Allowance expressed per-member-per-month (annualized/12, e.g. $3,800/yr -> $316.67). Convenience normalization for cost modeling. |
| normalization_status | varchar(36) | How the amount was derived: 'normalized' (83,457, has a clean dollar value), 'component_indicator_only' (324,274, no dollar by design), 'not_populated' (90,807, allowance component but bid left it blank), 'offered_no_numeric_allowance' (5,215, offered but unlimited/non-numeric). |
| normalization_basis | varchar(24) | What field the value/indicator was built from, e.g. 'cms_indicator_field'. Provenance of the normalization. |
| aggregation_policy | varchar(60) | How this row should be aggregated, e.g. 'indicator_component_not_financial_cap' — warns that indicator rows must not be summed as if they were dollar caps. |
| offered_status | varchar(15) | Tri-state offered flag for the component: 'offered' (188,207), 'not_offered' (155,354), 'offered_unknown' (160,192, offered flag indeterminate in the bid). Key column for 'does this plan have OTC'. |
| numeric_amount_observability | varchar(31) | Why a dollar amount is/ isn't present: 'all_offered_numeric' (83,457, clean dollars), 'offered_without_numeric_amounts' (264,942, offered but vendor/in-kind so no $), 'not_offered' (155,354). |
| null_amount_reason | varchar(30) | Explains a null benefit_amount: 'in_kind_or_vendor_administered' (250,819 — OTC run through a vendor card, no filed $), 'not_offered' (155,354), 'not_null_numeric_amount' (83,457 — i.e. amount IS present), 'unlimited' (5,215), 'unknown_requires_crosswalk' (8,908). A null amount is NOT missing data — read this column. |
| annualization_policy | varchar(17) | Rule applied to annualize, e.g. 'not_numeric' for indicator rows. Governs benefit_amount_annualized. |
| combined_limit_policy | varchar(29) | Whether this benefit shares a limit with another, e.g. 'none_detected'. Provenance/aggregation guard. |
| consumer_document_reconciliation_status | varchar(11) | Always 'not_checked' — placeholder; these PBP values have not been cross-checked against consumer SB/EOC documents. Degenerate single-value column. |
| confidence_tier | varchar(23) | Always 'source_faithful' — every row is a faithful copy of the CMS bid (no inference). Degenerate single-value column. |
| cost_share_populated_in_bid | boolean | Boolean: did the bid populate a cost-share block for this benefit. Mostly false for OTC (allowance-based, $0 cost-share). |
| otc_amount_source | string |  |
| otc_base_offered | boolean | Plan-level boolean: does the plan offer a base OTC benefit at all (distinct from the per-component otc_offered). Yes 261,661 / No 217,423 / null 24,669. |
| otc_base_offered_code | string | Raw CMS code behind otc_base_offered ('1'=Yes,'2'=No). |
| otc_base_offered_label | varchar(3) | Human label for otc_base_offered ('Yes'/'No'). |
| otc_benefit_family | varchar(16) | Which family this row's component belongs to: 'otc_list' (list_coverage), 'otc_items' (items_allowance), 'otc_drug_subitem' (NRT + naloxone). Mirrors otc_sub_benefit at a coarser level. |
| otc_benefit_type_code | string | CMS code for the OTC benefit type. Populated where the component is offered (~88,673 per applicable component). |
| otc_benefit_type_label | varchar(10) | Human label for otc_benefit_type_code. |
| otc_cap_carry_forward | boolean | Boolean: does an unused OTC allowance carry forward to the next period. Populated mainly on items_allowance rows. |
| otc_cap_carry_forward_code | string | Raw CMS code for carry-forward. |
| otc_cap_carry_forward_label | string | Human label for carry-forward (Yes/No). |
| otc_cap_present | boolean | Boolean: is there a spending cap on the OTC allowance. Populated on otc_items_allowance rows (88,673); null for the indicator-only components. |
| otc_cap_present_code | string | Raw CMS code for cap presence. |
| otc_cap_present_label | string | Human label for cap presence (Yes/No). |
| otc_cap_source | string | Where the cap value/flag was sourced from. Sparsely populated. |
| otc_coinsurance_pct_max | double |  |
| otc_component_detail_status | varchar(23) | Describes the detail level captured for the component, e.g. 'list_coverage_indicator'. Metadata about completeness of the component row. |
| otc_copay_amount_max | double | Upper bound of the OTC item copay range. When populated, almost always equals the min and is $0 (36,994 rows at 0/0); range exists for the rare plan with varying item copays. |
| otc_copay_amount_min | double | Lower bound of the OTC item copay range (mostly $0). Pair with _max; equal min=max means a single flat copay. |
| otc_delivery_mode_code | string | CMS code for OTC delivery mode (e.g. mail/retail/catalog). Very sparsely populated (~4,244 rows). |
| otc_delivery_mode_label | string | Human label for delivery mode. |
| otc_in_package_aggregate_max | boolean |  |
| otc_list_covered | boolean | Boolean specific to the otc_list_coverage component: is the OTC item list covered. Populated only on otc_list_coverage rows (88,673); null (415,080) on all other components by design. |
| otc_list_covered_code | string | Raw CMS code for list-covered. |
| otc_list_covered_label | string | Human label for list-covered (Yes/No); null on non-list rows. |
| otc_list_source | string | Source of the covered OTC item list. Sparsely populated. |
| otc_naloxone_indicator | boolean | Boolean specific to the otc_naloxone component: is OTC naloxone offered. Populated only on otc_naloxone rows (8,711), and that sub-benefit exists ONLY in report_year 2025-2026. Null (495,042) elsewhere. |
| otc_naloxone_indicator_code | string | Raw CMS code for naloxone indicator. |
| otc_naloxone_indicator_label | string | Human label for naloxone indicator (Yes/No). |
| otc_nrt_attestation_present | boolean | Boolean: is an NRT (nicotine replacement therapy) attestation present in the bid. Tied to the NRT component. |
| otc_nrt_attestation_raw | string | Raw NRT attestation text from the bid. Sparsely populated. |
| otc_nrt_indicator | boolean | Boolean specific to the otc_nicotine_replacement_therapy component: is OTC NRT offered. Populated only on NRT rows (75,606); the NRT sub-benefit exists only report_year 2019+. Null (428,147) elsewhere. |
| otc_nrt_indicator_code | string | Raw CMS code for NRT indicator. |
| otc_nrt_indicator_label | string | Human label for NRT indicator (Yes/No). |
| otc_offered | boolean | Row/component-level boolean: is THIS specific OTC component offered (distinct from plan-level otc_base_offered). Fill varies by component (e.g. 170,571 on items_allowance, 75,606 on NRT). Pairs with otc_offered_code/label and offered_status. |
| otc_offered_code | string | Raw CMS code for otc_offered. |
| otc_offered_label | varchar(3) | Human label for otc_offered ('Yes' 188,207 / 'No' 155,354 / null 160,192). |
| otc_package_aggregate_amount | double |  |
| otc_package_aggregate_annualized | double |  |
| otc_package_aggregate_category_codes | string |  |
| otc_package_aggregate_period_code | string |  |
| otc_package_aggregate_period_label | string |  |
| otc_package_id | string |  |
| otc_package_match_basis | string |  |
| otc_package_name | string |  |
| otc_package_type_code | string |  |
| otc_package_type_label | string |  |
| otc_pooled_group_amount | double |  |
| otc_pooled_group_category_codes | string |  |
| otc_pooled_group_count | bigint |  |
| otc_pooled_group_index | int |  |
| otc_pooled_group_name | string |  |
| otc_pooled_group_period_code | string |  |
| otc_pooled_group_period_label | string |  |
| otc_pooled_with | string |  |
| otc_section_d_category_codes | string |  |
| otc_section_d_group_index | int |  |
| otc_section_d_group_name | string |  |
| otc_sub_benefit | string | THE component dimension and part of grain. 4 values: 'otc_list_coverage' & 'otc_items_allowance' (all years), 'otc_nicotine_replacement_therapy' (2019+), 'otc_naloxone' (2025-2026 only). Each value drives which indicator columns are populated; only otc_items_allowance carries a dollar amount. |
| otc_targeting | varchar(36) |  |
| source_amount_column | string | Name of the upstream silver/PBP column the benefit_amount was read from. Lineage; null on indicator rows. |
| source_period_column | string | Name of the upstream column the period was read from. Lineage. |
| source_pbp_section | string | Which PBP section the row came from: 'B13B' (487,753 = modern format) or 'B13C' (16,000 = older 2010-era OTC layout). A format/version marker, not a benefit difference. |
| source_silver_table | varchar(59) | Always 'medicare_silver.pbp_section_b13_other_services' — the single upstream silver table feeding this view. |
| lmi_authorities | string | Intended to hold LMI (low-income / authorities) tagging, but EMPTY ('') for every row in this view. Carries no information here. |
| source_url | string | CMS download URL of the PBP PUF zip the row came from (31 distinct = 31 release files, one per report_year/quarter snapshot). Provenance. |
| sha256_content_hash | string | Content hash of the source file (31 distinct, 1:1 with source_url). Provenance/integrity, not a row key. |

Tools that read it: get_pbp_benefit

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