Skip to main content

MGB Rate Change Thresholds

Question:

MGB would like to perform a ROID-level rate-change analysis between Clear Rates versions. The goal is to get more transparency into why rates are changing. As a part of this analysis, they would like to define ROID-level rate change thresholds to identify significant changes.

Definitions:

What does "significant change" mean? Basically, whatever falls outside of these defined thresholds will proceed to the variance attribution phase.

So "significant change" means that the absolute rate change is too big to be explained by normal contract updates and needs further investigation.

Inclusion Criteria:

There are a few different ways that we can define the data that goes into identifying these thresholds:

  1. Canonical Rate Score
    • use all rates excluding outliers
    • only include validated rates
  2. Rate Change
    • use all rate changes
    • use only non-zero rate changes

Using (a) all rates excluding outliers or (b) only validated rates should give similar results. If they are different, (b) would give us a better idea of what expected/normal rate changes look like. Although we don't do this anymore in Clear Rates, it's similar to the idea of using the distribution of validated rates to inform our outlier bound selection.

By only looking at non-zero rate changes, we are asking ourselves WHEN rates DO change, what's the expected distribution. This gets rid of all the ZERO rate changes that can make the distribution look tighter than it really is.

Summary of Analysis​

ApproachRate InclusionChange Inclusion5th %ile25th %ile50th %ile75th %ile95th %ile
1All rates (excl. outliers)All changes-0.6%0.0%0.0%0.0%+19.7%
2Validated onlyAll changes0.0%0.0%0.0%0.0%+2.8%
3All rates (excl. outliers)Non-zero only-54.1%-2.4%+4.6%+35.6%+662.4%
4Validated onlyNon-zero only-7.4%-0.2%+2.7%+5.8%+38.3%

Analysis​

Approach 1: All Rates Excluding Outliers, All Rate Changes​

Looking at all rates excluding outliers, and considering all rate changes (including zero changes), we see that the 5th percentile of rate changes is -0.6%, and the 95th percentile of rate changes is +19.7%.

pct_change
pct_01-0.455
pct_02-0.183
pct_03-0.067
pct_04-0.024
pct_05-0.006
pct_250.000
pct_500.000
pct_750.000
pct_900.013
pct_910.027
pct_920.045
pct_930.069
pct_940.115
pct_950.197
pct_995.154
SQL Query
WITH
new AS (
    SELECT 
        roid,
        canonical_rate as new_rate
    FROM tq_dev.internal_dev_csong_cld_v2_3_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_0.tmp_whisper_provider_2025_10
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
old AS (
    SELECT 
        roid,
        canonical_rate as old_rate
    FROM tq_dev.internal_dev_csong_cld_v2_2_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_2_2.tmp_whisper_provider_2025_09
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
stats AS (
    SELECT 
        roid,
        new_rate,
        old_rate,
        1.0000 * (new_rate - old_rate) / old_rate AS pct_diff
    FROM new
    JOIN old
        USING (roid)
    WHERE old_rate > 0
    AND new_rate > 0
)
SELECT
    APPROX_PERCENTILE(pct_diff, 0.01) AS pct_01,
    APPROX_PERCENTILE(pct_diff, 0.02) AS pct_02,
    APPROX_PERCENTILE(pct_diff, 0.03) AS pct_03,
    APPROX_PERCENTILE(pct_diff, 0.04) AS pct_04,
    APPROX_PERCENTILE(pct_diff, 0.05) AS pct_05,
    APPROX_PERCENTILE(pct_diff, 0.25) AS pct_25,
    APPROX_PERCENTILE(pct_diff, 0.50) AS pct_50,
    APPROX_PERCENTILE(pct_diff, 0.75) AS pct_75,
    APPROX_PERCENTILE(pct_diff, 0.90) AS pct_90,
    APPROX_PERCENTILE(pct_diff, 0.91) AS pct_91,
    APPROX_PERCENTILE(pct_diff, 0.92) AS pct_92,
    APPROX_PERCENTILE(pct_diff, 0.93) AS pct_93,
    APPROX_PERCENTILE(pct_diff, 0.94) AS pct_94,
    APPROX_PERCENTILE(pct_diff, 0.95) AS pct_95,
    APPROX_PERCENTILE(pct_diff, 0.99) AS pct_99
FROM stats

Approach 2: Only Validated Rates, All Rate Changes​

Looking only at validated rates (canonical_rate_score = 5) and considering all rate changes, we see that the 5th percentile of rate changes is 0% and the 95th percentile of rate changes is +2.8%.

pct_change
pct_01-0.036
pct_02-0.010
pct_03-0.001
pct_04-0.000
pct_05-0.000
pct_250.000
pct_500.000
pct_750.000
pct_900.000
pct_910.000
pct_920.002
pct_930.007
pct_940.016
pct_950.028
pct_990.167
SQL Query
WITH
new AS (
    SELECT 
        roid,
        canonical_rate as new_rate
    FROM tq_dev.internal_dev_csong_cld_v2_3_0.prod_combined_abridged
    WHERE canonical_rate_score = 5
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_0.tmp_whisper_provider_2025_10
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
old AS (
    SELECT 
        roid,
        canonical_rate as old_rate
    FROM tq_dev.internal_dev_csong_cld_v2_2_2.prod_combined_abridged
    WHERE canonical_rate_score = 5
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_2_2.tmp_whisper_provider_2025_09
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
stats AS (
    SELECT 
        roid,
        new_rate,
        old_rate,
        1.0000 * (new_rate - old_rate) / old_rate AS pct_diff
    FROM new
    JOIN old
        USING (roid)
    WHERE old_rate > 0
    AND new_rate > 0
)
SELECT
    APPROX_PERCENTILE(pct_diff, 0.01) AS pct_01,
    APPROX_PERCENTILE(pct_diff, 0.02) AS pct_02,
    APPROX_PERCENTILE(pct_diff, 0.03) AS pct_03,
    APPROX_PERCENTILE(pct_diff, 0.04) AS pct_04,
    APPROX_PERCENTILE(pct_diff, 0.05) AS pct_05,
    APPROX_PERCENTILE(pct_diff, 0.25) AS pct_25,
    APPROX_PERCENTILE(pct_diff, 0.50) AS pct_50,
    APPROX_PERCENTILE(pct_diff, 0.75) AS pct_75,
    APPROX_PERCENTILE(pct_diff, 0.90) AS pct_90,
    APPROX_PERCENTILE(pct_diff, 0.91) AS pct_91,
    APPROX_PERCENTILE(pct_diff, 0.92) AS pct_92,
    APPROX_PERCENTILE(pct_diff, 0.93) AS pct_93,
    APPROX_PERCENTILE(pct_diff, 0.94) AS pct_94,
    APPROX_PERCENTILE(pct_diff, 0.95) AS pct_95,
    APPROX_PERCENTILE(pct_diff, 0.99) AS pct_99
FROM stats

Approach 3: All Rates Excluding Outliers, Non-Zero Rate Changes​

Looking at all rates excluding outliers, and considering only non-zero rate changes, we see that the 5th percentile of rate changes is -54.1% and the 95th percentile of rate changes is +662.4%.

pct_change
pct_01-0.823
pct_02-0.748
pct_03-0.675
pct_04-0.607
pct_05-0.541
pct_25-0.024
pct_500.046
pct_750.356
pct_902.848
pct_913.322
pct_923.919
pct_934.625
pct_945.503
pct_956.624
pct_9921.691
SQL Query
WITH
new AS (
    SELECT 
        roid,
        canonical_rate as new_rate
    FROM tq_dev.internal_dev_csong_cld_v2_3_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_0.tmp_whisper_provider_2025_10
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
old AS (
    SELECT 
        roid,
        canonical_rate as old_rate
    FROM tq_dev.internal_dev_csong_cld_v2_2_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_2_2.tmp_whisper_provider_2025_09
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
stats AS (
    SELECT 
        roid,
        new_rate,
        old_rate,
        1.0000 * (new_rate - old_rate) / old_rate AS pct_diff
    FROM new
    JOIN old
        USING (roid)
    WHERE old_rate > 0
    AND new_rate > 0
)
SELECT
    APPROX_PERCENTILE(pct_diff, 0.01) AS pct_01,
    APPROX_PERCENTILE(pct_diff, 0.02) AS pct_02,
    APPROX_PERCENTILE(pct_diff, 0.03) AS pct_03,
    APPROX_PERCENTILE(pct_diff, 0.04) AS pct_04,
    APPROX_PERCENTILE(pct_diff, 0.05) AS pct_05,
    APPROX_PERCENTILE(pct_diff, 0.25) AS pct_25,
    APPROX_PERCENTILE(pct_diff, 0.50) AS pct_50,
    APPROX_PERCENTILE(pct_diff, 0.75) AS pct_75,
    APPROX_PERCENTILE(pct_diff, 0.90) AS pct_90,
    APPROX_PERCENTILE(pct_diff, 0.91) AS pct_91,
    APPROX_PERCENTILE(pct_diff, 0.92) AS pct_92,
    APPROX_PERCENTILE(pct_diff, 0.93) AS pct_93,
    APPROX_PERCENTILE(pct_diff, 0.94) AS pct_94,
    APPROX_PERCENTILE(pct_diff, 0.95) AS pct_95,
    APPROX_PERCENTILE(pct_diff, 0.99) AS pct_99
FROM stats
WHERE pct_diff != 0

Approach 4: Only Validated Rates, Non-Zero Rate Changes​

Looking only at validated rates (canonical_rate_score = 5) and considering only non-zero rate changes, we see that the 5th percentile of rate changes is -7.4% and the 95th percentile of rate changes is +38.3%.

pct_change
pct_01-0.288
pct_02-0.170
pct_03-0.123
pct_04-0.093
pct_05-0.074
pct_25-0.002
pct_500.027
pct_750.058
pct_900.152
pct_910.185
pct_920.221
pct_930.265
pct_940.313
pct_950.383
pct_995.244
SQL Query
WITH
new AS (
    SELECT 
        roid,
        canonical_rate as new_rate
    FROM tq_dev.internal_dev_csong_cld_v2_3_0.prod_combined_abridged
    WHERE canonical_rate_score = 5
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_3_0.tmp_whisper_provider_2025_10
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
old AS (
    SELECT 
        roid,
        canonical_rate as old_rate
    FROM tq_dev.internal_dev_csong_cld_v2_2_2.prod_combined_abridged
    WHERE canonical_rate_score = 5
    AND provider_type = 'Hospital'
    AND provider_id IN (
        SELECT DISTINCT provider_id
        FROM tq_dev.internal_dev_csong_cld_v2_2_2.tmp_whisper_provider_2025_09
        WHERE provider_amc = True
    )
    AND network_type = 'PPO'
),
stats AS (
    SELECT 
        roid,
        new_rate,
        old_rate,
        1.0000 * (new_rate - old_rate) / old_rate AS pct_diff
    FROM new
    JOIN old
        USING (roid)
    WHERE old_rate > 0
    AND new_rate > 0
)
SELECT
    APPROX_PERCENTILE(pct_diff, 0.01) AS pct_01,
    APPROX_PERCENTILE(pct_diff, 0.02) AS pct_02,
    APPROX_PERCENTILE(pct_diff, 0.03) AS pct_03,
    APPROX_PERCENTILE(pct_diff, 0.04) AS pct_04,
    APPROX_PERCENTILE(pct_diff, 0.05) AS pct_05,
    APPROX_PERCENTILE(pct_diff, 0.25) AS pct_25,
    APPROX_PERCENTILE(pct_diff, 0.50) AS pct_50,
    APPROX_PERCENTILE(pct_diff, 0.75) AS pct_75,
    APPROX_PERCENTILE(pct_diff, 0.90) AS pct_90,
    APPROX_PERCENTILE(pct_diff, 0.91) AS pct_91,
    APPROX_PERCENTILE(pct_diff, 0.92) AS pct_92,
    APPROX_PERCENTILE(pct_diff, 0.93) AS pct_93,
    APPROX_PERCENTILE(pct_diff, 0.94) AS pct_94,
    APPROX_PERCENTILE(pct_diff, 0.95) AS pct_95,
    APPROX_PERCENTILE(pct_diff, 0.99) AS pct_99
FROM stats
WHERE pct_diff != 0
On this page: