VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA ma state county penetration

medicare_silver.ma_state_county_penetration·live·silver·monthly·Medicare Advantage & Part D Enrollment
Rows
N/A
Columns
24
History
2008-06-01 to 2026-04-01
Card
yesgrain, traps, filters
Typed tools
1

What it is

  1. 1CMS Medicare Advantage (and Part C/cost-plan) market penetration by county and month: for each U.S. county and monthly snapshot it gives the number of Medicare beneficiaries eligible, the number enrolled in a private MA/Part-C plan, and the resulting penetration rate.
  2. 2It is the demand-side / market-share denominator for MA cost analysis — the share of each local Medicare market that has moved from fee-for-service into managed care.

Grain

KeyGrain
fipsreport_month
  1. 1One row per county (FIPS) per monthly report.
  2. 2The minimal verified unique key is (fips, report_month): 691,560 rows = 691,560 distinct (fips, report_month) groups, with a proven max duplicate count of 1. fips alone has 3,533 distinct values across 215 monthly snapshots; report_month spans 215 months (2008-06 ..
  3. 32026-04).

How to read it

  1. 1Trend MA penetration for a county over time: SELECT report_month, eligibles, enrolled, penetration FROM medicare_silver.ma_state_county_penetration WHERE fips='04021' AND suppression_reason_code='none' ORDER BY report_month.
  2. 2Answers 'how has Medicare Advantage market share in Pinal County, AZ evolved month over month?' For a clean national county cross-section, add WHERE fips NOT IN ('00000','0') AND suppression_reason_code='none' to drop the Pending rollup bucket and small-cell-suppressed cells.

Traps

  1. 1The grain is (fips, report_month) — NOT any of the period columns the profiler latched onto. report_month, observed_period, and effective_period_start are three identical copies of the same month (0 mismatches), and effective_period_end is just that month's last day; don't mistake them for separate dimensions.
  2. 2ssa / (ssa, report_month) looks like an alternate key but is NOT unique: Rota, Saipan and Tinian (N. Mariana Islands) all collapse to ssa '63050', giving ~471 duplicate (ssa, month) pairs. Join and dedup on fips, never on ssa. Likewise fipsst is inconsistently zero-padded ('01' vs '1', '00' vs '0'), so aggregate by substring(fips,1,2) or state_name, not raw fipsst.
  3. 3Suppression is real and asymmetric: 13,675 rows are 'small_cell_suppressed' → enrolled is NULL but enrolled_lower_bound=0 / enrolled_upper_bound=10 (CMS masks counts of 1-10). penetration is also NULL for these. Treating NULL enrolled as 0 will understate enrollment; use the [0,10] band. A further 270 unsuppressed ('none') rows also have NULL enrolled (early territory months + the Pending bucket where eligibles are known but enrollment wasn't reported).
  4. 4penetration is mostly 0-1 but 68 rows exceed 1.0 (max 2.136, e.g. Pinal County AZ in 2010 where reported enrolled ~18k > eligibles ~8.5k). This is an upstream CMS data quirk, not a computed error — clamp or flag if you need a guaranteed 0-1 share. Also 300 rows have enrolled > eligibles overall.
  5. 5There is no vintage/revision/is_current dedup to do: cms_vintage_sequence=1, parse_revision=1, and asserted_at is a single date (2026-05-15) for every row. It is one immutable backfilled snapshot of 215 monthly CMS files (one source_url + sha256 per month). A 'Pending State/County Designation' rollup row (fips '00000', sometimes unpadded '0', ssacnty '999') exists in many months as a not-yet-geocoded bucket — exclude it (and the territory rows) for clean county-level CONUS analysis.

Columns (24)

ColumnTypeMeaning
fipsststringState portion of FIPS (first 2 chars). 64 distinct values — note it is NOT consistently zero-padded across the history (you see both '01'/'1', '04'/'4', '00'/'0'), so do not GROUP BY fipsst expecting 50-ish clean states; derive from substring(fips,1,2) or use state_name. Includes territories 60/66/69/72/78.
fipscntystringCounty portion of FIPS (last 3 chars). '000' for the Pending/rollup bucket.
report_monthdateFirst day of the report month (date). Half the grain key. 215 distinct months, monthly cadence 2008-06-01 .. 2026-04-01. Identical in value to observed_period and effective_period_start (0 mismatches) — those are redundant copies.
state_namestringHuman-readable state/territory name. Has casing/spelling drift over time ('Pending State Designation' vs 'PENDING STATE DESIGNATION', 'Pending County Design' truncation) — treat as descriptive, not a key.
county_namestringHuman-readable county name (e.g., Autauga, Baldwin). Descriptive only; same casing/truncation drift caveat.
fipsstring5-char county FIPS code = fipsst||fipscnty. THE county identifier and half the grain key. 3,533 distinct. Caveat: not always zero-padded — a special rollup bucket appears as both '00000' and '0' in different months (see gotchas).
ssaststringSSA state code (CMS's legacy Social Security Administration geography numbering, distinct from FIPS).
ssacntystringSSA county code. '999' marks the Pending/unknown-county rollup bucket; '000' = the state's primary county slot in SSA numbering.
ssastring5-char SSA code = ssast||ssacnty. An alternate geography key but NOT unique per month: the three Northern Mariana Islands counties (Rota/Saipan/Tinian) all map to ssa '63050', so (ssa, report_month) has ~471 collisions and is finer than the true grain. Use fips, not ssa, as the join key.
eligiblesintCount of Medicare beneficiaries eligible for MA in that county-month (the denominator). Never null, never zero in this data. This is total Medicare eligibility (FFS + MA), so penetration = enrolled/eligibles.
enrolledintCount enrolled in an MA/Part-C/cost plan (the numerator). NULL when small_cell_suppressed (13,675 rows) OR when the territory/Pending bucket did not report enrollment (270 'none' rows). When present and unsuppressed it equals both bound columns.
enrolled_lower_boundintLower bound of enrolled. Equals enrolled exactly for unsuppressed rows. For small_cell_suppressed rows it is 0 (the CMS small-cell band is [0,10]).
enrolled_upper_boundintUpper bound of enrolled. Equals enrolled for unsuppressed rows; 10 for every suppressed row (CMS masks counts 1-10 as the range 0-10). Use the [lower,upper] band to reason about suppressed cells instead of treating enrolled NULL as zero.
penetrationdoubleMA penetration rate = enrolled/eligibles, as a fraction (0-1 typically; double). Range observed 0.0016 .. 2.136, avg ~0.284. NULL whenever enrolled is NULL (13,946 rows). 68 rows exceed 1.0 (a known CMS source quirk where reported enrolled > eligibles, e.g. Pinal County AZ 2010 ≈ 2.1×) — clamp/flag if you need a clean 0-1 rate.
suppression_reason_codestringWhy a cell is masked. Two values: 'none' (677,885 rows, normal) and 'small_cell_suppressed' (13,675 rows, count was 1-10 so enrolled is hidden and bounds are [0,10]). This is the column to filter on to drop suppressed cells.
observed_perioddateRedundant copy of report_month (0 mismatches). Part of Vlada's temporal-provenance envelope; carries no extra info here.
asserted_atdateWhen Vlada asserted/loaded this snapshot. Single constant value 2026-05-15 for the entire table — confirms this is one backfilled load, not an incrementally appended feed.
effective_period_startdateStart of the month the data is effective for — identical to report_month (0 mismatches). Vlada bitemporal-envelope field.
effective_period_enddateLast day of the report month (e.g., 2021-05-31 for May-2021). Just the month-end of report_month.
cms_vintage_sequenceintIntended to order CMS re-releases of the same month. Constant = 1 everywhere — there are no superseding vintages, so no 'latest vintage' filter is needed.
parse_revisionintParser re-run revision counter. Constant = 1 everywhere; no reprocessed rows exist.
parser_artifact_hashstringHash of the parser artifact used. 215 distinct values — effectively one per monthly file, not a row-level discriminator.
source_urlstringCMS download URL for that month's ZIP (e.g., https://www.cms.gov/files/zip/ma-statecounty-penetration-may-2021.zip). Exactly 215 distinct = one file per month (1:1 with report_month), confirming the monthly-snapshot lineage.
sha256_content_hashstringSHA-256 of the source file contents. 215 distinct = one per monthly file; provenance/integrity, not a grain column.

Query it

describe_data_source(source_fqn="medicare_silver.ma_state_county_penetration")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.ma_state_county_penetration WHERE fips = '<value>' AND report_month = '<value>' LIMIT 100")
ItemValue
Typed tools that read itget_market_share
Identity keyfipsst
Joinhow_to_join(source_fqn="medicare_silver.ma_state_county_penetration")
Raw doorAthena (read-only via query_substrate): medicare_silver.ma_state_county_penetration
NoteGrain-governed; live in Glue / query_substrate. Source family ma_state_county_penetration. get_market_share reads medicare_semantic.vw_ma_state_county_penetration_v2 since 2026-09-30 (MASCP-1); this table is unchanged. Invariant KeySetStable (fipsst) fails any month a state leaves the file unless the exit is known (CT 2024-08, AK 2025-07).

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