CLD v0.1

This plan bridge version maps a PPO network to hospital data. It is able to map 93.9% of hospital data payer-provider-codes within CLD v0.1 scope.

Loc: hive.cld_v0_1.testing_plan_bridge

How it Works: Samples for Demonstration

See below 6 sample payer-provider-codes. Each have four flag columns: low_rate_variability, is_single_rate, is_single_plan, and has_ppo_plan.

Samples:

In sample 1-3: there is a single rate, a single plan, and all qualify as having PPO plans
In sample 4-5: there is a single rate and a single plan; we're not sure if "CIGNA" or "BCBS PAR (TRADITIONAL)" indicate a PPO plan, but this is the only rate for the payer-provider-code so we'll use this
In sample 6: there are multiple rates but all have the key word 'COMMERCIAL', which we're including in PPO. We should be careful with plan names that also contain "ALL OTHER PLANS".

sample	provider_id	provider_state	payer_id	rate_type	billing_code	billing_code_type	contract_methodology	billing_class	setting	ids	unique_payers	unique_plans	rate_array	low_rate_variability	is_single_rate	is_single_plan	has_ppo_plan	ids_with_ppo_plan	rates_with_ppo_plan	plans_with_ppo_plan	is_mapped	product_network_label	payer_payer_id	has_general_plan	has_hmo_plan
1	3067	WI	21	dollar	50431	HCPCS	percent of total billed charges	Facility	Outpatient	['32b0df99d37642c4a8ed4cdaca6ab1b6']	['ANTHEM']	['PPO']	[1496.0]	True	True	True	True	['32b0df99d37642c4a8ed4cdaca6ab1b6']	[1496.0]	[None]	True		42	False	False
2	766	CT	7	dollar	86780	HCPCS	fee schedule		Outpatient	['a6c15e99149a4128aa400365a09c6833', 'c82d623bb21b4482b09e64c3ef5fa145', 'aa983a2452dd477d96323632f96518fb', 'cd91eacb71c6492885450baa5bb08756', '23b7e5531d554e1081fe0b8ac99f406b', '28b8d10e2f3c46b0b6be1fa5c866d883']	['AETNA']	['ALL PLANS']	[23.89]	True	True	True	True	['a6c15e99149a4128aa400365a09c6833', 'c82d623bb21b4482b09e64c3ef5fa145', 'aa983a2452dd477d96323632f96518fb', 'cd91eacb71c6492885450baa5bb08756', '23b7e5531d554e1081fe0b8ac99f406b', '28b8d10e2f3c46b0b6be1fa5c866d883']	[23.89]	['ALL PLANS']	True	OPEN ACCESS MANAGED CHOICE	7	True	True
3	5325	TX	7	dollar	10121	HCPCS			ER	['f0b8c86d110345419969fbe747c379cd']	[None]	['Aetna PPO']	[1500.0]	True	True	True	True	['f0b8c86d110345419969fbe747c379cd']	[1500.0]	[None]	True	OPEN ACCESS MANAGED CHOICE	7	False	False
4	1723	NC	9	percentage	45005	HCPCS	percent of total billed charges		Outpatient	['1a84c72b511a402987736cd328a27f9f']	['CIGNA']	['CIGNA']	[72.5]	True	True	True	False	[None]	[]	[None]	True	NATIONAL OAP	9	False	False
5	6856	TX	266	percentage	81455	HCPCS	percent of total billed charges	Facility	Outpatient	['7427d58256b84abea1d01ac48ad56821']	['BCBS OF TEXAS']	['BCBS PAR (TRADITIONAL)']	[50.6]	True	True	True	False	[None]	[]	[None]	True	BLUE CHOICE PPO	169	False	False
6	6543	CA	95	dollar	36218	HCPCS	case rate		Outpatient	['ec841dfe413c4a4e834aaa660c773351', '677e9ce31fa8421ba8aeefef2080566d', 'd56c3f807d4d4f56a63fc81f2ef28042']	['UNITED']	['COMMERCIAL\| NAVIGATE', 'COMMERCIAL \| ALL OTHER PLANS', 'COMMERCIAL \| NON-OPTIONS PPO']	[4280.0, 4164.0]	True	False	False	True	['ec841dfe413c4a4e834aaa660c773351', '677e9ce31fa8421ba8aeefef2080566d', 'd56c3f807d4d4f56a63fc81f2ef28042']	[4280.0, 4164.0]	['COMMERCIAL \| NAVIGATE', 'COMMERCIAL \| ALL OTHER PLANS', 'COMMERCIAL \|NON-OPTIONS PPO']	True	CHOICE PLUS	643	True	True

click to download sample.xlsx with 1000 samples

Methodology

The SQL + Jinja templating below can be used to create the Plan Bridge for CLD v0.1.

Click to see SQL

CREATE TABLE hive.cld_v0_1.testing_plan_bridge_id_maps AS
WITH 
extracted_data AS (
    SELECT 
        product_network_label, 
        CASE
            WHEN has_ppo_plan THEN ids_with_ppo_plan
            ELSE ids
        END AS array_ids
    FROM hive.cld_v0_1.testing_plan_bridge
    WHERE (
        is_single_rate = True OR
        is_single_plan = True OR
        has_ppo_plan = True OR
        low_rate_variability = True
    )
    AND is_mapped = True
)
SELECT 
    distinct 
    product_network_label, 
    id
FROM extracted_data,
    UNNEST(array_ids) AS t(id)
WHERE 
    product_network_label IS NOT NULL
    AND id IS NOT NULL

The script initializes a few Jinja variables:

general_keywords: used to identify PPO and ALL COMMERCIAL plans
payer_keywords: additional payer-specific keywords that can be used for filtering
exclusions: keywords used to exclude plans
product_network_mapping: maps to payer MRF file label names (to be replaced in future with Network Spine "truthset")
payer_id_mapping: should not be necessary after hospital-payer ID assimilation

The script has three parts:

Prepare data by filtering to:
- billing_class != 'Professional'
- payer_class_name = 'Commercial'
- MS-DRG:
  - revenue_code IS NULL and billing_code_modifiers IS NULL (almost all payer-provider-MSDRG codes have a rate where this condition is met)
- HCPCS:
  - if available: revenue_code IS NULL and billing_code_modifiers IS NULL
  - for payer-provider-codes where this condition is not met, include all rates
Compute Metrics per Payer-Provider-Code combination
- for each provider-payer-contract_methodology-setting-code, create flags indicating:
  - "is_single_rate"
  - "is_single_plan"
  - "low_rate_variability" (rate spread is < 10%)
  - has_ppo_plan (if any plan_name contains one of the keywords from general_keywords + payer_keywords[payer_id])
- there are a few other columns generated in this step, but that's mostly for QA'ing
Build Bridge
- filter to payer-provider-code combinations where is_single_plan = True OR is_single_rate = True OR low_rate_variability = True OR has_ppo_plan = True
- assign product_network_label and payer_payer_id based on the hospital payer ID and provider state (when necessary)

Areas for Improvement:

In hive.cld_v0_1.testing_plan_bridge, under plans_with_ppo_plan, explode the array and get the most frequent items. SE can manually scan to assess to see if there are keywords that should be added to exceptions.
This version uses global exception keywords. The consumer version uses payer-specific exceptions. Payer-specific exceptions may be better in some cases
The general_keyword "ALL" can be a bit too broad at times. It may include plan names like ALL OTHER PRODUCTS.
After running data through the CLD pipeline, we'll also be able to identify if certain mappings (e.g. raw hospital data plan names) are frequently inaccurate and this information may be useful to improve the plan bridge

How it Works: Samples for Demonstration​

Methodology​

Areas for Improvement:​

On this page:

How it Works: Samples for Demonstration

Methodology

Areas for Improvement: