Version: 2.2

Negotiation Arrangement from Payer Filters

Background

Question: should we exclude negotiation_arrangement values like "capitation" and "bundle" from CLD?

We found a really high lab rate and traced that it has negotiation_arrangement = "capitation". This analysis shows that while they are rare, for labs - they can have 7-8x higher rates than FFS. Bundled rates are not selected for Laboratorys anyway, so inclusion/exclusion does not matter - they are already excluded.

Decision: Exclude capitation negotiation arrangement from CLD.

relevant Slack thread: https://turquoise-co.slack.com/archives/C08CECX0A5C/p1762441428012959

negotiation_arrangement	n	percent
capitation	156047	0.02
bundle	2382892	0.32
ffs	752289247	99.66

df = pd.read_sql(f"""
SELECT COALESCE(negotiation_arrangement, 'ffs') as negotiation_arrangement, count(*) as n
FROM tq_dev.internal_dev_csong_cld_v2_2_1.tmp_raw_mrf_payer_rates_2025_08
GROUP BY 1
""", con=trino_conn)
df['percent'] = df['n'] / df['n'].sum() * 100
df.round(2)

	p50_diff
count	920
mean	8.65253
std	9.40474
min	-0.962633
25%	1.27329
50%	5.12499
75%	14.6848
max	79.4475

# %%
df_bundled = pd.read_sql(f"""
SELECT 
    billing_code, 
    APPROX_PERCENTILE(canonical_rate, 0.1) as p10,
    APPROX_PERCENTILE(canonical_rate, 0.25) as p25,
    APPROX_PERCENTILE(canonical_rate, 0.5) as p50,
    APPROX_PERCENTILE(canonical_rate, 0.75) as p75,
    APPROX_PERCENTILE(canonical_rate, 0.9) as p90
FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_all
WHERE trace_raw_id IN (
    SELECT core_rates_id
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.tmp_raw_mrf_payer_rates_2025_08
    WHERE negotiation_arrangement = 'bundle'
)
AND canonical_rate IS NOT NULL
AND canonical_rate_source = 'payer'
AND provider_type = 'Laboratory'
GROUP BY 1
""", con=trino_conn)

# %%
df_capitation = pd.read_sql(f"""
SELECT 
    billing_code, 
    APPROX_PERCENTILE(canonical_rate, 0.1) as p10,
    APPROX_PERCENTILE(canonical_rate, 0.25) as p25,
    APPROX_PERCENTILE(canonical_rate, 0.5) as p50,
    APPROX_PERCENTILE(canonical_rate, 0.75) as p75,
    APPROX_PERCENTILE(canonical_rate, 0.9) as p90
FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_all
WHERE trace_raw_id IN (
    SELECT core_rates_id
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.tmp_raw_mrf_payer_rates_2025_08
    WHERE negotiation_arrangement = 'capitation'
)
AND canonical_rate IS NOT NULL
AND canonical_rate_source = 'payer'
AND provider_type = 'Laboratory'
GROUP BY 1
""", con=trino_conn)

# %%
df_ffs = pd.read_sql(f"""
SELECT 
    billing_code, 
    APPROX_PERCENTILE(canonical_rate, 0.1) as p10,
    APPROX_PERCENTILE(canonical_rate, 0.25) as p25,
    APPROX_PERCENTILE(canonical_rate, 0.5) as p50,
    APPROX_PERCENTILE(canonical_rate, 0.75) as p75,
    APPROX_PERCENTILE(canonical_rate, 0.9) as p90
FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_all
WHERE trace_raw_id IN (
    SELECT core_rates_id
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.tmp_raw_mrf_payer_rates_2025_08
    WHERE negotiation_arrangement = 'ffs'
)
AND canonical_rate IS NOT NULL
AND canonical_rate_source = 'payer'
AND provider_type = 'Laboratory'
GROUP BY 1
""", con=trino_conn)

# %%
df_merged = df_capitation.merge(df_ffs, on='billing_code', suffixes=('_capitation', '_ffs'))
df_merged['p50_diff'] = (df_merged['p50_capitation'] - df_merged['p50_ffs']) / df_merged['p50_ffs']
df_merged['p50_diff'].describe()

Background​

On this page:

Background