In marketing optimisation you are constantly tasked with making the right decision based on a number of known or projected factors, with the end result along the lines of maximising return or total revenue. The process of thought which you will go through (either knowingly or not) is a ‘what-if?’ analysis – weighing up the possible courses of action, the expected outcomes and deciding on the optimal solution.

If we think about these optimization problems formally, we can categorize them as either linear or non-linear optimisation. In this article, I’ll introduce linear optimisation (or Linear Programming [LP]) and give an example of how to use the Excel Solver add-in to solve some common search marketing scenarios.

## Introducing Linear Programming

LP is great because it is one of those mathematical techniques which is useful and relatively easily applied within many real-life situations and industries. A good introduction to LP is given over at shelovesmath.com

“Linear Programming sounds really difficult, but it’s just a neat way to use math[s] to find out the best way to do things – for example, how many things to make or buy. It usually involves a system of linear inequalities, called constraints, but in the end, we want to either maximize something (like profit) or minimize something (like cost).”

In simple terms, LP solves the problem of maximising a target variable (a goal, essentially) subject to some defined constraints (or inequalities)

We won’t delve into any technical definitions here, but it is useful to illustrate the basic form of such a problem would be to do something like maximize ‘x’ where:

f(x)=x^{2}-x+2 with the constraint -1 ≤ x ≤ 5

## The Budget Allocation Problem

Let’s now consider how this would look in a relevant real-world scenario. The problem we’ll look at is how to optimise the allocation of budget to a range of AdWords campaigns. This should be solvable without the use of an LP process, but should hopefully help to illustrate the process and potential.

There are a number of campaigns for which we have historic data and we want to optimise the allocation of budget to each based on this data, with the key aim being to maximise the revenue. We know that the traffic available to each campaign has a ceiling, meaning we can only spend so much on each one. We also have total marketing cap of £1,500/day:

Framing this as a LP problem gives us:

f(x_{1}, x_{2} … ,x_{n}) = x_{1}ROI_{1} + x_{2}ROI_{2} + … + x_{n}ROI_{n}

Our target variable is: Revenue

Our constraints are determined by available traffic (e.g size of the market):

x_{1}≤ £1000

x_{2} ≤ £500

x_{3} ≤ £750

and our total budget limit:

x_{1} + x_{1} + … + x_{n} ≤ £1500

Also, there is one final constraint based on the common sense notion that we can’t have a negative budget:

x_{i} ≥ 0

## Enter the Solver Add-in

This is a handy tool with Excel which, if fed the relevant data, can run the optimisation and remove any of the mathematical heavy-lifting. The ‘Solver’ add-in is shipped with all recent versions of Excel, it’s just not enabled by default. Following the few steps here will ensure the tool is available to you.

Let’s look at how we can combine the table of data with this tool to solve our problem

[**Note:** you’ll see that I’ve created Named Ranges in Excel for each column – I would recommend that you do this when using the solver, as it just makes inputting and tracking what data you’ve put in a lot easier]

**Target Variable:** we set this as our Revenue cell, choosing the option of ‘Max’ to aim for the highest amount:

**Variables:** we identify of x_{1},x_{2} etc in the dialogue as such – this is the instruction about which values to cycle through to determine the optimum output

**Constraints:** here we’ve defined the rules such that:

- the BudgetAlloc (cells which contain budgets for each campaign) do not exceed the corresponding ceiling amount
- The total spend does not exceed the daily allocation

The budget amounts used when trying to solve the problem are always non-negative

**Method:** There a few options for the algorithm to use, in this case we are requiring the Simplex LP option

## The Solution

Having put this data in and hit the solve button, our table now looks as such:

We have now determined the best place to allocate the budget to ensure maximum revenue is achieved.

## Adding Further Constraints

Taking a step back from getting that mathematical about the situation, we could have easily just determined the highest ROI campaigns, and started attributing budget which matched the Ceiling Spend until we ran out (leaving the poor returning campaigns with zero budget). To make the algorithmic approach more valid, we can add further constraints.

Let’s consider the scenario where there are stock limits for each product (our new constraint). With a bit of tweaking to our input data, we re-frame the question slightly to keep the format linear.

Using a known historic CPA to help calculate our Budget Allocation, we make finding the optimal number of each product to be sold the goal (named Expected Units).

Our set-up in the solver is now as below. The changes are: 1) a different range in ‘By Changing Variable Cells’ and 2) a new constraint ‘ExpectedUnits <= Stock’:

Running the solver now provides us with the optimal solution for applying budgets to maximize the achievable revenue (by way of setting target number of units for each product)

## Conclusion

Hopefully I’ve managed to provide a good introduction to the Solver in Excel, and by way of these simple examples have stimulated some thoughts about how you can use it to implement Linear Programming to your advantage!

Jasperwillem says

What kind of calculation power / permutations is this function limited too? Will it be the excellent coffee-time excuse? 😉

David Fothergill says

Hi Jasper,

There is a decent level of computational power at your disposal when using the solver. Although this post focused solely on Linear problems, there are options to use algorithms which tackle non-linear and non-smooth problems also, increasing the number and type of problems which are ‘solvable’.

Enrique Ramos says

I think that this tool is better (and free): http://phpsimplex.com/en

Juan says

The solution to this example has some bugs: Excel’s Solver rounded (up or down) & makes the solution breach the constraints, i.e.:

* from a point of view of the number of clicks (i.e. for campaign x3): 919*0.46 = 422.74 > 420 (upper than the ceiling spend for that campaign)

* for total spending in campaigns: (462*0.65)+(267*0.75)+(919*0.46)+(704*0.54)+(769*0.26)=1503.39 > 1500 (upper than the total budget daily)

Then the solution is false (the max revenue is lower).

Results using the free online tool phpsimplex.com are:

* for the number of clicks: http://bit.ly/10J6YgT

* for the optimal spending in each campaign: http://bit.ly/1pGeNAg

Best regards!

Juan says

Actually the number of clicks should be an integer, so the PHPSimplex solution is an approximation (the optimal revenue is a little lower).

David Fothergill says

Hi Juan,

Thanks for sharing your tool and your comment – you’re quite right, Excel has done some rounding which means it breaks to the constraints a wee bit. Revising the problem slightly to allow clicks to be the variable allows for the ‘integer’ constraint to be put in place, which gives a slightly different (but truer) result:

See result here – http://i.imgur.com/CChR5Y8.png

Juan says

Thank you for reviewing the results. However this new solution still violates some of the constraints (i.e. clicks for campaign x3):

919*0.46 = 422.74 > 420 (upper than the ceiling spend for that campaign)

This should be a maximum of 913 clicks.

Regards!