# formulary_churn

Formulary Churn · v1.0.0 · drug

Given a quarter-pair (report_quarter_from, report_quarter_to) AND a formulary or plan filter, returns the formulary delta: drugs added, drugs dropped, drugs re-tiered, and optionally drugs whose control flags (prior auth / step therapy / quantity limit) changed. Diffs drug_silver.fact_drug_formulary across two SPUF report_quarters on (formulary_id, drug) keys, where a drug is its RxCUI by default (grain='rxcui'), so a manufacturer or package NDC swap of the same drug is not churn; grain='ndc' gives the package-level diff. Pass rxcui_filter=[...] to ask whether coverage of specific drugs changed.

Returns: per-category row lists (cap 5000 each), aggregates (formularies_compared, n_added, n_dropped, n_tier_changed, n_control_changed, n_unchanged, churn_rate, tier_movement_distribution), truncated_by_category flags.

READING churn_rate: it is (added + dropped + re-tiered) divided by the number of drugs on the formulary in the FROM quarter — CHANGE EVENTS PER FROM-QUARTER DRUG, not a percentage. It is >= 0 and CAN EXCEED 1 (a formulary that drops everything and adds as many new drugs scores 2.0), because added drugs are not members of the denominator. The answer carries churn_rate_definition saying exactly this, the two denominators (n_in_from_quarter, n_in_to_quarter), and two bounded companions that ARE 0-1: share_dropped (dropped / FROM-quarter drugs) and share_added (added / TO-quarter drugs).

Use cases: year-over-year formulary stability tracking, drop investigations for a specific drug class, IRA-era churn rate (crossing 2025 → 2026 will surface the redesign-driven churn).

KNOWN GAPS: spuf_quarterly_publication_lag (CMS publishes 6 weeks after quarter end; very recent quarter changes won't show); ira_era_break_2026 (IRA Part D redesign in 2026 changed formulary structures; churn comparison crossing this boundary mixes regime changes with normal churn); formulary_churn_truncated (per-category lists capped at 5000).

**Inputs.**
- `report_quarter_from` (required): 
- `report_quarter_to` (required): 
- `formulary_id_filter`: 
- `contract_id_filter`: 
- `plan_id_filter`: 
- `include_control_changes`: 
- `grain`: 
- `rxcui_filter`: 
- `acknowledge_era_break`: 

**Known limits.**
- spuf_quarterly_publication_lag
- ira_era_break_2026
- formulary_churn_truncated
- formulary_churn_no_match

**A real answer** (customer seat, 15.26 s, args `{"report_quarter_from":"2024-Q3","report_quarter_to":"2024-Q4","contract_id_filter":"H1036"}`):

```json
{
 "value": {
  "headline": 2.0,
  "report_quarter_from": "2024-Q3",
  "report_quarter_to": "2024-Q4",
  "formulary_ids_compared": [
   "00019434",
   "00019435",
   "00019436",
   "00019440",
   "00019442",
   "00019444",
   "00019445",
   "00019449",
   "00019450",
   "00020457",
   "00020458",
   "00020459",
   "00020460",
   "00020461",
   "00020462",
   "00020463",
   "00020467",
   "00020468",
   "00021450",
   "00021454",
   "00021456",
   "00021457",
   "00021460",
   "00021461",
   "00021462",
   "00022520",
   "00022524",
   "00022525",
   "00022526",
   "00022530",
   "00022531",
   "00022532",
   "00023492",
   "00023493",
   "00023496",
   "00023497",
   "00023503",
   "00023505",
   "00023506",
   "00023511",
   "00024492",
   "00024493",
   "00024495",
   "00024496",
   "00024499",
   "00024500",
   "00024502",
   "00024504",
   "00024507",
   "00025456",
   "00025458",
   "00025460",
   "00025461",
   "00025462",
   "00025463",
   "00025464",
   "00026408",
   "00026409",
   "00026411",
   "00026412"
  ],
  "truncated_by_category": {
   "added": true,
   "dropped": true,
   "tier_changed": false,
   "control_changed": false
  },
  "include_control_changes": false,
  "churn_rate_definition": "churn_rate = (drugs added + drugs dropped + drugs re-tiered) / drugs on the formulary in the FROM quarter. It counts CHANGE EVENTS per drug that was on the formulary at the start, so it is >= 0 and CAN EXCEED 1: a formulary that drops everything and adds an equal number of new drugs scores 2.0. It is not a percentage of the formulary and must not be read as one. For bounded readings use share_dropped (dropped / FROM-quarter drugs, 0-1) and share_added (added / TO-quarter drugs, 0-1).",
  "churn_rate_range": {
   "minimum": 0.0,
   "maximum": null,
   "unit": "change events per FROM-quarter drug"
  },
  "aggregates": {
   "grain": "rxcui",
   "formularies_compared": 60,
   "n_added": 10000,
   "n_dropped": 10000,
   "n_tier_changed": 0,
   "n_control_changed": 0,
   "n_unchanged": 0,
   "churn_rate": 2.0,
   "churn_rate_definition": "churn_rate = (drugs added + drugs dropped + drugs re-tiered) / drugs on the formulary in the FROM quarter. It counts CHANGE EVENTS per drug that was on the formulary at the start, so it is >= 0 and CAN EXCEED 1: a formulary that drops everything and adds an equal number of new drugs scores 2.0. It is not a percentage of the formulary and must not be read as one. For bounded readings use share_dropped (dropped / FROM-quarter drugs, 0-1) and share_added (added / TO-quarter drugs, 0-1).",
   "n_in_from_quarter": 10000,
   "n_in_to_quarter": 10000,
   "share_dropped": 1.0,
   "share_added": 1.0,
   "tier_movement_distribution": {
    "up_1": 0,
    "up_2_or_more": 0,
    "down_1": 0,
    "down_2_or_more": 0
   }
  },
  "added_drugs": [
   {
    "formulary_id": "00025456",
    "ndc11": "00093500456",
    "rxcui": "1000001",
    "tier_level_value": 4
   },
   {
    "formulary_id": "00025456",
    "ndc11": "59651017301",
    "rxcui": "1000048",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "54838051240",
    "rxcui": "1000054",
    "tier_level_value": 4
   },
   {
    "formulary_id": "00025456",
    "ndc11": "59651017701",
    "rxcui": "1000058",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "49884022201",
    "rxcui": "1000064",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "59651017401",
    "rxcui": "1000070",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "59651017501",
    "rxcui": "1000076",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00378537501",
    "rxcui": "1000097",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00555077902",
    "rxcui": "1000114",
    "tier_level_value": 2
   },
   {
    "formulary_id": "00025456",
    "ndc11": "55150032901",
    "rxcui": "1000126",
    "tier_level_value": 2
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00555087202",
    "rxcui": "1000135",
    "tier_level_value": 2
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00555087302",
    "rxcui": "1000141",
    "tier_level_value": 2
   },
   {
    "formulary_id": "00025456",
    "ndc11": "66993037179",
    "rxcui": "1000153",
    "tier_level_value": 2
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00009470913",
    "rxcui": "1000158",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "68462030450",
    "rxcui": "1000405",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "61314020315",
    "rxcui": "1000647",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00574419035",
    "rxcui": "1000673",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "61314020415",
    "rxcui": "1000862",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "61314020615",
    "rxcui": "1000897",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00527131301",
    "rxcui": "1000913",
    "tier_level_value": 4
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00527140701",
    "rxcui": "1001004",
    "tier_level_value": 4
   },
   {
    "formulary_id": "00025456",
    "ndc11": "51672417005",
    "rxcui": "1001689",
    "tier_level_value": 3
   },
   {
    "formulary_id": "00025456",
    "ndc11": "00078059287",
    "rxcui": "1002300",
    "tier_level_value": 5
   },
   {
    "formulary_id": "00025456",
    "ndc11": "70700011685",
    "rxcui": "1006065",
    "tier_level_value": 4
   },
   {
    "formulary_id": "00025456",
    "ndc11": "24208067004",
    "rxcui": "1006120",
    "tier_level_value": 2
   },
   {
    "formulary_id": "00025456",
    "ndc11": "45802089626",
    "rxcui": "1006688",
    "tier_level_value": 4
   },
   {
    "formulary_id": "00025456",
   
... (truncated)
```

REST: `POST https://api.vladahealth.com/v1/tools/formulary_churn` with the inputs as the JSON body. MCP: the tool by name.