Skip to main content

Org-Level Accuracy Metrics

In general - it's common to see rate quality to vary at the MRF-level: e.g. a provider submits a bad file so most of their rates are probably bad. In this doc, we show payer-level and provider-level accuracy metrics, by bill type (Inpatient, Outpatient) for hospitals.

Contents:​

  1. Google Sheets
  2. Payer Heatmap
  3. Hospital Heatmap (Sample CBSA)
  4. SQL Queries

Google Sheets​

Payer Heatmap​

warning

The outlier rates for IP can be quite high. Most of these outliers are because the posted IP rates are below 90% of medicare.

alt text

Hospital Heatmap (Sample CBSA: Houston)​

alt text

SQL​

-- Payer-level accuracy m
SELECT 
    payer_id,
    payer_name,
    bill_type,
    100.0000 * SUM(
        CASE WHEN canonical_rate_score = 1 THEN 1 ELSE 0 END
    ) / COUNT(*) as pct_outlier,
    100.0000 * SUM(
        CASE WHEN canonical_rate_score = 5 THEN 1 ELSE 0 END
    ) / COUNT(*) as pct_validated,
    100.0000 * SUM(
        CASE WHEN canonical_rate_score = 5 THEN 1 ELSE 0 END
    ) / NULLIF(SUM(
        CASE WHEN best_payer_rate IS NOT NULL AND best_hospital_rate IS NOT NULL 
        THEN 1 ELSE 0 END
    ), 0) as pct_validated_where_both_reported,
    AVG(canonical_rate_score) as avg_score,
    STDDEV(canonical_rate_score) as stddev_score,
    count(*) as n_rates
FROM tq_dev.internal_dev_csong_cld_v2_0_0.prod_combined_abridged
WHERE canonical_rate_score > 0
AND provider_type LIKE '%Hospital%'
GROUP BY 1,2,3
ORDER BY 1,2,3
-- Hospital-level accuracy metrics
SELECT 
    provider_id,
    provider_name,
    health_system_name,
    state,
    cbsa_name,
    bill_type,
    100.0000 * SUM(
        CASE WHEN canonical_rate_score = 1 THEN 1 ELSE 0 END
    ) / COUNT(*) as pct_outlier,
    100.0000 * SUM(
        CASE WHEN canonical_rate_score = 5 THEN 1 ELSE 0 END
    ) / COUNT(*) as pct_validated,
    100.0000 * SUM(
        CASE WHEN canonical_rate_score = 5 THEN 1 ELSE 0 END
    ) / NULLIF(SUM(
        CASE WHEN best_payer_rate IS NOT NULL AND best_hospital_rate IS NOT NULL 
        THEN 1 ELSE 0 END
    ), 0) as pct_validated_where_both_reported,
    AVG(canonical_rate_score) as avg_score,
    STDDEV(canonical_rate_score) as stddev_score,
    count(*) as n_rates
FROM tq_dev.internal_dev_csong_cld_v2_0_0.prod_combined_abridged
WHERE canonical_rate_score > 0
AND provider_type LIKE '%Hospital%'
GROUP BY 1,2,3,4,5,6
HAVING COUNT(*) > 500
ORDER BY 1,2,3,4,5,6
On this page: