CSS for insurance claims and customer 360 data

Information: CSS (Customer Satisfaction Score) is a metric used to gauge customer satisfaction with a product, service, or interaction. By knowing dissatisfied customer we can come with many business plans to make them happy which will eventually prevent them from churning.

 

 

 

Requirement: Develop a PySpark code to calculate the Customer Satisfaction Score (CSS) for each patient using the health_insurance_claims table. The CSS calculation is based on the following metrics and assigned weights:

 

Billed vs. Allowed Amount: ("Allowed_Amount"/ "Billed_Amount") 100, Higher percentages indicate better satisfaction. weight=25%

 

Patient Paid: ("Patient_Paid" / "Billed_Amount") 100, Lower percentages are better as they indicate less out-of-pocket expenses. weight=25%

 

* Service time: Find the day difference between column "Service_Date" value and column "service_requested_date" value. Higher the difference lower the CSS. weight = 20%

 

* Product count: Use the " purgo_playground.customer_360_raw" table to get column purchase_history column (id column in customer_360_raw and "Patient_ID" in health_issuerance_claims), calculate the number of products (products are separated by commas) . Higher the count higher the CSS. Weight:20%

 

 

 CSS formula = (0.25Billed vs. Allowed Amount) -(0.25Patient Paid) - (0.2Service time)+(0.3Product count) )

 

After calculating CSS, normalise the CSS to bring it under the range (0 to 10) and display the Dataframe

 

Unity Catalog table: purgo_playground.health_insurance_claims