R for solving linear programming marketing problem

Question

I have this problem and I know the answer but I need to be able to solve it via R

There are 5 campaigns called A,B,C,D,E there is a maximum possible budget that can be spent on each campaign and a ROI for each dollar spent on each campaign.

it will look like this

Campaign   Max Budget    ROI
  A         156.09       0.77
  B         73.92        1.46
  C         65.8         2.14
  D         43.68        0.77
  E         41.01        1.81

there is two constrain, you can only spend a total of 100 in all the campaigns, and you cant spend more than your max budget on each campaing, and you are trying to maximize your profits and that is your ROI times the budget you spent on the campaign.

I know the answer will be to spend 65.8 on campaign C because it has the higher ROI then spend 34.2 on campaign E because you have your second highest ROI and with that you will hit the constrain of max spend of 100.

If somebody can help me to set this up with lpSolveAPI or lpsolve, thanks in advance

this is what I have tried so far

 library("lpSolveAPI")

 model<-make.lp(ncol=5)
 m1<-lp.control(model, sense="max", verbose="neutral")

 m2<-set.objfn(model, obj=c(120.1893,107.9232,140.812,33.6336,74.2462))

 m3<-set.bounds(model, upper =c(156.09,73.92,65.8,43.68,41.02))

 m4<-add.constraint(model, c(1,1,1,1,1), "<=",100)

 solve(model)


 get.variables(model)
 34.2  0.0 65.8  0.0  0.0

Why am I getting 65.8 on campaing C but 34.2 on A, should it be on E?? I assume my objective function is incorrect, for the objective function I multiply the ROI by the max budget and use those coefficients.

Why have you chosen to use linear programming for this? As you note in your question, it is optimal to sort by ROI and go down the list until your budget is fully spent. — josliber, Dec 19 '18 at 15:23

G. Grothendieck · Accepted Answer · 2018-12-20T13:46:37.090

Try this using dat in the Note below:

library(lpSolveAPI)

n <- nrow(dat)
model <- make.lp(0, n)
control <- lp.control(model, sense = "max")

set.objfn(model, dat$ROI)

set.bounds(model, upper = dat$Max_Budget)
add.constraint(model, rep(1, n), "<=", 100)

solve(model)
## [1] 0

get.variables(model)
## [1]  0.0  0.0 65.8  0.0 34.2

Note

The input in reproducible form:

Lines <- "
Campaign   Max_Budget    ROI
  A         156.09       0.77
  B         73.92        1.46
  C         65.8         2.14
  D         43.68        0.77
  E         41.01        1.81"
dat <- read.table(text = Lines, header = TRUE)

score 0 · Answer 2 · answered Dec 19 '18 at 15:39

I think using a whole equation-solving model is overkill here, as there is nothing to balance between campaigns.

If I understand it correctly, you want to spend as much money as possible on the campaign with the highest ROI, what's left on the second highest, what's then left..., etc, etc. Until all money is spent.

My approach would be sorting on ROI, and calculating a cumulative sum to know up to where you get. If your data is in a data.frame called df:

df <- df[order(df$ROI, decreasing=TRUE), ]
df$spend <- pmin(df$MaxBudget,
                 pmax(TotalMoney-cumsum(c(0, df$MaxBudget[-nrow(df)]),
                      0)

Parallelized, you spend on each campaign either the maximum you can, or the amount that is left after all "more worthwhile" campaigns have been financed, whatever is less. And "what is left" is defined is either nothing, or TotalMoney minus the sum of all higher campaigns.

R for solving linear programming marketing problem

2 Answers2

Note