CHEM14: Achieve margin / revenue / volume goals with optimized pricing guidance and guardrails

Pricing Manager / Pricing Scientist 

Business Objective:

Provide segmented optimized pricing guidance.

• A pricing strategy that follows a 'one-size-fits-all' approach, primarily attributed to our

• Constrained data science tools and capacities, as the result of a genuine lack of a clear starting point.

• Customer and product segmentation

• Optimization of prices 

• Improved margin 

• Customized prices

• Visibility and guidance in the negotiation and quoting process

• Margin % 

• N/A

For this use case the functional requirements are:

• Ability to analyze the last 2 years of historical billing data (aka, sales orders data, or txn data) as the source data to understand the customers willingness-to-pay.  

• Ability to use advanced filtering capabilities to determine the data scope of the relevant historical billing data for the purpose of understanding the pricing drivers.  

• Ability to use advanced filtering capabilities to filter out negative margin, and/or negative quantity and/or negative revenue. 

• Ability to profile the input data after applying the advanced filters to quickly understand the quality of the underlying data that will be used in the model: 

  • Transactions count 

  • Customer count 

  • Product count 

  • Total Revenue, Margin and Quantity 

  • Min/Max value in each data field (e.g., each column of the science DataMart) 

  • # of distinct values (aka, cardinality) for each data  

  • Data type 

  • Whether each field is a dimension 

  • Whether each field is a key   

• Ability to select potential pricing drivers for the purpose of determining their historical relevant importance in explaining the variation. 

• Ability to use a built-in statistical analysis method to understand the selected pricing driver’s relative importance in explaining the overall variation in the data.  

• Ability to include and exclude pricing drivers that drive the customer’s willingness-to-pay. 

• Ability to use an out-of-the-box segmentation model based on prespecified constraints based on:

  • Minimum No. of transactions per segment 

  • Minimum No. of customers per segment 

  • Minimum No. of products per segment 

• Ability to visualize the segmentation tree and understand the data scope within a segment and an associated metrics, e.g., # of txns, # of customers, # of products, segment revenue, segment margin, segment quantity, and segment margin pct.  

• Ability to use a built-in statistical model to calculate the segment level elasticity.  

• Ability to set the percentile value to calculate the recommended price bands (Floor, Target, Ceiling) 

• System to calculate the associate Floor, Target, and Ceiling for each segment node.  

• System to calculate projected impacts on volume, revenue and margin based on the pre-determined Floor, Target, Ceiling percentile and the calculated segment level elasticity. 

• Ability to leverage a summary report that provide quantitative metrics for each price drivers, so I can determine how the current segmentation model is either over/under segmented e.g., 

  • No. of transactions used by each pricing driver, and the associated % of total no. transactions. 

  • No. of customers used by each pricing driver, and the associated % of total no. of customers. 

  • No. of products used by each pricing driver, and the associated % of total no. of products. 

  • No. of segmented calculated for each pricing drivers.  

  • Total revenue used by each pricing driver, and the associated % of total revenue.  

  • Total margin used by each pricing driver, and the associated % of total revenue.  

  • Total quantity used by each pricing driver, and the associated % of total revenue. 

  • Coefficient of determination (R2) that determine how well the pricing driver predicts the recommended margin %  

• Ability to assess the margin and revenue uplift potential by setting up hypothesis such as: 

  • Assuming X1% of the transactions below Floor will be priced at Floor Price. 

  • Assuming X2% of the transactions between Floor and Target will move to Target Price 

  • Assuming X3% of the transactions between Target and Ceiling will move to Target Price 

  • Assuming X4% of the transactions above Ceiling will move to Ceiling Price 

Non-functional requirements

• Ability to model a segmentation and calculate the segment level recommendations in reasonable time 

This use case has the dashboards and reports as described in the functional requirements section. Check them out, here.

 

The following are the primary KPIs for this use case:

• Pocket Price: Pocket price is a term used to refer to the effective price paid by a customer in a transaction after considering all relevant discounts, promotions, and rebates. It is calculated by subtracting the cost of goods sold from the list price minus discounts, rebates, promotions, free freight, and similar offers 

• Pocket Margin % = (Pocket Price – GOGS) / Pocket Price 

• Weight Measure: It is the measure that will be used to influence the recommendations based on this measure. It is typically a representation of the size of the transaction. Such as Revenue, Margi, or Quantity.   

• Customer Revenue Spent: is a pricing driver attribute that will be derived from the underlying data by calculating the customer spend in the last 12 months and assigning a categorical attribute (Small, Medium, Large) based on predefined percentiles.  

• Forecasted Sales: Forecasted Sales for the rebate period, based on external forecast (input to Pricefx) 

• Forecasted Baseline value: Forecasted Sales for the rebate period which is eligible for the rebate program, based on external forecast (input to Pricefx) 

• Forecasted Rebate: the forecasted rebate to be paid out, based on the rebate program conditions and the forecasted baseline value 

• Revenue: Revenue based on the Price Waterfall data 

• Rebates: Rebates based on the Price Waterfall data 

• Net Margin %: Margin % based on the Price Waterfall data 

As a Pricing Manager, Pricing Analyst, Data Scientist Manager, I want to setup a science-based segmentation model based on the relevant price drivers, so I can empower the sales team with negotiation guidance to improve the overall all company financial metrics.  

User Story Name - DataMart Setup 

I want to: DataMart Setup 

so I can: Properly: 

1. set up the negotiation guidance process. 

2. filter data that should not be included in the negotiation guidance process. 

Acceptance Criteria:

• Required

1. The DataMart must include all the required data elements outlined in step 1 described in the solution design section.  

2. The DataMart should contain up to 12 potential attributes that may be used in the segmentation structure. 

3. The DataMart must include all data required to isolate the data that will be used in price optimization. 

• Optional 

The DataMart must include any derived attribute that may be considered for use in the segmentation structure 

---

User Story Name - Historical Customer Spend (Derived Attribute)

I want to: The system to automatically classify my customers based on  their historical revenue spend 

so I can: I can consider using this classification as criteria to set pricing 

Acceptance Criteria:

Required 

• The system will calculate the historical spend of each customer based on the last X months of data. 

• The system will order the customers based on their spending from lowest to highest. 

• The system will calculate the running total of the customers spend based on this ordering, allowing the system to calculate a percentile associated with this running total. 

• The system will classify the customers into 3-8 groupings based on percentiles selected by the user. 

• The system will calculate the associated revenue “cut-off” points associated with the selected percentiles. 

• The system will allow the user to override the revenue cut-off points associated with the selected percentiles.  

• The system will construct a table containing each customer classified using this criterion.   

• In addition, the user should have the ability to override the automatically calculated classification for any customer in the table 

---

User Story Name - Negotiation Guidance Setup 

I want to: Be able to define the input entities and metrics that should be used in the price optimization process and to define the data that should be excluded/included to assure that only relevant data is processed

so I can: Generate results (recommendations) that are meaningful to my business 

Acceptance Criteria:

• The user should be able to define both the customer and product entities.  

• The user should be able to define the optimized metric as either margin % or discount%  

• The user should the option to define a weight measure which price optimizer will use to define the relative importance of a transaction if the user chooses to do so 

• The user should be able to exclude data from the analysis by using filters that can be applied to any field in the Datamart

---

User Story Name - Data Profiling 

I want to: Understand the impact of my filters on the data being used by Price Optimizer 

so I can: Determine that only the data relevant for price optimization is being considered

Acceptance Criteria:

• The user should be able to see a breakdown of the impact of the filters on revenue, profits, quantity, customer count, product count, and transaction count. 

• The user should be able to see the following details for each field loaded:  max/min value, cardinality, number of nulls and distinct values.  

---

User Story Name - Select Price Drivers

I want to: Select the attributes to consider in the segmentation so that the system can analyze their importance in explaining the historical variation 

so I can: Make an informed decision when constructing a segmentation structure best aligned with my business objectives 

Acceptance Criteria:

• The user should be able to see a list of available attributes in the DataMart fed into the negotiation guidance process. 

• The user should be able to select the attributes that the user would like to consider for the segmentation structure 

Within the Optimization module, the user can select the DataMart that is the subject of optimization.

It is important to ensure that data fields listed below are predefined within the DataMart, those data fields will be used as an input for the fast-following steps. 

 

1. Customer Entity and all Customer Attributes considered for the segmentation. 

2. Product Entity and all product attributes considered for the segmentation. 

3. Quantity Measure 

4. Revenue Measure: Pocket Price 

5. Margin Measure such as Gross Margin 

6. Any data fields required for filters that need to be applied.  

 

These fields must exist in the DataMart: 

• 8 -12 potential attributes that may be leveraged in the segmentation structure used to build segments in negotiation guidance. 

• Strategic: attributes considered should be aligned with a customer’s pricing strategy  

• Available: attributes should be available in the historical data and at the time of quote 

• General: attributes should be relevant for a large portion of the business being processed in negotiation guidance 

• Compact: attributes should not have more than 20 distinct values to avoid manufacturing sparse segments (rule of thumb) 

1. Inputs: Two inputs: 

• Target Type: Pocket Margin % 

• Weight Measure: such as Pocket Price, Gross Margin, or Quantity.  

2.  Transactions Filters: will leverage Pricefx robust data filtering capabilities.  

• It is important to include transactions data from the last prior two years. 

• It is important to remove irrelevant transactions that will influence the recommendations such as Returns, Promotions, and Credits.  

• It is important to remove any transactions that exhibit negative margin, negative revenue, or transactions that have odd margin (example: margin greater than 99%.  

• Specialized Customer  

• Intercompany transactions 

• Large, heavily managed accounts  

• Specialized Products 

• Sample products 

• Shipping codes  

• Temp Product codes 

• Isolated / Specialized Geographies 

Once the scope of the data is defined, then the user will be able to select the relevant pricing drivers. Pricing drivers are the relevant Products, Customers and Transactions attributes that potentially will help in explaining the variations in the Pocket Margin %.  

(Optional Pricing Driver – Derived attribute)  

Customer Revenue Spent: System will calculate the 12-month revenue spend of the customer entity to be used in price optimization.  

 

Customer Revenue Spent table structures (to be managed outside the Optimizer module)  

 

The system will provide diagnostic type of analysis on the selected Price Drivers. This analysis will empower the user with information to determine the Pricing Drivers will be used to structure the segmentation. The analysis will illustrate the Price Driver’s importance (power) in explaining Pocket Margin % variation, along with the Price Drivers relative importance.  

Additionally, the system will provide additional analysis to help deep dive into the inter-relationship between those price drivers. At the end of the process, the user will be able to manually select the relevant price drivers that will form the basis of the segmentation structure.  

 

Based on the analysis referenced in the prior step, the system will be able to automatically recommend the Price Drivers to form the basis of the segmentation. The system will rank the selected price drivers accordingly. However, it is important that the user review the recommendation and make an educated judgement that will drive the business moving forward. The user must complete two actions before creating the segmentation structure: 

Select the segmentation dimensions. 

Determine the segmentation thresholds. 

Based on the prior setup, and the system will generate the segmentation structure accordingly. The user will be able to visualize the segmentation and traverse the segmentation hierarchy as illustrated in the image below.  

For each one of those leaf nodes, the system will calculate corresponding metrics that will provide 360-degree view on the segments. The following images illustrates the difference metrics and analysis that are autogenerated for each leaf node: 

Segmentation leaf level metrics

Segmentation metric distribution (at the leaf level)

Once the segmentation structure is created, the user should be able determine the percentile parameters that will be used to calculate the Floor/Target/Ceiling margin recommendations for each one of those nodes. 

 

In parallel and as part of helping the user understand the financial impact on the revenue and margin. The user can provide assumptions around the expected margin attainment. The image below illustrates those hypothesis parameters will be used to calculate the financial impact once the final recommendations are calculated: 

The final recommendations are stored in the “Results” step within solution. The Results step consists of 4 components: 

1. Impact: is a prescribed dashboard that provides descriptive sets of charts and analytics that help quantify the potential uplift. Additionally, it will provide capabilities on slicing and dicing the analysis to help the user quickly understand the quality of the potential uplift using the different levels of the segmentation. 

   

 

2. Tree View: it is similar view as in figure # …., the view tree view will be updated with the segment level Price Recommendations 

 

3. Recommendations: within this tab, all segment level recommendations will be stored in a table view. This table will be considered the source of truth for all the data that will be consumed in the downstream processes. Such as Pricefx Quoting.  

   

Each line in this table consists of the relevant data associated with each leaf of the segmentation tree. Such data includes: 

• Segment ID 

• Segmentation Dimensions 

• # of Transactions, # of Products, # of Customers 

• Statistical values such as: Sum of Error Squared, Divergence to Normal, Coefficient of Variation, Weighted Coefficient of Variation, Standard of Deviations, Weighted Standard of Deviation, Average, and Weighted Average 

• Segment level Revenue and Margin 

• Segment level Floor/Target/Ceiling percentiles 

• Segment level Floor/Target/Ceiling Margin %’s 

• Elasticity 

• Projected Quantity associated with Floor/Target/Ceiling 

• Relevant Revenue/Margin/Quantity deltas.   

 

4. Evaluation: within this tab, the user will be able to retrieve the recommendations based on input values that is associated with the segmentation level.