Retail Price Optimization

14 min readJun 13, 2024

Introduction

Pricing a product is a crucial aspect of any business. Retail price optimization refers to the process of determining the ideal price at which to sell products or services in order to maximize profitability and achieve business objectives.

Let’s focus on retail price optimization in the context of the food industry. In today’s competitive food industry, optimizing retail prices is crucial for maximizing profits and ensuring customer satisfaction. This blog explores our innovative approach to retail price optimization for food products in a burger café, leveraging data analytics and machine learning to reach the perfect balance between value and profitability.

Problem

In our burger café, we face the challenge of setting optimal prices for our food products to maximize revenue without alienating customers. The main problem is understanding how different prices impact sales volume and overall profitability, given the varying sensitivities of customers to price changes.

To solve this, we employ a data-driven approach to retail price optimization. By analyzing historical sales data and customer purchasing patterns, we can identify the optimal price points that balance demand and profitability. This approach allows us to make informed pricing decisions that enhance customer satisfaction and inventory planning.

Data

To begin with the problem let’s see what data we have in our hands to solve it.

We have 3 CSV files collected from the cafe

Dates: contains the data about dates (whether it is a working day, h)
Metadata: contains the metadata about the products
Transaction: contains the transactional data of food products

Let’s load the data and see what it contains

# importing the required libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt

# turning off warnings
import warnings
warnings.filterwarnings("ignore")

# loading the data
dates = pd.read_csv("Dates.csv")
products= pd.read_csv("Metadata.csv")
transactions = pd.read_csv("Transaction.csv")

# printing the first 5 rows
products.head()

SELL_ID: Unique ID of the basket/cart
SELL_CATEGORY: 0 if the product is sold alone 2 if the product is sold with some other products
ITEM_ID: Unique ID for the product
ITEM_NAME: Name of the product

We are selling 4 food items namely Burger, Coffee, Lemonade, Coke

# checking unique no of products
print(f"The total no of unique products is {products['ITEM_ID'].nunique()}")
print(f"The shape of sales data is {products.shape}")

# checking for null values
sales.isna().sum()

transactions.head()

CALENDAR_DATE: Date of transaction
PRICE: Price of the product
QUANTITY: Quantity of the product
SELL_ID: Unique ID of the basket/cart
SELL_CATEGORY: 0 if the product is sold alone 2 if the product is sold with some other products

print(f"The shape of the data {transactions.shape}")
print(f"We have data from {transactions['CALENDAR_DATE'].min()} to {transactions['CALENDAR_DATE'].max()}")
print(f"The max price is {transactions['PRICE'].max()}")
print(f"The min price is {transactions['PRICE'].min()}")

""" DISTRIBUTION OF PRICE"""

# Set the aesthetic style of the plots
sns.set(style="whitegrid")
# Create a figure and a set of subplots
plt.figure(figsize=(8, 5))
# Plot the distribution of the numerical column
sns.histplot(transactions['PRICE'], kde=True)
# Add titles and labels
plt.title('Distribution of Price')
plt.xlabel('Price')
plt.ylabel('Frequency')
# Show the plot
plt.show()

""" DISTRIBUTION OF QTY"""

# Set the aesthetic style of the plots
sns.set(style="whitegrid")
# Create a figure and a set of subplots
plt.figure(figsize=(8, 5))
# Plot the distribution of the numerical column
sns.histplot(transactions['QUANTITY'], kde=True)
# Add titles and labels
plt.title('Distribution of Quantity')
plt.xlabel('Quantity')
plt.ylabel('Frequency')
# Show the plot
plt.show()

""" CORRELATION BETWEEN PRICE AND QTY"""

# Set the aesthetic style of the plots
sns.set(style="whitegrid")
# Create a scatter plot
plt.figure(figsize=(7, 5))
sns.scatterplot(x='PRICE', y='QUANTITY', data=transactions)
# Add titles and labels
plt.title('Scatter Plot of PRICE vs QUANTITY')
plt.xlabel('PRICE')
plt.ylabel('QUANTITY')
# Show the plot
plt.show()

dates.head()

# checking for any null values
dates.isna().sum()

Here we have 1244 Null values in the HOLIDAY feature the missing values in the HOLIDAY feature denotes a working day, so let’s fill them with “No Holiday”

# filling NaN with No holiday
dates['HOLIDAY'] = dates['HOLIDAY'].fillna("No Holiday")
print(f"The shape of the data is {dates.shape}")
print(f"The Holidays are {dates['HOLIDAY'].unique()}")
print(f"We have the data from {dates['CALENDAR_DATE'].min()} to {dates['CALENDAR_DATE'].max()} ")

Transforming the Products Data

We have sell_id for each transaction, this sell_id represents the unique combination of the food products that are bought together. In our Products dataframe we have sell_id for each products, so let’s combine them

# Create dummy variables
dummies = pd.get_dummies(products['ITEM_NAME'],dtype=int)

# combining the dummies and sell id
sell_ids = pd.concat([products['SELL_ID'],dummies],axis=1)

# in the above dataframe we have duplicates so lets group them
sell_ids = df_dummies.groupby(df_dummies['SELL_ID']).sum().reset_index()

sella_ids.head()

Here if you see the sell_id 1070 refers to the purchase of burgers alone whereas the sell_id 2052 refers to the purchase of Burgers with a Lemonade

# merging the sales data with transactions data
df_merged = pd.merge(sales.drop(columns=["ITEM_ID"]),transactions.drop(columns=["SELL_CATEGORY"]),on="SELL_ID")
df_merged.head()

Here we have an individual row for each transaction of food at various dates, but we are interested in the number of quantities each food item is sold per day, so let’s group the transactions of the same products

# eliminating duplicates by grouping the column
df_transaction = df_merged.groupby(["SELL_ID","SELL_CATEGORY","ITEM_NAME","CALENDAR_DATE","PRICE"])["QUANTITY"].sum().reset_index()
df_transaction

print(f"The shape of data before aggregating {df_merged.shape}")
print(f"The shape of data after aggregating {df_transaction.shape}")

Let’s add features about dates to this data

# merging the df_transaction and dates dataframe
df = pd.merge(df_transaction,dates,on="CALENDAR_DATE")

# exporting the combined data to a csv file
df.to_csv("data.csv",index=False)

Exploratory Data Analysis

Now this data consists of transactions made on all days including festivals, holidays, and special days but we know that on holidays, weekends and school breaks the price of the food items change, so we are going to remove transactions made on those dates to remove the effect due to the holidays, weekend and school breaks so that we can determine the typical price of the product on the usual day

# removing transactions made on Holidays, Week-ends, School breaks
df_filtered = df[(df['HOLIDAY'] == "No Holiday") & (df['IS_SCHOOLBREAK'] == 0) & (df['IS_WEEKEND'] == 0)]

Univariate Analysis

Food Items

# Set the aesthetic style of the plots
sns.set(style="whitegrid")

# Create the count plot
plt.figure(figsize=(10, 6))
sns.countplot(x='ITEM_NAME', data=df, palette='viridis')

# Add titles and labels
plt.title('Item Count Plot')
plt.xlabel('Item Name')
plt.ylabel('Count')

# Show the plot
plt.show()

2. Distribution of Price

# Create subplots with two plots side by side
fig, axes = plt.subplots(1, 2, figsize=(18, 6))

# Set the aesthetic style of the plots
sns.set(style="whitegrid")

# Plot the first Kernel Density plot
sns.kdeplot(df['PRICE'], shade=True, color='b', ax=axes[0])

# Add titles and labels to the first plot
axes[0].set_title('Kernel Density Plot of Price (Original')
axes[0].set_xlabel('Price')
axes[0].set_ylabel('Density')

# Plot the second Kernel Density plot
sns.kdeplot(df_filtered['PRICE'], shade=True, color='b', ax=axes[1])

# Add titles and labels to the second plot
axes[1].set_title('Kernel Density Plot of Price (Filtered)')
axes[1].set_xlabel('Price')
axes[1].set_ylabel('Density')

# Adjust layout
plt.tight_layout()

# Show the plots
plt.show()

From the KDE plot we can clearly see that there are more than single peaks which means Price follows bi-modal distribution

3. Price vs Quantity

# Set the aesthetic style of the plots
sns.set(style="whitegrid")

# Create the scatter plot
plt.figure(figsize=(9, 4))
sns.scatterplot(x='PRICE', y='QUANTITY', data=df)

# Add titles and labels
plt.title('Scatter Plot of Price vs Quantity')
plt.xlabel('Price')
plt.ylabel('Quantity')

# Show the plot
plt.show()

We can clearly see that there are two groups in the scatter plot, Let's investigate it further

We can see that the burgers present in the upper part of the distribution while other foods such as Coffee, lemonade and coke present in the lower part

Finding Price Elasticity

Elasticity is the degree to which the effective desire for something changes as its price changes. In general, people desire things less as those things become more expensive. However, for some products, the customer’s desire could drop sharply even with a small price increase, and for other products, it could stay almost the same even with a big price increase. Economists use the term elasticity to denote this sensitivity to price increases.

More precisely, price elasticity gives the percentage change in quantity demanded when there is a one percent increase in price, holding everything else constant.

We are going to model the food quantity sold in terms of the food price using OLS from the stats model API so let us create a function that returns the elasticity of the food item (the slope of the price feature)

# defining a function to create and find price elasticity
def create_model_and_find_elasticity(data):

    # creating an OLS model and fitting it on the data
    model = ols("QUANTITY ~ PRICE",data=data).fit()
    # getting the price elasticity
    price_elasticity = model.params[1]
    # printing the stats
    print(f"\n\nThe price elasticity of the model is {str(price_elasticity)}\n\n")
    print(model.summary())
    # plotting
    fig = plt.figure(figsize=(10,5))
    fig = sm.graphics.plot_partregress_grid(model,fig=fig)
    plt.show()

    return price_elasticity,model

# creating a dictionary to store the elasticities
price_elasticities={}

Burger

# filtering the transactions with burger
burgers = df_filtered[df_filtered['ITEM_NAME']=="BURGER"]
print(f"\n\nThe shape of the data is {burgers.shape}\n")
burgers.head(2)

We are interested in finding the optimal price of a burger for maximum profit, So let’s now plot the price vs Quantity plot for burgers

# doing scatterplot for Price vs Quantity for burgers based on Sell id
sns.scatterplot(x='PRICE', y='QUANTITY', data=burgers,hue="SELL_ID",alpha=0.1)

We can see clearly even for burgers there are different groups in the plot which denote Burgers under different sell_id

Now, we have 4 SELL_ID 1070, 2051, 2052, 2053 for burgers

1070: Burgers
2051: Burger and Coke
2052: Burger and Lemonade
2053: Burger, Coffee, and Coke

As we can see burgers bought under different sell_id has different distributions hence we need to model each separately

a.Burger 1070 (Burgers that are bought alone)

# filtering the burgers with sell id 1070
burgers_1070 = burgers[burgers["SELL_ID"]==1070]

# doing scatterplot for Price vs Quantity for burgers 
sns.scatterplot(x='PRICE', y='QUANTITY', data=burgers_1070)

Let’s now try to fit a linear regression to find the relation between Price and Quantity (Modelling the Quantity interms of Price)

# fitting a OLS regression on burgers 1070
create_model_and_find_elasticity(burgers_1070)

By seeing the report we can infer that the r2 score is just 0.48 which is lesser this is because the OLS cannot fit the data properly as it has two trends this can be seen in the above plot there are two trends but we are trying to fit both the trends using the same model which results in low r2 score

Now let’s filter the data further to check if we can separate these two trends, we have a feature called IS_OUTDOOR so let’s try to separate the data based on this feature

# doing scatterplot for Price vs Quantity for burgers based on Sell id
sns.scatterplot(x='PRICE', y='QUANTITY', hue="IS_OUTDOOR",data=burgers_1070)
plt.show()

We can clearly see that there are two different trends so let's try to eliminate the additional trend due to iS_OUTDOOR (0)

# filterting burger transactions with IS_OUTDOOR==1
burgers_1070_filtered = burgers_1070[burgers_1070["IS_OUTDOOR"]==1]

# modelling the filtered data using the OLS
price_elasticities["burger_1070"],burger_1070_model = create_model_and_find_elasticity(burgers_1070_filtered)

Now we can see that our R2 score has been increased when we eliminate burgers with IS_OUTDOOR=0

Now we can see our OLS model is now fitting well on the filtered data with a r2 score of 0.8, we can also see that the coefficient of the Price feature is -8 which denotes it’s price elasticity

Now let’s try to model burgers with other sell_ids

b.Burger 1070 (Burgers that are bought alone)

# filtering the burgers with sell id 1070 and outdoor == 1
burgers_2051 = burgers[(burgers["SELL_ID"]==2051) & (burgers["IS_OUTDOOR"]==1)]

# fitting the ols model
price_elasticities['burgers_2051'],burger_2051_model = create_model_and_find_elasticity(burgers_2051)

Here we can see that our model is somewhat able to model the relation with a r2 score of 0.4

c. Burger 2052 (Burgers that are bought with Lemonade )

# filtering the rows with burger under sell id 2052
burgers_2052 = burgers[(burgers["SELL_ID"]==2052) & (burgers["IS_OUTDOOR"]==1)]

# fitting the ols model
price_elasticities['burgers_2052'],burger_2052_model = create_model_and_find_elasticity(burgers_2052)

2. Coke

# filtering the transactions with burger
coke = df_filtered[df_filtered['ITEM_NAME']=="COKE"]
print(f"\n\nThe shape of the data is {coke.shape}\n")
# doing scatterplot for Price vs Quantity for burgers based on Sell id
sns.scatterplot(x='PRICE', y='QUANTITY', data=coke,hue="SELL_ID",alpha=0.1)

Now, we have 4 SELL_ID 1070, 2051, 2052, 2053 for burgers

1. 2051 = Coke with Burger
2. 2053 = Coke with Burger and Coffee
3. 3028 = Coke that are bought alone

As we can see coke bought under different sell_id has different distribution hence we need to model each separately, We can also see that there are no cokes sold under sell id 3028 which means no coke are sold alone which is true nobody comes to a burger cafe to buy a coke alone

a. Coke 2051 (Coke that are bought with Burgers)

# filtering the rows where cokes are bought under sell id 2051
coke_2051 = coke[(coke['SELL_ID']==2051) & (coke['IS_OUTDOOR']==1)]
price_elasticities['coke_2051'],coke_2051_model = create_model_and_find_elasticity(coke_2051)

b. Coke 2053 (Coke that are bought with Burger and Coffee)

# filtering the rows where cokes are bought under sell id 2051
coke_2053 = coke[(coke['SELL_ID']==2053) & (coke['IS_OUTDOOR']==1)]
price_elasticities['coke_2053'],coke_2053_model = create_model_and_find_elasticity(coke_2053)

3. Coffee

Now, we have 2 SELL_ID 2053 and 3055 for burgers

a. 2053 = Coffee with Burger and Coke
b. 3055 = Coffee alone

We can ignore the sell_id 3055 because it has no transactions as people won’t buy coffee alone in a Burger cafe

# filtering the transactions with cofee with sell_id 2053
coffee = df_filtered[(df_filtered['ITEM_NAME']=="COFFEE") & (df_filtered['SELL_ID']==2053) & (df_filtered["IS_OUTDOOR"]==1)]
# fitting the model
price_elasticities['cofee'],coffee_model = create_model_and_find_elasticity(coffee)

4. Lemonade

Now, we have 2 SELL_ID 2053 and 3055 for burgers

a. 2052 = Lemonade with Burger
b. 3028 = Lemonade alone

We can ignore the sell_id 3028 because it has no transactions as people won’t buy lemonade alone in a Burger cafe

# filtering the transactions with cofee with sell_id 2053
lemonade = df_filtered[(df_filtered['ITEM_NAME']=="LEMONADE") & (df_filtered['SELL_ID']==2052) & (df_filtered["IS_OUTDOOR"]==1)]

# fitting the model
price_elasticities['lemonade'],lemonade_model = create_model_and_find_elasticity(lemonade)

Finding the Optimal price for maximum profit

Let's take coke_2051 data and see an example of how to find optimal price for achieving maximum profit

coke_profit = ( coke_selling_price — coke_buying_price) * (no_of_cokes_sold)

# checking the minimum price of a coke that has been sold so far
coke["PRICE"].min()

# checking the maximum cost of a coke sold so far
coke['PRICE'].max()

The lowest price a Coke is sold is 10.45 so let’s assume the buying price of the Coke to be 9 (considering the minimum profit of 1 unit when sold at the lowest price)

# assuming the buying price to be 9
buying_price = 9

# fixing the boundaries to search for optimal price
starting_price = 9.5
ending_price = 20

Now let’s try various values between starting and ending price to determine which price gives maximum profit

# creating a test dataframe
test = pd.DataFrame(columns=["PRICE","QUANTITY"])

# generating values between 9.5 and 20 in the interval of 0.01 to try out for finding optimal price
test["PRICE"] = np.arange(starting_price,ending_price,0.01)

# predictinig the quantities that will be sold for the given price 
test["QUANTITY"]= coke_2051_model.predict(test["PRICE"])

# computing the profit
test["PROFIT"] = (test["PRICE"]-buying_price)*test["QUANTITY"]

# finding the index of max profit  price yields maximum profit
idx = np.where(test["PROFIT"]==test["PROFIT"].max())

# finding the price which yileds max profit
opt_price = test["PRICE"].loc[idx].values[0]

print(f"The optimal price for maximum profit is {opt_price}\n\n")

plt.plot(test["PRICE"],test["PROFIT"])
plt.plot(test["PRICE"],test["QUANTITY"])
plt.axvline(x=opt_price, color='green', linestyle='--', label=f'Max Profit at Price {opt_price}')
plt.show()

Here the red line represents the Profit curve (profit vs quantity_sold) and the blue line represents the Price curve (price vs quantity_sold). In the price curve, we can see the number of quantities sold decreases as we increase the price, this is typical behavior of people. In the Profit curve, the quantities sold increase as we increase the profit but after reaching the peak the quantities sold decrease when we increase the profit further

This peak is the maximum profit that can be achieved so we should fix the food price as the corresponding price value

Using the above plot we found the optimal price for the coke_2051 as $15 This means The coke_2501 should be set as $15 to get the maximum profit but the highest cost that coke_2501 sold is $13.4 therefore the cafe has missed the profits that could have been made by optimally setting the cost of coke_2501 as $15

Let’s now write a function to find the optimal price for all products

# defining an empty dictionary for storing optimal prices
optimal_prices ={}

# setting the buying price as 9 for all products
buying_price = 9

# defining the function to calculate the optimal price for all products
def find_optim_price(data,model,buying_price):
    """
    data         : product sales data
    model        : OLS model for respective model
    buying_price : buying price of the product
    
    """
    # finding the starting price for the boundary to search for optimal prices
    starting_price = data["PRICE"].min()-1
    
    # finding the ending price for the boundary to search for optimal prices
    ending_price = data["PRICE"].max()+10
    
    # creating a dataframe 
    test = pd.DataFrame(columns=["PRICE","QUANTITY"])
    
    # generating values between 9.5 and 20 in the interval of 0.01 to try out for finding optimal price
    test["PRICE"] = np.arange(starting_price,ending_price,0.01)
    
    # predictinig the quantities that will be sold for the given price 
    test["QUANTITY"]= model.predict(test["PRICE"])
    
    # computing the profit
    test["PROFIT"] = (test["PRICE"]-buying_price)*test["QUANTITY"]
    
    # finding the index of max profit  price yields maximum profit
    idx = np.where(test["PROFIT"]==test["PROFIT"].max())
    
    # finding the price which yileds max profit
    opt_price = test["PRICE"].loc[idx].values[0]

    # lets plot the price vs profit and price vs quantity  
    plt.plot(test["PRICE"],test["PROFIT"])
    plt.plot(test["PRICE"],test["QUANTITY"])
    plt.axvline(x=opt_price, color='green', linestyle='--', label=f'Max Profit at Price {opt_price}')
    plt.show()

    return opt_price

# finding the optimal prices for all products
optimal_prices['burger_1070'] = find_optim_price(burgers_1070_filtered,burger_1070_model,buying_price)
optimal_prices['burger_2051'] = find_optim_price(burgers_2051,burger_2051_model,buying_price)
optimal_prices['burger_2052'] = find_optim_price(burgers_2052,burger_2052_model,buying_price)
optimal_prices['burger_2053'] = find_optim_price(burgers_2053,burger_2053_model,buying_price)

optimal_prices['coke_2051'] = find_optim_price(coke_2051,coke_2051_model,buying_price)
optimal_prices['coke_2053'] = find_optim_price(coke_2053,coke_2053_model,buying_price)

optimal_prices['coffee'] = find_optim_price(coffee,coffee_model,buying_price)
optimal_prices['lemonade'] = find_optim_price(lemonade,lemonade_model,buying_price)

Conclusion

Thus we have found optimal prices for the food products using the OLS regression. we’ve also learned that setting the right prices is key to balancing profit and customer satisfaction. By analyzing sales data, we found optimal prices that attract customers while maximizing our earnings.

Checkout the notebooks at https://github.com/prxdyu/retail_price_optimization/tree/main

checkout my portfolio at https://prxdyu.github.io/portfolio/

connect with me https://www.linkedin.com/in/prxdyu/