Healthcare Claims and Denials

• Denial prediction

  before submission, with the true cause and a preventable flag as labels; features are the payer, plan type, facility, clinician, service lines, diagnosis, authorization and charge lag.

• Denial management and root cause dashboards

  denial rate by payer, facility, service and CARC category; preventable share; rework success by category; dollars at risk and recovered.

• Accounts receivable and cash forecasting

  days to remit by payer, days in A/R, pending and not-yet-submitted claims at year end, the late-filing tail.

• Patient responsibility

  deductibles that reset on 1 January and fill during the year (here and with other providers), copays that stand in for the deductible on office and telehealth visits, coinsurance; the patient share of allowed falls from 35% in January to 18% in the autumn.

• Payer contract analysis

  billed against contracted against allowed, contractual adjustments, commercial above Medicare above Medicaid.

• Teaching and demos

  an RCM course, a healthcare analytics portfolio, a Power BI/Tableau dashboard, or a billing-system demo database, with no PHI anywhere.

• claims.cause_coverage_inactiveOne of nine cause_* flags: every problem present on the claim, denied or not.true on 2.5% of rows
• claims.denial_causeThe cause the denial was really for, behind the CARC code the payer sent.10 values: provider not enrolled, payer policy review, coverage inactive…, 88% empty
• claims.preventableWhether the denial could have been prevented at the front end or in coding.true on 80.9% of rows, 88% empty
• encounters.expected_dx_categoryThe diagnosis the visit should have been coded with.6 values: chronic condition, musculoskeletal, preventive or screening…
• encounters.dx_coded_wrongWhether the coded diagnosis departs from it.true on 1.8% of rows
• encounters.needs_prior_authWhether the service needed prior authorisation, next to whether it was obtained.true on 12.9% of rows
• claim_lines.modifier_missingA line that needed a modifier and went without one.true on 1.9% of rows
• claims.recoveredWhether a reworked denial was paid in the end, with the amount recovered.true on 75% of rows, 94.1% empty
• patients.outside_allowed_monthlyWhat the plan allows a month for the patient's care elsewhere; it fills the same deductible, out of the provider's sight.3.54 to 5,872.6, mean 224

• 13% of answered claims were denied in full on first pass; 81% of denials were preventable at the front end or in coding. Billed $53,542,169, allowed $20,950,467, paid by the plans $16,236,948.
• Imaging, procedures and therapy are denied more than office visits; traditional Medicare pays fastest and Medicaid managed care slowest.
• Median charge lag is 4 days with a tail that waits months, which is where late-filing denials come from. Median days in A/R: 24.
• Of reworked denials resolved by year end, 75% were recovered; coding and coordination-of-benefits denials come back far more often than late filing.
• At the cut-off (31 December 2025) 7,380 claims were still with the payer and 1,972 not yet submitted.

• every key resolves and every primary key is unique; one claim per visit; each claim's patient, plan, clinician and facility are the visit's; a patient's plan type is their plan's;
• every service suits its visit type; line charges are base rate x facility markup x units and contracted amounts base rate x plan fee level x units; claim totals re-add from their lines;
• allowed, deductible, copay, coinsurance, patient share and paid re-add exactly; the deductible accumulator is rebuilt in service order from 1 January (with the care the patient had elsewhere) and no patient pays past their plan's deductible; on a copay plan an office or telehealth visit costs the copay alone;
• every cause flag is recomputed from the rows it comes from (coverage end date, enrollment date, charge lag against the plan's limit, authorization, the coded diagnosis); every denial's cause is present on its claim and its CARC code and category match it; every ICD-10-CM code belongs to its diagnosis group;
• no visit before its clinician joined; nothing that happened is dated after the cut-off, and a claim not yet answered carries no answer; rework after the answer; days in A/R recompute.

• claim_linesService lines: units, modifier present or missing, charge, contracted and allowed amounts, line-level denials161,097 rows · 17 cols
• claimsOne claim per visit through its whole life: charge lag, submission, every cause that was present, the denial with CARC code and category, remittance, deductible, copay, coinsurance, paid, adjustments, rework and recovery, days in A/R85,659 rows · 54 cols
• encountersEvery visit: type, time (clinic hours or around the clock), the diagnosis expected and the ICD-10-CM code actually coded85,659 rows · 15 cols
• patientsAge, sex, plan, member id, annual deductible, what the plan allows a month for their care with other providers (it fills the same deductible), the date their coverage ended if it did, and other coverage24,000 rows · 14 cols
• providersClinicians by facility and specialty; the ones who joined during the year, with the date and the day their payer enrollment took effect140 rows · 9 cols
• services36 billable services with an illustrative base rate, whether they bill in timed units and whether they need a modifier (own codes, not CPT)36 rows · 7 cols
• payersTen health plans across commercial PPO and HMO, Medicare Advantage, traditional Medicare and Medicaid managed care: timely-filing limit, fee level against a base rate, days to pay, prior-auth policy, coinsurance and copay10 rows · 10 cols
• facilitiesClinics, hospital outpatient departments, an emergency department, imaging and therapy sites: chargemaster markup, front-desk registration error rate, billing backlog8 rows · 8 cols

Want it bigger, in another setting, or with your own columns? Have us build it or make it in Studio.