Derive Territory-Level CPD Variance Between Planned and Actual HCP Calls for BCBU Blue Ball Sales Team

Introduction: The sales excellence team identified differences between planned HCP call activity and the actual daily call performance for the BCBU – Blue Ball team. To support their analysis, we need to calculate the variance in Call Per Day (CPD) at a territory level, using planned call data and actual call execution data. This will help validate territory performance accuracy and detect data quality gaps.

 

Requirements: Develop SQL logic, Read the table “hcp_planned_calls“ and “hcp_actual_calls“. Filter both tables using Business Unit: BCBU, Sales Team Grouping: BLUE BALL, Time Bucket: CQTDW, Classification Type: BLUE BALL ALL PORTFOLIO TARGET. Compute the Planned CPD by planned_total_calls divided by planned_days_in_territory, Actual CPD by actual_total_calls divided by actual_days_in_territory and Variance CPD by Actual_CPD difference of Planned_CPD. Group the final output by Territory, Classification Value, Sales Team Grouping, Time Bucket and Return the CPD variance with one decimal.

 

Final output: Display the Result.

 

Unity Catalog: “hcp_planned_calls“, “hcp_actual_calls“