Customer_360 - Derive Interaction-Level KPI Aggregations with Classification and Sales Team Mapping

Introduction: Derive aggregated interaction-level KPIs by joining customer interaction data from c360_customer_match_and_merge with the sales team classification mapping table. To provides the aggregated interaction metrics such as valid calls, emails, samples, speaker programs, and priority calls grouped by key commercial attributes - business unit, field force, product, and therapeutic area. This derived dataset will serve as a foundation for multiple downstream reporting use cases like KPI validation, field force performance, and therapeutic area insights.

 

Requirements: Developt SQL logic, Integrate interaction data with classification mapping to derive standardized sales team groupings, Perform a join between c360_customer_match_and_merge and s_field_reporting_activity_sales_team_classification_and_grouping_mapping on matching field_force_name, product_name, and customer_type. Aggregate multiple call and interaction KPIs (valid calls, emails, samples, etc.) at the level of: customer_id, field_force_name, br_field_force_name, sales_team_grouping, therapeutic_area, product_name, and interaction_channel.Use SUM() aggregation for all count-based columns and group by required attributes.