Calculate the optimal selling price for a product in Retail

In this article, we will see how we can calculate for a product the optimal selling price that generate the most sales

How to consider the impact of a price change on the demand ?

There are many ways to measure the correlation between price change and demand variation, from simple to complex approach. Using a logistic regression with multiple features can help you understand the correlation, but most often we end up having a black box.

Yes, the price affect the demand, but they are many more factors mainly externals that will impact it & cannot be captured on the model.

Thus as usual the best solution is a simple approach.

Let’s think about the customer behavior in front of a price change (up or down) for a product.

There are basically 5 mains behaviors :

• The customer will not buy the product if the price increase (1).
• The customer will buy the product but with less quantity if the price increase (2).
• The customer will buy the product for the first time, since the price has decreased (3).
• The customer will buy the product as usual but with more quantity since the price has decreased (4).
• No change on behavior (5).

The behaviors (1), (3) & (5) can be observed on the product penetration% ; which is the number of customers who bought the product / total number of customers on the considered period.

The behaviors (2) & (4) will then be observed on the average number of quantity bought per transaction for the considered product ; which is total quatity sold of the product / total number of customers who bought the product.

These 2 above KPI’s constitue the commercial equation :

• Quantity sold(product i) = Total customers X %Penetration(product i) X average number of quantity bought per transaction (product i)

We need now to measure how the KPI’s vary when the price changes.

An important note is that the customer behavior is higly correlated with his purchase power, thus if you build any price optimal model it has to be by store since customer profile is different, and if you have any customer segmentation in place you can also add the segment as a dimension.

Prepare & test the model

This model can be developped using excel only, which we will do for the example.

First step will be to prepare the data, needed field are :

• Date
• Store id
• Product id
• Selling price
• Units sold
• Penetration %
• Average product sold per transaction

In order to have correct outputs it is better apply basic filters on :

• Products having at least 3 price changes on the period
• Each price change for a product must be for a period of 30 days minimum

In the example we will use the historical data of 3 years for a product in a particular supermarket.

Volume sold vs price change :

We can see that the product had 3 different selling price in the past (orange line), however we can’t see a significant change in the demand (pink line), moreover the demand is also impacted by the customer flow which can be influenced by external factor.

By taking the 2 kpi’s (i) customer penetration & (ii) average product sold per transactions you remove the customer flow impact

Customer penetration vs price change :

We can see that the penetration decrease for price above 2.10, but increase from 1.75 to 2.1.

Average product bought per transaction :

Here the number of product bought decrease when the price increase, which might be an expected pattern.

Now you can merge these 2 kpi’s to have the Average product bought per transaction per customer :

Then you just have to deduct the equation of the blue dot line which represent the polynomial trendline (order 3)

You can display the equation directly on the chart in excel.
(tip : to have the correct equation you must have 14 decimal places)
The equation y = ax² + bx + c
With :
y being the Average product bought per transaction per customer
x the selling price
a = -0.0005945747671
b = 0.00267656553976
c= 0.00009042797073

If you know or predict the customer flow for a period you can now build the below table :

Volume & sales forecast vs selling price

Volume & sales forecast vs selling price

You can now determine the optimal selling price for the maximum volume or maximum sales(& gross profit)

To conclude we have seen that taking only volume versus selling price to deduct a certain price elasticity might be a wrong approach as it does include external factors that infliuence the customer flow.
This simple model on excel can be translated into python model & thus be applied on thousands of products in a matter of minutes.