Customer_360 - Fix CPD Value Inflation Issue Across Brands for BLUE BALL Sales Team

Introduction: Users observed that CPD (Calls Per Day) values are appearing doubled or inflated across multiple brands and quarters in the dashboards. When the backend table s_field_reporting_activity_sales_team_brand_interactions_intmd_union_all was checked, the CPD values were already inflated at the source. It is focuses on validating the source calculations and identifying the correct CPD computation using the formula CPD = rep_calls_total_calls / rep_calls_total_days_in_territory. The team needs a query to reproduce the issue and verify if the inflated values match the dashboard output and source table.

 

Requirements: Develop SQL Logic, Read the file c360_s_field_report_activity_sales_team_brand_interactions_intmd_union_all. Filter the dataset for the following values, business_unit_code = BCBU, sales_team_grouping = BLUE BALL, classification_type = BLUE BALL ALL PORTFOLIO TARGET, interaction_channel = ALL, time_bucket_id = CQTDW, classification_value IN (‘EB’, ‘ED’, ‘ALL_TARGETS_PROFS’) Use the CPD formula as Total_CPD = SUM(rep_calls_total_calls / rep_calls_total_days_in_territory). Round off CPD value to one decimal. Group data by all non-aggregated columns. Return the dataset so the inflated CPD values can be compared with dashboard values.

 

Unity catalog: c360_s_field_report_activity_sales_team_brand_interactions_intmd_union_all