Business Insights
Products do not occupy shelves in isolation so a good price optimization strategy often has to consider a collection of products.
It is not uncommon that some products in a category sell infrequently and serve a supporting role in giving the customer the illusion of choice, even though 90% of them will purchase the popular item because it is a safe bet. Thus even closely related products can have radically different price behaviour.
Let’s explore how we can use optimization algorithms to identify optimal price points for a category of products given our understand of overall weekly demand.
Category Optimization
We will build on the results from part one of this post and use the same simulated data for Products A, B, and C. These are products in the same category and our goal is to optimize margin given an expected unit volume.
First we need to organize our data and use the purrr package to generate a list of coefficients we can put into vector form. We also need to append a vector of unit costs.
While we’re at it we will extract R Square values from the model summaries, again using purrr’s map.
## Put products into a list
PA <- mutate(PA, Prod = "A")
PB <- mutate(PB, Prod = "B")
PC <- mutate(PC, Prod = "C")
PAll <- union(union(PA, PB), PC)
PAll <- split(PAll, PAll$Prod)
P.Mods <- map(PAll, ~coef(lm(Units ~ Price + OnSale, .)))
R.Mods <- map(map(PAll, ~summary(lm(Units ~ Price + OnSale, .))), ~.x$r.squared)
P.Coefs <- as.data.frame(do.call(rbind, P.Mods))
colnames(P.Coefs) <- c("Intercept", "Slope", "OnSale")
P.Coefs <- cbind(P.Coefs, Cost = c(5.99, 8.99, 9.99))
P.Coefs
R.Mods Intercept Slope OnSale Cost
A 2102.2233 -113.654838 210.779332 5.99
B 151.1699 -2.916552 2.893399 8.99
C 974.8432 -17.622839 147.627937 9.99
> R.Mods
$A
[1] 0.5169017
$B
[1] 0.2467671
$C
[1] 0.4942282Our coefficients contain only negative values which is important because if the slope is positive the model will set an infinity large price, and that is not what we want. Positive (or very small) price coefficients suggest sales are based on traffic rather than price, so in those cases being in line with the market is generally a good starting point.
Bananas are a good example of an item that sells based on how many people walk through the door, provided the price is within customer expectations based on market realities.
Leaving that aside for a moment let’s re-define our margin optimization function to accept vectors and re-run an optimization.
MarginMaxVec <- function(Data, Price) {
Volume <- (Data$Intercept + Data$Slope * Price)
margin <- sum(Volume * (Price - Data$Cost))
return(-margin)
}
optim(par = c(1,1,1), fn = MarginMaxVec, Data=P.Coefs)> optim(par = c(1,1,1), fn = MarginMaxVec, Data=P.Coefs)
$par
[1] 12.24428 30.40505 32.65461
$value
[1] -14834.28
$counts
function gradient
196 NA
$convergence
[1] 0
$message
NULLOur function converged as indicated by the ‘0’ and the suggested prices are A,B, and C respectively are $12.24, $30.40, and $32.65. Recall that this is the same optimal price we found in part one of this tutorial.
This is not exactly what we want as it is the same thing as running three products individually. What we really want to understand, is that given the total expected demand for this category among all the customers who buy the category each week, what pricing structure will lead to the best margin.
In other words, we know that buying one product will result in selling fewer of the other, as customers will generally not buy three times as much.
Setting Volume Constraints
What we need to do is set up a constraint based on the expected weekly volume to force our function to make hard choices about price given costs and overall demand.
Let’s assume that we have a weekly market of 1,000 units across products A, B, and C. to illustrate how pricing changes due to the addition of constraints.
To use a constraint we will rely on the nloptr package and use the COBYLA algorithm for linear optimization with constraints. First we set up a constraint in the form of Volume – MaxVolume <= 0, in other words if the total volume returned by the constraint function becomes larger than our specified maximum it will stop being a valid solution.
We set up a vectorized nloptr function similar to how we used optim including a hard coded constraint of 1,000 units.
## Add unit volume constraints
library(nloptr)
MarginMaxVec <- function(x, Data, MaxVolume) {
Volume <- (Data$Intercept + Data$Slope * x)
margin <- sum(Volume * (x - Data$Cost))
return(-margin)
}
MyConstraint <- function(x, Data, MaxVolume) {
sum((Data$Intercept + Data$Slope * x)) - MaxVolume
}
nloptr(
x0 = c(5,5,5),
eval_f = MarginMaxVec,
lb = c(0, 0, 0),
ub = c(50, 50, 50),
eval_g_ineq = MyConstraint,
opts = list("algorithm" = "NLOPT_LN_COBYLA", "xtol_rel" = 1.0e-10, maxeval = 1000),
Data =P.Coefs,
MaxVolume = 1000
)
Minimization using NLopt version 2.7.1
NLopt solver status: 4 ( NLOPT_XTOL_REACHED: Optimization stopped because xtol_rel or xtol_abs (above) was reached. )
Number of Iterations....: 206
Termination conditions: xtol_rel: 0.0000000001 maxeval: 1000
Number of inequality constraints: 1
Number of equality constraints: 0
Optimal value of objective function: -14612.3188315072
Optimal value of controls: 13.52937 31.69696 33.93961The NLopt solver status is important, code 4 lets us know that the algorithm stopped at 206 iterations based on achieving the level of precision we set as 1.0e-10. Had this not been the case we might have increased the number of iterations allowed by the option maxeval.
The prices that optimize gross margin given overall demand of 1,000 units for this category are therefore: Product A $13.53, Product B $31.69, Product C $33.94.
If this was real data would want to be cautious before doubling our price of Product C which currently sits around $12. Although linear models give us a continuous estimate, we would be extrapolating quite far outside of the original data.
Pricing tends to be linear within a certain range determined by the market, if we go too far outside that range demand can drop off quickly. In a real situation we might also employ upper price bounds and constraints for inventory.
Conclusion
Category pricing is difficult because relationships between products and constraints are not always clear. This is especially the case across commodity items that are easily substitutable. Fortunately, optimization can help us understand the relationships between our products, and give us insights into how to organize pricing to ensure the best results.
