The Biggest Winner: Paying By Numbers
Watching ‘The Biggest Loser’ on TV on Tuesday night, Davina McCall used a metric during the show but didn’t give any explanation as to how it was calculated:
“And your percentage weight loss is 14.85%”.
How do you work out percentage difference? It’s a metric used every day in Paid Search, yet Davina didn’t explain how they worked it out. Read on to discover a few simple maths formulas and tips that can help with reports, optimisation and future scope for Paid Search campaigns.
% Difference
Analysing the difference between two numbers is crucial as at first glance they may not seem to have much difference between them. Take, for example, if you had a January ROI of 1.4 (a) and a February ROI of 2.6 (b). Despite not seeming that different from one another, they actually have a high % difference.
To work out percentage difference, follow the formula below:
January ROI 1.4 (a)
February ROI 2.6 (b)
Percentage change = (b-a) /a x 100
(2.6-1.4) /1.4 x 100 = 86%
So the formula for Biggest Loser contestant Sarah was:
(Sarah’s New Weight – Old weight) / Old Weight x 100
The Formula Triangles
Knowing just a few metrics in Paid Search can be integral to forecasting others. Despite not having used formula triangles since school, they come in incredibly useful when working out a missing figure which can be crucial in forecasting future campaign performance. Remember the horizontal line is a division and the vertical line is a multiplier. The key ones to remember are:
So if you only have two of these numbers you can work out the remaining missing number.
For example:
Click Through Rate = Clicks/ Impressions
However, if you don’t know how many clicks you have but you do know the CTR and impressions, you can work out the clicks.
Clicks = Click Through Rate x Impressions
VLookups
Lists such as keyword lists, ad copy lists, campaign lists, or simply just lists of statistics are a key part of Paid Search. But being able to compare one list from another is vital in seeing the differences between the two. The VLookup formula in Excel means you can do just that, compare one list up against another.
They can be tricky to begin with, but if you open up the VLookup dialogue box it’s easier to see what value to enter in and where.
The Lookup value is the 1st cell in which you wish to compare to the second list.
The Table Array is the columns you will be looking up in the second list when comparing it to the first.
The Column Index Number is the column number from this table that you want to compare to the first.
The Range Lookup is what you wish it to return if the cells do not match – i.e. false
For a more detailed explanation on VLookups please see the official Excel explanation.
Ad Rank
Ad rank is a formula which enables us to determine how much a bid on a keyword will be. It is worked out by multiplying the maximum cost per click the advertiser is willing to pay by the quality score of that keyword. Quality score is an algorithm Google uses to define how relevant an ad is to the users search query and is largely based on click through rate, relevance and landing page destination.
Adrank = Max CPC x Quality Score
Knowing the ad rank of the competitive set is important when thinking about your CPC costs. This video from Google gives a simple explanation of the ad rank situation.
Incremental ROI
Whereas an increase in ad spend can sometimes mean a decrease in ROI, working out the incremental ROI of the increase spend is an easy way of showing how it compares to the normal ROI of the account.
The formula is:
Incremental ROI = increased revenue/increased ad spend
This should give an insight into whether the increase in spend was in relation to the increase in revenue.
Learning these few key formulas will help with all paid search campaigns and give the advertiser a better insight into the performance. Be the biggest Paid Search winner.
