How price matrix performs calculations
Describes how the price matrix performs calculations in Optimizely Configured Commerce.
Optimizely Configured Commerce introduced the Price Matrix early in the development process to accommodate a wide variety of different pricing schemes in a generic way. It was designed against 4 separate ERP systems to find the common architectures and pricing options so that it could offer a comprehensive and robust pricing capability and support the viewpoint that commerce should be able to be conducted without having to rely on a live connection to the ERP.
The PriceMatrix table itself is intentionally flat and non-normalized to increase efficiency and speed in price calculations. To use the price matrix structures, however, it is incumbent upon the developer to ensure they understand the pricing algorithms and pricing rules in the targeted system, typically the ERP, and then must essentially replicate that rule set. Many customers find that using real-time calls to exposed APIs from the ERP is a better approach, which Configured Commerce supports. With that in mind, it is essential that the individuals implementing the Price Matrix fully understand how the different customer and product price levels are set, both from a business and technical point of view.
Initially, the Price Matrix was rolled out using a stored procedure which, while fast, required multiple single-reads into the database. With 3.7.1 we moved from stored procedures directly to a pricing service, encapsulating all of the stored procedure logic into code. We provide the Generic pricing service as defined by the application setting PriceCalculator, which has a lot of functionality and is a good basis for building out a customized version for specific use.
Once the system has selected a Price Matrix record to use, it will pull out the proper quantity break row and calculate the price. If no price matrix record is found, the Product List Price will be used.
The following steps describe the calculation logic for the Generic Price Calculator. It will be followed by ERP-specific adjustments to that.
The basic flow will be:
- Gather all appropriate records from the db
- Filter out the ones that don't matter
- Organize the records from most specific to most general
- Find the right record to use
- Calculate the prices
- Compare to sales price
- Return results
There are some unique complexities introduced when we introduce multiple units of measure and alternate currencies. Additionally, when we try to return a list of prices in a quantity range, we need to meld the sale price at each quantity break to the standard price and return the appropriate table information.
When we indicate most specific to most general, this also applies to warehouse and unit of measure.
| Price Basis | Adjustment Type | Basis Amount | Entered Amount | Calculation | Calculated Price |
|---|---|---|---|---|---|
| List | Amount | 125 | -10 | List Price + Amount | 115.00 |
| List | Percent | 125 | -10 | List Price * (1 + Percent) | 112.50 |
| Override | Amount | N/A | 110 | Amount entered | 110.00 |
| Override | Percent | N/A | N/A | INVALID | |
| Cost | Amount | 75 | 30 | Cost + Amount | 105.00 |
| Cost | Percent | 75 | 30 | Cost * (1 + Percent) | 97.50 |
| Margin | Amount | 75 | 30 | Cost + Amount | 105.00 |
| Margin | Percent | N/A | N/A | INVALID | |
| Markup | Amount | 75 | 30 | Cost + Amount | 105.00 |
| Markup | Percent | 75 | 30 | Cost * (1 + Percent) | 97.50 |
Each individual quantity break line stands on its own relative to the calculations.
Updated over 1 year ago