Tuning / QA Plan Pre-Build

Background

The plan bridge "maps" hospital rates to a network if:

1. there is just a single rate or low rate variability for that payer-provider-code
2. there is just a single plan for that payer-provider-code
3. there is a "PPO" plan (including "catch-all" plan_names such as "ALL COMMERCIAL")

Errors can occur in the following ways:

• False Positives (we incorrectly mapped a rate to a network)
  • there is a "single rate" but associated plan names are not a PPO network
  • there is a "single plan" but it is not a "PPO" plan
  • we incorrectly identified a "PPO" plan (e.g. if we flagged a plan name as PPO because it had "OAP" but plan name contained "SOAP")
• False Negatives (we did not map a rate to a network, but we should have)
  • a payer-provider-code has multiple different rates and multiple different plans, and no PPO or "ALL COMMERCIAL" plan was detected -- BUT it turns out that the plan name DOES contain a key word that indicates PPO

We currently have 94% of payer-provider-codes mapped to a PPO. We can increase this by reducing false negatives. And we can increase accuracy of the 94% that are already mapped by reducing false positives.

Possible Goals

• Tuning Goals:
  1. Lowest hanging fruit:
     • are there additional commonly occuring key words that we can add to reduce false negatives?
     • are there additional key words that commonly occur in "matches" that are "false positives"?
• QA Goal:
  1. Based on a random sample of 500 mappings, fewer than 5 are incorrect.

Queries to Help QA:

Single Rates (Potential False Positives)

most commonly occuring "plan names" with single rates

WITH 
single_rate_qa AS (
    SELECT *
    FROM hive.cld_v0_1.testing_plan_bridge
    WHERE (
        is_single_rate = True
        OR
        low_rate_variability = True
    ) AND (
        has_ppo_plan = False
    )
)
SELECT 
    exploded.unique_plans,
    count(*) as n
FROM single_rate_qa, UNNEST(unique_plans) as exploded(unique_plans)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10000

Multiple Rates, Multiple Plans | No "PPO" or "ALL PRODUCT" (Potential False Negatives)

most commonly occuring "plan names" with multiple rates/plans

WITH 
single_rate_qa AS (
    SELECT *
    FROM hive.cld_v0_1.testing_plan_bridge
    WHERE (
        is_single_rate = False
        AND
        low_rate_variability = False
        AND
        is_single_plan = False
        AND
        has_ppo_plan = False
    ) 
)
SELECT 
    exploded.unique_plans,
    count(*) as n
FROM single_rate_qa, UNNEST(unique_plans) as exploded(unique_plans)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10000

Summary of Matches and Non-Matches by Payer

This is helpful to see which plan-bridge mapping stats at the payer level (e.g. are there any particular payers where we're getting very few matches?)

WITH 
-- randomly sample 1000 payer-provider-codes for each payer
sample AS (
    SELECT *
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY payer_id ORDER BY RAND()) AS rn
        FROM hive.cld_v0_1.testing_plan_bridge
    )
    WHERE rn <= 1000
),
-- get counts of "single rate" and "single plan or ppo"
crosstab AS (
    SELECT 
        payer_id,
        (is_single_rate OR low_rate_variability) AS single_rate,
        (is_single_plan OR has_ppo_plan) AS single_plan_or_ppo,
        COUNT(*) AS n,
        COUNT(*) * 1.00 / SUM(COUNT(*)) OVER (PARTITION BY payer_id) AS percent
    FROM sample
    GROUP BY payer_id, (is_single_rate OR low_rate_variability), (is_single_plan OR has_ppo_plan)
)
SELECT 
    payer_id,
    single_rate,
    single_plan_or_ppo,
    n as count,
    percent
FROM crosstab c
ORDER BY payer_id, single_rate, single_plan_or_ppo

Canonical Selections Lower Than Medicare

The query below filters to payer '643' and 'MS-DRG'. Adjust filters as needed.

WITH
ba AS (
    SELECT *
    FROM 
        tq_dev.internal_dev_csong_cld_v1_0.tmp_ref_plan_bridge_analysis_2025_02,
        unnest(ids) AS t(id)
),
df AS (
    SELECT 
        canonical_rate,
        canonical_method_params,
        canonical_rate_id,
        canonical_rate_type,
        canonical_method_params,
        medicare_rate,
        ba.unique_plans,
        ba.unique_payers
    FROM tq_dev.internal_dev_csong_cld_v1_0.prod_combined_all a
    JOIN tq_dev.internal_dev_csong_cld_v1_0.tmp_ref_plan_bridge_2025_02 b
        ON a.canonical_rate_id = b.id
        AND a.payer_id = b.payer_id
    JOIN ba
        ON ba.id = a.canonical_rate_id
        AND ba.payer_id = a.payer_id
    WHERE a.payer_id = '643'
    AND a.billing_code_type = 'MS-DRG'
)
SELECT 
    t.unique_plan,
    count(*) as n
FROM df, unnest(unique_plans) AS t(unique_plan)
WHERE canonical_rate < medicare_rate
GROUP BY 1
ORDER BY 2 DESC