Introducing the Stockton Report: Find Keywords with Hidden Value

By Kevin Klein | @kkwrites | Former PPC Hero

PPC Hero series week wraps up today as Kevin looks at how to find keywords with hidden value. Take a look at all report focused posts this week, including Carrie’s take on location extensions, Jacob’s assessment of the destination URL report, Kayla’s look at how to use client service reports to improve retention, and Rachael’s insight into two key Analytics reports.


AdWords is full of useful reports, available at just the click of a button. My fine colleagues have spent the week guiding you down these fruitful thoroughfares towards optimization.


John Stockton is a hall of fame basketball player, known primarily for holding the NBA record for assists. It’s unlikely that John Stockton has ever heard of PPC Marketing, but if he had, rest-assured, the following would be his favorite report.


With a little bit initiative we can uncover something that I like to call “hidden value”. Which is to say we can quantify value in a way that AdWords does not do for us.


In particular I’m talking about finding out which of your keywords (or campaigns or ad groups) are quietly contributing to a healthy campaign, and at limited cost. So, some sort of off the cuff name might be, “Cost-Efficient Helper Keywords” or…John Stockton keywords.


Here’s how you can do this by manipulating a standard AdWords keyword report.


First, ensure that you have columns in your keyword report for cost, click assisted conversions, and impression assisted conversions.




Then download the keyword report.


I recommend getting rid of all extraneous columns, so that you’re spreadsheet looks like this:




From here, you’re going to want to add in the following three columns (which should be populated by the attendant formulas):


% Cost (keyword cost/total cost)


% of Click Assists (keyword click assists/total click assists)


% of Impression Assists (keyword impression assists/total impression assists)




These columns are highlighted in color in the above screen capture, which, if you possess the basic human capacity for sight, you have probably already ascertained.


The whole point of establishing these percentages is to identify keywords that are contributing assisted conversions at a high percentage compared to their spend. Or, in layman’s terms, providing invisible value.


By running a quick percentage difference equation (don’t worry, it’s way simpler than it sounds), we can illuminate exactly which keywords in your account are more valuable than you might think.


To do this, create two new columns.


As shown below, I’ve titled the first column “Cost/Click Assist Diff”. This column subtracts the % of the account’s total cost a keyword is responsible for from the total % of click assisted conversions that it is responsible for.


The second column does the same thing, but for impression assisted conversions instead of click assisted conversions.


Our spreadsheet now looks like this, if we hide the raw data:


5 columns


Generally speaking, a click is more valuable than an impression, because it’s more likely to eventually turn into a conversion.


So, we sort the Cost/Click Assist Diff column by descending, and we learn that the illustrious Keyword 1400 is contributing more hidden value than any other keyword in the account.


Looking more closely at the keyword, we see that it’s accounting for 0.175% of the account’s total spend, but nearly 3% of it’s click assists. Not bad, Keyword 1400, not bad.


Of course, percentages can be unreliable due to their volatility being tied directly to the size of the integers from which they come. So you can also look at the raw data. For example, Keyword 1400 spent $347.05 so far in November, while click-assisting on 6 conversions.


A quick descending of the raw “Impr. Assisted Conv” shows that keyword 1400 is also contributing more impression assisted conversions than any other keyword in the account.




So what? Well, it’s up to you to decide how valuable certain stages of your conversion funnel are, and then use this data to help inform your keyword bids!


Way to go, little guy!




With the application of a bit of simple math, any PPC Account manager can take a report provided to us my AdWords, and use it to reveal hidden value. And remember, this report can be applied at the keyword, ad group, or campaign level. Whatever suits your fancy.