Version: 2.1

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

{% set general_keywords = [
    'ALLCOMMERCIAL',
    'COMMERCIAL',
    'ALLPLAN',
    'ALLPRODUCT',
    'ALLPAY',
    'ALLCHOICEPLANS',
] %}

{% set ppo_keywords = [
    'PPO',
] %}

{% set hmo_keywords = [
    'HMO',
] %}

{% set payer_keywords = {
    7: [
        'COMM',
        'POS'
    ],
    9: [
        'OAP',
        'OPENACCESSPLUS'
    ],
    21: [
        'PREFERRED',
        'CHOICE'
    ],
    26: [],
    42: [],
    95: [
        'CHOICE',  
    ],
    266: [],
    270: [],
    248: [
        'PREFERRED',
        'CHOICE'
    ],
    246: [
        'PPC',
        'OPTIONS'
    ],
    253: [],
    70: [],
    272: [],
} %}

{% set exclusions = [
    'HMO',
    'OOS',
    'OUTOF',
    'ADVANTAGE',
    'DUAL',
    'INDEMNITY',
    'OON',
    'MEDICARE',
    'MEDICAID',
    'BH',
    'BEHAV',
    'INTERNATIONAL',
] %}

{% set payer_ids = payer_keywords.keys() %}


{% set product_network_mapping = {
    7: 'OPEN ACCESS MANAGED CHOICE',
    9: 'NATIONAL OAP',
    95: 'CHOICE PLUS',
    270: 'GROUP PPO',
    42: 'NY PPO',
    21: {'NY': 'NY PPO', 'CA': 'CA BLUE CROSS PPO'},
    266: 'BLUE CHOICE PPO',
    70: 'GROUP - OMNIA',
    246: 'BLUECHOICE PPO',
    248: 'PPO PARTICIPATING PROVIDER OPTIONS',
    253: 'PPO',
    272: 'PREFERRED PROVIDER NETWORK (PPO)',
    26: {
        'TX': 'BLUE CHOICE PPO',
        'NJ': 'GROUP - OMNIA',
        'FL': 'BLUECHOICE PPO',
        'IL': 'PPO PARTICIPATING PROVIDER OPTIONS',
        'MI': 'PPO',
        'MD': 'PREFERRED PROVIDER NETWORK (PPO)'
    }
} %}

{% set payer_id_mapping = {
    7: '7', 9: '9', 95: '643',
    42: '42', 21: '42', 270: '403',
    266: '169', 70: '229', 246: '388',
    248: '389', 253: '49', 272: '44',
    26: {
        'TX': '42', 'NJ': '229', 'FL': '388',
        'IL': '389', 'MI': '49', 'MD': '44'
    }
} %}


CREATE TABLE hive.cld_v0_1.testing_plan_bridge AS
WITH

-------------------------------------------------------
-- 1. Prepare Hospital Data
--  
-- Almost all MSDRG payer-provider-code combinations have a rate available 
-- with both NULL revenue code and NULL modifier.

-- But about almost half of HCPCS payer-provider-code combinations do not have a 
-- rate available with both NULL revenue code and NULL modifier. 
-------------------------------------------------------
hcpcs_exceptions AS (
    SELECT *
    FROM (
        SELECT
            payer_id, 
            provider_id, 
            billing_code,
            SUM(
                CASE WHEN revenue_code IS NOT NULL THEN 1 ELSE 0 END
            ) as has_revenue_code,
            count(*) as n
        FROM glue.hospital_data.hospital_rates
        WHERE
            COALESCE(billing_class, '') != 'Professional'
            AND payer_class_name = 'Commercial'
            AND billing_code_type = 'HCPCS'
            AND (
                negotiated_dollar > 0
                OR negotiated_percentage > 0
            )
            AND billing_code IN (
                SELECT distinct billing_code
                FROM hive.cld_v0_1.prod_rate_object_space
            )
        GROUP BY 1,2,3
    )
    WHERE has_revenue_code = n
),
base AS (
    SELECT *
    FROM glue.hospital_data.hospital_rates
    WHERE 
        revenue_code IS NULL
        AND billing_code_modifiers IS NULL
        AND COALESCE(billing_class, '') != 'Professional'
        AND payer_class_name = 'Commercial'
    UNION ALL
    SELECT hr.*
    FROM glue.hospital_data.hospital_rates hr
    JOIN hcpcs_exceptions he
        ON hr.payer_id = he.payer_id
        AND hr.provider_id = he.provider_id
        AND hr.billing_code = he.billing_code
    WHERE
        COALESCE(billing_class, '') != 'Professional'
        AND payer_class_name = 'Commercial'
        AND billing_code_type = 'HCPCS'
        AND (
            negotiated_dollar > 0
            OR negotiated_percentage > 0
        )
),
rates AS (
    SELECT *, 'dollar' as rate_type, negotiated_dollar as rate
    FROM base
    WHERE negotiated_dollar > 0
    UNION ALL
    SELECT *, 'percentage' as rate_type, negotiated_percentage as rate
    FROM base
    WHERE negotiated_percentage > 0
),

-------------------------------------------------------
-- 2. Compute Metrics For Each Payer-Provider-Code Combination
-------------------------------------------------------
df AS (
    SELECT 
        provider_id,
        provider_state,
        payer_id,
        contract_methodology,
        billing_class,
        setting,
        billing_code_type,
        billing_code,
        rate_type,
        count(distinct rate) as unique_rates,
        array_distinct(array_agg(rate)) as rate_array,
        (max(rate) - min(rate)) / avg(rate) as rate_spread,
        array_distinct(array_agg(raw_payer_name)) as unique_payers,
        array_distinct(array_agg(plan_name)) as unique_plans,
        array_agg(id) as ids,
        MAX(
            COALESCE(
                CASE 
                    {% for payer_id in payer_ids %}
                    WHEN payer_id = {{ payer_id }} THEN 
                        regexp_like(
                            REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ',''), 
                            '{{ "|".join(general_keywords + ppo_keywords + payer_keywords[payer_id]) }}'
                        )
                    {% endfor %}
                    ELSE False 
                END,
                False
            )
        ) as has_ppo_plan,
        array_distinct(array_agg(
            CASE 
                {% for payer_id in payer_ids %}
                WHEN 
                    payer_id = {{ payer_id }} 
                    AND 
                    regexp_like(
                        REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ',''), 
                        '{{ "|".join(general_keywords + ppo_keywords + payer_keywords[payer_id]) }}'
                    )
                THEN id
                {% endfor %}
                ELSE NULL
            END
        )) as ids_with_ppo_plan,
        array_distinct(array_agg(
            CASE 
                {% for payer_id in payer_ids %}
                WHEN 
                    payer_id = {{ payer_id }} 
                    AND 
                    regexp_like(
                        REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ',''), 
                        '{{ "|".join(general_keywords + ppo_keywords + payer_keywords[payer_id]) }}'
                    )
                THEN rate
                {% endfor %}
                ELSE NULL
            END
        )) as rates_with_ppo_plan,
        array_distinct(array_agg(
            CASE 
                {% for payer_id in payer_ids %}
                WHEN 
                    payer_id = {{ payer_id }} 
                    AND 
                    regexp_like(
                        REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ',''), 
                        '{{ "|".join(general_keywords + ppo_keywords + payer_keywords[payer_id]) }}'
                    )
                THEN plan_name
                {% endfor %}
                ELSE NULL
            END
        )) as plans_with_ppo_plan,

        MAX(
            COALESCE(
                CASE 
                    {% for payer_id in payer_ids %}
                    WHEN payer_id = {{ payer_id }} THEN 
                        regexp_like(
                            REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ',''), 
                            '{{ "|".join(general_keywords) }}'
                        )
                    {% endfor %}
                    ELSE False 
                END,
                False
            )
        ) as has_general_plan,

        MAX(
            COALESCE(
                CASE 
                    {% for payer_id in payer_ids %}
                    WHEN payer_id = {{ payer_id }} THEN 
                        regexp_like(
                            REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ',''), 
                            '{{ "|".join(general_keywords + hmo_keywords + payer_keywords[payer_id]) }}'
                        )
                    {% endfor %}
                    ELSE False 
                END,
                False
            )
        ) as has_hmo_plan
    FROM rates
    WHERE payer_id IN (
        {{ payer_ids | join(',') }}
    )
    AND payer_class_name = 'Commercial' 
    AND billing_code IN (
        SELECT distinct billing_code
        FROM hive.cld_v0_1.prod_rate_object_space
    )
    AND billing_code_type IN ('HCPCS', 'MS-DRG')
    AND rate > 0
    AND NOT regexp_like(
        REPLACE(REPLACE(UPPER(plan_name),'_',' '),' ',''), 
        '{{ exclusions | join("|") }}'
    )
    GROUP BY 1,2,3,4,5,6,7,8,9
),
df_with_flags AS (
    SELECT 
        *,
        rate_spread < 0.1 as low_rate_variability,
        unique_rates = 1 as is_single_rate,
        CARDINALITY(unique_plans) = 1 as is_single_plan
    FROM df
)

-------------------------------------------------------
-- 3. Build Plan Bridge
-------------------------------------------------------
SELECT 
    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,
    has_general_plan,
    has_hmo_plan,
    ids_with_ppo_plan,
    rates_with_ppo_plan,
    plans_with_ppo_plan,
    CASE
        WHEN 
            (
                is_single_plan = True
                OR is_single_rate = True
                OR low_rate_variability = True
                OR has_ppo_plan = True
            )
        THEN True
        ELSE False
    END as is_mapped,
    CASE
        {% for payer_id, value in product_network_mapping.items() %}
            {% if value is mapping %}
                {% for state, label in value.items() %}
                    WHEN payer_id = {{ payer_id }} AND provider_state = '{{ state }}' THEN '{{ label }}'
                {% endfor %}
            {% else %}
                WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
            {% endif %}
        {% endfor %}
    END as product_network_label,

    CASE
        {% for payer_id, value in payer_id_mapping.items() %}
            {% if value is mapping %}
                {% for state, mapped_id in value.items() %}
                    WHEN payer_id = {{ payer_id }} AND provider_state = '{{ state }}' THEN '{{ mapped_id }}'
                {% endfor %}
            {% else %}
                WHEN payer_id = {{ payer_id }} THEN '{{ value }}'
            {% endif %}
        {% endfor %}
    END as payer_payer_id

FROM df_with_flags

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: