Analyzing HCP Interactions: Revenue Conversion, Non-Conversion, and Overall Performance Metrics

Introduction: This analysis focuses on tracking the effectiveness of various interactions (such as calls, emails, meetings) in terms of converting into revenue. It aims to differentiate interactions that lead to revenue conversion from those that do not, offering insights into which interaction types yield the best outcomes. By examining these patterns, organizations can optimize their strategies for engaging HCPs, improving conversion rates, and driving overall sales performance.

 

Requirements: Read the table 'purgo_playground.hcp_overall_performance'.

 

Add Revenue Conversion Flag to find the count: Revenue_Converted_Flag, is added with values are set to 1 if the interaction resulted in revenue conversion (Revenue_Converted = "Yes"). 0 otherwise (Revenue_Converted = "No").

 

Each HCP Performance: Analyze performance for each interaction type for each “HCP_ID” with count of interactions (calls, emails, meetings, etc.), sum of "Sales_Amount" from each interaction type, number of interactions that converted to revenue, number of interactions that did not convert to revenue and also percentage of interactions that led to revenue conversion. Order by each HCP_ID and Interaction_Type's.

 

HCP Overall Performance:

 

For overall performance group by each HCP_ID with same logic and ordered by Total Sales to prioritize HCPs with the highest sales.

 

Write pyspark code without syntax error.

 

Unity Catalog: purgo_playground.hcp_overall_performance