Create a Segmentation Model

To describe the pricing of a segment, you cannot use the actual prices directly because different products have different prices. You need to use a “target metric” that is comparable among all the products.  To create the segments, you must define all the attributes (parameters) and observe the target metric distributed for each segment. The goal of this price optimization / Segmentation model is to reshape this distribution and increase the profit specific for each segment by targeting a higher aggregate margin %.  

This exercise will provide you guidelines to create a Segmentation model using an existing Datamart and model logic (algorithm).  

The Optimization capability provides a framework that helps you analyze and segment your business, optimize your product portfolio, and improve your pricing. At the end of this exercise, you will have price recommendation values to be used in your sales quotes, products pricelist among other business areas. Today you are a Pricing Analyst, and you must determine the ideal target price for products at your company, analyzing competitor pricing and market expectations.  

For this exercise, you will navigate through and existing Datamart and Model Type, identify the optimized prices required and create a Segmentation model. 

Review a Datamart

Select Datamarts from the main menu path: Analytics > Data Manager > Datamarts as follow: 

The system displays a list of existing Datamarts. For this exercise you will use the Standard Sales Data listed, click on it: 

The system displays the fields contains in the selected Datamart. Notice the different Data Sources used under the Owner column.  

Scroll down and locate those fields that were created to store specific calculated data Customer Discount and Customer Rebate Pct. Notice those fields do not have a source associated in the Owner column. These are fields that must be required in the Segmentation process later. 

Click on the Data tab to display the actual data included in this Datamart. 

Click on the blue arrow to go back to the main window. 

Review Model TypesSelect Model Types from the main menu path: Optimization > Pricing Guidance > Model Types as follow: 

The system displays a list of existing model types, the one you will use in this exercise is the Segmentation. You can review the algorithm behind the model by clicking the View definition under the Definition column to display the elements and steps used in the calculation. 

Review Model Types: 

Select Models from the main menu path: Optimization > Pricing Guidance > Models, as follow: 

The system displays existing models with number of records calculated and model type. 

On the top-right corner of the window, click on the Add Model icon.

Provide a name with your Student number (no spaces), a label/name to identify your model, and slect from the Model Type drop-down box the Segmentation option. Click on the Add icon. 

The system displays a confirmation message at the top of the window: 

The system displays the main tabs to build the model: 

Definition – represents the area where the logic is determined to identify the calculation context with user inputs (filters, mapping parameters, percentiles, etc.). 

Analysis – represents the area where the data is uploaded, selected, and distributed for the Segmentation.  

Calculation – represents the execution to generate the Segmentation, including several steps with calculation results. 

Results – represents the display of the Segmentation results (tree-branches) with an Evaluation area to search for specific segment (tree-search) using different attribute values. 

To populate the Field rows on the right area, you must select the source of data (Datamart) for this model. Open the drop-down and select Standard Sales Data (DM) option: 

As soon as you select the Datamart, the filters are displayed. It is very important to avoid negative values or null values in the model, so you must create a filter for data quality purposes. Click on the Set Filter option: 

Using the drop-down arrows, select the displayed filter options for Gross Margin Percent. 

You will need to create more than one filters at a time. Click on the Add Rule option, select the AND condition: 

Using the drop downs, complete the following filters for Gross Margin and Global List Price selections, and click on the Apply icon. 

Note: Using these filters will avoid using negative values (from the Datamart) for margin and list prices in the model calculations. 

The additional displayed fields are used as the Mapping area that tells the system logic (behind the model) which data to bring into the model from the different Data Sources/Datamarts to perform the calculations. Using the dropdowns select the following options: 

Note: These selections are based on the business requirements and Datamart’s content to perform the model’s calculation. 

The price optimization/Segmentation is based on percentiles of the target metric distribution. Every segment is given a floor and stretch percentiles that define guardrails for the target percentile. The system already displays the Percentile Values, such as floor and ceiling (stretch) prices, you can update them here based on your business requirements. If the Target is left blank, it will be calculated automatically based on the optimization/Segmentation model. For this exercise, you can leave this area as default it. 

Floor Percentile – represents the floor optimization target in the segment. 

Target Percentile – will be used to propose an optimization target value for items whose target is between floor and target before calculation. It is optional to be entered into the model or calculated based on each segment score. 

Ceiling (stretch) Percentile – represents the stretch optimization target value in the segment. 

The Additional Parameters area helps to determine the amount of data to be used in the Segmentation model calculation for the available transactions in the Datamart. This selection will help to build the dimensions, data profiling and drivers within the model. Update the following parameters. 

Max Cardinality – largest count of distinct values that a dimension is allowed to have, to be taken into the account for price drivers (across the spectrum). 

Min #Transactions in Segment – smallest size a segment can have. If the segment is too small, it won’t be created, and the model will use the above (parent) level in the segment tree. 

Min #Customers/#Products in Segment – smallest size of customer/product in a segment. 

Policy Start Date/End Date – will provide time bounds when using the policy records. 

After completing all the parameters, filters, and data mapping sections, click on the Save Model icon on the top-right corner of the window. 

The system displays a confirmation message at the top of the window. 

Now the Field Rows are available based on the data mapping fields and the Datamart used as a source in the previous steps. 

Click on the Source Data tab. The model is empty because you need to complete the Analysis of the model and execute the data uploading process to populate the data records from the Datamart. 

Click on the Analysis tab on the top-left corner of the window. 

This section is used to prepare the data for the model the way you require it. In this Data Preparation section, you must click on the Calculate icon to start uploading the data into the model from the Datamart. 

Note: DataPrep step materializes the data applying all the filters and helps to reduce the processing time of the next steps. The “Incremental Calculate” icon can be used for partial calculations for specific changes/updates. 

Click on the Continue icon in the validation message. 

The system will display the status of the model preparation passing from Pending > Processing > Ready (give couple minutes to the system to process the request, click on the Refresh icon, if necessary).  The system now displays the number of rows of data loaded from the Datamart that will be used in this model. 

You can validate the actual data loaded into the model by going back to the Definition tab. 

Click on the Source Data tab. 

The system displays all the loaded data into the model. Notice some functions icons on the top-right area, they are available to maintain the records within the model, if case of changes.  

Purge Cache – clears the cache. The cache applies to queries on the model from the Data Preparation calculation. 

Truncate – deletes all/filtered rows from the calculation. This option is used when filters in the Definition area are changed and to avoid duplications.  

Upload – loads and XLS/XLSX sheet or a CSV/TXT file.  

Mass Edit – allows you to apply mass changes to editable attributes. 

Status History – displays information related to the records status history.  

Export – allows you to export the records to an external XLS or CSV format file. 

Analysis Tab

Click back on the Analysis tab. 

Data Profile Tab

Click on the DataProfile tab. 

In this Data Profile step the system provides a summary of the filtered transactions dataset. You must click on the Calculate icon to calculate the dimension values. 

Note: This step is optional, only to display the result table with all the dimensions values and to automatically remove fields with too large cardinality (previously defined) from possible Segmentation levels (we recommend executing this step only for models with changes for data troubleshooting purposes). 

Click on the Continue icon. 

Note: if you get an error message on the top of the screen about the version used, just Refresh your browser at the top-right corner:  

The system will take couple minutes to process the Data Profiling. When it is “Ready” status, it will display a list of all the dimensions (flagged fields) from the Datamart with minimum and maximum values, number of records found for each dimension and other status information as a result of the calculation. Pick any dimension from the list and click on the “Show” icon (for example Country). 

Note: The Profile calculation provides the minimum and maximum values for each dimension based on the parameters determined in the Definition tab.  

As an example, for the “Country” dimension previously selected, the system displays all the records found for each country between the minimum value (Australia) and the maximum value (Vietnam). Click on the OK icon at the bottom-right corner to close the window.

Back in the Data Profile section, scroll down the list to see if there are any dimension with “Null” values. Notice the dimension ProductImage has null values. The system identifies these dimensions to be excluded from the Segmentation.  

Other dimensions to analyze if they should be or not part of the Segmentation process are the ones related to “periods”. Keep scrolling to the bottom of the list and locate Pricing Dates fields (week/month/quarter/year). 

Note: Notice the PricingDateWeek dimension has 105 distinct records, which is higher than the cardinality value (100), so this field will be excluded from the Segmentation. When the dimension contains several periods (e.g., years – 2020 to 2022) it wouldn’t work precisely for the Segmentation either since the dimension is too wide/large. 

PriceDrivers Tab

Click on the PriceDrivers tab. 

The Price Driver step helps to calculate the significance of each attribute (dimension) when it is used in the Segmentation (decision tree). The system displays the eligible dimension (attributes) list on the left area, these are the dimensions that have records under the Max Cardinality value. Click on the Expand arrow icon on the lower corner:  

The system displays the whole list of eligible dimensions to easily filter, search, select/unselect dimensions. You can click on the Dimension main label to sort the list or use (Ctrl+F) to use a search field browser for specific dimension search. You can search for any dimension and see if they are displayed in the list. For example, the dimension UniqueId is not part of the list since it has more than hundred records and is over the Max Cardinality value. 

Note: This filtering options will help to identify the missing dimensions that were excluded from the Data Profile due to Max Cardinality exceeds. 

For this exercise, we must reduce the calculation time, please unselect the following dimensions using the check boxes, as follows:

Note: Those unselected dimensions are not relevant for the Segmentation results that we are expecting in this exercise. During your company’s Segmentation model creation, you and your pricing team can decide which of the available dimensions are relevant (or not) for your Segmentation results.  

Click on the Close icon at the bottom corner. 

Back in the main Price Drivers window, the system offers more than one Regression Type to select from a dropdown field, this defines how the system will calculate the significance values for each dimension. For this exercise, leave it as default it.  

Regression options: 

Per Individual Product and Customer – the significance of each dimension is calculated first per customer, then per product. The results allow calculation of average and standard deviation of significance. 

On full Transaction data set – one decision tree is built resulting in one significance value per dimension. 

Click on the Calculate icon located on the top-right area.  Click on the Continue icon to confirm the information message. 

Note: Pricefx uses a simple CART (regression tree algorithm) approach with Gini in decision criterion and the attribute significance (level of importance) follows from that. CART is only used to identify possible “price drivers” as best variable (value) used to explain the relevance of an attribute. 

The system will take couple minutes to process the Data Drivers (you can use the Refresh icon, if needed). When it is “Ready” status, it will display a Histogram chart with the 10 most relevant dimensions ranked by level of importance. The system runs the “regression” algorithm and provides these results based on the previously selected eligible dimensions. Notice that “City” has the most relevant importance in this model (than Country which is at the end of the importance scale), meaning that City has the biggest impact on the price recommendation for this Segmentation scenario. (If you unselect the City dimension from the previous list, maybe the Country, State or Region will take highest relevance for the pricing Segmentation). 

Note: You can decide which of all the available dimensions are meaningful for your Segmentation, by selecting/unselecting the dimensions and performing the calculations several times (if necessary). By running this Drivers step, the Segmentation will automatically sort all the dimensions from the most important to the least important and preselect the significative ones based on historical statistical information. 

On the right-side of the window, you can see the results from the Drivers processing. Click on the “Show” icons to display the criteria selected and the results of the processing (if required). 

On every step you will see on the top-right corner additional icons, these are helpful to check time response during every calculation process, allows to attach additional information to the model and provides visibility to the internal tables. Click on the Job Tracking icon: 

Job Tracking – lists an overview of all the job/task processed within the model.  

Attachments – allows the user to upload external files relevant for the model. 

Tables – lists the model and parameters tables used in the Segmentation. 

The system will display the time that calculation processing takes every time you click on the “Calculate” icon. For example, these results will help you to estimate the processing time depending on the number of dimensions you select for the Segmentation (data profile and data driver processing times). 

Click on the Calculation tab on the top-main steps menu.  

The Segmentation tab will offer several Segmentation methods (with algorithms behind) and the available dimensions within the cardinality. Under the Parameters section, open the Segmentation Method dropdown and select the Manual Attribute Selection option.  

Note: This Segmentation method will prepare the calculation records (a work to do) for the next “scoring” step to create and populate the lookup tables with intermediate results.  

The system displays the Segmentation Levels already defined dimensions with their scores (level of importance previously recommended) because of the Drivers processing. If you decide to change the level of importance of the dimensions as the last-minute change, you can do it here before the next calculation process. Click on the Expand arrow icon. 

You can select any other available dimension and prioritize it in the list the way you need it. For example, select the State dimension and hover by the check box to grab it and move/drag it to the top of the list for the first level of importance. 

Notice some previous selected dimensions were unselected to change a bit the importance levels for a new Segmentation. Here the system provides the flexibility for you to change the level of importance as you needed to get a different Segmentation result. 

Note: These changes are very rare in real business, usually companies agreed to use the recommended level of importance that the system provides. However, if changes are relevant as part of the Segmentation testing, here is where you can make those changes. These changes will affect the way how the Segmentation branches are displayed at the end of the process. 

Click the Close icon to return to the Segmentation section. Click on the Calculate icon to start the Segmentation process. The system will create all the segments based on the dimensions and parameters previously defined. 

Click on the Continue icon to confirm the calculation. 

The system will take couple minutes to process the Segmentation (you can use the Refresh icon, if needed). When it is “Ready” status, the system displays for each dimension (segment) the results (totals) about number of transactions, number of customers, number of products, etc. in the Explained Variance area: 

Note: You will have available the “Export to Excel” icon to share the information in a spreadsheet format. The “Settings” icon will allow you to change the display of the columns in the Explained Variance section.  

Scroll the bottom bar to the right to see additional results related to revenue, volume, and margin for each dimension. The column “R2” represents how well (far/close) the Segmentation reduces the variance of the Optimization Target (margin %, for example). 

Note: All these columns show the percentage of the values present in the segments at each Segmentation level. Here you can have an idea if the dimensions and definition parameters selected are the best choice for the Segmentation. The wider the segment, the better opportunity for the target metric.  

On the All Results area on the right side, the system displays the parameters used in the calculation. Click on the “Show” for the GenerateSegments field: 

Notice the generated segments and the number of items (branches) within each segment.  For each dimension a segment was created. Close the window by clicking the OK icon at the bottom right corner. 

Click on the Scoring tab. This Score approach calculates a score (statistical distribution) for each segment. This score is used to define a target percentile (if not provided in the Definition step).   

Click on the Calculate icon, then Continue icon to confirm the calculation process.  

The system displays a Score Distribution chart. For each segment, the score is linearly transformed to the target price percentile (score = 0 correspond to the floor percentile and the score = 100 to the stretch percentile). 

Note: The wider the segment, the higher the score. 

On the right side, the system displays the Results for the scoring calculations success/failed records with respective explanations (alerts)., including the number of segments processed in the scoring calculation. For this example, no errors were reported

Click on the Pricing tab. This step performs the calculation for the target metric from the percentile’s distribution (scoring – floor/stretch) for each segment.

Click on the Calculate icon, then in the Continue icon to confirm the calculation messages.

Pricing is part of the previous Scoring method with the floor and stretch distribution, but now displaying a statistical distribution chart for the target percentile. It transfers percentiles values into margin percentages (%).  

Note: You can review similar outputs on the Results area for any failure and alerts from the calculation (if any). 

Click on the Elasticity tab. This is another approach (different than Scoring approach) that provides calculated elasticity factors from the Segmentation data. 

Click on the Calculate icon, then in the Continue icon to confirm the calculation message. 

For each segment created, the system displays the target metric value “sweet spot price” using the parameters from the elasticity function. Final elasticity values can be reviewed at the segment tree-analysis at the end of the Segmentation steps using the Optimal Target Chart. Also notice additional parameters (columns) from the elasticity functions, which are required and displayed in the tree-analysis Segmentation at the end of the process. 

Note: You can review similar outputs on the Results area for any failure and alerts from the calculation (if any). 

Click on the BreakEven tab. This step is optional as an alternative to calculate price elasticity directly, where the result will be the average margin % of the segment shifted to the target margin %.

Click on the Calculate icon, then in the Continue icon to confirm the calculation messages.

For this exercise, the system only displays the results of the calculations. The BreakEven estimated values (if any) will be shown in the final tree-analysis view further on.  

Note: An example result of this BreakEven function happens if you know your business, you can make a judgement that a 10% price increase for a specific product should not result in more than 5% drop in volume. If the BreakEven analysis tells you that you would still have the same profit with an 8% drop in volume, then you can assume that a 10% price increase should increase the profit.  

Click on the Policies tab. This step provides a list of segments together with their definitions and price recommendatio

Click on the Calculate icon, then in the Continue icon to confirm the calculation message. 

Note: The Run ID field will be used for further executions to update the policy records table using the same Run ID type as originally. For this example, leave it in blank.   

The system displays a list with all the Segmentation resulted records, using the bottom bar, scroll to the left to see all the columns displayed.   

Note: There are additional function icons in this Policy Records area to maintain the records using mass functions, advanced filters, and settings to change the display of the columns. 

On the right-side you can see the number of records created and stored in the Policy Records table.  

The last tab Update Optimization Realization Data is under construction still, but soon will display information into specific Dashboards for Segmentation model results and accelerators. 

Click on the Results tab.  

The system displays the Tree (analysis) View area as the result of the Segmentation model. Open the first segment by clicking on the small arrow icon. 

The system displays the first hierarchical segments created based on the most relevant dimension, which was the “State” that you updated (instead of the City) during the Data Preparation and Data Drivers steps. 

Note: You can use your mouse to enlarge/reduce the size of the tree display in the window. 

Each segment has the three-dots icon on the lower corner, pick a segment and click on it. The system allows you to open only the branches available for that segment and/or highlight the segment. Select the Open Branch option. 

The system only opens (displays) the branches under the parent segment. Here is where you can identify if all those selected dimensions were necessary for the Segmentation process and update the level of importance in the Drivers section. Close the tree by clicking on the root of the third segment level. 

Click on the Hierarchy icon on the top corner. The number on the corner says how many more segments levels has the current/selected segment. 

The system displays the results/values for the selected segment on the right side. Notice the State (dimension) name and the segment number created in the Tree Node area. Also, the Parameters defined previously as threshold values for transaction, customer, and product fields. 

Scroll down using the vertical bar to see the Segment Statistics section. Notice the result values for revenue, margin, unit price and target metrics. 

Locate the Price Recommendation section to see the values resulted from the Scoring method.

The Charts section display additional details in graphical charts, you can navigate through using the “Show” links. For example, click on the Volume/Revenue/Margin link: 

Hover around the bubbles to see insight information for quantity, list price, customer, and margin %.  

Locate the BreakEven section to see the results of the alternative elasticity calculations. Click on the BreakEven Price Curve “Show” link.  

The system displays a chart with a curve based on the BreakEven results between the Delta Price % and Delta Volume %.  

Note: The BreakEven approach provides price recommendation on how much you can allow to decrease your volume values and these results can also be visible in the Policy Records (BE columns). 

The Transaction Histogram displays the Target Metric distribution between the Floor and Stretch/Ceiling values.  

Finally, the Transaction Data section display the list of transactional data used for the calculations and coming from the Datamart selected at the beginning of the Segmentation model. Using the bottom bar, scroll to the right to see the rest of the data.

Pick any of the segments from the tree, “copy” the Segment number from the Parameters section and notice the Number of Transactions resulted in the Segment Statistics section: 

Select the Policy Records option from the main menu path: Optimization > Pricing Guidance > Policy Records: 

Select your Segmentation Model form the Price Records list: 

On the right side, the system displays all the records generated from the Segmentation process. Locate the column Name and “paste” the selected segment number from the Segmentation tree: 

Validate the same resulted values from the Segmentation in the Policy Records. Notice the same Number of Transactions calculated match the Segmentation results. You can scroll to the right to see all the Policy Records results for the selected segment.  

Note: The policy records will store the Segmentation results for each segment. These records can be exported to a different file for further validations. 

Back to the Segmentation Tree View (Results tab), you also have additional icon, on the left side, to search for specific segment information avoiding opening the tree-branches. 

Search in node content – advanced search by segment number 

Search in Segmentation attributes – advanced search by attribute 

Expand/Collapse node information – minimize/maximize node content 

Focus selected node – zooms out selected node  

Move all nodes into view – centers all the nodes into the window  

Click on the first Search in node content filter icon. The system opens an advanced filtering area to search for a specific segment. Using the dropdowns enter your Segment and click on the Apply icon.  

The system displays the right branch path in the tree view, and you can see the results on the right side for the searched segment. 

Click on the Evaluation tab. Like the Policy Records, this section allows you to use additional filters to find segments.   

You can use as many of the available filters and use the dropdowns to select specific segment information to get the results. For example, select a State and click on the Apply icon.   

The system displays the results of the segment (State of Georgia) based on the previous filters used.