Introducing a New Performance Metric: Keyword Total Weighted Value
April 29, 2015
One of the biggest mistakes a paid search professional can make is to operate under the auspices that the data available to them in their various interfaces— AdWords, Analytics, etc— is the only data out there.
If you’ve got blue paint and you’ve got yellow paint, well then you’ve got green paint too, right?
That’s the type of thinking— data resourcefulness— that anyone who makes his or her bacon in an advertising interface should be using. I’m going to show you one of my favorite ways to deconstruct some of the metrics provided by AdWords in order to understand how to find keywords in your account that you may be under or overvaluing.
In most instances, a keyword’s performance is based on a surface level metric, commonly something like conversion volume, or cost per conversion— both of which are of course available in the AdWords interface.
Those managers who prefer to dig a little bit deeper into the conversion funnel, will also utilize the click assist conversion and impression assisted conversion columns in AdWords. Here’s a quick refresher on exactly what each of those metrics represents:
Click Assisted Conversion: the total number of conversions for which this keyword contributed an assisting click.
Impression Assisted Conversion: the total number of conversions for which this keyword contributed an assisting impression.
What we’re going to do now, is use these columns to find the Total Weighted Value of each of your keywords. Total Weighted Value is a moniker of my own devising, so I’ll attempt a definition.
Total Weighted Value: The quantification of a keyword’s total contributions to the conversion funnel, customized to each account.
That last part— customized to each account— is what makes this metric compelling. The conversion funnel for every business is different; Total Weighted Value will account for how important each conversion type is to you.
Let’s Do It
The first thing you need to do is make sure you have the following columns in your AdWords interface at the keyword level: converted clicks (or conversions, depending on your business preferences), click assisted conversions, impression assisted conversions.
Download the report— using a substantial date range, such as 90 days— and open up the excel sheet. The first thing we are going to do is calculate the click assisted conversion (CAC) to converted click ratio. To do this, add up your total click assisted conversions, and then divide that number by the amount of total converted clicks.
In my excel sheet, I call this the CAC to Conversion Rate.
CAC to Conversion Rate = total click assisted conversions / total converted clicks
I like to create a row above the baseline metrics to place this data.
Alright, now we’re going to find the impression assisted conversions to converted clicks ratio, doing basically the same exact thing. As you might have intuited, in my excel sheet I call this IAC to Conversion Rate.
IAC to Conversion Rate = total impressions assisted conversions / total converted clicks
The TWV column has crept into the above screen shot, but we’re not quite ready to calculate that yet.
As you can see, my IAC to Conversion Rate is very high— we’re seeing more than 3 impression assisted conversions per regular conversion. Because this would overvalue impression assisted conversions in our upcoming equation, we’re going to hedge that value a little bit (impression assists should always be valued less than a click assist).
Here’s what we’re going to do:
- Take all of the Search clicks (Search only— that’s very important) for your date range, and divide it by your Search conversions.
- Take all Search impressions (again, make sure this is Search only), and divide it by your Search conversions.
- Take your quotient from step two, and divide it by your quotient from step one.
In longhand, the process should look like this:
(Search impressions / Search conversions) / (Search clicks / Search conversions)
In my account, for the chosen date range, it took 2870 impressions to a conversions, and 42 clicks to a conversion. 2870 divided by 42 is 68.3. In my equation, I will weight an impression assisted conversion to have 1/68.3 the value of a click assisted conversion.
This seems harsh when you think about the raw numbers, but the reality is a click assisted conversion is empirically a part of your conversions funnel— an impression assisted conversion is not.
But enough on the theory— it’s time to calculate the Total Weighted Value of our keywords. You should create a new column in Excel, and then populate it with the following equation:
Converted clicks + (click assisted conversions * CAC to Conversion Rate) + (impression assisted conversions * (CAC to Conversion Rate*(1/68.3)))
Note: Don’t forget to swap the 1/68.3 in this formula for the impression-to-conversion value of your own account!
That’s a lot to grok, so here’s a screenshot of my excel sheet as a visual aide.
The TWV number by itself isn’t all that useful, however, so what we’re going to do is add two new columns to the sheet: Conversion Rank and TWV Rank.
For the Conversion Rank column, first order your “converted clicks” column by descending.
Then, switch over to your Conversion Rank column and give your top most keyword a rank of “1”, your second keyword a rank of “2”, so on and so forth.
Now, order your Total Weighted Value column by descending.
And give your keywords their TWV rank the same way you did for Conversion Rank.
We arrive now at the final step: calculating the difference between your TWV Rank and your Conversion Rank.
Create a new column— this is the last one, I swear— and populate it with this formula:
Conversion Rank – TWV Rank
Drag that formula down for all your keywords, and the order your “Difference” column by descending. The higher the number in the “Difference column” the more hidden value that keyword has.
Here’s a look at how your sheet might look at the end of all this.
You can do whatever you want with this information, but it’s important to know which keywords may be more valuable to you than surface level data suggests. Personally, I aggressively increased bids for keywords with a lot of hidden value. The result?
A 21% increase in assisted revenue.
Browse By Category
How to Drive Calls that Actually Convert
Our experts are here to explain how gaining call tracking data can help you close the gap in your customer journey and tailor their experience so they actually convert.
New Facebook Analytics Updates from F8
Learn about journeys and automated insights, new tools announced at Facebook's F8 conference for the Facebook Analytics tool.
Spotify Advertising: The Why and How
This blog post gives you the rundown on Spotify, including why and how you might advertise on Spotify's new self-serve ad platform, Ad Studio.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
5 Facebook Ad Settings to Audit In Your Account
5 Easy settings to audit in your Facebook Ads account to improve performance and understand website traffic.
Analyzing the AdWords Distance Report
The distance report provides a unique view of your geographic data. The report helps users understand how distance from a location impacts search ad performance.
How to Master Attribution for Easy Budget Planning
In this webinar, Hanapin’s Cassie Oumedian and Google’s Julia Meter will provide tips on using AdWords attribution to directionally decide where you should allocate your budget, and how you can use the changing industry to your advantage.