This document is intended for the end user of the solution. It will guide you through the use of the solution as it comes "out of the box."
- Introduction
- How to Install the Pricing Solution
- Economic Foundations
- Using the Pricing Engine
- Monitoring the Pricing Engine
- Glossary
- References
The Interactive Pricing Analytics Pre-Configured Solution (PCS) is an Azure cloud application providing a set of tools to recommend prices for wholesale and retail products based on elasticity estimates from transaction records of past sales. It is targeted at mid-size companies with small pricing teams who lack extensive data science support for bespoke pricing analytics models.
This document explains how to install the solution in your Azure subscription, load your transaction data in it, and run the tools from your desktop using the Excel workbook that comes with the solution, to generate prices, e.g. for a monthly promotion, and predict the effect of these prices on sales and revenue.
This document also explains the pricing theory behind the tools, documents the solution architecture as it resides in Azure. The accompanying Technical Deployment Guide goes into more detail about how to integrate the solution with your cloud or on-premise data.
A "solution" refers to an assembly of Azure resources, such as predictive services, cloud storage and
scheduled data pipelines that constitute an Azure application.
There is an entry in the Cortana Intelligency Gallery
that has a single-button install for this solution.
To deploy the solution, click Deploy on the Interactive Price Analytics web page, and .
Assuming you've already set up an Azure subscription, this will place a copy of the resources there. Please follow the installation instructions for the one manual step needed (set up username and password for database). Take note of the final page of the CIQS deployment, listing names of resources deployed. It will help you find where these resources can be found in your Azure Portal. The final deployment page is always available in your CIQS deployments.
Selected Microsoft partner System Integrators and ISVs offer services to customize an installed solution to build pricing decision support applications on Azure specific to a clients' specific needs.
You will need the Excel application workbook for the Solution for interacting with the elasticity models. It has multiple sheets, one for each web service in pricing analysis. Before a sheet can be used, it must be set up by connecting the appropriate web service to the workbook. Please connect these services by going to https://services.azureml.net and pasting the request-response URL into the AzureML plugin after clicking "Add".
Detailed connection instructions are found in the worksheet, on the "Instructions" tab. After adding the services, save the Excel spreadsheet under an appropriate name; we use AnalysisTemplate.xslx. The configured workbook will be used to run the Solution, and display data output from the analytical pipeline.
The Pricing Analytics PCS is an economical, full-featured application driven by the sales transaction history of your business. It discovers products whose prices deviate from optimum and suggests pricing promotions for sets of related products. A pricing manager can examine sales predictions (as forecasts, demand curves, and cross-product effects) as a function of recommended price changes, then track the effect of the pricing changes over time on a dashboard.
Pricing is a core business function for every single company. Yet, there are no one-size-fits-all solutions because every business has a unique history and position in the market. Enormous human, marketing and technical resources are spent on getting pricing right. Today price setting is informed by a combination of experience of long-time employees, competitive market intelligence, experiments and promotions, and supplier forecasts.
Given the abundance of historical data, pricing seems ripe for the application of machine learning. However, there are many confounding effects like holidays and promotions. Machine learning tradition prioritizes prediction performance over causal consistency, but pricing decisions need high-quality inference about causation. Causation is most reliably established using randomized controlled experiments, but experimentation, also known as A/B testing, is extremely difficult to set up on existing operations systems. Observational approaches do not require infrastructural changes, but tools that guard against the many statistical pitfalls are essential.
The pricing solution implements an advanced observational approach to factor out the effect of known confounding factors.
The core concept is that of price elasticity of demand, a measure of how sensitive the aggregate demand is to price.
Self-price elasticity is the percentage "lift" in sales of a product if we discountput it by 1%. Most consumer products have elasticities in the range of 1.5-5. In the chart below, the product has an elasticity of -2 in this small range of prices.
Products with more competitors are easier to substitute away from and will have higher elasticity. Discretionary products are more elastic than staples.
Elasticity (consumer response to price) varies by
- current price point,
- time,
- sales channel,
- location,
- customer segments,
- from product to product, and
- by other considerations.
This Solution includes a model that computes an elasticity for each combination of (Item, Location, Channel, Segment, Week).
Optimal price is any price that optimizes some business objective. The most natural business objective is gross margin, the difference (Price - Marginal Cost). Suppose you sell products with negligible marginal cost, (e.g. electronic media). Then you should lower or raise prices until you reach elasticity of -1. At elasticity -1 you are in equilibrium: you lose a dollar worth of sales quantity every time you raise prices enough to make a dollar from the higher price and your gross margin stays the same.
More frequently, you have substantial marginal costs (cost to make one more piece or acquire it from the supplier). Then the constant -1 in the above calculation is replaced by - (Price)/(Gross Margin), also known as the inverse Lerner Index. For example, suppose your marginal cost of oranges is $4 and you are selling them for $5, making your gross margin $1. Your inverse Lerner index is -5 and you should increase prices at lower elasticities (e > -5), and decrease them otherwise.
Once you've installed the Solution on Azure you work with it from a PowerBI Dashboard and a set of Excel spreadsheets.
In this section, workflow-based examples describe how the solution helps you see your data, build pricing models on schedule or interactively, identify related products, review pricing recommendations, and view promotion impacts in terms of additional sales and margins.
Even before you begin to analyze your data, it may be useful to simply look at it. The Power BI solution dashboard contains two panes for descriptive analysis.
The Time Series tab allows you to see the data over time, filtering down to products, sites, channel, segments and time periods.
- Actual sales time series, filterable by category hierarchy, site, and channel
The Demand-vs-Price tab shows the simplest possible (and inaccurate) method of estimating price elasticity. We plot demand versus price and obtain downward sloping lines expressing how demand decreases with increasing price. Again, the full complement of filters is available.
First, we need to build a model from transactional data. You can either use the pre-built example or input your own data in the provided spreadsheet. Please download the spreadsheet.
Pre-built model. The solution database is pre-populated with a demonstration dataset (Orange Juice) with dates shifted to create an appearance of data arriving weekly. The pre-configured model is automatically re-built weekly from the current data. You will therefore have a model called 'latestDemoBuild' available out of the box.
When the implementor connects the solution to your business data warehouse, data updates per that ETL job (whether it is SSIS, ADF or some other method).
Use your data. The solution allows you to build a model without a full integration from the Excel workbook. You need to prepare your data in the same format as the OJ data given as an example. Please follow this schema:
Navigate to the AzureML plugin and find the service with "BuildModel" in its name. (If you don't have a BuildModel service connected, please connect the service per the Instructions tab of the workbook).
Now, select all of your data, including headers, and click Predict as Batch in the AzureML plugin window. On OJ data, this will take about 3 minutes to output the products, locations and date ranges the engine recognizes. The output should look like this.
We named our model OJ_Demo. You can build models from many datasets, just give them different names to avoid overwriting any models you want to keep around.
Now that our model is built, we get to the main function of the solution, which is to enable a pricing manager to optimize prices offered. The workflow starts with reviewing the Pricing Suggestion dashboard tab. You pick suggestions from the dashboard, analyze them individually, and set new prices.
The steps in the workflow are as follows:
- review overpriced/underpriced items report and suggested prices
- visualize the demand curves
- review elasticities and cross-products effects: substitute and complementary products
- simulate the effect of changing prices for individual products on revenue and margin
- set the new prices in your ERP
Pricing suggestions are directly exposed in the solution dashboard. Please navigate to the Pricing Suggestions tabs in the Power BI solution dashboard and filter down to the specific item and location.
RunDates are used to keep track of the weekly model runs and monitor the performance over time. For analysis, always make sure to select the latest available RunDate in the filter to get the freshest information.
The right side of the Pricing Suggestion tab contains charts ordered by the incremental margin opportunity. If you click on site 130 (highest opportunity) in the example data, the dashboard filters down and tells you the largest margin improvement opportunity is in the Minute Maid juice ($189k). It currently sells for $1.26, massively below cost of $1.84. It seems like the store is running a promotion on the juice and losing money on it. The system proposes to sell it at $2.29 and forecasts demand of 17,159 units at that price.
Dominick's juice is currently sold at $0.99, the marginal cost is $1.25, and the suggested price is $1.69. At the suggested price, we predict the sales would reach 2,386 units.
(Please note that marginal costs in the example data are fictitious and serve to illustrate the margin calculations. They are not provided in the original OJ dataset.)
It may be difficult to trust an opaque model to make a good suggestion if it doesn't expose the elasticity value for each product---the basis of its prediction. The easiest way of seeing the estimated elasticities is to look at the Power BI solution dashboard's the Elasticities tab.
On this tab you can see the distributions of elasticities. For example, you can see that Tropicana juice (red) is the least elastic. The distribution of estimated Tropicana elasticities (over sites, channel, etc.) is centered around -3.5, with two peaks around -3.4 and -3.7. Elasticities of Dominick's and Minute Maid juices have very similar distribution, and both are more elastic products than Tropicana. This corresponds nicely to the slopes of the lines on the Demand vs Price tab, and to the fact of Tropicana being the most desirable of the products.
Over time the estimated elasticities have fluctuated but the overall picture of higher elasticity of Tropicana and lower ones for Minute Maid and Dominick's hold.
Note we have filtered down to only the elasticities estimated in the latest model run (Sunday May 28th). Each model run estimates elasticities from the entire history up to and including the model run date. With long histories, models tend to be stable: if you click through the model runs, the results are very similar.
You can also query the elasticities using the Excel service GetElasticities. With this service, you can query the latest model build (use "latestModelBuild" for datasetName). Because elasticities can change in time, a query date is required in cell A6 of the spreadsheet.
The output consists of a dataframe containing the estimated elasticity of every Item at every Site, as well as the 90% confidence interval for the estimate. The range of elasticities can be seen in one glance by clicking on the generated external figure link containing the elasticity histogram.
Elasticities in the negative-single-digit range are expected. Elasticities much below -10 are a cause for suspicion. Elasticities above zero point to the model not capturing a strong confounding effect. Please review your business with a statistician if you get elasticities above zero (we can help).
Demand Curve visualization is available as an Excel service only. You invoke the DemandCurve AzureML service from the Demand Curve tab of the workbook.
The service gives you the estimated quantity and gross margin of a single product sold over a range of price points for the product. The inputs identifying the product are Location, Product, Channel, Segment. Date and Marginal Cost are also inputs; Date identifies at which point in time the forecast should be made, using elasticity estimate around that time. Marginal cost is needed to calculate the gross margin.
The curve is visualized in the linked picture provided in the last column:
The blue curve is the estimated revenue, the green curve is the gross margin (GM). In this particular illustration, the GM optimal point is all the way out at the top end of the considered range. Note that the current price at this date is much lower than typical for Minute Maid ($2.39) and the model is pushing for a higher price.
The Excel tabular output lets you see the predicted number more precisely and use them in your Excel calculations.
Cross-price elasticities indicate how price of one product affects sales of other products. For instance, if the cross-price-elasticity of demand for peanut butter (the affected product) with respect to price of jelly (the affecting product) is -3, you can expect a 3% increase of demand for peanut butter if you lower the price of jelly by 1%.
When deciding on the price of a product, the purpose of finding the related products is to select which other products to include in the "promotion simulation" step.
The cross-elasticities can be seen on the solution dashboard's Related Products tab.
In Excel, you use the cross-price elasticity service similarly to the elasticity service, specifying a date, and location at which to look for cross-price effects. In Excel, the output is a data frame for your inspection and graphing, and an at-a-glance matrix of cross-elasticities. In our Orange Juice example, we see that all products are substitutes with positive cross-elasticities, and none are complements with negative ones. The strongest effect we see is Minute Maid on Dominicks, meaning that lowering the price of Minute Maid eats into sales of Dominicks, whereas the converse effect is weaker.
We currently assume there is no cross-elasticity affect across sites and channels.
Before making a final pricing decision, many pricing managers will desire to review the predicted effect of a price change, comprehensively across products. This interactive feature is available only in Excel because it requires the user to input a hypothetical promotional scenario (entering data is awkward in Power BI).
In this simulation, we are at the end of week of 1/14 and we look at the predicted effect of setting the price of Minute Maid to $1.75 for two weeks (beginning at the week of 1/28) at location 100. We can see a major increase of Minute Maid demand in week 1 of the promotion and a further increase in week 2 (this is consistent with the anecdote we heard from pricing managers in distribution that it takes a week or so for people to really notice a promotion). Note how the seasonal trend in Tropicana demand is bent down by the promotion in Minute Maid as people substitute to the brand on sale.
This analysis lets you look at the effect of a promotion more holistically.
Each promotion run has a SuggestionRunID, which identifies a promotion.
Well understood and stable model performance reduces uncertainty for decision makers. The following dashboards and services provide visibility into the model operation.
These solution features are intended to expose model perfomance metrics and ensure that the accuracy you are accustomed to is not fluctuating.
At-a-glance view of the model performance is available in the "Model Diagnostics" dashboard tab.
There are two sections, the Elasticity model and the Forecast model diagnostics.
For the Elasticity section, we show the estimated values. On average, these should not move very much for any of the sites, products or items. You are looking for a stable performance across model runs.
For forecasts, we are showing the Symmetric Average Percent Error (SAPE) as the main performance metric, again dis-aggregated by item, site, channel, etc.
There are two Excel services that allow you to pinpoint items where the model may be particularly far off target. Fundamentally, they look for bad forecast failures. You should be extra careful using predictions identified by these services.
This service gives the items which have much higher error than most, it shows how many standard deviations these forecasts were off.
This service displays the failed forecasts on a time axis to allow you to see if forecast failure for individual outliers might be related to exceptional demand events. The counterfactual is visualized as trend line.
- Item: The SKU being sold. We may refer to it as ItemKey when it is not obvious whether we are talking about a product in general (say, identified by a UPC code) or a product being sold at a place, time, and method.
- Site: A store or a warehouse where the demand is fulfilled.
- Channel: Sales channel, for instance In-Store sales vs Internet shipping order. Prices may differ by channel.
- Customer Segment: A grouping of similar customers, distinct from Channel, but may be correlated, or usage of the sales channel may be a means of price discrimination. A typical segmentation might be on "UsedCouponX", with prices differing for different coupon types.