Posted by Tom_C
While brainstorming potential blog post topics I decided that keyphrase research doesn’t get enough love here and I merrily agreed to write a post on the topic. Little did I know that this would result in a morning of maths. Monday morning to be precise. Monday maths morning. I might make that a recurring theme.
This post is going to be all about the adcenter Ad Intelligence plugin for Microsoft Excel. For those that don’t know what it is, this is a nifty plugin for excel which allows you to import keyphrase data into your spreadsheets.
Why do you care about Ad Intelligence
Reasons you should care about the Adcenter Ad Intelligence plugin for Excel:
- It’s really easy to install
- It works from right within excel so is really easy to use with existing keyphrase data
- Data feels like it’s right at your fingertips – running functions and querying data is VERY quick.
- The comparative keyphrase traffic levels are well correlated with Google’s so you can use this data to predict Google data with a high degree of accuracy*
Note that you’ll need to have an adcenter account to use the plugin but you can sign up for one of those relatively easily and given the above benefits I think it’s worth it.
* – Please see below for some maths notes. This is NOT a linear correlation so you can’t just use a multiple of the data to generate the data. The important take-away for non-maths types is that the ORDERING of the keyphrases with respect to traffic levels is well correlated to Google’s data.
The plugin integrates very smoothly with Excel (as you would hope!) and can be installed very quickly, once installed you see a new tab within Excel with the following options:
Or, if you’re in the UK you get fewer options:
There’s a couple of awesome things to realise about this:
- Since it’s just another tab within your excel it’s going to integrate with zero hassle into any other data that you can format in excel – for example a keyword export from adwords or from google analytics…
- There’s a country/language dropdown which allows you make sure you’re getting the correct data. International support FTW (though this is limited to only a few countries at the moment).
But what do all these functions do?
You provide the URL and the tool returns a bunch of relevant keyphrases. Here’s the data for SEOmoz:
The keyword suggestion tool is really handy for doing speculative keyphrase research – trying to find keyphrases that you might have missed. There’s three aspects to the keyword suggestion tool.
The first is the "Campaign Association" which looks at the keywords that other advertisers have in their account as well as the selected phrase. For example here is the data for the term "football" (from the UK) and you can see that it contains nicely related phrases but which don’t always contain the keyword in them. Note how this has a commercial bent since it’s pulled from bidding data not search data:
The popular keywords tool is useful for analysing trending terms and spikes in your data. I’m not as convinced about the usefulness of this data – looking at it I think tools like Google Trends are probably more useful. Here are the screenshots for the Animals Category (because the data it pulls out is amusing and all about monkeys):
This is, for me, one of the most interesting features of the tool and probably the one that I will use most frequently. This tool generates monthly (or daily) traffic figures for a list of keyphrases that you select. For example:
The demographics tool is very cool and not only gives you a breakdown by age (truncated in the screenshot below – in reality the percentages add up to 100) but it also gives you a gender breakdown which can be very useful information:
In summary, this tool is essential for performing keyphrase research – not only is the data solid but it’s also lightning quick (compared to other sources anyway) and provides you with some information which it can be very difficult to get elsewhere (such as the demographic split). There are a few other features which I’ve not included here, mainly related to bidding on adcenter or related to the content network. They’re not so applicable to keyphrase research so I’ve not included them here. Perhaps a future blog post!
If you’re a Non-maths Person (NMP) then you can stop reading here.
So while the above is all very well and certainly shaded pretty colours (that’s all done by default by the tool!) it’s pretty useless if the data isn’t of a high quality. I’ve spent a fair amount of time trying to ascertain whether the traffic data is worth using or not and the conclusion is that the Google Adwords traffic data that you get through the Adwords Tool is well correlated to the traffic data generated by the Ad Intelligence tool, but the correlation is not linear. Instead, I’ve found for UK data that the following equation determines the relationship:
How did I determine this formula?
Step 1 – Pull out keyphrase data levels from the adwords tool and the ad intelligence tool for the top football keyphrases (as determined by the Google tool)
Step 2 – Look at the correlation between the two data sets.
This provides some really positive-looking graphs like this one for instance over the top keyphrases related to ‘Football’:
Collating all the data together across a number of verticlas we see that there isn’t a particularly strong correlation:
This is still being skewed by the head terms. Trying to come up with a way of smoothing the data I decided to analyse the logs of the traffic levels. What I saw was quite a remarkable result:
Please note, one thing that I’ve not really touched on in this post is the fact that the Google Data isn’t 100% accurate (and in fact can be quite a way off at times) so I’m not advocating that you can find the true search volume, merely that you can find a relation between the data that Google gives you and the data that Adcenter gives you.
More: continued here