Building and Integration of a simple ML Price Optimization model into Priceloop NoCode

Source: Alina Grubnyak-Unsplash

In many conversations with our clients and also working with them, we experienced that there is a huge shortage of IT people and therefore hindering many business opportunities. In particular, in pricing department this is the case, you have pricing managers who build their pricing logic/strategies in Excel sheets. However, once it goes into production, usually IT teams are needed to implement it and then once changes need to be done in the pricing logic (called Change Request), usually that can takes up to weeks or month depending on priorities.

With Priceloop NoCode we want to solve this. We built an Excelsheet-like application that offers similar functionality if you were to use Excel but with a standardized API so that IT departments can easily connect to it allowing both business and IT to work together seamlessly. On our roadmap, we are also working on much more easier integration into different data sources and marketplaces.

In this article, I want to show you how you can load data from our platform, create a pricing ML model (heavily based on this Kaggle notebook and adopted for our article) and then export the results into our platform. Once the data is in the platform, the pricing manager can use the output to join it with different meta tables and then add additional pricing logic with our Priceloop built-in functions. After the pricing logic has been applied, the IT can use our API again to load it into their production system. This is a typical situation in many companies. The full code example can be found on our Github.

⚠️ We are on a public alpha now: https://alpha.priceloop.ai/. Try us out and give us feedback at hello@priceloop.ai. The documentation can be found here.

Loading data

For this use case we already preloaded mock data into the Priceloop NoCode platform. In our example, we have some category data, sales data and additional data like holidays, is_weekend etc.

Here’s a snapshot of how the data looks like:

Category data

Sales data

Additional data

Now your data science team can easily work with the data. They can use our Python API to connect to our platform. First let’s install our library.

pip install priceloop-api

Once you have it, you can now use your username/password to connect to our platform. In the future, we will use API keys instead but for now this is how you would authorize against our system. We will also import everything that we need for the modelling:

%matplotlib inline
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime
from statsmodels.formula.api import ols
from statsmodels.graphics.regressionplots import plot_partregress_grid
from priceloop_api.utils import DefaultConfiguration, read_nocode, to_nocode
configuration = DefaultConfiguration.with_user_credentials("username", "password")

Now let’s load our data:

category_data = read_nocode("cafe___sell_meta_data", configuration, limit=20, offset=0)
transactions_data = read_nocode("cafe___transaction___store", configuration, limit=6000, offset=0)

• At the moment you also need to provide a limit/offset, we will make it more user-friendly soon; limit is the maximum of data points that you want to retrieve and offset is where you want to start

Building the ML model

Once we loaded the data, we now can build the model but before that we need to do some cleaning and then also transformations of the data.

# remove duplicates
transactions_data = transactions_data.drop_duplicates()
# merge category data with sales data
data = pd.merge(category_data, transactions_data.drop(columns=["SELL_CATEGORY"]), on="SELL_ID")
# need to group it as we have multiples transactions per day for each SELL_ID
cleaned_data = data.groupby(["SELL_ID", "SELL_CATEGORY", "ITEM_NAME", "CALENDAR_DATE", "PRICE", "COSTS"]).QUANTITY.sum()
cleaned_data = cleaned_data.reset_index()

In this simple example we will use linear regression (OLS) to find our price elasticity:

Here we start with one product first SELL_ID “1070”:

burger_1070 = cleaned_data[cleaned_data["SELL_ID"] == 1070]
burger_1070_model = ols("QUANTITY ~ PRICE", data=burger_1070).fit()
print(burger_1070_model.summary())

This is the regression result:

We can see that the elasticity is around -7, the R-squared is pretty low. In fact this is not a good model but this is also not the goal of this article. Rather we want to showcase the workflow with our platform.

Now since we did it for one product, we can do it for all products as well:

model_elasticity = {}
def create_model_and_find_elasticity(data):
    model = ols("QUANTITY ~ PRICE", data).fit()
    price_elasticity = model.params[1]
    return price_elasticity, model
for i, df in cleaned_data.groupby(["SELL_ID", "ITEM_NAME"]):
    e, model = create_model_and_find_elasticity(df)
    model_elasticity[i] = (e, model)

This is the output:

# all elasticities are negative, this is good
model_elasticity
{(1070, 'BURGER'): (-7.478107135366496,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4fad3af40>),
 (2051, 'BURGER'): (-1.9128005756803146,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4faa1c8e0>),
 (2051, 'COKE'): (-1.9128005756803146,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4fae2af40>),
 (2052, 'BURGER'): (-2.271811473474679,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4fae32be0>),
 (2052, 'LEMONADE'): (-2.271811473474679,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4fae32490>),
 (2053, 'BURGER'): (-5.226102393167906,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4fae39490>),
 (2053, 'COFFEE'): (-5.226102393167906,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4fae40e20>),
 (2053, 'COKE'): (-5.226102393167906,
  <statsmodels.regression.linear_model.RegressionResultsWrapper at 0x7fd4fae47850>)}

Once we found all elasticities, the next step is to find the optimal price where profit is the highest. Again let’s start with one product “1070”.

burger_1070 = cleaned_data[cleaned_data["SELL_ID"] == 1070]
start_price = 8.5
end_price = 25
opt_price_table = pd.DataFrame(columns = ["PRICE", "QUANTITY"])
opt_price_table["PRICE"] = np.arange(start_price, end_price, 0.01)
opt_price_table["QUANTITY"] = model_elasticity[(1070, "BURGER")][1].predict(opt_price_table["PRICE"])
opt_price_table["PROFIT"] = (opt_price_table["PRICE"] - burger_1070.COSTS.unique()[0]) * opt_price_table["QUANTITY"]ind = np.where(opt_price_table["PROFIT"] == opt_price_table["PROFIT"].max())[0][0]
opt_price_table.loc[[ind]]

We can see that the optimal price is around ~17.

Now we can also find the optimal price for all products.

def find_optimal_price(data, model):
    start_price = data["COSTS"].unique()[0] + 0.5
    end_price = data["COSTS"].unique()[0] + 20
    opt_price_table = pd.DataFrame(columns = ["PRICE", "QUANTITY"])
    opt_price_table["PRICE"] = np.arange(start_price, end_price, 0.01)
    opt_price_table["QUANTITY"] = model.predict(opt_price_table["PRICE"])
    opt_price_table["PROFIT"] = (opt_price_table["PRICE"] - data["COSTS"].unique()[0]) * opt_price_table["QUANTITY"]
    ind = np.where(opt_price_table["PROFIT"] == opt_price_table["PROFIT"].max())[0][0]
    opt_price_table["SELL_ID"] = data["SELL_ID"].unique()[0]
    opt_price_table["ITEM_NAME"] =  data["ITEM_NAME"].unique()[0]
    optimal_price = opt_price_table.loc[[ind]]
    optimal_price = optimal_price[["SELL_ID", "ITEM_NAME", "PRICE"]].rename(columns={"PRICE": "ML_PRICE"})
    return optimal_price
optimal_prices = []
for i, df in cleaned_data.groupby(["SELL_ID", "ITEM_NAME"]):
    ml_price = find_optimal_price(df, model_elasticity[i][1])
    optimal_prices.append(ml_price)
optimal_prices_output = pd.concat(optimal_prices)
optimal_prices_output["RUN_TS"] = now = datetime.now()

This is how the output looks now:

Finally, we can export the result to our platform.

to_nocode(optimal_prices_output, "optimal_prices_output", configuration, mode="replace_data")

• Currently, our API supports four modes: replace_data, new, append and recreate_and_delete (default mode). For our use case replace_data is the best option because we want to add additional logic into our platform so that once the data science team retrains the model, all additional logic won’t be removed from our platform.

Applying additional pricing logic in NoCode

Once the data is in NoCode, we can join our data and add additional logic to it. The cool thing is now, if the pricing manager wants to change the min/max prices or other pricing/business logic that they applied, they can do it in our platform without the need to do a change request. Normally, in many production system those logics are encoded into code and therefore IT need to change this.

For example, here we just add a new formula and join the category table with the optimal prices output table that our data science team created to get the minimum prices. We do the same thing for the maximum prices as well.

Finally, we do a check that our machine learning prices is between the min and max prices. If the ML price is lower than the min price, we would take the min price and if the ML price would be higher than the max price, we would take the max price as the final price.

Obviously, this is a very simple example. When working with our customers, those logic can become very complex involving many functions and chaining.

For now we support a limited number of functions but we are adding new functions on a weekly basis and also add support for custom external functions soon. For more information see our function documentation.

Bringing it into production

Finally, once everyone is satisfied, the IT department can use our API again to load the final output into their production system. At the moment, this is the best way to do it. We’re working on adding various sources that you can export to.

from priceloop_api import ApiClient
from priceloop_api.api.default_api import DefaultApi
from priceloop_api.utils import DefaultConfiguration
configuration = DefaultConfiguration.with_user_credentials("username", "password")
with ApiClient(configuration) as api_client:
    api_instance = DefaultApi(api_client)
    workspaces = api_instance.list_workspaces()
    workspace = api_instance.get_workspace(workspaces[0])
    table = api_instance.get_table(workspace.name, workspace.tables[0].name)
    table_data = api_instance.get_table_data(workspace.name, "optimal_prices_output", limit=100, offset=0)
    print(table_data)

Summary

In this article, I showed you how you can easily build a simple ML pricing optimization model and then interact with our Priceloop NoCode platform. At Priceloop, we are working on a completely novel way of how businesses and IT can work together in a more seamless way and therefore bridging the gap between both areas. We are in early stage of our platform and we are excited what comes next and how it will be used by our users.

Hopefully, you enjoyed this article and will try out our NoCode platfrom soon. If you found this article useful, give me a high five 👏🏻 so others can find it too, and share it with your friends. Follow me here on Medium (Dat Tran) or on Twitter (@datitran) to stay up-to-date with my work. Thanks for reading!