Tiers

SchemaMethodology

✅: Currently Implemented
🔨: Work in Progress

1. Exact Code Match and Exact Rate Match

There’s a rate in raw data that exactly matches the rate object in CLD.

✅ Payer/Hospital MRF Negotiated Dollar Amount

A dollar amount pulled from MRF that exactly matches the rate object.

More Details

Identification

These rates are stored in prod_combined_all under the any of the following columns:

payer_derived_rate 
payer_fee_schedule_rate
payer_negotiated_rate
hospital_case_rate_dollar
hospital_percent_of_total_billed_charges_dollar
hospital_fee_schedule_dollar
hospital_other_dollar
hospital_null_methodology_dollar

If they are selected as the canonical rate, their canonical_rate_type is raw: {column_name}, where column_name is one of the column names listed above.

Traceability

For payer_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in core_rates)
• payer_file_hash
• payer_location_data_source_name
• payer_last_updated_on

For hospital_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in hospital_rates)
• hospital_file_id
• hospital_ingested_on
• hospital_loaded_on
• hospital_filename

✅ Hospital MRF Estimated Allowed Amount

An estimated allowed amount pulled from Hospital MRF that exactly matches the rate object.

More Details

Identification

These rates are stored in prod_combined_all under the any of the following columns:

hospital_case_rate_allowed_amount
hospital_percent_of_total_billed_charges_allowed_amount
hospital_fee_schedule_allowed_amount
hospital_per_diem_allowed_amount
hospital_other_allowed_amount
hospital_null_methodology_allowed_amount

If they are selected as the canonical rate, their canonical_rate_type is raw: {column_name}, where column_name is one of the column names listed above.

Traceability

For hospital_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in hospital_rates)
• hospital_file_id
• hospital_ingested_on
• hospital_loaded_on
• hospital_filename

✅ Payer/Hospital MRF Negotiated Percentage with Exact Charge Match

A percentage pulled from MRF that exactly matches the rate object. We calculate a dollar amount by multiplying the percentage with a gross charge from hospital data or claims data with a sufficient number of claims.

More Details

Identification

These rates are stored in prod_combined_all under the any of the following columns:

payer_gc_hosp_perc_to_dol
hospital_case_rate_gc_hosp_perc_to_dol
hospital_perc_of_total_billed_charges_gc_hosp_perc_to_dol
hospital_fee_schedule_gc_hosp_perc_to_dol
hospital_other_gc_hosp_perc_to_dol
hospital_null_methodology_gc_hosp_perc_to_dol

Columns with gc_hosp in the name indicate that the gross charge is from hospital MRF data.

If the column is selected as the canonical rate, the canonical_rate_type is transform: {column_name}, where column_name is one of the column names listed above.

Methodology

The exact match percentage rate from payer or hospital MRF data is multiplied with either a line item gross charge or claim-level gross charge.

Example:

roid	provider_name	payer_name	billing_code	payer_percentage_rate	mrf_gross_charge_provider	payer_gc_hosp_perc_to_dol
945670394387312349	Clifton-Fine Hospital	Aetna	78472	68	2483.5	1688.78

Traceability

For payer_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in core_rates)
• payer_file_hash
• payer_location_data_source_name
• payer_last_updated_on

For hospital_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in hospital_rates)
• hospital_file_id
• hospital_ingested_on
• hospital_loaded_on
• hospital_filename

✅ Komodo Claims Allowed Amounts

An average allowed amount from claims data with at least 11 claims (N > 11) matching the rate object.

More Details

Identification

These rates are stored in prod_combined_all under the following column:

median_allowed_amount_primary_line_item

The number of claims associated with this allowed amount is stored in count_encounters_primary_line_item.

If this column is selected as the canonical rate, the canonical_rate_type is raw: median_allowed_amount_primary_line_item.

2. Exact Provision Match or Exact Code Match

There’s a stated provision or exact match on code, but involves a translation.

✅ Payer/Hospital MRF Negotiated Percentage with Estimated Charge Match

A percentage pulled from an MRF file that exactly matches the rate object. We calculate a dollar amount by multiplying the percentage with a gross charge. However, a gross charge is not available for the provider-code in hospital data or in Komodo claims, and we estimate a gross charge using a market average.

More Details

Identification

These rates are stored in prod_combined_all under the any of the following columns:

payer_gc_hosp_cbsa_perc_to_dol
payer_gc_komodo_perc_to_dol
payer_gc_komodo_cbsa_perc_to_dol
hospital_case_rate_gc_hosp_cbsa_perc_to_dol
hospital_case_rate_gc_komodo_perc_to_dol
hospital_case_rate_gc_komodo_cbsa_perc_to_dol
hospital_perc_of_total_billed_charges_gc_hosp_cbsa_perc_to_dol
hospital_perc_of_total_billed_charges_gc_komodo_perc_to_dol
hospital_perc_of_total_billed_charges_gc_komodo_cbsa_perc_to_dol
hospital_fee_schedule_gc_hosp_cbsa_perc_to_dol
hospital_fee_schedule_gc_komodo_perc_to_dol
hospital_fee_schedule_gc_komodo_cbsa_perc_to_dol
hospital_other_gc_hosp_cbsa_perc_to_dol
hospital_other_gc_komodo_perc_to_dol
hospital_other_gc_komodo_cbsa_perc_to_dol
hospital_null_methodology_gc_hosp_cbsa_perc_to_dol
hospital_null_methodology_gc_komodo_perc_to_dol
hospital_null_methodology_gc_komodo_cbsa_perc_to_dol

Columns with `gc_hosp` in the name indicate that the gross charge is from hospital MRF data. Columns with `gc_hosp_cbsa` in the name indicate that the gross charge represents the market average based on hospital MRF data. `gc_komodo` represents the average gross charge amount at the line-item level, whereas `gc_komodo_cbsa` represent the market average at the line-item level.

If they are selected as the canonical rate, their canonical_rate_type is transform: {column_name}, where column_name is one of the column names listed above.

Methodology

The exact match percentage rate from payer or hospital MRF data is multiplied with either an estimated line item gross charge or claim-level gross charge. In this case, since 93459 is not a DRG or OP Surg, we use the line item gross charge.

Example:

roid	provider_name	payer_name	billing_code	medicare_rate	lower_bound	upper_bound	hospital_percent_of_total_billed_charges_percentage	komodo_provider_gross_charge	hospital_perc_of_total_billed_charges_gc_komodo_perc_to_dol
-7741034584512658311	Memorial Satilla Health	Aetna	93459	3083.83	2371.45	45366.9	59.6	24408.5	14547.5

Traceability

For payer_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in core_rates)
• payer_file_hash
• payer_location_data_source_name
• payer_last_updated_on

For hospital_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in hospital_rates)
• hospital_file_id
• hospital_ingested_on
• hospital_loaded_on
• hospital_filename

✅ Payer/Hospital MRF Per Diem Dollar Amount

A per diem rate pulled from an MRF file that exactly matches the rate object. We derive a dollar amount by multiplying the per diem rate by the average length of stay according to CMS.

More Details

Identification

These rates are stored in prod_combined_all under the any of the following columns:

payer_per_diem_mult_alos
hosp_per_diem_mult_alos
hosp_gc_hosp_per_diem_perc_to_dol_mult_alos
hosp_gc_hosp_cbsa_per_diem_perc_to_dol_mult_alos
hosp_gc_komodo_per_diem_perc_to_dol_mult_alos
hosp_gc_komodo_cbsa_per_diem_perc_to_dol_mult_alos

Columns with `gc_hosp` in the name indicate that the gross charge is from hospital MRF data. Columns with `gc_hosp_cbsa` in the name indicate that the gross charge represents the market average based on hospital MRF data. `gc_komodo` represents the average gross charge amount at the line-item level, whereas `gc_komodo_cbsa` represent the market average at the line-item level.

If they are selected as the canonical rate, their canonical_rate_type is transform: {column_name}, where column_name is one of the column names listed above.

Methodology

The exact match per diem rate from payer or hospital MRF data is multiplied with the average length of stay from CMS (redshift.reference.ref_cms_msdrg).

If it the per diem rate is from hospital data, the MRF may have reported a percentage value (In payer MRF data, per diem rates can only be dollar amounts). Multiply the percentage value with either a line item gross charge or claim-level gross charge.

Example:

roid	provider_name	payer_name	billing_code	hospital_per_diem_dollar	medicare_alos	hosp_per_diem_mult_alos
4102328277465935282	Lehigh Valley Hospital - Schuylkill E. Norwegian Street	Cigna	204	1882.98	2.7	5084.05

payer_per_diem_mult_alos = medicare_alos * payer_per_diem_rate
hosp_per_diem_mult_alos = medicare_alos * hospital_per_diem_dollar
hosp_per_diem_perc_to_dol_mult_alos = medicare_alos * komodo_provider_gross_charge_line_item * hospital_per_diem_percentage
hosp_gc_claim_per_diem_perc_to_dol_mult_alos = medicare_alos * komodo_provider_gross_charge_claim * hospital_per_diem_percentage

Traceability

For payer_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in core_rates)
• payer_file_hash
• payer_location_data_source_name
• payer_last_updated_on

For hospital_ columns, rates may be traced back to the raw MRF using:

• trace_raw_id (id in hospital_rates)
• hospital_file_id
• hospital_ingested_on
• hospital_loaded_on
• hospital_filename

✅ APR-DRG Rate

An APR-DRG rate in the MRF file is tranformed to a MS-DRG rate using a custom TQ APR-DRG -> MS-DRG crosswalk. In the case of many-to-one (many APR-DRGs map to a single MS-DRG), we surface the simple, unweighted average of the APR-DRG rates.

✅ APC Rate

An APC rate in the MRF file is mapped to a HCPCS code using CMS reference data. Since the APC -> HCPCS crosswalk is a one-to-many mapping, each HCPCS code inherits the rate from its associated APC.

3. Inferred Provision Match

There’s a provision that we have inferred from the data.

✅ MS-DRG: Case Rate

We observed a sufficient number of MS-DRG rates in our data, whose quotient after dividing by the CMS MS-DRG weight share a common value, suggesting that this quotient value represents an inpatient case rate. We use this inferred case rate to impute missing rates in the MRF data.

Details

Identification

These rates are stored in prod_combined_all in msdrg_base_rate. And the following columns contain metadata about this inferred rate:

• msdrg_cms_weight: Medicare CMS MS-DRG weight
• msdrg_n_freq: The number of rate objects with common payer-network-provider sharing the common base rate in our data
• msdrg_n_total: The number of rate objects with common payer-network-provider that have non-null MS-DRG rates
• msdrg_candidate_base_rate: msdrg_candidate_base_rate * msdrg_cms_weight = msdrg_base_rate

Note that msdrg_base_rate is only populated n_freq is > the threshold (e.g. 10).

msdrg_n_total is useful because we would be more confident if 20 out of 22 available rates share a common base rate, compared to if 20 out of 300 available rates share a common base rate

Methodology

First, divide each MS-DRG rate by the CMS weight. Then, for each payer-network-provider-base_rate, count the number of distinct MS-DRG rate objects. Identify the msdrg_candidate_base_rate: the most frequent base_rate after sorting payer-network-provider-base_rates by their counts in descending order.

IF both conditions below are met:

1. the base-rate frequency is greater than 10
2. this most frequent rate represents > 90% of rates available

Then multiply the msdrg_candidate_base_rate by the CMS weight to obtain the imputed dollar amount for the MS-DRG code.

Example

1 Suppose these are the Raw Rates we've pulled into CLD. It contains MS-DRG rates for payer 643 and provider 4740

roid	payer_id	provider_id	billing_code	billing_code_type	medicare_rate	msdrg_cms_weight	hospital_case_rate_dollar	hospital_percent_of_total_billed_charges_dollar	hospital_fee_schedule_dollar	hospital_other_dollar	hospital_null_methodology_dollar	hospital_case_rate_allowed_amount	hospital_percent_of_total_billed_charges_allowed_amount	hospital_fee_schedule_allowed_amount	hospital_per_diem_allowed_amount	hospital_other_allowed_amount	hospital_null_methodology_allowed_amount	payer_fee_schedule_rate	payer_negotiated_rate	payer_derived_rate
-5442131808280423016	643	4740	266	MS-DRG	45630.9	5.9908			33489.1										nan
5029280079946284030	643	4740	426	MS-DRG	nan	10.4754			58558.4										nan
-6381720439379338956	643	4740	850	MS-DRG	nan	9.2119			51495.3										nan
874302072793997687	643	4740	785	MS-DRG	6858.25	0.8735			4882.94										5448
3536227669113194900	643	4740	451	MS-DRG	nan	3.086			17251										nan

2 Cast Long to Compute Base Rate

payer_id	provider_id	billing_code	rate_type	rate_value	msdrg_cms_weight	rate_value / cms weight	msdrg_candidate_base_rate	msdrg_base_rate
643	4740	850	hospital_fee_schedule_dollar	51495.3	9.2119	5590	5590	51494.5
643	4740	266	hospital_fee_schedule_dollar	33489.1	5.9908	5590	5590	33488.6
643	4740	785	hospital_fee_schedule_dollar	4882.94	0.8735	5590	5590	4882.86
643	4740	426	hospital_fee_schedule_dollar	58558.4	10.4754	5590	5590	58557.5
643	4740	451	hospital_fee_schedule_dollar	17251	3.086	5590	5590	17250.7
643	4740	785	payer_negotiated_rate	5448	0.8735	6240	5590	4882.86

3 Frequencies of Rate / Weight

rate_value / cms weight	count	total
5590	5	6
6240	1	6

✅ MS-DRG: Base Percentage Rate

We have a sufficient number of MS-DRG percentages in our data that share a common value, suggesting that this percent value represents an inpatient base percentage rate. We multiply this percentage rate with the gross charge to calculate a dollar amount.

Details

Identification

These rates are stored in prod_combined_all in one of the following columns:

• msdrg_gc_hosp_base_perc_to_dol
• msdrg_gc_hosp_cbsa_base_perc_to_dol
• msdrg_gc_komodo_base_perc_to_dol
• msdrg_gc_komodo_cbsa_base_perc_to_dol

And the following columns contain stats about this inferred rate:

• msdrg_percentage_candidate_base_rate:
  • msdrg_percentage_candidate_base_rate * mrf_gross_charge_provider = msdrg_gc_hosp_base_perc_to_dol
  • msdrg_percentage_candidate_base_rate * komodo_provider_gross_charge_line_item = msdrg_gc_komodo_base_perc_to_dol
• msdrg_percentage_n_freq: The number of rate objects with common payer-network-provider sharing the common base percentage rate in our data
• msdrg_percentage_n_total: The number of rate objects with common payer-network-provider that have non-null MS-DRG percentage rates

Note that percentage base rates are only populated n_freq is > the threshold (e.g. 10).

Methodology

First, for each payer-network-provider-percentage_rate, count the number of distinct MS-DRG rate objects. Identify the msdrg_percentage_candidate_percentage_rate: the most frequent percentage_rate after sorting payer-network-provider-percentage_rates by their counts in descending order.

IF both conditions below are met:

1. the percentage-base-rate frequency is greater than 50
2. this most frequent percentage represents > 90% of percentages available

Then multiply the msdrg_percentage_candidate_base_rate by both gross_charge_line_item and gross_charge_claim

Example

In the example provider-network below, we show a sample of 20 MS-DRG codes associated with Massac Memorial Hospital and Blue Cross Blue Shield of Illinois.

For this provider and payer, MRF data only has 67 MS-DRG rates (as indicated in msdrg_percentage_n_total). And for all 67 codes, the hospital MRF lists a percentage rate of 96% (msdrg_percentage_candidate_base_rate). You can see in this sample that four of the codes have the 96% rate while the remaining codes do not have a percentage value.

We infer that this 96% is the percentage rate that applies to all inpatient services.

Then, we can multiply the 96% by the gross charge that is posted for the provider-code in MRF data: mrf_gross_charge_provider. If it is missing, we can use the market average: mrf_gross_charge_cbsa_median.

roid	provider_name	payer_name	billing_code	msdrg_percentage_n_freq	msdrg_percentage_n_total	hospital_percent_of_total_billed_charges_percentage	msdrg_percentage_candidate_base_rate	mrf_gross_charge_provider	mrf_gross_charge_cbsa_median	msdrg_gc_hosp_base_perc_to_dol
-2310587601763185178	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	884	67	67	96	96	7310.03	30427	7017.63
3748485849035765283	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	914	67	67	96	96	4967.2	24333	4768.51
-3326151480648574509	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	690	67	67	96	96	9833.33	25212.1	9440
2458859743945816842	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	536	67	67	96	96	6219.78	29731	5970.99
-894822416703273813	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	355	67	67	nan	96	nan	173581	166638
-8425865937623103443	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	630	67	67	nan	96	nan	nan	nan
5555216475658987727	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	827	67	67	nan	96	nan	201914	193837
2941482172829300115	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	040	67	67	nan	96	nan	nan	nan
-8113203645445796880	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	665	67	67	nan	96	nan	nan	nan
7673044593358656121	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	339	67	67	nan	96	nan	nan	nan
-6493381181053086035	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	769	67	67	nan	96	nan	47440.8	45543.2
-5751945905751068853	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	150	67	67	nan	96	nan	50014.2	48013.6
717731562919940722	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	625	67	67	nan	96	nan	nan	nan
-8600549736135298035	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	743	67	67	nan	96	nan	51015.9	48975.3
-2049471481425625319	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	849	67	67	nan	96	nan	100056	96053.7
2527562561503767834	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	462	67	67	nan	96	nan	nan	nan
4890587398768238901	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	357	67	67	nan	96	nan	55170.3	52963.5
-4868403757484625318	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	826	67	67	nan	96	nan	451508	433448
-3507380914673773605	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	434	67	67	nan	96	nan	nan	nan
1042835213765956552	Massac Memorial Hospital	Blue Cross Blue Shield of Illinois	419	67	67	nan	96	nan	55590.4	53366.8

✅ HCPCS: Outpatient Procedure Grouper Case Rate

We pull OP surgery groupers from first party data and combine them with MRF data to infer OP grouper case rates. These case rates can be used to impute values missing from MRF data.

Details

Identification

These rates are stored in prod_combined_all in one of the following columns:

• opg_base_rate

And the following columns contain stats about this inferred rate:

• opg: the outpatient procedure grouper code
• opg_candidate_base_rate: this column contains the inferred OPG base rate; it is the same as opg_base_rate and the only difference is that opg_base_rate may be NULL if opg_n_freq (the sample size) is too low
• opg_n_freq: the count of HCPCS codes for the payer-network-provider-opg that share the same rate as the mode
• opg_n_total: the total number of HCPCS codes for the payer-network-provider-opg that have a rate available

Methodology

The opg column contains the mapping from the HCPCS code to the OPG group. For each payer-network-provider-code-rate, count the number of distinct HCPCS rate objects. Identify the rate with the most distinct rate objects.

IF all conditions below are met:

1. the frequency count is > 15
2. total number of codes in the grouper > 40
3. frequency count / total count > 70%

THEN we will use this rate as the opg_base_rate

So if there are 20 MRF rates available for grouper 4, which has 50 codes and 16 of the 20 MRF rates are all $2200, then we would use$2200 to impute rates for the remaining 30 codes.

✅ HCPCS: Outpatient Base Percentage Rate

We have a sufficient number of HCPCS percentages in our data that share a common value, suggesting that this percent value represents an outpatient base percentage rate. We multiply this percentage rate with the gross charge to calculate a dollar amount.

Details

Identification

These rates are stored in prod_combined_all in one of the following columns:

op_gc_hosp_base_perc_to_dol
op_gc_hosp_cbsa_base_perc_to_dol
op_gc_komodo_base_perc_to_dol
op_gc_komodo_cbsa_base_perc_to_dol

And the following columns contain stats about this inferred rate:

• op_percentage_candidate_base_rate
  • op_percentage_candidate_base_rate * mrf_gross_charge_provider = op_gc_hosp_base_perc_to_dol
  • op_percentage_candidate_base_rate * komodo_provider_gross_charge_claim = op_gc_komodo_base_perc_to_dol
• op_percentage_n_freq: the count of HCPCS codes for the payer-network-provider that share the same percentage rate
• op_percentage_n_total: the total number of HCPCS codes for the payer-network provider that have a rate available

Methodology

The opg column contains the mapping from the HCPCS code to the OPG group. For each payer-network-provider-code-rate, count the number of distinct HCPCS rate objects. Identify the rate with the most distinct rate objects.

IF the following condtions are met:

1. the frequency count is > 200
2. frequency count / total count > 90%

Then we will use this rate as the opg_base_rate.

✅ MS-DRG: RC - Global Percentage

Identifies cases where most Revenue Codes in Payer MRF data share a single percentage. We can then infer this percentage represents a global percentage reimbursement for inpatient services. We use this percentage to impute rates for all MS-DRG codes.

Details

Identification

These rates are stored in prod_combined_all in one of the following columns:

rc_global_gc_hosp_base_perc_to_dol
rc_global_gc_hosp_cbsa_base_perc_to_dol
rc_global_gc_komodo_base_perc_to_dol
rc_global_gc_komodo_cbsa_base_perc_to_dol

And the following columns contain stats about this inferred rate:

• rc_global_inpatient_base_rate
  • rc_global_inpatient_base_rate * mrf_gross_charge = rc_global_gc_hosp_base_perc_to_dol
  • rc_global_inpatient_base_rate * komodo_gross_charge = rc_global_gc_komodo_base_perc_to_dol
• rc_global_inpatient_billing_code_array
  • the array of RC codes that were reported in the MRF with a common percentage rate

Methodology

Using core rates, group by negotiated_rate and pull arrays of revenue codes. The percentage rate associated with the highest-cardinality array is considered to be the global rate, as long as the array has more than 30 distinct revenue codes.

Note that this tier ignores RC-family carveouts (e.g. where 30+ revenue codes have a global inpatient rate and NICU has a separate rate). This is ok because the MS-DRG RC - Family imputation tiers will catch these carveouts.

If there is no percentage rate with an array > 30 codes, no global rate is identified.

If there is a tie (2 percentage rates each with 30 codes), the higher rate is selected as the global rate.

Example

SELECT 
    provider_id, negotiated_type, rate, billing_code_array, n_codes
FROM tq_dev.internal_dev_csong_cld_v1_0.tmp_int_imputations_rc_global_2025_02
WHERE payer_id = '643'
AND negotiated_type = 'percentage'
AND provider_id = '6204'
AND n_distinct_rates = 1

provider_id	negotiated_type	rate	billing_code_array	n_codes
6204	percentage	80	['0100', '0101', '0110', '0111', '0112', '0113', '0117', '0119', '0120', '0121', '0122', '0123', '0127', '0129', '0130', '0131', '0132', '0133', '0137', '0139', '0140', '0141', '0142', '0143', '0147', '0149', '0150', '0151', '0152', '0153', '0157', '0159', '0160', '0161', '0164', '0167', '0169', '0180', '0182', '0183', '0185', '0189', '0190', '0191', '0192', '0193', '0194', '0199', '0200', '0201', '0202', '0203', '0206', '0207', '0208', '0209', '0210', '0211', '0212', '0213', '0214', '0219']	62

✅ MS-DRG: RC - Global Per Diem

Identifies cases where most Revenue Codes in Payer MRF data share a single per diem rate. We can then infer this per diem represents a global per diem reimbursement for inpatient services.

Details

Identification

These rates are stored in prod_combined_all in one of the following columns:

rc_global_per_diem_mult_alos

And the following columns contain stats about this inferred rate:

• rc_global_inpatient_per_diem_base_rate
  • rc_global_inpatient_base_rate * medicare_alos = rc_global_per_diem_mult_alos

Methodology

Same as MS-DRG: RC - Global Percentage

✅ MS-DRG: RC - Family Percentage

Identifies RC-based provisions in MRF data (e.g. ['RB', 'OB', 'NICU', 'Psych', 'Rehab', 'Nursery', 'CCU', 'Hospice', 'ICU']) that share a common percentage rate. Then, we use this percentage value to impute rates for all MS-DRGs associated with the RC family.

Details

Identification

These rates are stored in prod_combined_all in one of the following columns:

rc_family_gc_hosp_perc_to_dol
rc_family_gc_hosp_cbsa_perc_to_dol
rc_family_gc_komodo_perc_to_dol
rc_family_gc_komodo_cbsa_perc_to_dol

And the following columns contain stats about this inferred rate:

• rc_family_percentage_rate
  • rc_family_percentage_rate * mrf_gross_charge = rc_family_gc_hosp_perc_to_dol
  • rc_family_percentage_rate * komodo_gross_charge = rc_family_gc_komodo_perc_to_dol
• rc_family_perc
  • the RC family name (e.g. 'RB', 'OB', 'NICU', 'Psych', 'Rehab', 'Nursery', 'CCU', 'Hospice', 'ICU')

Methodology

Step 1: Using core rates, group by negotiated_rate and pull arrays of revenue codes. Filter to where the array cardinality is < 30.

Step 2: Use hive.cld_utils.rc_group_family to map arrays to rc_family.

The mappings of revenue code array to rc_family are shown below. But we need a way to determine whether an array in the data (e.g. ['0171', '0172', '0173']) is sufficiently similar to the array in the crosswalk (e.g. the array for NICU below is ['0172', '0173', '0174', '0179']). We use a similarity metric called Jaccard Index, which is simply the n_intersection divided by the n_union.

For this example:

• array A: ['0171', '0172', '0173']
• array B: ['0172', '0173', '0174', '0179']
• Intersection: ['0172', '0173']
• Union: ['0171', '0172', '0173', '0174', '0179']

So n_intersection = 2 and n_union = 5 and n_intersection / n_union = 2/5 = 40% similar.

rc_family	rev_code_array
RB	['0123', '0110', '0120', '0113', '0121', '0140', '0126', '0111', '0117', '0127', '0129', '0119', '0149', '0136', '0116']
OB	['0112', '0122']
Nursery	['0171', '0170']
Psych	['0124', '0114', '0144']
CCU	['0210', '0212', '0213', '0214']
ICU	['0203', '0204', '0200', '0206', '0207', '0208', '0201', '0202', '0209']
Hospice	['0115', '0125']
Rehab	['0118', '0138', '0128']
NICU	['0172', '0173', '0174', '0179']

To assign a rc_family to an array, we select the rc_family with the highest Jaccard Index score as long as the score is > 25%.

Example

SELECT 
    rc_family, 
    billing_code_array as mrf_codes, 
    rev_code_array as mapping_codes, 
    n_intersection, 
    n_union, 
    jaccard_index, 
    rank
FROM tq_dev.internal_dev_csong_cld_v1_0.tmp_int_imputations_rc_carveouts_2025_02
WHERE payer_id = '76'
AND provider_id = '3670'

In the example below, we would be able to identify row 3 as a RC provision for ICU. We would then use the MRF percentage value associated with this provision to impute rates for all ICU DRGs.

	rc_family	mrf_codes	mapping_codes	n_intersection	n_union	jaccard_index	rank
1	CCU	['0206', '0214']	['0210', '0212', '0213', '0214']	1	5	0.2	1
2	CCU	['0200', '0201', '0202', '0203', '0207', '0208', '0209', '0210', '0211', '0212', '0219']	['0210', '0212', '0213', '0214']	2	13	0.15	2
3	ICU	['0200', '0201', '0202', '0203', '0207', '0208', '0209', '0210', '0211', '0212', '0219']	['0203', '0204', '0200', '0206', '0207', '0208', '0201', '0202', '0209']	7	13	0.54	1
4	ICU	['0206', '0214']	['0203', '0204', '0200', '0206', '0207', '0208', '0201', '0202', '0209']	1	10	0.1	2

✅ MS-DRG: RC - Family Per Diem

Identifies RC-based provisions in MRF data (e.g. ['RB', 'OB', 'NICU', 'Psych', 'Rehab', 'Nursery', 'CCU', 'Hospice', 'ICU']) that share a common per diem rate. Then, we use this per diem value to impute rates for all MS-DRGs associated with the RC family.

Details

Identification

These rates are stored in prod_combined_all in one of the following columns:

rc_family_per_diem_mult_alos

And the following columns contain stats about this inferred rate:

• rc_family_per_diem_rate
  • rc_family_per_diem_rate * medicare_alos = rc_family_per_diem_mult_alos
• rc_family_per_diem
  • the RC family name (e.g. 'RB', 'OB', 'NICU', 'Psych', 'Rehab', 'Nursery', 'CCU', 'Hospice', 'ICU')

Methodology

Same as MS-DRG: RC - Family Percentage

✅ MS-DRG: MRF-Reported Base Rates

✅ HCPCS: RC - Global Percentage

✅ MS-DRG: CSTM-ALL

✅ HCPCS: CSTM-ALL