The OptiPrice App provides price recommendations for products based on numerous parameters like volume, incoterms etc. for similar CPC’s. Please refer to business documentation for details.

This document will entirely focus on the front-end Qlik visualisation layer where-in price recommendations are provided to end user after processing and running back-end pricing algorithms

This dashboard is meant to provide some analysis tools of the result of the machine learning price recommendation tool used by the SpP GBU. When the business starts a pricing campaign (around every quarter), a run of the model will be started to provide updated price recommendations. This is a multi-GBU platform, and that the the model will have to run on a monthly basis, independently on when the campaigns will be run at the GBUs.

Most of the data come from the back end which is built in Dataiku (Pricing Back-End project). The data connections are via Big Query which is used to store the Dataiku results.

Overview of methodology and related data:

The objective of the Qlik dashboard is to give price recommendations on a CPC (customer/product combination) level. Every product family of the GBU has its own independent machine learning model trained to do so. In order to provide recommendations, the following three major steps are performed in the back end (separately applicable to every product family):

• Identify the importance of every price lever. This outputs coefficients also called weights based on the SHAP values of the ML model used.
• For every CPC in scope, find its more comparable neighbours using previously calculated weights.
• The recommended price of the targeted CPC is then based on the median price of the set of comparable CPC(s).

To learn more about the back-end methodology, please refer to the business documentation below:

Back-end | Business documentation - Google Slides

Brief overview of the dashboard:

CPC Prioritisation sheet

Analyse the outcome of the run to estimate its impact.

KPI(s) Displayed:

Number of CPC: Number of CPC for which the AI tool tries to recommend a new price. With no filters applied, it sums the CPC(s) for all the product families that are in scope.

Price Increase with potential impact: Number of CPC for which the recommended price is higher than the original price.

 Note that the recommended price can never be lower than the original one but will stay the same if there is no increase.

CPC with price increase recommendation: Difference between the recommended price and the original price multiplied by the volume of sales.

 Min Volume threshold considered is based on the last 12 months of transactions.

 Median price increase: Average difference in price between the recommended price and original price including only the CPC with an increase.

Price increases potential impact: chart enables a user to begin analysing price increase potentials by H4 / Prod L4, GBU Region, GBU Customer segment, Material/Product and Sold-To/Ship-To dimensions. This visual allows users to switch dimensions H4 /Product family, and customers and start understanding where the impact comes from.

Default selections: By default, an end user will view either CS or SP GBU data and the GBU will be selected by default when the user opens the sheet. The currency and Unit of measure selections are also defaulted to Euros and KG. Admin users that have privilege to access both GBUs will see data for both GBUs but the GBU field will always be pre-selected to one of the GBUs. Admin Users can then switch the GBU selection from one to the other however they will not be able to select both GBUs at the same time.

Filters: The following filters are displayed in the CPC Prioritisation sheet to enable user to filter data and drill-down on dimensions such as GBU Product Family / H4 Prod L4 , Product / Material , Customer , Market Cluster / Group of Activity, CPC, Sales Rep and GBU. These filters are displayed at the top of the sheet.

Version Date & Version Run date: Version date and Version run date are display information presented to user to indicate when a particular version of the data set was created at the backend and the exact run date for that particular version. Version date is displayed in YYYY-MM format whereas version run date is displayed in the YYYY-MM-DD format.

Currency & Volume unit switches: These filters or selection elements allow users to select a currency and unit of measure of their choice. Based on the selection by the user, the dashboard converts or switches monetary measure values to selected currency and the volume measures to units of measure selected by the user.

Visuals: There are four KPIs and 2 charts. The KPIs Number of CPCs, Price Increase potential impact, minimum potential impact considered and CPC with Price increase recommendation. For the business definition of these KPIs please refer to business documentation. The two charts displayed are a horizontal bar chart and a table object. The Horizontal bar chart dimensions are Product family, Sold-To, GBU region and Customer Segment. The measure or the metric calculated along these dimensions are the measure of price increase potential impact.

The Table object displays detailed information on CPC basis. The fields displayed are shown below. There are also buttons to sort the values in the table Price and Volume. By default the sort order is such that numeric fields such as Price, Volume, Dates are sorted numerically in descending order while textual values are sorted alphabetically in ascending order.

Show/Hide conditions: Show / hide conditions are used through out the dashboard to conditionally show and hide fields and measures depending on the GBU selected by user or the system. This is primarily to show fields and measures that are relevant to a GBU but not the other and vice versa.

Comparables sheet

The measures displayed when a target CPC is selected are Minimum volume threshold, Original Price, Proposed adjustment, median of volume adjusted price. The full business definition of these metrics can be found in the business documentation. A table object displays these metrics across dimensions such as GBU region, customer segment, market cluster / group of activity, incoterms, end use, country ship to, sold to and manufacturing plant.

Volume Adjustments & SHAP analysis sheet

In the volume adjustment sheet the step of recommending a price based on the set of comparable CPC(s) is explored, this step is meant to account for the fact that the volume of sales from one CPC to another can vary, and hence have an impact on the recommended price. In order to achieve this, we can apply a volume adjustment step based on what our model has concluded in terms of impact of the volume on price.

For every family, we are fitting a function that outputs a price variation given a specific volume. The parameters of the function are extracted from DataIku and stored in BigQuery to be loaded in the dashboard. In this manner, we can plot the curve related to the function for each family directly in the dashboard.

For more information on this, please refer to the business documentation.

Note that the SHAP values used to find the price variations (as the y-axis in the graphs) are initially on a LOG10 SCALE! . You will often find "pow (10, x)" in the measures to go back to the "normal" scale. This allows us to have understandable values for the users (the log10 inverse function is 10^x). Volumes are also represented on a log10 scale to handle outliers with very high values.

The primary objective of this sheet is to provide a simulation tool to help the business understand and explain how volume adjustments are performed in the back end. The users can select any CPC as a comparable and a specific volume from the drop-down list to be adjusted to. The sheet then we displays all the steps leading to the volume adjusted price.

Visuals presented:

Volume importance by CPC from the model: scatterplot displaying all the CPC of the family with the price variation associated to their volume (in log scale). This price variation directly comes from the SHAP values output by the model (and brought out of the log scale as mentioned above). This is the scatterplot that is used to fit the volume adjustment function inside the back end.

Resulting volume curve: Line chart representing the function fitted on previous scatterplot. Plotted thanks to the function parameters we import from the back end.

On both these objects, we add reference lines to show where the selected CPC and target volume stand and better understand the associated variations on the price.

The KPIs displayed at the bottom of the sheet represent the calculation details that lead to the volume adjusted price. Once again, since the values are initially on a log scale, subtracting the two adjustment ratios would not provide the right results: we need to divide the values. log(a - b) = log(a) / log(b)

Volume Adjustments sheet displays KPI measures such as Comparable volume, Comparable original price, Target adjustment ratio, Comparable adjustment ratio, final ratio and the Volume adjusted price. The business definition for these measures and KPIs can be found in the business documentation. To display all information selection of product family, version, volume and Target CPC are required for the charts and KPIs in the sheet to populate.

Product family and a version should be selected to view the charts in the SHAP analysis sheet. There is a table that displays Feature weights per product family along with the feature, feature value, average feature value, & feature weights. A horizontal bar chart displays average price variation based on feature and SHAP values. A bubble chart plots the SHAP value per feature.

Data extracts

The data extract tab or sheet provides a means for the end user to filter & download / extract the data in spreadsheets for further analysis outside of the Qlik App. A version number should be selected by user to reduce the dataset for download.

Data Model

The OptiPrice data model is as shown below. The main dimension table is the DIM_CPC table. Fact_Recommendation is the primary fact table where the recommendation metrics are maintained. Bridge_Recommendation table acts as a link table or a bridge table between the Fact_Recommendation table and the Fact_Comparable table which is the second fact table. Volume_curves and SHAP_values tables are maintained purely to hold values to use in the front-end visuals within Volume adjustment sheet or the Shap analysis sheet. All the tables in the model are linked via various keys so that data filtering and drill-down / roll-up functionality within Qlik works as expected. Island tables such as FX_Rates, UOM_Rates are used as reference tables. Link_Table is part of the Qlik row level security section access visibility feature that ensures end-users can view data only belonging to their respective GBU.

The OptiPrice apps data model is based on a standard star schema model, albeit with two fact tables linked via a Bridge_Recommendation table. As described above there is a single dimension table and a couple of special tables for volume curve plotting and SHAP analysis.

ID_reco, ID_family_reco, ID_reco_fact_recommendation, ID_comparable and CPC are the primary keys used to link all the various tables together. A number of these keys are composite keys. i.e formed by combining two or more fields to form a single key (composite Key) field.

The list of tables and fields that are part of the data model are attached below.

Security and Visibility Model

The visibility model for the “OptiPrice” app is based on single field which is the GBU of the User. This is implemented such that users from one GBU can not access data from the other GBU and vice versa. The two GBUs that are applicable and in scope of the dashboard app currently are Novecare (CS) and Speciality Polymers (SPP). In the case of Speciality Polymers, for all confidential accounts, any sensitive fields, are masked. These sensitive fields are Sold-To Name / Account Name.

The confidential accounts information is sourced from the “Solvay Data/Apps/Common Data/Salesforce/Tables_Icare/Account.QVD” QVD file. Wherein the field” PRS_Code_Account__c” and “Confidential__c” denote the account and the confidentiality flag. This information is ingested into the data model and then any customer account flagged as confidential is identified and the corresponding sensitive fields are masked with a pre-defined text called “Confidential” instead of maintaining and displaying the actual value of the field. The following sensitive fields are masked for confidential accounts

• "Sold-To"
• "Sold-To Name"
• "Sold-To group"
• "Ship-To"
• "Ship-To Name"