Customer_360 - Calculate and Compare “Calls Per Day (CPD)” Across FES and Execution Sapphire Sources for ONC SAPPHIRE OBU Team

Introduction: To identify alignment of field effectiveness across both systems by ensuring accurate aggregation at the ONC SAPPHIRE business unit level for the CQTDW (Current Quarter To Date Week) reporting period.

 

Requirements: Develop SQL Logic, read the data “c360_sales_team_interaction_report”,”c360_field_report_rep_territory_pivot_obu_acl” . Compare the field team’s average Calls Per Day (CPD) from both FES and Execution Sapphire datasets. Filter the condition, business_unit_code = 'OBU', field_force_name = 'ONC SAPPHIRE', classification_type = 'ONC SAPPHIRE ALL PORTFORLIA_TARGET' (FES) / ONC SAPPHIRE ALL PORTFOLIO_TARGET (Execution), time_bucket_id = 'CQTDW', sales_team_grouping = 'ONC SAPPHIRE', interaction_channel = 'ALL', identify the metrics Calculated as From FES: ROUND(SUM(rep_calls_hcp_calls_per_day),1), From Execution Sapphire: ROUND(SUM(avg_total_prescriber_calls_per_day),1)

 

Final Output: Provide the Results with column “CPD“, “Report_name“

 

Unity Catalog: “c360_sales_team_interaction_report”, “c360_field_report_rep_territory_pivot_obu_acl”