Customer_360 - Sales Team Call Performance & Interaction Classification Aggregation

Introduction: Commercial analytics team needs insights into sales team performance and HCP interactions across brands and field force groups. This pipeline will aggregate rep call activity by time periods and classify interactions. The goal is to calculate total calls and target-specific calls for CBU field force and enrich raw interactions.

Requirements: Develop SQL logic, Read call interaction data fromc360_sales_team_brand_interactions_reporting_us. Filter records where br_field_force_name = 'CBU', product_name = 'ALL', interaction_channel = 'ALL', time_bucket_id IN (CQTDW, PQW1 … PQW7), target_category NOT IN ('High Targets','Other Targets'), Calculate Total Calls Per Day (sum of Rep_Calls_Hcp_Calls_Per_Day), Target Calls Per Day (only where target_category = 'TARGETS') Group by br_field_force_name, time_bucket_id.

 

Final Output: Show the results for the column “br_field_force_name“, “time_bucket_id“,”total_calls_per_day”, “target_calls_per_day“.

 

Unity Catalog: c360_sales_team_brand_interactions_reporting_us