WKS - Parent Customer Data Aggregation

Workshop on 05 Jun 2025 to cover requirements for aggregation of customer master data.

Participants

Goal

In this workshop we define the processes to aggregate customer master data from child to parent, so it can be used in CPQ.

Workshop materials

Main results

Challenge Overview

The central problem discussed is that the customer master table (from SAP/ERP, currently consumed by Price Manager) is defined at a child customer level, while the CPQ tool requires customer information aggregated to a parent customer level for quoting and pricing purposes.

Key Fields Required at Parent Customer Level for CPQ

Parent Customer ID:

• Logic: Distinct value ASO_ID_2 (remove duplicates).

• Purpose: The primary identifier for the parent customer in CPQ.

Parent Customer Name (Title):

• Logic: Max ASO_Name_2 (or any consistent method, as names should typically be the same across children for a parent).

• Purpose: Mandatory field in CPQ customer records.

Salesperson ID / Name:

• Logic: Select the salesperson associated with the highest cumulative revenue for the parent customer (across all its child customers). This ensures the salesperson representing the most significant volume is assigned to the parent.

• Purpose: To populate the salesperson associated with the parent customer in CPQ. The Salesperson ID will be used in CPQ, assuming it links to the list of system users.

Geographical Region ID (e.g., A1, A2, etc.):

• Logic:

  • STEP1: Aggregate volume (or revenue) at the postcode level across all child customers.

  • STEP 2: Identify the postal code with the highest volume for the parent customer.

  • STEP 3: From that highest-volume postcode, pick one address (e.g., the address with the highest volume within that postcode, or "the first one").

  • STEP 4: Use this selected postal code/address to retrieve the "Geographical Region ID" using an existing lookup table (which is already in Zilliant).

• Purpose: To define the "center of gravity" for the customer's volume, used for calculating geographically dependent charges (e.g., delivery charges) and for pricing optimization. This should be a custom field in CPQ.

• Clarification: This is a geographical reference for pricing purposes, not necessarily the actual delivery address for all children. It will not be directly populated from the sales representative's input, but derived from the aggregated data.

TMA ID / Name (Industry Type / Customer Category):

• Logic: Select the TMA ID/Name associated with the highest revenue for the parent customer.

• Purpose: To categorize the parent customer for pricing guidance. This will be a custom field in CPQ.

Customer Annual Revenue:

• Logic: Sum of the total revenue for all child customers associated with the parent.

• Purpose: To represent the overall size/volume of the parent customer. This will be a custom field in CPQ.

Implementation Approach:

• Parent Customer Master Table Creation: It was decided that a new, aggregated parent customer master table needs to be created. This table should contain only one row per parent customer with the aggregated fields..

• Responsibility for Aggregation: The aggregation logic and the creation of this new parent customer master table should ideally be performed by the Zilliant team. This work will be more complex than simple API calls and may involve a "sequel script" running within a job workflow.

• CPQ Consumption: CPQ will then consume this aggregated table via API, rather than directly processing raw child-level customer master data, which would be too complex and unperformant.

• Future CRM Integration: The discussion also touched upon the broader context of customer data from future CRM systems (C4C) and ERP (S/4HANA). The aim is to create a solution that is flexible enough to adapt to these changing "bricks" in the overall architecture. The current technical choice of relying on a "steady source" (Zilliant Price Manager for customer data) is seen as a short-term, but not long-term, solution..

Detailed Customer Data Aggregation Logic for Zilliant Price Manager - to share with Zilliant Team

The objective is to create a new, aggregated "Parent Customer Master Table" where each row represents a unique parent customer, and key attributes are derived from their associated child customers using the following logic:

Input Data:

• 'Child' Customer Master Data: Contains fields like Customer_ID, Customer_Name, ASO_ID_2, ASO_Name_2, Salesperson_ID, Salesperson_Name, Postal_Code, Address, TMA_ID, TMA_Name, Annual_Revenue (at child level).

Aggregation Logic per Parent Customer:

1. Parent Customer ID (ASO_ID_2):

   • Logic: Select the distinct ASO_ID_2 from the Customer_Master_Data. This will be the primary key of the new aggregated table.

2. Parent Customer Name (ASO_Name_2):

   • Logic: Get ASO_Name_2, for each ASO_ID_2. If multiple names exist under the ID , a MAX() or MIN() aggregation could be applied for consistency.

3. Salesperson ID:

   • Logic: For each ASO_ID_2, identify the Salesperson_ID whose associated Customer_IDs (under this parent) collectively contribute the highest cumulative revenue over the defined historical period.

   • This involves summing Annual_Revenue per Salesperson_ID within each ASO_ID_2 group and selecting the Salesperson_ID with the maximum sum.

4. Geographical Region ID:

   • Logic:

     1. For each ASO_ID_2, sum Annual_Revenue for each unique Postal_Code across all its child customers.

     2. Identify the Postal_Code with the highest cumulative revenue for that ASO_ID_2.

     3. Map this highest-revenue Postal_Code to its corresponding Geographical_Region_ID using the existing Postal_Code to Region_ID lookup table within Zilliant.

     4. This Geographical_Region_ID will be the value for the parent customer.

5. TMA ID / Name (Industry Type / Customer Category):

   • Logic: For each ASO_ID_2, identify the TMA_ID/TMA_Name associated with the Customer_IDs (under this parent) that collectively contribute the highest cumulative revenue over the defined historical period.

   • Similar to Salesperson ID, this involves summing Annual_Revenue per TMA_ID within each ASO_ID_2 group and selecting the TMA_ID with the maximum sum.

6. Customer Annual Revenue:

   • Logic: Calculate the sum of total revenue (Annual_Revenue) for all child customers associated with the ASO_ID_2.

Output:

• A new, flat "Parent Customer Master Table" containing ASO_ID_2 as the primary key, and the derived aggregated fields (Parent Customer Name (ASO_Name_2), Salesperson ID, Geographical Region ID, TMA ID/Name, Customer Annual Revenue).

Process & Maintenance:

• This aggregation logic should be implemented as an automated job (e.g., a SQL script or similar data transformation process) within Zilliant Price Manager.

• The job should run regularly (e.g., daily or weekly) to ensure the aggregated data remains in sync with new customer additions, updates, and changes in historical volumes.

• The output table will be made available for API consumption by the CPQ tool.

Action list

• @a user to write the for this parent customer master table, including the logic for each field, to be shared with the Zilliant team. (Done in the section above)
• Valio Team to discuss this potential implementation with Zilliant.

Decisions

• Parent Customer Master Table Creation: It was decided that a new, aggregated parent customer master table needs to be created. This table should contain only one row per parent customer with the aggregated fields..
• Responsibility for Aggregation: The aggregation logic and the creation of this new parent customer master table should ideally be performed by the Zilliant team. This work will be more complex than simple API calls and may involve a "sequel script" running within a job workflow.
• No Address Fields in CPQ (for aggregation logic): The system should rely solely on the calculated postal code/geographical region ID for pricing rules, without needing to store or use an actual street address for the parent in this context.

List of requirements

• Parent Customer Data Consumption: Implement integration to consume a pre-aggregated "Parent Customer Master Table" from an external source (to be provided by Zilliant). Ensure this table contains a single row per parent customer, free of child-level duplication.

• Core Parent Customer Fields (for CPQ Quote Population):

  • Ingest and utilize the Parent Customer ID as the primary identifier in CPQ.

  • Ingest and utilize the Parent Customer Name (Title) for mandatory customer name fields in CPQ.

  • Ingest and utilize the Salesperson ID (associated with highest cumulative revenue) to populate the salesperson field in CPQ.

  • Ingest and utilize the Geographical Region ID (derived from highest volume postcode) to populate a custom field for pricing and charges calculation.

  • Ingest and utilize the TMA ID/Name (Industry Type/Customer Category, associated with highest revenue) to populate a custom field for customer categorization.

  • Ingest and utilize the Customer Annual Revenue (sum of total revenue) to populate a custom field representing the parent customer's overall size.

• Integration & Data Handling:

  • Ensure CPQ queries this aggregated table via API for performance, rather than storing a full replica locally.

  • Design CPQ's customer record to rely on these pre-aggregated fields, avoiding complex child-to-parent aggregation logic within CPQ itself.

  • Define fields in CPQ as custom attributes as needed for the ingested aggregated customer data.

  • Support automatic updates to the customer data in CPQ when the aggregated table is refreshed (e.g., when new customer master data is received and aggregated).

Session recording

https://tldv.io/app/meetings/68416adabf460a00135436b3/

Related pages