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)=x2-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(x1, x2 … ,xn) = x1ROI1 + x2ROI2 + … + xnROIn
Our target variable is: Revenue
Our constraints are determined by available traffic (e.g size of the market):
x2 ≤ £500
x3 ≤ £750
and our total budget limit:
x1 + x1 + … + xn ≤ £1500
Also, there is one final constraint based on the common sense notion that we can’t have a negative budget:
xi ≥ 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 x1,x2 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
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)
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!