Match Analysis

Match rates between Clear Rates and DPR are generally high, ~80%: analysis sheet

Here, we provide a more granular examples of matches and non-matches. The first two tables each show 40 random samples, filtered to where both pipelines selected a payer rate and where both pipelines selected a hospital rate.

The last two tables zoom into the non-matches to examine how the pipelines differ.

• For payer MRF data, it seems that Clear Rates selecting the max rate helps by selecting a rate that is higher than ASP
• For hospital MRF data, the differences are less clear and may require further investigation.

Payer to Payer

sample_id	roid	billing_code	provider_id	payer_id	canonical_rate_source	dpr_source	canonical_rate	rate	asp_payment_limit	diff
1	7805887482355888845	J7503	1109	7	payer	payer_nominal	5.5	1.82	1.833	3.68
2	-3785167021382683263	J0218	7282	42	payer	payer_nominal	991.22	992.11	393.823	-0.89
3	6872194306135143436	J0691	5292	7	payer	payer_nominal	1.46	1.46	0.724	0
4	-1705520135355671303	J9600	643	76	payer	payer_nominal	23789.5	23789.5	3004.74	0
5	298977954765883761	J3032	5448	76	payer	payer_nominal	18.23	18.23	19.847	0
6	-7847205829343902329	J9225	4609	391	payer	payer_nominal	4766.63	4766.63	5166.29	0
7	7254553018103660952	Q4121	6369	522	payer	payer_nominal	90.63	152	48.851	-61.37
8	-6304474523111916902	90653	6636	643	payer	payer_nominal	180.32	180.32	83.49	0
9	-7529191513102218298	J1602	3978	42	payer	payer_nominal	28.79	11.1	11.029	17.69
10	2274771801280105846	J9071	1691	76	payer	payer_nominal	0.87	0.87	0.784	0
11	3280486017079825355	J0202	1774	76	payer	payer_nominal	2441.49	2441.49	2440.7	0
12	-1331423263018427103	J3380	5539	76	payer	payer_nominal	21.16	21.16	22.062	0
13	3169788773398705395	J9000	6525	522	payer	payer_nominal	15	15	2.723	0
14	1278697020137346795	J3471	676	76	payer	payer_nominal	0.5	0.5	0.499	0
15	8562013374203450036	J3489	4084	42	payer	payer_nominal	20.04	11.19	5.203	8.85
16	-1592864495033779614	J7203	2670	7	payer	payer_nominal	4.36	4.36	4.579	0
17	-3687426840408180009	Q5111	4216	7	payer	payer_nominal	163.59	163.59	136.044	0
18	6331403447192055974	J0585	1278	7	payer	payer_nominal	9.07	9.07	6.502	0
19	7791467806453095414	J7185	723	76	payer	payer_nominal	1.49	1.49	1.54	0
20	-7378664667863523832	J2305	5166	7	payer	payer_nominal	2.82	2.82	1.419	0
21	-7685398188784328964	J9260	1965	7	payer	payer_nominal	6.4	6.4	2.263	0
22	5789752757872399450	J7510	3711	7	payer	payer_nominal	0.48	0.48	0.273	0
23	-8439173216162804897	J0744	2808	76	payer	payer_nominal	2.1	2.1	2.319	0
24	4771822844883794986	90715	2457	76	payer	payer_nominal	54.6	54.6	39.709	0
25	7679312730793223777	J1817	5528	169	payer	payer_nominal	9.74	9.74	3.237	0
26	-1251275895769190794	J0121	3781	7	payer	payer_nominal	3.87	3.87	4.011	0
27	-3450953930231750193	J2799	4242	7	payer	payer_nominal	38.24	38.24	25.075	0
28	1152492387463321397	J1602	6350	643	payer	payer_nominal	61.1	61.1	11.029	0
29	-4465226858126124858	J0872	2442	42	payer	payer_nominal	0.11	0.05	0.052	0.06
30	-8825313624177935077	J2371	1294	54	payer	payer_nominal	0.01	0.01	0.002	0
31	-8991582442920328791	J9313	4581	454	payer	payer_nominal	26.71	26.71	23.394	0
32	-2881315886327726459	J9228	852	76	payer	payer_nominal	170.01	170.01	183.414	0
33	-5965414585304878685	J9181	6500	522	payer	payer_nominal	2	2	1.303	0
34	2046822327227787220	J1437	6466	42	payer	payer_nominal	85.91	85.91	18.951	0
35	-1078919782956591339	J1200	5919	43	payer	payer_nominal	4.98	4.28	0.904	0.7
36	6282477303020140172	P9046	3041	49	payer	payer_nominal	139.39	139.39	21.231	0
37	-4549885917615647727	J9350	3095	42	payer	payer_nominal	737.1	737.1	652.746	0
38	4804868193448574591	J0517	1154	111	payer	payer_nominal	163.3	163.3	168.13	0
39	-4298428616310310782	J3095	6178	643	payer	payer_nominal	7.09	7.09	7.467	0
40	8999681950228944252	J2798	2947	49	payer	payer_nominal	30.82	12.41	12.137	18.41

Hospital to Hospital

sample_id	roid	billing_code	provider_id	payer_id	canonical_rate_source	dpr_source	canonical_rate	rate	asp_payment_limit	diff
1	-4680545240004768765	J2315	798	174	hospital	hospital	4.12	5.36	4.238	-1.24
2	-8424486232147733134	J7614	6842	43	hospital	hospital	0.09	0.09	0.088	0
3	881021143243814218	J9317	6727	455	hospital	hospital	43.72	43.72	36.258	0
4	-229657013183575367	J9359	1466	42	hospital	hospital	206.38	206.38	216.743	0
5	1263582894183633882	J0885	4556	454	hospital	hospital	8.59	8.59	7.676	0
5	1263582894183633882	J0885	4556	454	hospital	payer_nominal	8.59	10.14	7.676	-1.55
6	-8322175451571963196	J0899	3010	299	hospital	hospital	3.98	3.98	1.45	0
7	1490354106708639663	J9017	6734	522	hospital	hospital	18	18	5.857	0
8	-6667256743895028744	J3410	4961	7	hospital	hospital	31.11	31.11	16.928	0
9	1675846972015173721	J2941	3180	643	hospital	hospital	163.55	163.55	51.961	0
10	6736607532248410744	Q4110	1949	643	hospital	hospital	120.96	120.96	65.405	0
11	2981535935509196780	J3420	839	76	hospital	hospital	2.56	2.56	0.947	0
12	-4770717164359272822	J9268	5876	43	hospital	hospital	7203.76	7203.76	2543.89	0
13	-4467980611022781803	Q2050	1638	52	hospital	hospital	429.38	429.38	109.284	0
14	5528335584084157983	J1885	5234	169	hospital	hospital	0.36	1.54	0.346	-1.18
15	-8911462772454191908	J9348	4744	76	hospital	hospital	1438.75	1438.75	685.906	0
16	-6618215307773860079	J9150	6418	7	hospital	hospital	53.38	74.94	19.762	-21.56
17	1719674156902640611	J9030	5709	76	hospital	hospital	2.89	2.89	3.123	0
18	-1055749315212436147	90677	5092	76	hospital	hospital	372.05	372.05	312.902	0
19	1453198918369562613	J3247	5681	7	hospital	hospital	14.31	14.31	17.844	0
20	-2823564268471267204	J2360	1267	229	hospital	hospital	23.94	23.94	9.448	0
21	-8473025655698250579	J7313	5653	383	hospital	hospital	441	441	497.796	0
22	-4272834111922969571	J0878	1164	643	hospital	hospital	0.04	0.04	0.03	0
23	3664425045804280425	J1430	6588	403	hospital	hospital	518.83	518.83	519.516	0
24	-2986215860648717328	J2357	1111	354	hospital	hospital	45.79	40.4	40.563	5.39
25	-2006206163758869994	J0485	5784	76	hospital	hospital	9.92	9.92	3.888	0
26	-4341328181497803708	J7324	2562	7	hospital	hospital	294.98	294.98	97.698	0
27	9053038351657526085	J1572	3445	76	hospital	hospital	76.14	76.14	56.116	0
28	992078022095294121	J0834	4094	643	hospital	hospital	73.45	73.45	28.021	0
29	-4962943182150427106	J7325	1564	42	hospital	hospital	18.03	18.03	9.09	0
30	4748619980987029378	J9360	1747	52	hospital	hospital	4.88	4.88	5.291	0
31	-4409257255302566944	J2765	6597	403	hospital	hospital	2.24	2.24	0.997	0
32	-1382750651988363218	J3411	1172	354	hospital	hospital	5.04	2.48	1.787	2.56
33	-5295676099234821492	J1742	5760	7	hospital	hospital	572.74	641.4	205.061	-68.66
34	-304950893294481444	J0735	2669	42	hospital	hospital	21	21	17.672	0
35	-6310886391479037400	J7515	1237	354	hospital	hospital	1.13	1.13	0.769	0
36	5742142133972744717	Q4282	2587	643	hospital	hospital	1105.2	1105.2	336.763	0
37	6957853556960700429	J3245	6300	7	hospital	hospital	324.91	324.91	126.238	0
38	-108879732997145425	Q0138	68	42	hospital	hospital	0.67	0.67	0.308	0
39	6838521763534886311	Q4293	6821	7	hospital	hospital	2060.11	2060.11	1584.7	0
40	-2204680940971390758	J2783	6883	76	hospital	hospital	1228.74	1151.52	376.48	77.22

Non-Matches Only: Payer to Payer

Only records where the rate difference is greater than 20 percent.

sample_id	roid	billing_code	provider_id	payer_id	canonical_rate_source	dpr_source	canonical_rate	rate	asp_payment_limit	diff
1	3442770939807247619	J2679	1479	42	payer	payer_nominal	12.37	7.15	7.427	5.22
2	-8711223187418504156	J9225	565	42	payer	payer_nominal	15666.8	4971.34	5166.29	10695.4
3	4036999270868315285	J9325	6586	522	payer	payer_nominal	228	123.17	73.547	104.83
4	-317162380371793570	Q4232	763	7	payer	payer_nominal	335.96	431.41	130.11	-95.45
5	2708082855851877541	Q5103	6558	522	payer	payer_nominal	154	106	19.679	48
6	-6458560222580200267	J8560	759	42	payer	payer_nominal	236.28	76.46	75.763	159.82
7	842993207590589178	J3486	4090	76	payer	payer_nominal	5.18	10.22	3.138	-5.04
8	-5421404036335181480	J9269	6410	522	payer	payer_nominal	794	382.45	348.616	411.55
9	-813840554369825845	J9324	605	42	payer	payer_nominal	336.75	81.06	80.1	255.69
10	-3540174748366783405	Q4236	1340	76	payer	payer_nominal	482.72	613.6	483.646	-130.88
11	5035911345685174536	J9323	1546	42	payer	payer_nominal	28.44	10.56	10.343	17.88
12	4067773002960411059	J9229	6536	42	payer	payer_nominal	3627.82	2758.44	2698.05	869.38
13	-4999227485301809645	P9041	2518	42	payer	payer_nominal	15.92	10.62	10.615	5.3
14	-3187198685699586358	J2802	2604	42	payer	payer_nominal	23.67	10.52	10.996	13.15
15	-8616383729915695218	J2680	6314	7	payer	payer_nominal	27.88	8.83	7.532	19.05
16	-7919507028228110615	J9298	1778	168	payer	payer_nominal	308.47	205.02	197.648	103.45
17	7219040265346449940	J0741	3869	7	payer	payer_nominal	50.89	23.13	23.629	27.76
18	-3454892018836519312	91304	2668	42	payer	payer_nominal	201.93	161.54	161.538	40.39
19	5757401257566507148	J9205	69	42	payer	payer_nominal	109.34	65.28	65.255	44.06
20	-6850570051323121175	J1455	1208	111	payer	payer_nominal	21.63	38.28	15.584	-16.65
21	-1044986740256299410	J0699	6210	300	payer	payer_nominal	9.69	2.48	2.4	7.21
22	-6344785341743276429	J9032	775	42	payer	payer_nominal	107.12	50.92	52.04	56.2
23	-6715090684309556676	Q4141	6512	522	payer	payer_nominal	66.27	49.23	64.746	17.04
24	1444816335801755946	J2506	4477	390	payer	payer_nominal	128.3	20.66	58.399	107.64
25	5155333171308303794	J9172	6338	522	payer	payer_nominal	90.34	46.46	49.077	43.88
26	-2702152091004230294	J9356	752	42	payer	payer_nominal	99.78	62.92	62.973	36.86
27	4459022910811737023	J1817	4454	390	payer	payer_nominal	9.23	2.57	3.237	6.66
28	7239030578820899240	Q4239	6611	522	payer	payer_nominal	4112.36	2819.9	2038.5	1292.46
29	7670914969823075792	Q0224	605	42	payer	payer_nominal	27349.8	6583.5	7239	20766.3
30	6563492746383898146	J1205	777	76	payer	payer_nominal	32.48	60.46	40.382	-27.98
31	-6993070531747163330	J0598	4694	7	payer	payer_nominal	118.82	63.88	65.725	54.94
32	5864104758403467373	J9245	6616	42	payer	payer_nominal	226.22	138.74	108.03	87.48
33	-6594207418966623305	J2788	4080	42	payer	payer_nominal	74.46	38.15	27.859	36.31
34	-8132696384033905362	Q5130	5400	169	payer	payer_nominal	415	186.75	149.212	228.25
35	-4879530376262257212	90732	2446	42	payer	payer_nominal	688.71	120.12	133.472	568.59
36	144209992783636988	J1932	3106	42	payer	payer_nominal	66.09	22.87	24.45	43.22
37	-560958688848813992	90376	6486	42	payer	payer_nominal	1096.02	461.62	479.719	634.4
37	-560958688848813992	90376	6486	42	payer	payer_nominal	1096.02	554.46	479.719	541.56
38	1208288454281888963	J9358	4093	42	payer	payer_nominal	38.73	28.81	30.002	9.92
39	-7761972582611790786	J9330	1836	168	payer	payer_nominal	110.35	30.32	32.286	80.03
40	-7400274397570217573	J9032	4056	42	payer	payer_nominal	155.15	60.89	52.04	94.26

Non-Matches Only: Hospital to Hospital

Only records where the rate difference is greater than 20 percent.

sample_id	roid	billing_code	provider_id	payer_id	canonical_rate_source	dpr_source	canonical_rate	rate	asp_payment_limit	diff
1	7934391813973520564	J1380	5454	7	hospital	hospital	30.52	13.62	7.712	16.9
2	8158919683481236159	J3000	6049	42	hospital	hospital	25.59	34.1	31.353	-8.51
3	-6735052947644998049	J3410	6309	7	hospital	hospital	30.04	50.99	16.928	-20.95
4	-5233145161398777335	J2270	2410	76	hospital	hospital	16.08	8.47	4.545	7.61
5	4023217497236409449	90746	1933	7	hospital	hospital	153.43	222.66	70.376	-69.23
6	8960125874707512556	J1430	3079	643	hospital	hospital	811.22	404.96	519.516	406.26
7	-2944789871177132571	J2790	7141	7	hospital	hospital	222.35	141.37	82.423	80.98
8	4028188049377886045	90714	1990	643	hospital	hospital	125.64	81.33	36.365	44.31
9	6888438034592324385	P9045	6065	7	hospital	hospital	92.88	115.54	53.077	-22.66
10	-4519454093397716238	J0480	2412	42	hospital	hospital	9270.37	12128.3	4672.47	-2857.97
11	6524361811912189015	J2794	2973	76	hospital	hospital	11.73	30.26	10.941	-18.53
12	-8271033648680810411	J9264	5871	43	hospital	hospital	23.17	44.8	12.122	-21.63
13	-6756919058719355417	91322	5297	7	hospital	hospital	402.36	277.05	161.652	125.31
14	6703473264256606373	J3470	1664	643	hospital	hospital	114.64	80.93	31.554	33.71
15	-2338465728440495046	J0712	4304	643	hospital	hospital	7.72	13.7	4.244	-5.98
16	3805045594411494441	J2175	6321	403	hospital	hospital	59.3	18.6	15.694	40.7
17	260959226889775230	J1110	4776	76	hospital	hospital	120.8	153.68	97.073	-32.88
18	-2217013366724573711	J3410	5871	7	hospital	hospital	37.14	29.71	16.928	7.43
19	8827238030076259155	J2210	3814	643	hospital	hospital	76.06	56.6	22.057	19.46
20	-4630087942892232897	90671	1667	643	hospital	hospital	431.67	334.79	261.146	96.88
21	-1662338288106475029	J9332	5737	643	hospital	hospital	64.14	92.6	32.396	-28.46
22	-629322504186211435	J2350	5920	43	hospital	hospital	238.3	72.05	59.704	166.25
23	-494280277097054195	J0221	2420	643	hospital	hospital	433.53	583.984	206.615	-150.454
24	-810501813156163729	J0218	1221	643	hospital	hospital	953.1	1319.38	393.823	-366.28
25	4982120396574141121	J9226	3134	42	hospital	hospital	70618.6	56211.1	45560.4	14407.5
26	6310697176938114173	J2860	1120	7	hospital	hospital	290.58	204.19	161.955	86.39
27	7265447766792892647	J0491	3173	42	hospital	hospital	27.55	17.18	18.104	10.37
28	4986985263724597482	J1364	6575	76	hospital	hospital	240.05	180.08	75.053	59.97
29	-6828483150075906622	A9606	5728	643	hospital	hospital	780.01	428.24	nan	351.77
30	-9188378172914436756	J0740	2661	643	hospital	hospital	981	1619.34	529.375	-638.34
31	7804304057287688404	Q4132	2177	643	hospital	hospital	160.58	79.33	89.182	81.25
32	-7635763866279493965	J9280	6049	7	hospital	hospital	81.11	97.542	28.272	-16.432
33	-8749251074429170631	J7170	7158	76	hospital	hospital	60.78	175.832	57.434	-115.052
34	-3391556859930073596	90656	5773	643	hospital	hospital	21.43	28.02	22.35	-6.59
35	-897303083467161462	J3240	1747	7	hospital	hospital	2985.01	5373.02	2113.24	-2388.01
36	-7561669746405106709	J2357	3165	42	hospital	hospital	62.87	39.46	40.563	23.41
37	-3341966273598968520	J2760	480	628	hospital	hospital	1035.29	827.4	436.507	207.89
37	3715863839938073645	J2760	480	628	payer	hospital	1260.99	827.4	436.507	433.59
38	89472390779228388	J1950	1282	174	hospital	hospital	4489.72	2457.83	1737.1	2031.89
39	-365811989576434368	J1325	3142	42	hospital	hospital	24.92	15.96	16.2	8.96
40	1403571412965325980	J7516	5733	7	hospital	hospital	242.2	128.89	72.688	113.31

Hospital Non-Matches Deeper Dive

DPR / Clear Rates are the same on average

On average, DPR and Clear Rates are the same. Hospital non-matches just have a bit more variance than payer non-matches. There are some outliers (where Clear Rates is way higher or way lower than DPR) but these are rare.

---

Zooming into 10 random samples where differences are large, it generally appears that Clear Rates is selecting the ready-as-is fee-schedule facility rate where available.

Sample 1:

74.01 is ready-as-is

• Clear Rates: 74.010
• DPR: 199.280
• ASP: 53.401

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J3250		250	Outpatient	Facility	HMO	HMO	fee schedule	70	70.04	200	milligram	200	MG	53.401
J3250		250	Outpatient	Facility	PPO	PPO	fee schedule	74	74.01	200	milligram	200	MG	53.401
J3250		250	Outpatient	Facility	BAV	Unsorted	percent of total billed charges	168	167.39	200	milligram	200	MG	53.401
J3250		250	Inpatient	Facility	BAV	Unsorted	percent of total billed charges	168	167.39	200	milligram	200	MG	53.401
J3250		250	Inpatient	Facility	HMO	HMO	percent of total billed charges	200	199.28	200	milligram	200	MG	53.401
J3250		250	Inpatient	Facility	PPO	PPO	percent of total billed charges	200	199.28	200	milligram	200	MG	53.401

Sample 2:

25.53 is ready-as-is

• Clear Rates: 25.53
• DPR: 102.12
• ASP: 28.021

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J0834		636	Outpatient	Facility	TRADITIONAL/PPO/HMO	PPO	fee schedule	25.53	25.53	0.25	milligram	0.25	MG	28.021
J0834		636	Outpatient	Facility	TRADITIONAL/PPO/HMO	HMO	fee schedule	25.53	25.53	0.25	milligram	0.25	MG	28.021

Sample 3:

5.51 is ready-as-is

• Clear Rates: 5.51
• DPR: 16.56
• ASP: 5.188

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J0360		636	Outpatient	Facility	BCBS BLUECARD	Unsorted	fee schedule	5.6	5.51	20	milligrams per milliliter	20	MG	5.188
J0360		636	Outpatient	Facility	BCBS BLUECARD	Unsorted	fee schedule	5.6	5.51	20	milligrams per milliliter	20	MG	5.188
J0360		636	Outpatient	Facility	BCBS BLUECARD	Unsorted	fee schedule	5.6	5.51	20	milligrams per milliliter	20	MG	5.188
J0360		636	Inpatient	Facility	BCBS BLUECARD	Unsorted	percent of total billed charges	16.6	16.56	20	milligrams per milliliter	20	MG	5.188
J0360		636	Inpatient	Facility	BCBS BLUECARD	Unsorted	percent of total billed charges	16.6	16.56	20	milligrams per milliliter	20	MG	5.188
J0360		636	Inpatient	Facility	ALL PRODUCTS	Unsorted	percent of total billed charges	34.2	34.2	20	milligrams per milliliter	20	MG	5.188
J0360		636	Inpatient	Facility	ALL PRODUCTS	Unsorted	percent of total billed charges	34.2	34.2	20	milligrams per milliliter	20	MG	5.188
J0360		636	Outpatient	Facility	ALL PRODUCTS	Unsorted	percent of total billed charges	34.2	34.2	20	milligrams per milliliter	20	MG	5.188
J0360		636	Outpatient	Facility	ALL PRODUCTS	Unsorted	percent of total billed charges	34.2	34.2	20	milligrams per milliliter	20	MG	5.188
J0360		636	Inpatient	Facility	BCBS BLUECARD	Unsorted	percent of total billed charges	61	61.03	20	milligrams per milliliter	20	MG	5.188
J0360		636	Inpatient	Facility	ALL PRODUCTS	Unsorted	percent of total billed charges	126	126.05	20	milligrams per milliliter	20	MG	5.188
J0360		636	Outpatient	Facility	ALL PRODUCTS	Unsorted	percent of total billed charges	126	126.05	20	milligrams per milliliter	20	MG	5.188

Sample 4:

24.94 is ready-as-is

• Clear Rates: 24.94
• DPR: 73.62
• ASP: 26.503

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
Q5119		636	Outpatient	Facility	COMMERCIAL	Unsorted	fee schedule	0.5	24.94	500	milligram	10	MG	26.503
Q5119		636	Outpatient	Facility	COMMERCIAL	Unsorted	fee schedule	2.5	24.94	100	milligram	10	MG	26.503
Q5119		636	Inpatient	Facility	COMMERCIAL	Unsorted	percent of total billed charges	1.5	73.62	500	milligram	10	MG	26.503
Q5119		636	Inpatient	Facility	COMMERCIAL	Unsorted	percent of total billed charges	7.4	73.62	100	milligram	10	MG	26.503

Sample 5:

8.09 is ready-as-is

• Clear Rates: 8.09
• DPR: 27.84
• ASP: 7.979

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J9400			Outpatient	Facility	COMMERCIAL	Unsorted	fee schedule	8.09	8.09	1	milligram	1	MG	7.979
J9400			Inpatient & Outpatient	Professional	COMMERCIAL	Unsorted	fee schedule	27.84	27.84	1	milligram	1	MG	7.979

Sample 6:

755.6 is ready-as-is

• Clear Rates: 755.6
• DPR: 1644.13
• ASP: 742.895

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J1212		250	Inpatient	Facility	CHS GROUP HEALTH PLAN BCBST	Unsorted	percent of total billed charges	712	712.06	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	CHS GROUP HEALTH PLAN BCBST	Unsorted	percent of total billed charges	712	712.06	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	BLUE ACCESS	Unsorted	other	755.5	755.6	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	BLUE PREFERRED	Unsorted	other	755.5	755.6	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	BLUE TRADITIONAL	Unsorted	percent of total billed charges	1572	1572.13	50	milliliter	50	ML	742.895
J1212		250	Inpatient	Facility	BLUE TRADITIONAL	Unsorted	percent of total billed charges	1644	1644.13	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	BLUE PREFERRED	Unsorted		nan	nan	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	BLUE ACCESS	Unsorted		nan	nan	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	CHS GROUP HEALTH PLAN BCBST	Unsorted		nan	nan	50	milliliter	50	ML	742.895
J1212		250	Inpatient	Facility	BLUE PREFERRED	Unsorted		nan	nan	50	milliliter	50	ML	742.895
J1212		250	Inpatient	Facility	BLUE ACCESS	Unsorted		nan	nan	50	milliliter	50	ML	742.895
J1212		250	Outpatient	Facility	BLUE TRADITIONAL	Unsorted		nan	nan	50	milliliter	50	ML	742.895

Sample 7:

65.03 is ready-as-is

• Clear Rates: 65.03
• DPR: 165.286
• ASP: 69.835

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J9145		636	Outpatient		ALL PLANS	PPO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	HMO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	PPO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	HMO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	PPO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	HMO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	PPO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	HMO	fee schedule	32.5	65.03	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	other	412.4	824.88	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	other	415.6	831.11	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	other	1649.7	3299.49	20	milligrams per milliliter	10	MG	69.835
J9145		636	Outpatient		ALL PLANS	Unsorted	other	1652.9	3305.72	20	milligrams per milliliter	10	MG	69.835

Sample 8:

31.2 is ready-as-is

• Clear Rates: 31.2
• DPR: 73.24
• ASP: 17.226

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J1120		636	Outpatient	Facility	HB PCMH UHC	Unsorted	fee schedule	30	31.2	500	milligram	500	MG	17.226
J1120		636	Inpatient	Facility	HB PCMH UHC	Unsorted	other	nan	nan	500	milligram	500	MG	17.226

Sample 9:

77.8 is ready-as-is

• Clear Rates: 77.8
• DPR: 163.35
• ASP: 46.911

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J1000			Outpatient	Professional	CIGNA HMO-POS	POS	fee schedule		24.05			5	MG	46.911
J1000			Outpatient	Professional	CIGNA HMO-POS	HMO	fee schedule		24.05			5	MG	46.911
J1000			Outpatient	Professional	CIGNA PPO	PPO	fee schedule		24.05			5	MG	46.911
J1000			Outpatient	Professional	CIGNA PPO - DHP	PPO	fee schedule		32.67			5	MG	46.911
J1000			Outpatient	Professional	CIGNA HMO-POS - DHP	HMO	fee schedule		32.67			5	MG	46.911
J1000			Outpatient	Professional	CIGNA HMO-POS - DHP	POS	fee schedule		32.67			5	MG	46.911
J1000			Outpatient	Facility	CIGNA HMO-POS	POS	fee schedule		77.8			5	MG	46.911
J1000			Outpatient	Facility	CIGNA HMO-POS	HMO	fee schedule		77.8			5	MG	46.911

Sample 10:

112.04 is ready-as-is

• Clear Rates: 112.04
• DPR: 271.32
• ASP: 32.085

billing_code	billing_code_modifiers	revenue_code	setting	billing_class	plan_name	payer_product_network	contract_methodology	dosage_std_dollar	negotiated_dollar	parsed_quantity	parsed_unit_label	asp_quantity	asp_unit_label	asp_payment_limit
J1190		636	Outpatient	Facility	COMMERCIAL	Unsorted	fee schedule	112.5	112.04	250	milligram	250	MG	32.085
J1190		636	Outpatient	Facility	COMMERCIAL	Unsorted	fee schedule	55	112.04	500	milligram	250	MG	32.085
J1190		636	Inpatient	Facility	COMMERCIAL	Unsorted	percent of total billed charges	135	135.66	250	milligram	250	MG	32.085
J1190		636	Inpatient	Facility	COMMERCIAL	Unsorted	percent of total billed charges	135	271.32	500	milligram	250	MG	32.085

Code

To get payer-payer / hospital-hospital matches and non-matches:

_type = 'hospital-hospital'
if _type == 'payer-payer':
    canonical_rate_type = 'raw: payer'
    dpr_source = 'payer'
elif _type == 'hospital-hospital':
    canonical_rate_type = 'raw: hospital'
    dpr_source = 'hospital'

# %%
df = pd.read_sql(f"""
WITH
asp AS (
    SELECT
        code as billing_code,
        CAST(payment_limit AS DOUBLE) as asp_payment_limit
    FROM (
        SELECT 
            code, 
            payment_limit, 
            rank() OVER (PARTITION BY code ORDER BY eff_end_dt DESC) as rank
        FROM tq_production.reference_legacy.ref_asp_pricing_file
    )
    WHERE rank = 1
),
matches AS (
    SELECT 
        payer_id,
        provider_id,
        billing_code,
        MAX(
            CASE 
            WHEN ABS(canonical_rate - rate) / canonical_rate < 0.2 
                OR ABS(canonical_rate - rate) < 5
            THEN 1 ELSE 0 END
        ) AS match
    FROM tq_dev.internal_dev_csong_sandbox.dpr_vs_cld_hospitals_2025_08_24
    WHERE canonical_rate_type LIKE '{canonical_rate_type}%'
    AND dpr_source LIKE '%{dpr_source}%'
    AND canonical_rate_type NOT LIKE '%allowed_amount%'
    AND roid IN (
        SELECT roid
        FROM tq_dev.internal_dev_csong_cld_v2_1_0.tmp_int_combined_2025_06
        WHERE network_type = 'PPO'
    )
    AND billing_code IN (
        SELECT billing_code
        FROM asp
    )
    GROUP BY 1, 2, 3
),
sample AS (
    SELECT
        row_number() OVER () AS sample_id,
        t.*
    FROM (
        SELECT payer_id, provider_id, billing_code
        FROM matches
        WHERE match = 0
        ORDER BY RANDOM()
        LIMIT 20
    ) t
)
SELECT 
    DISTINCT
    sample.sample_id,
    cld.roid,
    cld.billing_code,
    cld.provider_id,
    cld.payer_id,
    cld.canonical_rate_source,
    cld.dpr_source,
    cld.canonical_rate,
    cld.rate,
    asp.asp_payment_limit
FROM tq_dev.internal_dev_csong_sandbox.dpr_vs_cld_hospitals_2025_08_24 cld
JOIN sample
    ON cld.payer_id = sample.payer_id
    AND cld.provider_id = sample.provider_id
    AND cld.billing_code = sample.billing_code
LEFT JOIN asp
    ON cld.billing_code = asp.billing_code
WHERE roid IN (
    SELECT roid
    FROM tq_dev.internal_dev_csong_cld_v2_1_0.tmp_int_combined_2025_06
    WHERE network_type = 'PPO'
)
ORDER BY sample.sample_id

To get samples from hospital data:

import seaborn as sns
import matplotlib.pyplot as plt
sns.set_theme(style='whitegrid', rc={'figure.figsize': (14, 12)})

# %%
df['diff'].describe(percentiles=[0.01, 0.05, 0.1, 0.25, 0.5, 0.75, 0.9, 0.95, 0.99])

# %%
ax = sns.kdeplot(df['diff'])
plt.title('Distribution of Rate Differences (Canonical - DPR) for hospital-hospital mis-matches only (n=1000)')


# %% [markdown]
"""
Examples
"""
# %%
samples = df.loc[abs(df['diff_percent']) > 100].head(10)

# %%
for sample in samples.itertuples():
    check = pd.read_sql(f"""
    WITH
    asp AS (
        SELECT
            code as billing_code, 
            quantity,
            unit_label,
            CAST(payment_limit AS DOUBLE) as asp_payment_limit
        FROM (
            SELECT 
                code, 
                REPLACE(COALESCE(REGEXP_EXTRACT(dosage, '[0-9.]+'), '1'),',','') as quantity,
                COALESCE(REGEXP_REPLACE(dosage, '[0-9.]+', ''), '') as unit_label,
                payment_limit, 
                rank() OVER (PARTITION BY code ORDER BY eff_end_dt DESC) as rank
            FROM tq_production.reference_legacy.ref_asp_pricing_file
        )
        WHERE rank = 1
    )
    SELECT 
        hr.billing_code,
        hr.billing_code_modifiers,
        hr.revenue_code,
        hr.setting,
        hr.billing_class,
        hr.plan_name,
        hr.payer_product_network,
        contract_methodology,
        CASE
            WHEN (
                    dd.parsed_quantity IS NOT NULL AND 
                    CAST(dd.parsed_quantity AS DECIMAL(18, 2)) > 0
                )
                THEN (
                    -- dosage standardize
                    (hr.negotiated_dollar / CAST(dd.parsed_quantity AS DECIMAL(18, 2))) * CAST(asp.quantity as DECIMAL(18, 2))
                )
            ELSE NULL
        END as dosage_std_dollar,
        negotiated_dollar,
        dd.parsed_quantity,
        dd.parsed_unit_label,
        asp.quantity as asp_quantity,
        asp.unit_label as asp_unit_label,
        asp.asp_payment_limit
    FROM tq_production.hospital_data.hospital_rates hr
    JOIN asp
        ON hr.billing_code = asp.billing_code
    LEFT JOIN (
        SELECT 
            description, 
            code, 
            any_value(parsed_quantity) as parsed_quantity, 
            any_value(parsed_unit_label) as parsed_unit_label
        FROM tq_production.life_sciences.ff_hospital_descriptions_2025_07
        WHERE parsed_quantity != ''
        GROUP BY description, code
    ) dd
        ON hr.description = dd.description
        AND hr.billing_code = dd.code
    WHERE 
        hr.billing_code = '{sample.billing_code}'
        AND hr.provider_id = '{sample.provider_id}'
        AND hr.payer_id = {sample.payer_id}
        AND hr.payer_class_name = 'Commercial'
    ORDER BY negotiated_dollar
    """, con=trino_conn)

    print(f"- Clear Rates: {sample.canonical_rate}")
    print(f"- DPR: {sample.rate}")
    print(f"- ASP: {sample.asp_payment_limit}")
    print('\n')
    print(check.to_markdown(index=False))
    print('\n\n')