Healthcare Payment Optimization Engine

Reducing transaction costs and improving provider experience using SQL Stored Procedures and Power BI.

SQL Server Stored Procedures Power BI (DAX) Cost Modeling

The Business Problem

Scenario: A healthcare payment processor was experiencing rising operational costs. Too many providers were requesting paper checks ($3.50/txn) instead of digital EFTs ($0.10/txn).

The Objective: Identify the specific "High Cost" providers and quantify the potential savings of converting them to digital payments, while ensuring payment latency (Customer Experience) remained low.

Step 1: SQL Automation (Stored Procedures)

To automate the identification of "High Risk" providers, I built a Stored Procedure in SQL Server. This replaces manual Excel reporting with a single-click solution.

This script calculates the Cost to Serve and flags providers with poor Customer Experience scores (Latency > 10 Days).

CREATE PROCEDURE Identify_High_Cost_Providers AS BEGIN SELECT Provider, Total_OpEx, CASE WHEN Avg_Latency_Days > 10 THEN 'High Risk (Slow Payment)' ELSE 'Healthy' END AS Customer_Experience_Score FROM vw_Provider_Scorecard WHERE Total_OpEx > 500 ORDER BY Total_OpEx DESC; END;

Step 2: The Executive Dashboard

I designed a Power BI dashboard to visualize the Measurement Framework. Using DAX, I calculated key performance indicators to track Digital Adoption and Financial Impact.

45% Digital Adoption Rate
$15,400 Potential OpEx Savings
12 Days Avg Days to Pay
Power BI Dashboard

Figure 1: The Payment Optimization Dashboard identifying $15k in immediate savings opportunities.

The Logic: DAX Measurement Framework

To ensure accuracy, I built dynamic measures using DAX rather than relying on simple column aggregations.

Potential Savings = CALCULATE( COUNTROWS(Transactions), Transactions[Method] = "Check" ) * 3.40

Business Impact

← Back to Portfolio