Running a linear programming model to maximize binned predictions

I have a dataframe like:

>>> df
[Output]: day  spent_amount  location  prediction
1        10         US         '0-2'
1        20         US         '3-5'
1        30         US         '3-5'
2        10         US         '3-5'
2        20         US         '3-5'
2        30         US         '6+'
3        10         US         '0-2'
3        20         US         '0-2'
3        30         US         '6+'


To sum up, I have 3 days, 3 spent_amounts, also 2 locations (I didn't add UK since that is pretty much similar to US) and predictions that are generated by a previous model. So the model predicted for instance on day 3, if you spent 20 for marketing in the US the predictions of sold goods will be between 0 and 2.

My goal is to maximize the value of sold goods based on the predictions and considering the daily budget as the limiting constraint. But since I need to quantify the predictions, I am confused about how to replace them. One idea is to replace them with the mean of two ends. For instance 1 for the prediction '0-2'. But that generates unrealistic results. How can I determine the numeric values to assign to each prediction group?

• Have you checked portfolio optimization? Maybe the answers here give you some direction.
– EhsanK
Jul 24 '19 at 18:49

First, I'm not sure that maximizing estimated sales is the correct criterion. If it's possible that a higher marketing spend could raise sales but not enough to compensate for the extra marketing cost, then you might want to work with a net value objective (sales margin less marketing cost).

Second, supporting what Oguz Toragay said, if you can get distributional data for the values in the prediction groups, you could use that to compute mean (or median) sales in each group and go on from there.

Third, if you believe that falling at the low / middle / high end of one prediction interval is likely to coincide with the same locations in other intervals, you could run scenarios. (To clarify that condition, what I mean is believing that if one marketing spend gets a prediction of 0-2 and another gets a prediction of 3-5, and if circumstances would conspire to put actual sales at the 0 end of the 0-2 interval, then those same circumstances would tend to put sales at the 3 end of the 3-5 interval.) Under that assumption, you could run one LP using the low end of each interval, one LP using the high end of each interval (with some plausible choice for the high end of "6+"), one LP using the middle of each interval, and maybe a couple more using the first and third quartiles of each interval. So you now have three to five solutions (likely not the same), which you can order according to degree of "conservatism", present to the decision maker (who is hopefully not you), and say in essence "pick one based on your risk preference".

• Hi prubin, thanks for the message. Just to answer each paragraph: I just named them marketing cost to save the content of the data. They are just a replica of the actual dataset. Second, can you please explain the second approach more? Third, I liked the idea of moving forward with the similarities between points. That might be helpful to determine which end of the interval, our prediction should be. Jul 24 '19 at 21:37
• The second approach just means that you replace each interval (for instance, 3-5) with a point estimate based on the distribution of actual (rather than predicted) sales volumes. For instance, take the 3-5 prediction for day 2 in the US with marketing spend 20. That prediction is presumably based on a sample of observations with actual sales figures. Compute the median of those historical figures (call it 3.7 for illustration purposes), and use that in lieu of 3-5. If you don't like medians, you can use means (or other point estimates, if you can think of one). Jul 25 '19 at 22:06
• That was the approach I actually did yesterday. Thanks again for your time and help! Jul 25 '19 at 23:30

Do you have the distribution of the values in each prediction group? Or are you able to fit those values using a distribution model, into statistical values? If so, you may consider the expected value of each prediction group in your model. Then VAR(Value At Risk) and C-VAR may be used to cover the uncertainty in your predictions. It’s just an idea, I really doubt about the applicability of the approach.

• Hi Oguz, thanks for the reply. Yes, I have the distribution of all the predictions. I can check VAR and C-VAR approaches and see whether they are applicable Jul 24 '19 at 21:30