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.
So What?
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.
