Adword Budget Optimizer

How much should my AdWords budget be?

This is a very common and an important question for advertisers. It is also one that doesn’t have a straightforward answer. If AdWords is profitable for you, then by all means your budget should be as high as possible. If AdWords is not profitable for you, then your budget should be ZERO.For most advertisers, the real answer lies somewhere in between. Some products and services can be profitable for you to advertise, while others may not be. Some keywords, geographic locations, mobile vs traditional platforms, and other targeting options will drive Adwords profitability. Adword Budget Optimizer example spreadsheet will assist you allocate advertising dollars efficiently among various pay-per-click advertising vehicles.

If you expect AdWords to make profit for you from get go, you are setting yourself up for unpleasant surprises. There is often an initial investment required until you can figure what is working and what isn’t.

Generating optimal number of qualified leads within Adword Budget Constraints

An advertiser wants to allocate a pay-per-click marketing budget among five internet marketing vehicles – Google Display, Google Adwords, Facebook, LinkedIn and Twitter. Every single one of the pay-per-click vehicles has a different pricing and effectiveness per click. The objective is to achieve the highest number of qualified leads by dividing the advertising budget optimally among these 5 pay-per-click vehicles. The advertiser is constrained by budget limits and each pay-per-click vehicle has an advertising spending limit as well.

Adword Budget Optimizer – A simple approach with Excel

An analyst has come up with the following details about the cost and effectiveness of each of the 5 pay-per-click advertising vehicles for a consumer electronics manufacturer:

“One size does not fit all” – The above inquiry and lead conversion figures are from hardware equipment/electronics sector and are pretty meaningless for a single manufacturer. Way too many variables come into picture to have a “universal lead conversion rate”. The notable ones are:

• Landing Page Layout
• Traffic Source
• Definition of Conversion (different for B2B/B2C, non-profit, educational or full blown E-Commerce sites)
• Industry
• Price of Product
• Ease of Checkout

These variables have been used in this example to simplify the optimizer. The lead conversion numbers can be indexed to something realistic. Refer to MarketingSherpa’s 2012 PPC Edition chart of their Search Marketing Benchmark Reports and the conversion rates by industry. The objective of the problem is to achieve the highest number of qualified leads by allocating the ad budget optimally among these 5 pay-per-click vehicles.

Solution Steps:

Step 1 – Setup the objective

The main task is to maximize the total number of qualified leads. Number of qualified leads depend on Ad Spend$ (column C), Average Cost per Click (Column D), Expected Clicks Required to get 1 inquiry (Column F), Expected number of Active Inquiries (Column G), Expected Inquiries required to get 1 Qualified Lead (Column H).

Step 2 – Identify the decision Variables

The key to find out the decision variables is to determine the amounts to spend on each pay-per-click vehicle to maximize the number of leads. Columns C3 through C7 serve as the decision variables or the Ad Spend $. The initial amount was set based on the constraints in the next step. You need to ensure that the sum of all Ad Spend (columns C3 through C7) must add up to $50K which is our allowed budget. The solver will optimize the spend allocation based on the objective and constraints

Step 3 – List all constraints

As seen from below screen grab from excel, the problem is bounded by budget limits:

• Total allowed budget of $50K. Total Ad spend cannot exceed this amount
• Display Network  $12K
• Ad Word $15K
• Facebook $6K
• LinkedIn  $12K
• Twitter $12K
• All decision variables or Ad Spend are integer
• Unconstrained variables are all non-negative

Integer constraints ensure that Ad Spend are kept in whole dollar amounts.

Step 4 – Setup Excel Solver
The objective cell is S9 which is maximization of “total expected number of qualified leads”. Since the objective function is linear first order, Simplex LP is the chosen solving method.

Note: You must have Excel Solver installed before you do this exercise. In Excel 2010, Solver can be found under Data menu. If you are unsure about it, the installation steps can be found here.

Step 5 – Interpret the Answer Report

Solver has finished its job and has optimized Ad Spend allocation among 5 pay-per-click vehicles (Column M3 through M7). The amounts are highlighted in yellow.

As you can see, LinkedIn constraint did not hit the target limit and $7000 slack is available. After running this optimization model, the objective function value (expected number of qualified leads) improved from original 495 to 528, which remained our objective.

Download Adwords Budget Optimizer Spreadsheet