Customer_360 - Enrich Sales Data with Representative–Territory Hierarchy

Introduction: Customer 360 Sales Data Pipeline, each sales record in the table

”c360_s_field_reporting_sales_customer_pivot” needs to be enriched with information about the field representative table and their territory hierarchy. This enrichment allows the business to link every sale to the correct sales representative and territory. Build accurate field performance dashboards. Understand geographical sales distribution and account ownership. Support Incentive Compensation (IC) and territory analytics. The enrichment is done by integrating alignment UUIDs and representative-territory data from mapping tables.

 

Requirements: Develop SQL logic, Load the main sales data “c360_s_field_reporting_sales_customer_pivot”(frs) with another source table for Alignment mapping table “c360_field_reporting_sales_alignment_uuid” and for representative and territory mapping table “c360_s_alignment_basic_representative_territory”. Perform Left Join Logic operation as below

 

Join Logic 1 - JJoin sales with alignment UUID using:

TRIM(frs.pt_cdl_uuid) = TRIM(dependent_uuid.field_reporting_sales_uuid)

Join Logic 2 - Join the result with representative–territory table using:

TRIM(dependent_uuid.frs_alignment_uuid) = TRIM(rep_terr.pt_cdl_uuid) and matching territory_code

 

Keep only records where primary_representative_flag = 'true'. Convert representative names and territory names to UPPERCASE. Capture 3 levels of hierarchy: territory_name_1, territory_name_2, territory_name_3

 

 

Expected Output: Display the results of enriched sales record per customer with representative and territory info

 

Unity Catalog: “c360_s_field_reporting_sales_customer_pivot“, “c360_field_reporting_sales_alignment_uuid“, “c360_s_alignment_basic_representative_territory“