Data Mapping (Optimization - Price Waterfall)
- Jana Volencová (Deactivated)
This section details how to create the data structure needed if you want to use the Optimization - Price Waterfall Accelerator and how to configure it during the deployment.
Objectives of Data Mapping
By default, the Accelerator generates an “aggregation” table containing data in a format understood out-of-the-box by the deployed logics. For this, the Accelerator needs to have the raw transactions lines available in a Datamart. Then, the Accelerator will ask for a mapping between the fields of the transactions Datamart and the ones that will be used in the Accelerator.
This approach ensures that the expected fields are correctly aggregated and that their names are compatible with the deployed logics.
Standardized Waterfall
To be as fast and generic as possible, a standardized waterfall is built into the Accelerator and is designed to cover most of the customer needs. This waterfall looks like this:
A key part of the setup is mapping the actual customer waterfall columns to this standard one. This could mean that some of the elements of the customer waterfall will be aggregated within a single element of the standard waterfall and that some elements may not be used at all.
Illustrative example:
As a result:
Customers need to be aware of the fact that the results will not reflect their exact waterfall.
We can standardize most things – mainly the way we consume results (Optimization Realization Dashboards, Price Setting, Agreements & Promotions, etc.).
What to Map and How
The prerequisite to the Accelerator deployment is the availability of a Datamart containing the transaction data and the fields listed below. You may need to create such a Datamart if the existing data are not directly available or integrate information from several distinct sources (e.g. Master Data + Data Source + Price Extension).
Transactions Datamart Prerequisites
The transaction source must contain the following fields (create them if needed):
Product field – ID of each product. Be careful: it cannot be a source key. If it is, duplicate the field to be able to access it.
Product Group field – ID of each product group. Be careful: it cannot be a source key. If it is, duplicate the field to be able to access it. Must be defined as a dimension.
It must be a segmentation level of the segmentation model you will use in the optimization.
Customer field – ID of each customer. Be careful: it cannot be a source key. If it is, duplicate the field to be able to access it.
Customer Group field – ID of each customer group. Be careful: it cannot be a source key. If it is, duplicate the field to be able to access it. It must be defined as a dimension.
It must be a segmentation level of the segmentation model you will use in the optimization.
Quantity field – Number of products in each transaction.
Global List Price field − Global list price in each transaction (extended to quantity, Optimization Price Waterfall Accelerator does not expect unit prices). It is a price defined at a product level.
Specific List Price field − extended to quantity. List price after applying a specific adjustment (defined at the level of the customer group) to the global list price in each transaction. If it doesn’t exist in your waterfall, you can map it with the Global List Price field again.
Invoice Price field – extended to quantity. It is the price after applying the on-invoice discount (defined at the customer level) to the specific list price in each transaction. If it doesn’t exist in your waterfall, you can map it with the Specific List Price field again.
Net price field − Price after applying an off-invoice discount to the invoice price in each transaction (extended to quantity, Optimization Price Waterfall Accelerator does not expect unit prices). If it doesn’t exist in your waterfall, you can map it with the Invoice Price field again.
Net net price field − Price after applying a vendor rebates to the net price in each transaction (extended to quantity, Optimization Price Waterfall Accelerator does not expect unit prices). If it doesn’t exist in your waterfall, you can map it with the Net Price field again.
Pocket Margin field – extended to quantity. The master unit cost of each product will be inferred from Pocket Margin, Net Net Price, and Quantity.
Avoid null values in the first level of segmentation used to calculate the elasticities, because the corresponding transaction rows will be ignored by the elasticity model and will cause an error in Optimization Price Waterfall models. If necessary, create a new field by using an expression that replaces empty values with a text (e.g., “Not provided”).
There are also some label fields asked during the mapping. If not provided, the model will use the corresponding id field (product field for the product label field, and so on.)
Dealing with Missing Fields
In some cases, you will want to instantiate a waterfall without all of the discounts. However, the fields are still required by the Accelerator to do its mapping, meaning that it will expect values you cannot give from a Data Source as they are not available in your problem. In this case, you can simply provide the same fields several times to simulate two sequential values separated by a discount of 0.
Example:
In this example, the column of the Datamart “Invoice Price” is used twice to compensate a missing “Net Price”. The “Net net price” exists and the discount between it and the “Net Price” will be computed with the “Invoice Price” instead. The discount between the column “Invoice Price” and “Net Price” will always be 0, as they refer to the same value.