Interactive price analytics using transaction history data

Data Factory
Machine Learning
Excel
Blob Storage
SQL Database

The Price Analytics solution uses your transactional history data to show you how the demand for your products responds to the prices you offer. It recommends pricing changes and allows you to simulate how changes in price would affect your demand, at a fine granularity.

The solution provides a dashboard where you can see the following:

  • Optimal pricing recommendations.
  • Item elasticities at an item-site-channel-segment level.
  • Estimates of related-product effects such as cannibalization.
  • Forecasts given current process.
  • Model performance metrics.

Using direct interaction with the pricing model in Excel, you can:

  • Paste your sales data there and analyze your prices without the need to integrate the data into the solution database first.
  • Simulate promotions and plot demand curves (showing demand response to price).
  • Work with dashboard data in numerical form.

The rich functionality isn't confined to Excel. It's driven by web services that you or your implementation partner can call directly from your business applications, integrating price analysis into your business applications.

Potential use cases

This architecture is ideal for the retail industry, providing pricing recommendations, estimations, and forecasts.

Architecture

Architecture Diagram

Download an SVG of this architecture.

  1. Azure Machine Learning enables building pricing models.
  2. Azure Blob Storage stores model and any intermediate data that's generated.
  3. Azure SQL Server stores transaction history data and any generated model predictions.
  4. Azure Data Factory is used to schedule periodic (for example, weekly) model refreshes.
  5. Power BI enables a visualization of the results.
  6. Excel spreadsheets consume predictive web services.

Components

Solution description

At the core of a rigorous price analysis workflow is price elasticity modeling and optimal pricing recommendations. The state-of-the-art modeling approach mitigates the two worst pitfalls of modeling price sensitivity from historical data: confounding and data sparsity.

Confounding is the presence of factors other than price that affect demand. We use a "double-ML" approach that subtracts out the predictable components of price and demand variation before estimating the elasticity. This approach immunizes the estimates to most forms of confounding. The solution can also be customized by an implementation partner to use your data capturing potential external demand drivers other than price. Our blog post gives more detail on the data science of prices.

Data sparsity occurs because the optimal price varies at a fine grain: businesses can set prices by item, site, sales channel, and even customer segment. But pricing solutions often only give estimates at product category level, because the transaction history may only contain a few sales for each specific situation. Our pricing solution uses "hierarchical regularization" to produce consistent estimates in such data-poor situations: in absence of evidence, the model borrows information from other items in the same category, same items in other sites, and so on. As the amount of historical data on a given item-site-channel combination increases, its elasticity estimate will be fine-tuned more specifically.

This pricing analytics solution idea shows you how you can develop a pricing model for products that is based on elasticity estimates from transaction history data. This solution is targeted at mid-size companies with small pricing teams who lack extensive data science support for bespoke pricing analytics models.

Interaction with the pricing model is via Excel where you can easily paste your sales data and analyze your prices without the need to integrate the data into the solution database first. In the spreadsheet, you can simulate promotions and plot demand curves (showing demand response to price), and access dashboard data in numerical form. The rich functionality of the pricing model can also be accessed from web services, integrating price analytics directly into your business applications.

Azure Machine Learning is the core logic in this solution from which elasticity models are created. Machine learning models can be set up with to avoid two common pitfalls of price modeling from historical data: confounding effects and data sparsity.

The solution provides the following advantages:

  • Shows you in one glance (via the dashboard) how elastic your product demand is.
  • Provides pricing recommendations for every product in your item catalog.
  • Discovers related products (replacements and complements).
  • Lets you simulate promotional scenarios in Excel.

Deploy this scenario

The AI Gallery solution, which is an implementation of this solution architecture, has two key roles: technical resources and end users (such as pricing managers).

Technical resources deploy the solution and connect it to a business data warehouse. For more information, read the Technical Guide. End users using the model via a spreadsheet (or integrated into a business application), should read the User Guide.

Getting started

Deploy the solution with the button on the right. Instructions at the end of the deployment will have important configuration information. Please leave them open.

The solution deploys with the same example data set of orange juice prices that you find behind the Try-It-Now button on the right.

While the solution is deploying, you can get a head start and do the following:

  • See what is available in the Try-It-Now dashboard.
  • Read the User Guide for usage instructions from the perspective of a pricing analyst (MSFT login required).
  • Review the Technical Deployment Guide for a technical implementation view (MSFT login required).
  • Download the interactive Excel worksheet.

After the solution deploys, complete the first walkthrough (MSFT login required).

Solution dashboard

The solution dashboard's most actionable part is the Pricing Suggestion tab. It tells you which of your items are underpriced or overpriced. The tab suggests an optimal price for each item and the predicted impact of adopting the suggestion. The suggestions are prioritized by the largest opportunity to earn incremental gross margin.

An implementation of this pricing analytics solution idea is described in the AI Gallery solution and GitHub repro. The AI Gallery solution uses your transactional history data to show how the demand for your products responds to the prices you offer, recommend pricing changes, and allow you to simulate how changes in price would affect your demand, at a fine granularity. The solution provides a dashboard, where you can see optimal pricing recommendations, item elasticities at an item-site-channel-segment level, estimates of related-product effects such "as cannibalization", forecasts given current process, and model performance metrics.

Solution architecture

The solution uses an Azure SQL Database instance to store your transactional data and the generated model predictions. There are a dozen elasticity modeling core services, which are authored in Azure ML using Python core libraries. Azure Data Factory schedules weekly model refreshes. The results display in a Power BI dashboard. The provided Excel spreadsheet consumes the predictive Web Services.

Read the Technical Deployment Guide for a more detailed discussion of the architecture, including the topic of connecting your own data and customization (GitHub login required).

Pricing

To calculate a current estimate, use the Azure pricing calculator. The estimated solution should include the following service costs:

  • S1 standard ML service plan
  • S2 SQL Database
  • App hosting plan
  • Miscellaneous ADF data activities and storage costs

If you're just exploring the solution, you can delete it in a few days or hours. The costs are will stop being charged when you delete the Azure components.

Next steps

Learn more about the component technologies:

Learn more about pricing solutions:

Explore related architectures: