Excel is often used to create a sales price model. It works well in an office but wouldn’t it be good if we could deploy a model to the web so that stakeholders using a mobile/tablet device can access the model. This is where R and Shiny comes in handy. In this paper I’m presenting a proof of concept of such an app.
There are various inputs that are used in the model. Some of this comes from systems and others are input by the stakeholder to customise the price. For the purpose of this paper I’ve exported system data to CSV files.
The first CSV file gives us the standard price list.
setwd("~/R pricing")
p <-read.csv("price_list.csv") #Product RRPs - these are discounted based on inputs.
head(p,10)## Product RRP.Lower RRP.Upper
## 1 Elite Custom Race Bottle Cage - 2014 25 27.5
## 2 Litespeed Pinhoti Frame 2015 1230 1353.0
## 3 Kona Big Kahuna 1240 1364.0
## 4 Giro Privateer Off Road Shoe 70 77.0
## 5 Maloja FadriM Multisport Jacket 90 99.0
## 6 Amplifi Stratos MK II - 27L 35 38.5
## 7 Ritchey WCS C260 Blatte 78 85.8
## 8 Amplifi MK II Armourgel Back Protection Shirt 56 61.6
## 9 Maloja Women's AndrinaM Bike Shorts 23 25.3The second CSV file is a lookup table that gives a discount based on the months traded.
setwd("~/R pricing")
t <-read.csv("tenure.csv") #The longer the customer has traded the bigger the discount
head(t,10)## months_trading impact
## 1 1 3.0
## 2 2 3.0
## 3 3 3.0
## 4 4 3.0
## 5 5 2.5
## 6 6 2.5
## 7 7 2.5
## 8 8 2.5
## 9 9 2.0
## 10 10 2.0I’ve created a function for the model that receives the CSV data along with data collected from the user. This data is:
business_name <-"Nelsons"
rating <- 6
turnover <- 20000
competitor_intensity <-5
months_trading <-12
product <-"Maloja FadriM Multisport Jacket"
quantity <-20The shiny app for this model can be found here. The code is listed below.
get_price <- function(t, p, business_name, rating, turnover, competitor_intensity,
months_trading, product, quantity) {
#Lookup the impact of trading history.
t_impact<-t[t[,1] == months_trading,]$impact
#Pull in the price bands from the imported price list
p_lower<-p[p[,1] == product,]$RRP.Lower
p_upper<-p[p[,1] == product,]$RRP.Upper
#Calculate the different discounts based on the function parameters
comp_adjust <-(as.numeric(competitor_intensity)/100)
turn_adjust <- (as.numeric(turnover)/4000000)
trade_adjust <- t_impact/200
rating_adjust<-(as.numeric(rating)/200)
#Aggregate the different discounts
discount <-comp_adjust+turn_adjust+trade_adjust+rating_adjust
#Adjust the lower price to give the sales person some negotiating room
p_lower <- p_lower * (1-discount)
price_band <- cbind(p_lower, p_upper)
#Calculate the total revenue amount
revenue_range <-price_band * as.numeric(quantity)
#Combine the price band and revenue into 1 table and add names
revenue_range <-rbind(price_band,revenue_range)
colnames(revenue_range) <- c("Lower", "Upper")
rownames(revenue_range) <- c("Price", "Amount")
return (revenue_range)
}You can make this function as complicated as you need to. I kept it simple here to make the code easier to read.
In the shiny app Server.R file I wrapped the inputs into a Reactive function so the code is only called when one of the inputs change. You’ll notice I also put the function call here to calculate the model.
One other aspect was populating the selectInput() element on the UI with the Product name. To do this we have to write UI code generation code in the Server.R file. It’s not too complex once you realise there is a 2-way link between UI and Server.
Other than that it’s plain Shiny code.
library(shiny)
shinyServer(function(input, output) {
dataInput <- reactive({
setwd("~/R pricing")
t <-read.csv("tenure.csv")
p <-read.csv("price_list.csv") #
businessname <- input$businessname
product<- input$product
quantity <- input$quantity
rating <- input$rating
turnover <- input$turnover
months_trading <- input$months_trading
competitor_intensity <- input$competitorintensity
x <- get_price(t, p, businessname , rating, turnover, competitor_intensity,
months_trading, product, quantity)
})
#This renderUI function is required to render the selectInput element with the Product list.
output$productSelector <- renderUI({
selectInput("product", "Product", as.vector(p$Product))
})
#Render the output from the reactive function above
output$range <- renderTable({
dataInput()
})
})The UI I’ve selected for the app is based on the fluid page. It contains a side bar for the instructions and a main panel for the input fields.
One thing I learnt was that to add columns to the main panel there is a function called Column(spans, offset). This enables the full width of the main panel.
library(shiny)
shinyUI(fluidPage(
title = 'Price Calculater',
sidebarLayout(
sidebarPanel(
tags$h2("Sales Price Calculator App"),
tags$p("This proof of concept app is often modelled with Excel. With R and Shiny the app can be published as a web app. This makes the app highly accessible to sales people who are often out on the road."),
tags$p("Inputs are collected about the customer and transaction"),
tags$ol(
tags$li("Business Name: This is not really used, yet"),
tags$li("Rating: The rating is selected by the sales person to indicate prestige/brand strength of the customer"),
tags$li("Turnover: This is used to indicate the level of sales, bigger turnover results in a bigger discount."),
tags$li("Competitor Intensity: If there are competitors of the client in the vicinity this calls for a competive price hence bigger discounts may need to be negotiated."),
tags$li("Product: This app takes an base price list and discounts prices based on the inputs above."),
tags$li("Months in business: The longer a customer has been in business the more established they should be hence can be given a bigger discount.")
),
tags$p("The inputs are fed into a simple business rule to calculate the minimum price and set an upper indicative price. The expected revenue range is also calculated.")
),
mainPanel(column(5,id = 'Inputs',h1(),
textInput("businessname", "Business Name"),
uiOutput("productSelector"),
textInput("quantity", "Quantity", value=20),
sliderInput("rating", "Rating",1,10,5),
textInput("turnover", "Turnover",value=20000),
sliderInput("competitorintensity", "Competitor Intensity",1,10,5),
sliderInput("months_trading", "Months Trading",3,36,12)),
column(2,offset=1,h1(),tableOutput("range")))
)
)
)