Revenue Leakage Dashboard

Cross-Module Analytics

Tracks the patient/service lifecycle from clinical diagnosis through revenue collection. Each stage transition is a potential leakage point where cases drop out of the revenue pipeline. This funnel spans clinical modules (EHR, CPOE, RIS/LIS) through RCM (Billing & Claims, Patient Access).

Service Lifecycle Funnel

Cases remaining at each stage with associated revenue

Stage-by-Stage Leakage

Where cases and revenue are lost in the pipeline

Transition	Cases Lost	Revenue Lost	Likely Cause

Funnel Stage → HIS Module Mapping

Each funnel stage maps to specific HIS modules where the data originates

Diagnosed

EHR & Patient Mgmt

Ordered

CPOE

Approved

Policy & Contract Mgmt

Booked

Scheduling

Delivered

RIS / LIS / PIS

Charged

Billing & Claims

Collected

Billing & Claims / Patient Access

Developer Reference

Implementation Guide

How to build the revenue-leakage pipeline in production using the actual HIS table schemas defined in the module data specifications.

1. Data Pipeline Architecture

Every table in the HIS links back to two shared keys: patient_id (owned by ehr-patient-mgmt) and encounter_id (owned by scheduling). The funnel is a LEFT JOIN chain starting from diagnosed problems and progressing through orders, authorizations, appointments, results, charges, and payments. Each LEFT JOIN naturally reduces the row count — cases that never reached a downstream stage simply return NULL, making drop-off detection trivial.

Join Order	Table	Join Key	Module
1 — anchor	encounters	encounter_id	Scheduling
2 — INNER	patient_problems	encounter_id	EHR & Patient Mgmt
3 — LEFT	imaging_orders / laboratory_orders	encounter_id	CPOE
4 — LEFT	prior_authorizations	encounter_id	Policy & Contract Mgmt
5 — LEFT	appointments	encounter_id	Scheduling
6 — LEFT	radiology_exams / lab_results	order_id	RIS / LIS
7 — LEFT	charges	encounter_id	Billing & Claims
8 — LEFT	claims + payment_allocations	charge_id	Billing & Claims

2. SQL: Funnel Staging View

This view joins across module tables to compute the funnel. Each row represents one diagnosed problem within an encounter, with columns indicating the furthest stage reached and the expected tariff from the charge master.

CREATE OR REPLACE VIEW vw_revenue_funnel AS
SELECT
    enc.encounter_id,
    enc.patient_id,
    enc.facility_id,
    enc.encounter_date,

    -- Stage 1: Diagnosed
    pp.problem_id,
    pp.icd_code,

    -- Stage 2: Ordered (imaging OR lab)
    COALESCE(io.order_id, lo.order_id)        AS order_id,
    CASE WHEN io.order_id IS NOT NULL THEN 'imaging'
         WHEN lo.order_id IS NOT NULL THEN 'laboratory'
    END                                        AS order_type,

    -- Stage 3: Approved
    pa.authorization_id,
    pa.auth_status,

    -- Stage 4: Booked
    appt.appointment_id,
    appt.appointment_status,

    -- Stage 5: Delivered (result exists)
    COALESCE(rx.exam_id, lr.result_id)        AS result_id,

    -- Stage 6: Charged
    ch.charge_id,
    ch.charge_amount,

    -- Stage 7: Collected
    cl.claim_id,
    cl.claim_status,
    pay.payment_amount,

    -- Derived: furthest stage reached
    CASE
        WHEN pay.payment_amount > 0              THEN 'Collected'
        WHEN ch.charge_id IS NOT NULL            THEN 'Charged'
        WHEN COALESCE(rx.exam_id, lr.result_id) IS NOT NULL THEN 'Delivered'
        WHEN appt.appointment_id IS NOT NULL      THEN 'Booked'
        WHEN pa.authorization_id IS NOT NULL      THEN 'Approved'
        WHEN COALESCE(io.order_id, lo.order_id) IS NOT NULL THEN 'Ordered'
        ELSE                                         'Diagnosed'
    END                                        AS reached_stage,

    -- Expected revenue from charge master
    COALESCE(cm.standard_rate, cr.contracted_rate, 0) AS expected_tariff

FROM encounters enc
INNER JOIN patient_problems pp
    ON pp.encounter_id = enc.encounter_id
    AND pp.status = 'active'

LEFT JOIN imaging_orders io
    ON io.encounter_id = enc.encounter_id
    AND io.order_status NOT IN ('draft', 'cancelled')

LEFT JOIN laboratory_orders lo
    ON lo.encounter_id = enc.encounter_id
    AND lo.order_status NOT IN ('draft', 'cancelled')
    AND io.order_id IS NULL          -- prefer imaging if both exist

LEFT JOIN prior_authorizations pa
    ON pa.encounter_id = enc.encounter_id
    AND pa.auth_status = 'approved'

LEFT JOIN appointments appt
    ON appt.encounter_id = enc.encounter_id
    AND appt.appointment_status NOT IN ('cancelled', 'no_show')

LEFT JOIN radiology_exams rx
    ON rx.order_id = io.order_id
    AND rx.exam_status = 'completed'

LEFT JOIN lab_results lr
    ON lr.order_id = lo.order_id
    AND lr.result_status = 'final'

LEFT JOIN charges ch
    ON ch.encounter_id = enc.encounter_id
    AND ch.void_flag = FALSE

LEFT JOIN claims cl
    ON cl.charge_id = ch.charge_id
    AND cl.claim_status NOT IN ('voided', 'duplicate')

LEFT JOIN payment_allocations pay
    ON pay.claim_id = cl.claim_id
    AND pay.payment_amount > 0

-- Tariff lookup: charge master first, then contract rate
LEFT JOIN charge_master cm
    ON cm.cpt_code = COALESCE(io.cpt_code, lo.cpt_code)

LEFT JOIN contract_rates cr
    ON cr.cpt_code = COALESCE(io.cpt_code, lo.cpt_code)
    AND cr.payer_id = pa.payer_id;

3. SQL: Leakage Summary Query

Query the staging view to compute drop-off counts and revenue lost at each transition. This powers the dashboard table above.

WITH stage_ord AS (
    SELECT UNNEST(ARRAY[
        'Diagnosed','Ordered','Approved','Booked',
        'Delivered','Charged','Collected'
    ]) AS stage,
    GENERATE_SERIES(1, 7) AS seq
),
funnel_counts AS (
    SELECT
        so.stage,
        so.seq,
        COUNT(f.encounter_id) FILTER (
            WHERE so_reached.seq >= so.seq
        ) AS cases_at_stage,
        SUM(f.expected_tariff) FILTER (
            WHERE so_reached.seq >= so.seq
        ) AS revenue_at_stage
    FROM stage_ord so
    CROSS JOIN vw_revenue_funnel f
    JOIN stage_ord so_reached
        ON so_reached.stage = f.reached_stage
    GROUP BY so.stage, so.seq
)
SELECT
    cur.stage                                       AS stage_from,
    nxt.stage                                       AS stage_to,
    cur.cases_at_stage - nxt.cases_at_stage         AS cases_lost,
    cur.revenue_at_stage - nxt.revenue_at_stage     AS revenue_lost,
    ROUND(
      (cur.cases_at_stage - nxt.cases_at_stage)::numeric
      / NULLIF(cur.cases_at_stage, 0) * 100, 1
    )                                               AS drop_pct
FROM funnel_counts cur
JOIN funnel_counts nxt ON nxt.seq = cur.seq + 1
ORDER BY cur.seq;

4. Scheduling & Refresh

The staging view can be materialized for dashboard performance. Two refresh strategies are recommended:

Option A — Scheduled (simple)

Convert to MATERIALIZED VIEW and refresh hourly via pg_cron: REFRESH MATERIALIZED VIEW CONCURRENTLY vw_revenue_funnel;

Option B — Event-driven (real-time)

Trigger a refresh when downstream HL7 ADT/ORM/DFT messages or FHIR Encounter/Claim status-change webhooks arrive. Lower latency but requires an event bus (e.g., RabbitMQ or NATS).

Indexing note: Ensure encounter_id is indexed on every stage table (patient_problems, imaging_orders, appointments, charges, etc.) to keep the LEFT JOIN chain performant at scale.

5. Stage-by-Table Reference

Quick reference mapping each funnel stage to its source table, primary key, status column, and the predicate that marks a case as having reached that stage.

Stage	Source Table	PK	Status Column	Completion Predicate
Diagnosed	patient_problems	problem_id	status	= 'active'
Ordered	imaging_orders / laboratory_orders	order_id	order_status	NOT IN ('draft', 'cancelled')
Approved	prior_authorizations	authorization_id	auth_status	= 'approved'
Booked	appointments	appointment_id	appointment_status	NOT IN ('cancelled', 'no_show')
Delivered	radiology_exams / lab_results	exam_id / result_id	exam_status / result_status	= 'completed' / 'final'
Charged	charges	charge_id	void_flag	= FALSE
Collected	claims + payment_allocations	claim_id	payment_amount	> 0