Built the way a centralized revenue cycle analytics team works: clean the data once, define each metric once, parameterize by the axes every hospital shares, so the same logic reruns across client engagements. Five core metrics across denials, coding, charge capture, and managed care, end to end in SQL.
A centralized revenue cycle analytics team is a shared service. Consultants on client engagements come to it and ask for a denial breakdown, an AR aging view, a collection benchmark. The job is not to write a bespoke script for each ask; it is to build a library of analyses that are parameterized, documented, and rerunnable, so the same denial-rate logic produces a defensible number for client A on Monday and client B on Friday with only the inputs changing. This project is built to that standard: a trusted data layer, a metric dictionary, and a segmentation layer parameterized by the two axes every hospital shares, payer and service line. The deliverable is judged less on getting a number and more on whether someone else can trust and reproduce it.
Real claims data is PHI and cannot be shared in a public portfolio, so the dataset is synthetic: 5,000 claims generated by a seeded Python script. It is modeled on real revenue cycle structure: payer-specific contract rates, denial behavior that varies by payer, charges inflated above allowed amounts, and realistic data-quality defects (inconsistent payer spellings, missing pay dates, duplicate claims, impossible dates). Because the generating process is known, every computed metric is validated by reconciling it back to the parameters it was built from. The by-payer denial rates, for instance, recover the encoded Medicaid-high, Medicare-low ordering exactly. Nothing in the repo ships data files; everything regenerates from the seeded script.
The pipeline runs in the order a consultant's analyst would work a fresh client extract: get to know the file, check its quality, clean it once into a trusted layer, lock the grain and definitions, then compute. Metrics never touch raw tables; they read a single cleaned view, so every number reconciles.
The overall denial rate is 12.2% by claim count and 12.3% by dollars. Segmented by payer, it recovers the real pattern the data was built on: Medicaid 18.6% at the top, Medicare 7.5% at the bottom, Commercial 12.9% in between. The reason mix is where it becomes actionable. Rolled up to category, about 60% of denials, and 60% of denied dollars, are preventable front-end errors: authorization, eligibility, and coding. That is the one sentence that goes in a client deck. Computing both count and dollar weightings matters: authorization and timely-filing denials skew to larger claims, so a recovery-dollar priority differs from a frequency priority.
Net collection rate, payments divided by the contractually allowed amount, is 91%. Gross collection, payments divided by billed charges, is 45%. The gap is not a collection failure; it is the contractual adjustment, because charges are inflated list prices nobody pays in full. The clearest proof sits in the by-payer cut: net collection is flat at ~91% across every payer (collection performance is uniform), while gross swings from 25% to 82%, purely tracking each payer's contract rate. Reporting gross by payer would tell a client they only collect 25% from Medicaid, which is flat wrong. They collect 91% of what Medicaid allows; the 25% just reflects Medicaid's steep contractual discount. Net separates performance from contract terms; gross conflates them.
Days in AR is really two questions. Throughput, on paid claims, runs a median of 35 days and a mean of 39 (mean above median confirms the right skew, which is why the median leads). Aging, on open claims, is the risk view: 78.5% of open AR dollars sit in the 90+ bucket, the danger zone where receivables turn uncollectible. By payer the throughput ordering again recovers the build: Medicaid slow (52 days), Medicare fast (31). Splitting throughput from aging, instead of collapsing both into one average, is what reading days in AR correctly actually means.
The clean claim rate, claims that adjudicate correctly on first submission with no rework, is 83.8%. On its own that is a front-end efficiency number. Stratified against denials, it becomes the spine of the whole analysis: clean claims are denied 3.9% of the time; claims that needed rework are denied 55.7% of the time, roughly 14 times higher. Front-end claim quality is not just one metric among five; it is the upstream lever that drives denials, AR aging, and ultimately collected cash. Clean means never reworked, which is stricter than not-denied, and it is the leading indicator that predicts the downstream failures.
All five metrics are consolidated into a single view at the payer × service-line grain, storing additive components (counts, dollar sums) rather than only percentages, so any rollup is a re-summation, never an average of rates. Its rollups reconcile to the standalone metric files to the decimal, which is the proof the consolidation is correct. This is the artifact a consultant reruns on a new client's extract: same SQL, new data, full scorecard plus any cut.
| Payer type | Denial rate | Net collection | Clean claim | Avg days to pay |
|---|---|---|---|---|
| Medicaid | 18.6% | 90.7% | 80.0% | 52 |
| Commercial | 12.9% | 91.1% | 83.2% | 38 |
| Self-Pay | 7.9% | 90.8% | 87.9% | 56 |
| Medicare | 7.5% | 90.8% | 87.1% | 31 |
The metrics are not five separate numbers; they are one chain from front-end cause to bottom-line effect. Clean claim rate (83.8%) determines how many claims get reworked, and reworked claims deny at 55.7% versus 3.9% for clean. That feeds the denial rate (12.2%, ~60% preventable). Denied and reworked claims then sit unpaid, which is why 78.5% of open AR dollars are 90+ days aged. What survives lands as net collection (91% of contractually owed). Read top to bottom, the leakage traces straight back to step one: front-end discipline is the single upstream lever, and pulling it cascades down denials, AR aging, and cash.
A landing table loads the raw extract permissively, so seeded defects survive to be detected rather than rejected at load. A trusted layer of views then standardizes payers by normalized pattern rules (which generalize to a new client's unseen spellings), de-duplicates with a window function, quarantines corrupt rows behind a metric gate, and derives timing and bucket helpers once. Each metric is a short, documented, parameterized query reading that one trusted view, using conditional aggregation for rates, window functions for share-of-total and a hand-built median (SQLite has no percentile function), and a null-guard idiom for parameters. Correctness is established by reconciliation throughout: data-quality checks against seeded ground truth, metric values against the known generating process, and the segmentation rollups against the standalone metric files.
SQLite as a zero-install engine with SQL written in portable ANSI style so the logic lifts to SQL Server, Snowflake, or Postgres. Python (NumPy, pandas) for the seeded synthetic data generator. An Excel pivot deliverable mirroring the five metrics is planned as a follow-on.
Interested in revenue-cycle analytics, denials and collections work, or healthcare consulting?
Get in touch