Plan Bridge

Map raw hospital plan names to standardized network IDs using keyword matching, exclusion filters, and confidence scoring.

The Problem

Hospital MRFs publish rates with plan names like 'Blue Cross PPO' or 'Aetna HMO Network 1'. These strings must be mapped to a Clear Rates network_id before hospital rates can join the Rate Object Space. The Plan Bridge performs this translation.

Pipeline Flow

1

Prepare Base

Filter hospital_rates by billing code type. Deduplicate to prevent array explosion on repeated plan names.

→ —

2

Keyword Matching + Confidence Scoring

Standardize plan names (uppercase, remove spaces). Match against PPO/HMO/Exchange keywords per payer. Apply exclusion filters. Compute confidence signals (is_single_plan, is_single_rate, low_rate_variability, has_network_plan).

→ —

3

Build Final Mapping

Filter to is_mapped=True, extract IDs, UNNEST to one row per hospital_rates.id.

→ tmp_ref_plan_bridge

4

Hospital MRF Build

JOIN hospital_rates ON plan_bridge.id → JOIN network_spine → network_id resolved.

→ tmp_raw_mrf_hospital_rates

Keywords and Exclusions

Plan names are normalized to uppercase with spaces removed before matching.

PPO Keywords

• General: ALLCOMMERCIAL, COMMERCIAL, BROAD, ALLPLAN
• Network type: PPO, PREFERRED
• Payer-specific examples: UHC → CHOICE or CORE; Cigna → OAP or OPENACCESSPLUS

HMO Keywords

• ALLCOMMERCIAL, COMMERCIAL, HMO, HEALTHMAINTENANCE

Exclusion Filters — plan names matching any of these are excluded from mapping:

OUTOF, ADVANTAGE, DUAL, INDEMNITY, OON, MEDICARE, MEDICAID, BEHAV, INTERNATIONAL, FEDERAL, MULTIPLAN, PHCS, GOVERNMENT

Confidence Signals

Signal	Meaning
is_single_plan	Only one distinct plan_name in the group
is_single_rate	Only one distinct rate value
low_rate_variability	Rate spread < 10%
has_network_plan	At least one plan_name matched a keyword regex

Mapping Decision

A group is mapped (is_mapped = true) if any of the four signals above is true. All matched rows are UNNESTed to one row per hospital_rates.id in the final bridge table.

Silent failure mode

If keywords don't match a normalized plan_name, no error is raised — hospital rates for that plan simply aren't assigned to any network. Always verify coverage after adding a network by querying hospital_rates.plan_name for the relevant payer_id, then check tmp_ref_plan_bridge for matched rows.

Editing Plan Bridge

All keyword and exclusion logic lives in two files:

• variables_ppo.sql — PPO matching
• variables_hmo.sql — HMO matching

Normalization rule: plan names are uppercased and all spaces/special characters stripped before matching. Write every keyword and exclusion in that same normalized form.

Adding a keyword

Payer-specific keyword (most common) — add the normalized string to ppo_payer_keywords or hmo_payer_keywords under the target payer's payer_id:

variables_ppo.sql — add a keyword for Aetna (payer_id = 7)

{% set ppo_payer_keywords = {
  7: [
      'COMM',
      'AETNA',
      'AETNAHMO',     -- ← new keyword
  ],
  ...
} %}

variables_hmo.sql — add a keyword for Aetna (payer_id = 7)

{% set hmo_payer_keywords = {
  7: [
      'COMM',
      'HMO',
      'AETNA',
      'AETNAHMO',     -- ← new keyword
  ],
  ...
} %}

General keyword — add to ppo_general_keywords (matches across all payers) only if the term reliably signals a commercial PPO plan for every payer in the network spine:

variables_ppo.sql — add a general keyword

{% set ppo_general_keywords = [
  'ALLCOMMERCIAL',
  'COMMERCIAL',
  'BROAD',
  'ALLPLAN',
  'ALLPRODUCT',
  'ALLPAY',
  'ALLCHOICEPLANS',
  'ALLOTHER',
  'OPENENROLLMENT',   -- ← new general keyword
] %}

Verify before committing

Before adding a keyword, confirm what it would match:

SELECT DISTINCT
    REGEXP_REPLACE(UPPER(plan_name), '[^A-Z0-9]', '') AS normalized,
    plan_name,
    COUNT(*) AS cnt
FROM tq_production.hospital_rates
WHERE payer_id = 7
  AND REGEXP_REPLACE(UPPER(plan_name), '[^A-Z0-9]', '') LIKE '%AETNAHMO%'
GROUP BY 1, 2
ORDER BY cnt DESC
LIMIT 50

Adding an exclusion

Global exclusion — add to ppo_exclusions (or hmo_exclusions) to block any plan name containing this substring across all payers:

variables_ppo.sql — add a global exclusion

{% set ppo_exclusions = [
  'OUTOF',
  'ADVANTAGE',
  'DUAL',
  'INDEMNITY',
  'OON',
  'MEDICARE',
  'MEDICAID',
  ...
  'TRICARE',      -- ← new exclusion
] %}

Payer-specific exclusion — add to ppo_payer_exclusions when a term should only be excluded for one payer. The key is (payer_id, state) where state is 'NATIONAL' for payers without state restrictions:

variables_ppo.sql — add a payer-specific exclusion for Cigna (payer_id = 76)

{% set ppo_payer_exclusions = {
  (76, 'NATIONAL'): [
      'SAR',
      'SHAREDADMIN',
      'STAR',
      'SPRING',
      'FEHB',
      'MGMCD',
      'PSYCHIATRIC',
      'MDCPSEMPLOYEE',
      'DENTALVISION',     -- ← new payer-specific exclusion
  ],
  ...
} %}

Exclusion exception — if a plan name contains an excluded term but should still be matched, add the full normalized string to ppo_exclusion_exceptions:

variables_ppo.sql — add an exclusion exception

{% set ppo_exclusion_exceptions = [
  'HMO/PPO/POS/INDEMNITY',    -- already present; INDEMNITY excluded globally but this exact string is allowed
  'BROADINDEMNITY',            -- ← new exception
] %}