Healthcare Claims Data Analysis

Analyze medical and pharmacy claims for real-world evidence (RWE) and healthcare analytics.

When to Use This Skill

• Analyzing medical claims (837P/837I) or pharmacy claims
• Building patient cohorts from administrative data
• Calculating healthcare utilization metrics
• Conducting treatment pattern analysis
• Real-world evidence studies

Claims Data Overview

Medical Claims (837)

Field	Description	Example
claim_id	Unique claim identifier	CLM123456
member_id	Patient identifier	MBR789
service_date	Date of service	2024-01-15
diagnosis_codes	ICD-10 codes (dx1-dx12)	E11.9, I10
procedure_code	CPT/HCPCS code	99213
place_of_service	Service location code	11 (Office)
allowed_amount	Plan allowed amount	150.00
paid_amount	Plan paid amount	120.00
provider_npi	Rendering provider	1234567890

Pharmacy Claims

Field	Description	Example
claim_id	Rx claim identifier	RX456789
member_id	Patient identifier	MBR789
fill_date	Prescription fill date	2024-01-15
ndc	National Drug Code	00002-3227-30
drug_name	Medication name	Metformin 500mg
quantity	Quantity dispensed	60
days_supply	Days supply	30
ingredient_cost	Drug cost	45.00
pharmacy_npi	Dispensing pharmacy	9876543210

Quick Start

Load Claims to Snowflake

-- Medical claims table
CREATE OR REPLACE TABLE CLAIMS.MEDICAL (
    claim_id VARCHAR,
    member_id VARCHAR,
    service_from_date DATE,
    service_to_date DATE,
    admission_date DATE,
    discharge_date DATE,
    claim_type VARCHAR,  -- P=Professional, I=Institutional
    place_of_service VARCHAR,
    dx1 VARCHAR, dx2 VARCHAR, dx3 VARCHAR, dx4 VARCHAR,
    dx5 VARCHAR, dx6 VARCHAR, dx7 VARCHAR, dx8 VARCHAR,
    proc_code VARCHAR,
    proc_modifier VARCHAR,
    revenue_code VARCHAR,
    drg_code VARCHAR,
    provider_npi VARCHAR,
    facility_npi VARCHAR,
    billed_amount FLOAT,
    allowed_amount FLOAT,
    paid_amount FLOAT,
    member_liability FLOAT,
    _loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Pharmacy claims table
CREATE OR REPLACE TABLE CLAIMS.PHARMACY (
    claim_id VARCHAR,
    member_id VARCHAR,
    fill_date DATE,
    ndc VARCHAR,
    gpi VARCHAR,
    drug_name VARCHAR,
    generic_name VARCHAR,
    brand_generic_ind VARCHAR,
    quantity FLOAT,
    days_supply INTEGER,
    refill_number INTEGER,
    prescriber_npi VARCHAR,
    pharmacy_npi VARCHAR,
    ingredient_cost FLOAT,
    dispensing_fee FLOAT,
    plan_paid FLOAT,
    member_paid FLOAT,
    _loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Member eligibility
CREATE OR REPLACE TABLE CLAIMS.ELIGIBILITY (
    member_id VARCHAR,
    eff_date DATE,
    term_date DATE,
    lob VARCHAR,  -- Line of business
    plan_type VARCHAR,
    gender VARCHAR,
    birth_date DATE,
    zip_code VARCHAR,
    state VARCHAR
);

Common Analyses

Patient Cohort Building

-- Diabetes cohort: 2+ diagnoses or 1 Rx
WITH diabetes_dx AS (
    SELECT DISTINCT member_id
    FROM CLAIMS.MEDICAL
    WHERE dx1 LIKE 'E11%' OR dx2 LIKE 'E11%' OR dx3 LIKE 'E11%'
    GROUP BY member_id
    HAVING COUNT(DISTINCT claim_id) >= 2
),
diabetes_rx AS (
    SELECT DISTINCT member_id
    FROM CLAIMS.PHARMACY
    WHERE gpi LIKE '27%'  -- Antidiabetics
),
diabetes_cohort AS (
    SELECT member_id FROM diabetes_dx
    UNION
    SELECT member_id FROM diabetes_rx
)
SELECT 
    dc.member_id,
    e.gender,
    DATEDIFF('year', e.birth_date, CURRENT_DATE) AS age,
    e.state
FROM diabetes_cohort dc
JOIN CLAIMS.ELIGIBILITY e ON dc.member_id = e.member_id
WHERE e.term_date >= CURRENT_DATE;

Healthcare Utilization Metrics

-- PMPM (Per Member Per Month) cost
WITH member_months AS (
    SELECT 
        member_id,
        DATEDIFF('month', eff_date, LEAST(term_date, CURRENT_DATE)) + 1 AS months_enrolled
    FROM CLAIMS.ELIGIBILITY
    WHERE eff_date <= CURRENT_DATE
),
member_costs AS (
    SELECT 
        member_id,
        SUM(paid_amount) AS total_medical
    FROM CLAIMS.MEDICAL
    WHERE service_from_date >= '2024-01-01'
    GROUP BY member_id
)
SELECT 
    AVG(mc.total_medical / mm.months_enrolled) AS medical_pmpm,
    SUM(mc.total_medical) / SUM(mm.months_enrolled) AS aggregate_pmpm
FROM member_costs mc
JOIN member_months mm ON mc.member_id = mm.member_id;

Treatment Patterns

-- First-line therapy analysis
WITH first_rx AS (
    SELECT 
        member_id,
        drug_name,
        fill_date,
        ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY fill_date) AS rx_order
    FROM CLAIMS.PHARMACY
    WHERE gpi LIKE '27%'  -- Antidiabetics
)
SELECT 
    drug_name,
    COUNT(*) AS patient_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 1) AS pct
FROM first_rx
WHERE rx_order = 1
GROUP BY drug_name
ORDER BY patient_count DESC;

Medication Adherence (PDC)

-- Proportion of Days Covered
WITH rx_fills AS (
    SELECT 
        member_id,
        fill_date,
        days_supply,
        LEAD(fill_date) OVER (PARTITION BY member_id ORDER BY fill_date) AS next_fill
    FROM CLAIMS.PHARMACY
    WHERE gpi LIKE '2720%'  -- Metformin
      AND fill_date >= '2024-01-01'
),
covered_days AS (
    SELECT 
        member_id,
        SUM(LEAST(
            days_supply,
            COALESCE(DATEDIFF('day', fill_date, next_fill), days_supply)
        )) AS days_covered,
        DATEDIFF('day', MIN(fill_date), MAX(fill_date)) + 
            MAX(days_supply) AS observation_period
    FROM rx_fills
    GROUP BY member_id
)
SELECT 
    member_id,
    days_covered,
    observation_period,
    ROUND(days_covered * 100.0 / observation_period, 1) AS pdc,
    CASE WHEN days_covered * 100.0 / observation_period >= 80 THEN 'Adherent' 
         ELSE 'Non-Adherent' END AS adherence_status
FROM covered_days
WHERE observation_period >= 90;

Episode of Care

-- Group claims into episodes (30-day gap = new episode)
WITH claims_sorted AS (
    SELECT 
        member_id,
        service_from_date,
        paid_amount,
        LAG(service_from_date) OVER (PARTITION BY member_id ORDER BY service_from_date) AS prev_date
    FROM CLAIMS.MEDICAL
    WHERE dx1 LIKE 'M54%'  -- Back pain
),
episode_flags AS (
    SELECT 
        *,
        CASE WHEN DATEDIFF('day', prev_date, service_from_date) > 30 
             OR prev_date IS NULL THEN 1 ELSE 0 END AS new_episode
    FROM claims_sorted
),
episodes AS (
    SELECT 
        *,
        SUM(new_episode) OVER (PARTITION BY member_id ORDER BY service_from_date) AS episode_num
    FROM episode_flags
)
SELECT 
    member_id,
    episode_num,
    MIN(service_from_date) AS episode_start,
    MAX(service_from_date) AS episode_end,
    COUNT(*) AS claim_count,
    SUM(paid_amount) AS episode_cost
FROM episodes
GROUP BY member_id, episode_num;

Place of Service Codes

Code	Description
11	Office
21	Inpatient Hospital
22	Outpatient Hospital
23	Emergency Room
31	Skilled Nursing Facility
81	Independent Lab

GPI Drug Classification

GPI Prefix	Therapeutic Class
27	Antidiabetics
39	Cardiovascular
40	Antihypertensives
44	Antihyperlipidemics
57	Psychotherapeutics
66	Analgesics

HEDIS-Style Measures

-- Diabetes A1c Testing Rate
WITH diabetic_members AS (
    SELECT DISTINCT member_id
    FROM CLAIMS.MEDICAL
    WHERE dx1 LIKE 'E11%' OR dx2 LIKE 'E11%'
),
a1c_tests AS (
    SELECT DISTINCT member_id
    FROM CLAIMS.MEDICAL
    WHERE proc_code IN ('83036', '83037')  -- A1c CPT codes
      AND service_from_date >= DATEADD('year', -1, CURRENT_DATE)
)
SELECT 
    COUNT(DISTINCT a.member_id) AS tested,
    COUNT(DISTINCT d.member_id) AS diabetic,
    ROUND(100.0 * COUNT(DISTINCT a.member_id) / COUNT(DISTINCT d.member_id), 1) AS testing_rate
FROM diabetic_members d
LEFT JOIN a1c_tests a ON d.member_id = a.member_id;

Best Practices

1. Continuous enrollment: Require minimum enrollment period for studies
2. Washout periods: Look back for prior conditions/treatments
3. Claims lag: Allow 60-90 days for claims runout
4. Validate cohorts: Cross-check Dx and Rx definitions
5. Adjust for confounders: Age, gender, comorbidities

Reference Files

• references/place_of_service.md - POS codes
• references/gpi_classification.md - Drug classification
• references/hedis_measures.md - HEDIS specifications

Requirements

pandas>=2.0.0
snowflake-connector-python>=3.0.0

Evidence Grounding: Clinical Trials CKE

Invoke $cke-clinical-trials when trial registry evidence enhances claims analysis:

• Feasibility analysis: search for trials by condition to match against claims-based cohort characteristics
• RWE benchmarking: compare real-world treatment patterns against clinical trial protocols
• Site selection support: identify active trial sites and match with claims-based patient geographies

See $cke-clinical-trials for setup, query patterns, and the feasibility analysis SQL pattern.