A Simple Adword Spend Optimization

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 will not be. Some keywords, geographic locations, mobile vs traditional platforms, and other targeting options will drive Adwords profitability.

If you expect AdWords to make profit for you from day one, you are setting yourself up for unpleasant surprises. There is often an initial investment required until you can ascertain 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.

A Simple Adword Budget Optimization

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:

	Average Cost Per Click	Expected No. of Clicks required To Get 1 Inquiry	Expected No. of Inquiries required To Get 1 Qualified Lead
Google Display	$0.88	7	12
Google Adwords	$0.79	9	10
Facebook	$0.81	5	11
LinkedIn	$0.94	8	21
Twitter	$0.90	7	10

“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

They have just been used in this example to simplify the optimization problem so as 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.

Again, the purpose of this 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:

• Total allowed budget of $50K. Total Ad spend cannot exceed this amount
• Display Network has an upper limit of $12K
• Ad Word Budget has an upper limit of $15K
• Facebook Budget has an upper limit of $6K
• LinkedIn Budget has an upper limit of $12K
• Twitter Budget has an upper limit of $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 main pursuit.

Download Ad Spend Optimization Spreadsheet

#linear-programming, #lp-simplex, #marketing-budget-optimization

Adword Spend Optimization using goal programming

Adword-Budget-Spend

The difficulty of figuring out an optimal solution of a multiple objective engineering or a business problem lies in the competing conflicts that may exist between the optimal solutions for the different objectives. The best solution for a given objective might be undesirable for another.

 

 

 

 

 

 

Both functions 1 and 2 are maximized at two different point of feasible region. A feasible solution is considered efficient or Pareto optimal if there are no other feasible solution with all the objective functions.

Adword Spend Optimization using goal programming

A common inbound marketing budget requirement is to generate maximum number of qualified leads as cheaply as possible. Excel Solver and Matlab can be used as effectively to allocate a marketing budget among various internet marketing vehicles. A Marketing Research Analyst collected the following Ad-word conversion rate across different social media and search engine vehicles for a client in financial industry.

Internet Mktg. Vehicle	Min. Lead Conversion Rate	Max. Lead Conversion Rate	Expected Lead Conversion Rate
Google Search	3.50%	8.00%	7.32%
Google Display	1.65%	5.82%	4.97%
Bing Search	2.10%	6.32%	5.25%
Bing Display	1.52%	6.95%	5.21%
Yahoo Search	1.89%	5.82%	4.97%
Yahoo Display	1.45%	5.92%	5.10%
Facebook	4.50%	8.50%	6.25%
LinkedIn	3.89%	7.20%	6.30%
Twitter	3.23%	4.80%	4.15%

The client has stipulated these conditions,

• All $1M is to be invested
• At most $600,000 of ad-word spend can be in Search and Display Network
• At least $650,000 of the total ad spend should have the maximum potential of qualified lead conversion rate to be 4% or greater

The client requirement stipulates  to set up a goal based programming where the minimum lead conversion rate is assumed to be to twice as important as the expected lead conversion rate and three times as important as Ad-words on Display network. Particularly, the following goals are set on each objective:

1. The minimum overall lead conversion rate must be at least 3.5%

2. The expected lead conversion rate must be at least 5.5%

3. At most, $200,000 can be allocated in Display networks (Bing, Google, and Yahoo)

4. At minimum, $250,000 must be allocated towards Social Media networks (Facebook, LinkedIn, and Twitter)

Mathematical Background:

For a set of objective functions, we need to repose multi-objective problem as a single objective, f_p(x) by placing limits on remaining objectives, and keep hard constraints.

 

 

 

subject to

g_j(x) ≤ 0   (inequality constraints)

h_k(x) = 0  (Hessian Matrix)

x_i^L ≤ x_i ≤ x_i^U

The above problem can be formulated as

minimize fp(x)

subject to f_l(x) ≤ ε_l      l = 1,2,…,n_obj (l ≠ p)

g_j(x) ≤ 0

h_k(x) = 0

x_i^L ≤ x_i ≤ x_i^U 

Problem Formulation

In this goal attainment problem, the following variables need to be defined for under and overachieving goals.

U1 – Under achievement of goal 1 which is the minimum lead conversion rate

O1 – Over achievement of goal 1

U2 – Under achievement of goal 2 which is expected lead conversion rate

O2 – Over achievement of goal 2

U3 – Under achievement of goal 3

O3 – The planned budget $200,000 allocated towards Display Network advertising should not be exceeded. O3 needs to be minimized.

U4 – At least $250,00 must be allocated towards Social Media advertising. Under achieving U4 is undesirable and thus needs to be minimized

O4 – Over achievement of goal 4

The objective function must be constructed in a way that the sum of undesirable deviations from the predetermined goals is minimized.

Goal 1 – is to have 3.5% minimum lead conversion over the number of clicks. Underachievement of the goal of 3.5% is not desirable. Hence, the variable to minimize is therefore U1.

Goal 2 – is to have 5.5% expected lead conversion over the number of clicks. Under achievement of 5.5% goal is undesirable. U2 therefore needs to be minimized.

Goal 3 – Budget allocation towards online display network should be restricted within $200,000. Over achievement of this goal is undesirable. This variable has to be minimized.

The objective function f(x), can be written as

f(x) = 3U1 + 1.5U2 + 1O3

Since, minimum lead conversion rate is assumed to be to twice as important as the expected lead conversion rate and three times as important as Ad-words on Display network.

Decision Variables – Let GS represent the Google Search Network, GD represent Google display network, similar abbreviations for other search engines, FB represents FaceBook, TW represents the Twitter, and LI for LinkedIn.

There are few soft and hard constraints in this problem. Soft constraints give more scope for relaxing the restrictions depending on the preference of the constraint being satisfied in the objective.

Solver results interpretation:

1. Goal 1 is over achieved since O1 = 40.  The client wanted to limit the minimum overall lead conversion to $35,000. Anyone would love higher returns than expected as long as other hard constraints have been satisfied !

2. Goal 2 is also over achieved since the slack variable O2 = $9,700. This actually a good news since the expected lead conversion rate exceeds the target $55,000 (5%). The actual conversion is $55,000 + $9,700 = $64,700 or 6.47% !

3. Goal 3 is fully achieved as both U3 and O3 are zero.

4. Goal 4 is over achieved.

The client’s ad budget allocation should be as:

$400,00 towards Google Search Ad-words

$0 for Google Display Ads

$0 for Bing Search Ad-words

$200,000 towards Bing Display Ads

$0 for both Yahoo Search and Display

$400,000 towards Facebook

$0 for both Twitter and LinkedIn

to achieve the ad-spend allocation optimally.

Note that the goal setting approach to multi-objective optimization can bring inefficient solutions. Although, they can satisfy all the goals, but cannot be considered as optimal.

Download Adword Spend Optimization Spreadsheet

#efficient-frontier, #linear-goal-programming, #lp-simplex, #multi-objective-optimization, #pareto-optimization

Estimate System Reliability From Monte Carlo Simulation

Your material handling system serves as oxygen of your business, so you want to do everything in your disposal to minimize downtime. The best way to increase system uptime is with a good preventative maintenance plan. As an engineer, you may have come across maintenance/performing repairs on Material Handling Systems such as Autonomous Vehicles, Industrial Robots, Conveyor Belts, or Machine Cells. Additionally, you probably have randomly generated demand, created inter-arrival rate of shipments, or simulated processing time in different machines etc. In this simulation study, I am going to estimate reliability of a material handling module of a warehouse over a period of time relevant to the system. The Weibull distribution is most commonly employed to determine reliability function.

Estimate System Reliability From Monte Carlo Simulation

A continuous distribution to plot a model fit from the failure rate of the system is created from a Monte Carlo simulation of 7000 trials. The idea behind Monte Carlo simulation is the generation of random events in a computer model, and this generation is repeated many times and counted for occurrence of a specific condition. The Monte Carlo simulation allows us to consider various aspects of system characteristics which cannot be easily captured by analytical methods such as K-out-of-N systems, redundancies, fault trees, repair and maintenance for components.

Furthermore, the reliability analysis from the Weibull distribution provides the information needed for identifying failures, troubleshooting, scheduling periodic maintenance and inspections. The restrictive modeling assumptions that had to be introduced to fit the models to available solutions, is thus avoided.

A series-parallel system of machines is considered here, each working in parallel and connected serially. To determine failure or Mean Time To Failure (MTTF) to be precise, consider each machine type to have k out of n system. Given that there are n machines in a machine type, the entire system fails when k of those n machines fail at any time. The simulation demonstrated here has 2 Warehouse Management Servers (k1), 3 Autonomous Guided Vehicles (k2), and 2 Robotic Palletizers.

 

Reliability Block Diagram

WMS  – 2 Warehouse Management Servers in parallel

AGV – 3 Autonomous Guided Vehicles in parallel

RP – 2 Robotic Palletizers in parallel

MC – Machining Cell

As you can see in the block diagram, all individual have some sort of redundancy. The failure rate of the AGV depends on the failure of WMS components, similar dependency applies to two Palletizers and Machine Cell.

The failure rate of the above components was formulated as:

• manufacturer provided reliability data. As evident, the failure rate almost doubles every 180 days from WMS.

The failure test runs for each day for 600 days. Each sub-system (components) should operate until it fails. Once it fails, it remains in failed status. The time that each component fails is recorded in the spreadsheet.

The cut sets are as follows:

• {WMS1, WMS2}
• {AGV1, AGV2, AGV3}
• {RP1, RP2}
• {MC}

Monte Carlo simulation of the system was conducted using Excel and the number of failures during 7000 trials was 990. The mean time to failure or MTTF of the system was 563 days. Look at the chart below, the probability of failure of the system converged to 14.15% after 7000 trials and it can be reasonably assumed that it will remain around 14.15% over a long period of time. The probability of failure is 0.1415 and was accurate within 3% after 1600 trials.

 Download the Reliability Simulation Spreadsheet here

#monte-carlo-simulation, #system-reliability, #weibull-distribution