.

# Price Optimisation Using Decision Tree (Regression Tree) - Machine Learning

INTRODUCTION TO THE RESEARCH QUESTION

The research was conducted to find out what price  maximises profit without sacrificing the high demand for the product due to the price being too high nor sacrificing the margins on the product due to the price being too low.

The goal is to experiment with different price levels for the same product in one market place and country to see how sales volumes change with prices and which volume level of products can be sold for that optimal price range.

As a data scientist, it is my responsibility to identify the optimum prices of products so that the items can be sold for maximum profits.

Sales managers and small business owners are faced with the decision of, at what price to sell each of their products in each marketplace or country in order to maximize profit. With each line of product being added and a lot of products to monitor, it is very difficult to determine the optimum price for each product. Using proper analytic and research methods to determine the optimal price for each product will help the small business owners and large companies generate more sales and revenue at the optimum price.

METHODS

The sample included N = 262 individual orders for Interlocking Hearts Design Cake Knife/Server set with OrderItemSKU as 2401 from the period 1st March 2014 to 20th April 2016 with an ecommerce company which sells on Amazon.co.uk

The Profit response variable is measured as the product sale price on amazon.co.uk which includes amazon.co.uk commission and any applicable postage costs less the purchase price of the Hearts Design Cake Knife/Server set from the supplier.

Predictors included:

i)              Isweekday  - Whether item was sold on a WEEKDAY or not

ii)             Isweekend - Whether item was sold on a WEEKEND or not

iii)            Isworkday - Whether item was sold on a WORKDAY or not

iv)           IsBankHoliday - Whether item was sold on a BANK HOLIDAY or not

v)            OrderItemQuantity – total quantity of items bought in one single sale from a customer

vi)           ProductSalePrice – total amount a customer pays for  one single checkout purchase

vii)          SupPurchasePrice – Amount paid to supplier as cost price for the product

Analysis

The distributions for the predictors and the profit response variable were evaluated by examining frequency tables for categorical variables and calculating the mean, standard deviation and minimum and maximum values for quantitative variables.  The YES and NO values of the categorical predictor variables; IsWeekday, IsWeekend, IsWorkday, and IsBankHoliday were recoded to “0” and “1” where “0” means No and “1” means YES.

Where there were duplicate order numbers which happened in one instance, only one observation was chosen for the analysis. Also, all profit figures were considered positive figures. Where there were some negative profit figures in the data, I took the absolute figures of them to obtain only positive values (just for experiment purposes as the data size is too small).

Scatter plot was generated for the relationship between the profit and product sale price.

Decision tree (Regression Tree ) was used to classify the Product Sale Price which resulted in the many numbers of profits at each sale retaining the best possible sales and profits at the same time.   A Test Accuracy score was run to see the model accuracy.

RESULTS

Table 1 below shoes the frequency distribution of the categorical predictor variables IsWeekday, IsWeekend, IsWorkday, and IsBankHoliday.

Below are the histograms for the all predictor variables relative to the Profit of the Interlocking Hearts Design Cake Knife/Server set product.

Table 2 below shows the mean, standard deviation and minimum and maximum values for quantitative variables; Product Sale Price, Order Item Quantity, and Supplier Purchase Price

Fig 1 below shows the Scatter plot that was generated for the relationship between the profit and product sale price. The relation was curvy in nature as can be seen below:

Fig1

so Ordinary Least Square (OLS) regression was used to test the relationship between the 2 variables after centering the product sale price variable.

With the OLS result the Product Sale Price had a p-value of 7.39e-32 which indicates the relationship between the profit and   product sale price is very significant. However, with R-squared value of 0.412 it means the    Product Sale Price only account for 41.2% of the variability in the profit meaning other factors contribute to explaining the variability. However, that is not what I am concerned with at the moment as I only want to determine the optimum price – the price that gives more profit and more sales at the same time. For the same reason, I have not analysed the significance of the relationship between the other quantitative predictor variables Order Item Quantity and Supplier Purchase Price

The Regression Tree that was generated using all the stated predictor variables can be seen in Fig 2 below

Fig 2

The full and bigger tree can be seen here;

Product Sale Price Regression Tree

As the predictor variables were 7 in total, the first split is on the X[4] which is the index of Product Sale Price in the X-axis (the predictor variables axis). There are 157 data that was included in the regression and on the first split, if the price is <=12.375 the split moves to the left otherwise to the right. The Gini index or co-efficient was used in the regression.

And finally a Test Accuracy score was run to see the model accuracy and it indicated that the model was 0.98095238095238091 (98%) accurate.

From the regression tree that was generated, it can be seen the optimum price which gives maximum profit and maximum sales is a price which is greater than £12.375 but less than or equal to £13.72 which is depicted in the Fig 4 below. At that price range there was the greater number of times of sales occurred and profit was made, a total of 58 times, where profit was made much higher than any other leaf in the tree.

Fig 4

.

Conclusions/Limitations

This project used Regression Tree to find out what price maximises profit without sacrificing the high sales volume for the product due to the price being too high nor sacrificing the margins on the product due to the price being too low for N = 262 individual orders for Interlocking Hearts Design Cake Knife/Server set from the period ranging from 1st March 2014 to 20th April 2016 with an ecommerce company which sells on Amazon.co.uk

From the results of the Regression Tree, it can be seen that the optimum price which maximises profit without sacrificing the high demand for Interlocking Hearts Design Cake Knife/Server set product is greater than £12.375 but less than or equal to £13.72.

So the company can price the item within that price range. Also from the IsWeekday Histogram, it can be seen that more profit are made when it is not week day. This can also be seen in the IsWeekend histogram. So during weekends the company can price the item much closer to the upper brackets of the optimum price which is £13.72 and during the weekdays price the item much closer to the lower bracket of the price range which is £12.375.

Furthermore, from the IsBankHoliday histogram, it indicates profits are lower during bank holidays so it will be ideal for the company to price towards the lower brackets of the optimum price to increase sales and profit. And from the Order Item Quantity histogram, it indicated profits were higher when the item was bought in quantity of 3. Hence the company can consider doing bundling of items, that is, sell 3 in one pack to customers instead of selling it in single pieces so as to maximise profit.

This project successfully developed a predictive algorithm for the optimum price that appears to have little bias and variance. In addition, it provides more information on which particular days of the week or period are most likely to have an impact on the profit and total sales. However, there are some limitations that should be taken into account when considering pricing  based on the results of this project. First, profits should only be positive profits as we are only looking at price that will give profit. The project did not deal with loss figures. Hence the profit figures should all be positive so as to get the best price. No consideration was made for other external costs that can influence profit as in warehousing, staff, electricity and other costs. All these were held constant. A consideration of these amongst other variable might enhance the algorithm.

The dataset is relatively small as it is from a small ecommerce company hence future testing of the model should be made on larger dataset that has thousands and millions of transactions for each product. The significance of other variables must also be checked in future algorithms to also know what effect they have on the price and also which other variables will be confounded.

Full Python Code can be found on my Github page : Click here

Views: 50813

Comment

Join Data Science Central

Comment by John Stites on March 23, 2017 at 10:19am
Doug is right on with his comments. The assumption that the slope of the demand curve is less than 1 is not tested. The slope of the demand curve or "price elasticity" should drive pricing strategy.For example if,you raise the price of the product by 10% and the number of units sold decreases by 5% then it makes sense to increase prices. Airline seats are a very different story. They are wasting assets, once the plane pulls away from the gate they are gone. Additionally, the demand for seats is very price sensitive and a reduction in the price of (for example) 5% will generate an increase in demand of (again just an example) of 10%. Airline economics are very different than consumer goods economics. One must look at the enterprise level and not the unit level to optimize profits
Comment by Douglas A Dame on November 1, 2016 at 8:57am

Interesting.

Given the info you've provided, I'm not sure that using "profit" as you've defined it is going to get you to an optimum selling price. Even if we can assume a "stable market for this product" during the 25 months of data you're analyzing, which will continue into the future.

My concern is that you have three very different costs of acquiring the product ... 9.8 vs 29.4 vs 39.2 ... which given the range, has a dramatic range on the profit calculation. Given this scenario, at any given point in time, regardless of the then-current cost, the profit maximizing price will be the one that maximizes the gross revenue from sales.(*)  As I understand the way you're modeling this, unit-cost is a huge confounding factor. Looking at Gross Sales rather than Gross Profit would remove this uncontrollable factor from the solution. ("Uncontrollable factor" from the perspective of the business owner, not in the sense that term is used in statistical modeling.)

The trends over time of sales price and supply cost are worth looking at too. Perhaps you did, and didn't find anything interesting enough to share.

(* For simplicity, I am assuming the average shipping cost to the retail customer, which is apparently being borne by the seller and thus impacts profits, does not vary as the sales price goes up or down. In other words, the geographic mix of customers isn't impacted. I am also ignoring any possible economies of scale in the "get the goods from the supplier" part of the cycle ... but depending on what kind of volume or bulk-shipping discounts are available, EOQ considerations could be significant.)

HTH - Doug Dame

Comment by Amal de Silva on November 1, 2016 at 8:39am

This assumes that you have unlimited inventory or supply. In most price optimization applications when you reduce the price too much the demand increases to a point that there is no supply/inventory  to satisfy the demand. For example, in airline price optimization there are limited number of seats in a plane.