Capture search volumes and seasonality data straight into Excel

Yes, you heard that right. Introducing the SEOgadget Adwords API Extension for Microsoft Excel – easily capture large volumes of search query data directly into Excel using your Google Adwords API key. Save huge amounts of time gathering data and spend more time analysing!

Wow! Tell me about the features

• Exact, Broad and Phrase Match
• Generate local search volumes by country code
• Rapidly extract search volumes for 1000′s of keywords
• Acquire Mobile and Desktop Search Volume
• Generate 1000′s of related queries using suggest service

What are the standard functions?

The SEOgadget Adwords Excel plugin creates a series of functions designed to fetch data from the Google Adwords API. Once you’ve installed the plugin, you’ll get a handful of new functions to make fetching keyword data a far more effective and efficient process. Here they are:

getAdWordAvg()

getAdWordAvg(keyword,"[MATCHTYPE]","[COUNTRYCODE]","DEVICE")

Example: =getAdWordAvg(A1,"EXACT","GB","WEB")

Description: returns average search volume from the adwords API. Matchtype accepts broad, exact and phrase match. Country codes can be found in the Adwords documentation and devices can be mobile or web.

getAdWordStats()

getAdWordStats(keyword,"[MATCHTYPE]","[COUNTRYCODE]","DEVICE")

Example: =getAdWordStats(A1,"EXACT","GB","WEB")

Description: returns local search volume and previous 12 months separated by commas

getAdWordIdeas()

getAdWordIdeas(keyword,"[MATCHTYPE]","[COUNTRYCODE]","DEVICE",[NoOfResults])

Example: =getAdWordIdeas(A1,"EXACT","GB","WEB",100)

Description: returns keyword suggestions based on API suggest service

There are Array functions too?

Yes, there are! Use these with a little caution as they can request a lot of data (800 rows per API call) – I suggest you set Excel to manual calculation. Then press F9 (or click Calculate Now) to recalculate the workbook when ready. You’ll find the calculation options under the formulas tab (and also under Excel Options which also offers an option to stop auto-calculation on exit).

If you don’t disable auto-calculate, no problem – just use the array formula to capture data, then copy and paste values to avoid accidentally re-requesting very long lists of keywords.

arrayGetAdWordStats()

arrayGetAdWordStats([TABLE],"[MATCHTYPE]","[COUNTRYCODE]","DEVICE")

Example: =arrayGetAdWordStats(myKWlist,"EXACT","US","MOBILE")

Description: array formula (auto adds {} brackets) will return data from a list of keywords in a table (average search volume and seasonal data).

arrayGetAdWordIdeas()

arrayGetAdWordIdeas([TABLE],"[MATCHTYPE]","[COUNTRYCODE]","DEVICE",[NoOfResults])

Example: =arrayGetAdWordIdeas(Table1,"BROAD","US","WEB",20)

Description: array formula (auto adds {} brackets) will return suggstions from a list of keywords in a table (average search volume and seasonal data).

Performance & requirements

The SEOgadget Excel plugin works with Excel 2003, 2007 and 2010 in 32 and 64 bit. If you’re going to be doing big bits of research using arrays, you’ll need quite a powerful computer (a good excuse to ask for a faster machine with more memory!). Under load testing, my i7 Windows 7 machine took around 3 to 4 minutes to process 30,000 keywords. You probably don’t want to use that much data every day, though.

Obviously, you’ll need a Google Adwords API key to get search volume data using this tool.

How to install

1) Download the zip file, extract the “SEOgadget” folder and open “distribution/setup.xls” – enable the macros and follow these step by step instructions. When you open the setup file you’ll see this screen:

*Don’t forget to enable macros!*

2) Add your API credentials and click “Update Credentials”

3) Click the “Install API Add-in” button and wait for this dialogue:

4) Restart Excel and start capturing your search data!

Now for the technical bit

SEOgadget hired Master Datasmith Tom Gleeson to develop this plugin – (here he is on Twitter). I highly recommend Tom (he’s our Excel developer of choice). If anyone knows how to automate the hell out of Excel, it’s Tom. Here’s his technical bit.

The add-is was developed using a marvelous open-source tool called ExcelDNA. This allows for the fast (and cheap) development of .NET add-ins for Excel. Microsoft would prefer if everybody used their Visual Studio (not the free express version, the expensive one) based VSTO technology to build such add-ins; don’t, ExcelDNA is way better and easier to use. ExcelDNA is built around a C core (making it fast and robust) and allows for the development of add-ins using either C# or VB.NET.

The biggest downside with ExcelDNA is the lack of documentation, but the user forum is very active and top notch. For a good introduction see Ross Mclean’s series of blog posts on the subject.

So why not use plain old VBA to build the add-in? The answer to that lies with the other piece of technology used i.e. Google’s  v201101 Adwords API. Most APIs these days use either JSON or simple XML formats to pass data and requests back and forth between servers and clients. In the early days of Web Services it was envisaged that a highly structured (and extremely complex) variation of XML, namely SOAP, would be used for this task. Thankfully common sense prevailed and SOAP is now very much a minority sport.

Unfortunately, the Adwords API developers went with SOAP, but, made up for it by providing developers with a set of excellent client libraries for all the major development platforms, such as the JVM, CPython and .NET but alas, not for VBA.

This .NET client library also accounts for the choice of the C# , rather than VB.NET, as the add-in’s development language, as all the examples (like most .NET libraries) were coded in the language, using C# makes its easier to get up to speed.

(The ability to package the resulting code in a single DLL (a .xll rather than VBA’s .xla) is also another good reason for using ExcelDNA if VBA is not an option.)

The add-in is available for three platform types:

1. Excel 2007/2010 32 bit (requires NET 2.0 run time, which is now to be found on all but the most ancient of machines).
2. Excel 2003 (should also support back to Excel 2000, and possibly ‘97, but not tested). This too requires NET 2.0. It lacks however, the very useful helper “array” functions that auto-resize arrays to fit the returned tables and the ability to run in multi-threaded mode.
3. Excel 2010 64 bit. This requires not just a 64bit OS, but a 64 bit installation of Excel (the default for Excel is to install the 32 bit version even on 65 bit machines, if installed using the supplied SETUP.xls it will check this for you). The 64bit version requires the NET 4.0 run time, which is very likely to be already installed if using a modern Windows 7 64 bit OS. But if not or you’re not sure, then power up Internet Explorer and visit http://www.hanselman.com/smallestdotnet/ the site will check what versions of .NET are installed and suggest the most suitable download.

I want to play! Where can I download this plugin?

This plugin is completely free to download and use. As it’s a free tool, we may only be able to provide very limited support and by downloading and installing the plugin, you agree that the tool is not provided under any warranty, and that the software author and SEOgadget are excluded from any liability to the users of this software.

DOWNLOAD HERE

Google Adwords API Extension for Excel is one of our latest posts from: SEOgadget.co.uk.

Since launch, we’ve had some user feedback requesting better “how to” content and support material for our keyword tools. While we’ve been working on product quality and platform stability, some of the more important elements (user guides, instructional aids) have been put on the back burner.

Photo Credit: JD

I hope you like our helpful “how to” video (warning: may contain Jazz music)

Embed this video

<br /> <iframe title="YouTube video player" width="640" height="510" src="http://www.youtube.com/embed/2PnIvLI4dWg"></iframe><br /> The SEOgadget <a href="https://tools.seogadget.co.uk"/>Keyword Tool</a> hosted on <a href="http://www.youtube.com/watch?v=2PnIvLI4dWg">Youtube</a>.<br />

A few charts I made earlier

Just recently I used our tools to throw together some data for an SEO pitch. The data looked at job sectors, such as architecture, interior design by salary research terms. These charts are very easy to make – just follow these instructions.

Here’s another example – which UK towns do we love the most?

“I love Reading” – ahh, perhaps that’s less about location and more about reading.

Filters highlight massive opportunities

Obviously this tool was built to solve a problem for SEOgadget – speeding up detailed keyword research projects. One of the features available to our users is the filtering capability. Here’s an example:

Combine this filter with any category, or further filters for traffic and search volumes, and you can quickly pinpoint keywords worth targeting in your SEO campaign:

Uncategorised keywords with the default “low hanging fruit” filter active reveals a tasty ranking for SEOmoz’s “Open Site Explorer”. Perhaps I should be sending that traffic via their affiliate program

Handy How To Video for Our Keyword Tools is one of our latest posts from: SEOgadget.co.uk.

An industry friend recently said to me; “by the time you get round to writing the how to, it’ll be out of date”. It’s funny how you get so immersed in building something that you lose sight of the obvious things. Perhaps one mind only has the capacity to work with a fixed amount of variables, and for me, those variables have been so focused on engineering and testing that marketing took a back seat (the irony).

Image credit: Adam Selwood

Building and launching a keyword tool has been an incredible experience. Not that I use past tense lightly, there’s much left to do but all-in all I think what we’ve achieved here is pretty cool.

Anyway – it’s time to get started on writing about, and improving documentation for our product. This post is all about how to make best use of our tools, by segmenting your keyword lists, making categories and creating filters.

A quick how to on keyword categories

Let’s imagine for a moment that you’re an SEO company. What types of keyword might be of interest to you? Location based search? SEO consulting product terms? CRO? Social Media? How about searches for your own brand terms (and variations of those). I’ve spent ages, days (and sometimes weeks) working with large lists of keywords, rankings and search volumes. It’s laborious enough to collect the data, but categorising (with say, Excel array formulas) and lookups to combine ranking and search volumes is just a nightmare after even a few thousand keywords in Excel. It’s slow, crashy and painful.

There is so much value in grouping, categorising and ranking your keywords if you can find an easier way to get the job done.

Imagine you’re interested in learning about how people search for SEO agencies in some of the top UK cities. Creating a “location” based category is easy. Click the create category button to arrive at the category editor:

As you type in new markers (in this case, UK city names) the results list dynamically updates. You can sort the list by rank, volume or visits. Sorting and adding markers helps you to see new oppotunities, at least, keywords that could lead to new opportunities.

Click suggest on any of the more interesting keywords to expand your category:

Select any of the keywords you’d like to add to your project and the tool sets about automagically capturing the data.

I’d like to add keywords in bulk

No problem. One suggestion would be to grab a list of your most important keywords from your favourite rank checker, Think Pragmatic’s UberSuggest, Mergewords.com, Wordstream’s Keyword Suggest Tool,  or pretty much any technique that can generate you a list of keywords.

I like Mergewords a lot, because you have precise control of the keyword list you’re generating. Simple, and smart.

Paste in those keywords

Note that right hand column in the screenshot? That’s a list of travel destinations, perfect for a category named “travel destinations”.

Generate your keyword list and paste them in to the “manually add new keywords” tool.

Duplicates are removed and as soon as you add the keywords, we start to populate the category with all of the juicy keyword data:

Take an hour to really work with the tool

Working inside the tool is an iterative process. It’s been designed to compliment the SEO process, and to work with you answering questions as you come up with them. At first glance though, the default, uncategorised view (after first creating a campaign) can appear daunting at first. Work with the tool though and it’s not long before your first categories are right in front of you and the penny has dropped.

Export your data and create awesome pivot charts

Ok, I owe Claire a write up on this topic. At the core of every keyword category in this tool is the data you need to confidently create a new content category and sub category based architecture. That’s definitely a different post (it’s coming soon!). In the meantime, this step by step guide on how to create a pivot table (and chart!) should help you turn the exported data from this tool into great presentation fodder:

Create an awesome filter

Want to see a list of keywords that rank on page 2, with search volume and analytics entries? No problem, create a filter like this:

Thank you

Thank you to everyone using this tool, giving feedback, asking questions or generally saying really nice things. It’s exciting, challenging and invigorating and truly rewarding to be doing this. There’s a growing list of brilliant ideas and important changes to the messaging we use to help users understand the tool and get the most from it. Thank you!

How to Use Our Keyword Tools is one of our latest posts from: SEOgadget.co.uk.

For the past few months, I’ve been working on a project to help make the keyword research process easier. Getting clear visibility on keyword search volume and opportunity data is painstaking and time intensive, but it’s such an important part of the initial and ongoing process of SEO. I’ve wanted to build something to automate the more laborious elements of keyword research for some time, and it’s (finally) ready to show the outside world.

Introducing the SEOgadget Keyword Tool BETA

Previewing our keyword research tool

One of the greatest challenges with keyword research is making your data actionable. It’s difficult to build large keyword lists without losing focus on which terms you’re targeting, and in what order. Grouping keyword data into categories is also really problematic with the tools most search marketers have at their disposal. Unless you belong to a very well resourced in-house team or a very large search marketing agency, large keyword research projects can be a huge bottleneck.

What does this tool do?

This tool allows you to create and export categorised keyword lists, with ranking, local search volumes and analytics visits from Google for each phrase. The categorisation data allows you to quickly and easily create pivot charts (for each keyword category).

The beta version of our tool integrates with the Google Analytics API allowing you to download keywords that drove traffic to your (or your clients) site in the past 30 days. As soon as you sign up and connect to your Analytics account, we start downloading your keywords, collecting search volumes in your local market and their ranking.

Make keyword categories and create powerful filters

While we’re collecting your data, you can do a few really cool things. Create keyword categories, add new keywords and create filters to deep dive into your data. Here’s a break down of what you get:

What’s a keyword category?

Let’s imagine you’re an online retailer who sells shoes. The types of keyword you’re interested in might be grouped by gender (“shoes for men”, “shoes for women”), colour (“red”, “black”, “green”), or type (“boots”, “shoes”, “heels”) and so on. This simple example can be applied to almost any form of industry online. Automotive, for example has categories such as make, model, colour, age and location. Travel has destination, route, accomodation, date, location and travel type.

Understanding how people search in any given market is a critial prerequisite to designing a site architecture capable of catching all of traffic.

Our tool allows you to create a keyword category and define what keyword “markers” might identify a phrase belonging to a category:

Use suggest to find new keyword ideas or add your own

You’ll see the suggest link on the right hand side of the category editor. When clicked, the tool will suggest 10 keywords you might want to add to your campaign. This is a great way to discover new opportunty, allowing for easier keyword expansion methodology.

Of course, I’m a big fan of the excellent Mergewords and Think Pragmatic’s Uber Suggest for generating big keyword idea lists. Adding a list of keywords to your campaign using external tools is easy. All you have to do is generate, copy and paste:

Powerful filtering

What keywords present the best “low hanging fruit” opportunity? What phrases rank on page 2? With filtering you can create filters by search volume, analytics and rank.

Some of my favourite filters are:

- Page 3 rankings (Rank higher than or equal to 30 but lower than 20)
- Page 2 rankings ((Rank higher than or equal to 20 but lower than 10)
- Low hanging fruit (Keywords with more than one visit, a page two ranking and measurable search volume)

Filters are configurable and can be combined and chained. This is one of the areas I’m most fascinated to learn from our users on how these filters are applied and what can be learned from them.

Over to you

Our beta testers have been helping us test and optimise the site in the past week and thanks to their feedback, we’re prioritising and working on some new features for late January 2011. If there’s something that springs to mind, be sure to let us know.

Some of the items we have planned are:

- Improving the suggestion tool to make it easier to add even more keywords more quickly
- Filter paid / non-paid traffic, ecommerce integration
- An enhanced Google Analytics management interface to allow users to restrict data by a specific date
- A agency account profile allowing for larger volumes of keywords
- Improve the charting / embeddable reports
- Save charts as PDF
- Make ranking / search volumes data available via an API

Ask a question and tell us what you need – Support Forum

There are a few ways to get in touch – commenting here is great but we have a support forum for you to talk to other users of the tool too.

The beta period and what we’re trying to achieve

While this site is in beta, particularly in the next few weeks, we’re monitoring, and tweaking site performance. Initially, getting a full data set for a campaign could take between a few hours and a day, depending on the size of your website. In this time our development team are monitoring and optimising the back-end functionality to improve the tool, make it slicker and more powerful. If anything crops up, remember we’re at the beginning of our beta journey, and the more input we recieve early on, the better!

I’m really excited to be ready and I’m really grateful for your input and support at a really exciting time for us all!

Have fun, tell us what you think and sign up!

Announcing Keyword Research – Inside Our New Tool is one of our latest posts from: SEOgadget.co.uk.

At the recent SEOmoz / Distilled Pro Seminar in London, I gave a presentation on advanced keyword research. Today I’d like to share one of the tips I gave on how to extract your competitors target keyword list, with relative ease using tools we all have at our disposal.

Image credit: House of Sims

Here’s the pro training slide, just if you’re keen for a sneaky preview of this post:

Predictable, SEO people can be

That meticulous attention to detail SEM practitioners apply to the optimised pages of their big dynamic sites can be quite predictable sometimes. Even really big sites consist of only a few page templates, which makes extracting data from them simple. Let’s use this blog post to learn the basic skills needed to generate an instant keyword list, with no keyword research tools whatsoever. Yet.

It’s been a while since we mentioned Xenu’s Link Sleuth

How did you guess I was going to mention Xenu? Xenu’s a cracking piece of software, enjoyed by SEOs pretty much since the dark ages, and I totally recommend it. While I write this post, Xenu is dutifully crawling a jobs website ready for me to export the data to Excel. Note the “Title” column, an oft-overlooked column in Xenu’s captured data list:

Carry out a crawl of your target website, with the appropriate settings to avoid crawling external links. We’re really only interested in the onsite data, list of URLs and Titles.

Dust off your Excel Skills

Start by exporting the data you’ve gathered into Excel via a TAB separated text file. That’s quite easy if you’re unfamilar with the process, just go to file > export to tab separated file. Import the file into Excel, just like this:

PS – by a handy coincidence, Excel imports text files with the TAB delimiter selected by default. You can just click finish straight up when you’re importing one of these files.

Make a table and work out some formulas

There are some seriously cool functions in Excel for handling text. My favourites, in no particulalr order, are; “LEFT”, “MID”, “RIGHT”, “FIND” and “SEARCH”. An honourable mention goes out to “LEN”, too.

Here’s what they do:

LEFT – Returns the specified number of characters from the start of a text string
MID – Returns the characters from the middle of a text string, given the starting position and length
RIGHT – Returns the specified number of characters from the end of a text string

When you combine the queries above with the numerical output from the queries “FIND” (Returns the starting position of one text string from inside another), and “LEN” (returns the number of characters in a string), you can quickly construct mechanisms to extract and repurpose the keyword data extracted from your site crawl.

Here’s a simple example:

=IFERROR(LEFT(Table1[[#This Row],[Title]],FIND(” in”,Table1[[#This Row],[Title]],1)),”No Jobs”)

If you translated this Excel query into English, it might read:

Return the characters in column “Title” in this row, until the string “in” is found. If there’s an error with this formula, display the text “no jobs”.

Download the example

You can see this example in action in the template file I’ve created for you to download here. Check out the “Optimised KW 1″ column, which extracts generic job category terms from our test crawl data.

Here’s a more complex example, also included in the data

=IFERROR(MID(Table1[[#This Row],[Title]],(FIND(“in “,Table1[[#This Row],[Title]],1)+3),FIND(” | “,Table1[[#This Row],[Title]],1)-(FIND(“in “,Table1[[#This Row],[Title]],1)+3)),”No Location”)

This one extracts the location contained in the title data, which you can see in action in our “Location” column. The query looks a little more daunting, but if you de-construct it, bit by bit – you’ll see that the query is basically the same as the first, using the text “in ” as a starting point and returns characters until the ” | “. As FIND is used to return the position of the pipe, we subtract the starting position of the “in ” to get the number of characters between our two markers – thus extracting our precious location from the title. WIN.

This trick isn’t just for keyword research

If you can improve your skillset to include extracting data, like job titles, locations, stock / gift items, books, car data etc, you can repurpose it or make it “more unique” quite easily, too. Think about concatenating your new data points together in new sentences and title constructions, ready to upload to your own database (laughs evil laugh).

If you’re keen to get the data from other sources, such as search results pages, product listing pages and the like, you could also give importXML a try using Google Docs. Of course you’d probably be keen to get the data into Excel as quickly as possible either by importing it, or editing it within Excel using Google Cloud Connect. Have fun!

Extract Your Competitor Keyword Strategy [Excel Skills] is one of our latest posts from: SEOgadget.co.uk.

There’s always been a ton of debate surrounding the value of search engine rankings. While I don’t always value single ranking metrics for certain SEO activities, I value ranking data highly for keyword and competitor research. Aggregate ranking data can also provide a view of the health of your website – something I take quite seriously!

Image credit: James H

What is search engine visibility?

“Search engine visibility” is a term I associate with a metric, largely because of the ranking checker (Advanced Web Ranking) we use, but also because of a KPI in my last “in-house” role. Before you write off this metric as useless, let’s look at how it may be calculated, why that calculation can be valuable and where it can let us down.

As I mentioned in the post, “Site Performance After Hosting Upgrade“, we monitor the rankings for SEOgadget’s top 200 industry and traffic driving search terms on a daily basis. The chart above shows the search engine rankings expressed as a percentage, which works a little like this (assuming one keyword):

- Position 1 = 30/30 (100% Visibility)
- Position 2 = 29/30 (96.6% Visibility)

Calculated visibility metrics – strengths

Albeit a rather simplistic calculation, there’s definitely a beauty in the visibility principle. Imagine you’ve collected daily rankings for the same keywords for a few years. With a visibility metric, you’re able to compare year on year rankings and, with a strong sense of certainty, report on an overall improvement in rankings. You could attribute incremental improvements to various SEO activity if you wish, and it wouldn’t be out of the question to demonstrate an increase in traffic for your monitored terms as visibility improves. From this perspective, a visibility score could be considered a proxy metric to traffic performance.

Even if your keyword list changed, provided you had a consistent keyword selection methodology (eg: “seasonal” terms in travel, top 200 according to Hitwise, etc) you’d still have a comparable set of figures from one year to the next.

Calculated visibility metrics – weaknesses

Visibility is an overly simplistic calculation. In my example above, there’s no weighting to favour higher positions (a position 1 ranking is “worth” more than a position 12 ranking). If you were measuring rankings in Bing and Google, how would you account for the likely difference in traffic and ranking value because of search engine market share? There are obvious problems with the approach, none of which I’ll deal with here, suffice it to say I am aware of them and I choose to accept them for now.

Visibility in competitive analysis

Very recently we carried out an investigative piece of research on a new market niche for a client. Part of the work was to identify the top traffic driving keywords in that sector, and identify who ranked best for those terms. In order to assess overall search engine visibility, we calculated visibility metrics for each domain. This gave us an extremely strong sense of who was leading the pack in the rankings, and who (potentially) we should consider investigating in more detail (anonymised data):

I think competitive analysis is where visibility scores really add value – remember, this is a like for like comparison of the same keywords in the same search engine, all of which have been identified as traffic generating terms.

The perfect visibility score

My perfect visibility score might take the following factors into account:

• Type of search results included in the page (image, video, local) and prominence in each section
• Weighting for positioning based on estimated CTR for that position
• Weighting for search engine based on market share or traffic for multiple engine rankings

I’ve been meaning to write about this subject for months – and I’d love to hear thoughts and opinion (for / against) for search engine visibility as a means to aggregate individual rankings data. Do you use this metric in your KPI’s or SEO health checks and if so, how would you like to see it improve?

Search Engine Visibility As a Metric is one of our latest posts from: SEOgadget.co.uk, UK SEO consultants helping people and organisations succeed in search.

Image credit: Håkan Dahlström

Yes, you read the title of this post correctly. Keyword research data accuracy can be extremely difficult to gauge, even with access to the best keyword research tools. Today, I’ve been testing the output of our (internal) Google Adwords API tool. Having the tool allows much faster automation of large keyword research projects for SEOgadget clients, but I’m still interested in the differences between the API data, the public legacy Google Adwords tool data and the new Google Keyword Tool data.

2009 Legacy Vs Beta Shootout

Last year we compared the legacy Google Keyword Tool to the newer, beta version. Back then there were some significant differences in the data and things haven’t changed all that much. In this test there’s such variation in the numbers that there’s no point trying to spin fancy correlative measures or any statistics whatsoever in this post. The charts speak for themselves, and you can take a look a the data by downloading it here.

The only important thing to remember is both the external tools give local and global search volume from the previous month. The API data provided in this spreadsheet is a monthly average (over 12 months) for the UK locally and globally.

Google UK Local Search Volume

Click to enlarge the chart – the difference in search volume is amazing.

Google Global Search Volume

Again, click to enlarge the chart – differences in search volume numbers are significant.

Which Tool is Best?

I’ve been feeling generally better about the reliability of the data from the API tool over the external tools. Much of that sense is based on an understanding that the data is averaged across the year – you’re far less likely to get tricked into making a decision based on a single, seasonally impacted value. Of course, the newer Adwords Keyword Tool allows you to export the seasonal (monthly) values too and in reality there probably is no “best” tool or approach. Good keyword research should take as much data into account as possible, including additional insights from Webmaster Tools and your clients paid search campaigns.

Keyword research is about common sense decision making with an awareness of the strengths and weaknesses of the data from your tools. Provided you can keep those strengths and weaknesses in mind and stay objective, you’re better off having the numbers than not at all. I think the topic of keyword research in SEO is so subjective that many SEM’s bring a difference of opinion on methodology.

Keyword Research Guesswork With Legacy, Adwords and API Tools is one of our latest posts from: SEOgadget.co.uk, UK SEO consultants helping people and organisations succeed in search.

I spent an hour or so today playing with the data available via the Google Webmaster Tools “Top Search Queries” report. As you probably know, Google launched an enhancement to the functionality to include impressions and clicks, making a click through rate calculation vaguely possible. The tool was well received by many, albeit rather cautiously. I thought it might be fun to look at the data and draw some charts with Excel Pivot Tables, trying to answer questions like “how far do the click numbers in Webmaster Tools agree with my own figures?”.

Image credit: Hey Paul

A Word to the wise

This post isn’t designed to come to any conclusions on the quality of the data, and I’ll make my usual recommendation now: you should always perform your own analysis, on your own sites to come to your own conclusions. There are some limitations to the numbers I’ve used, the most glaringly obvious being that Google Keyword Tool data is currently exporting values for March 2010. The Google Webmaster data can only export data from mid March to Mid April. That’s probably going to impact the numbers somewhat. The other item to be aware of is this – the ranking position data does not survive the export process, meaning that if you’re not careful you could be comparing impressions data for a keyword on page 2, not page 1. Be aware!

Methodology

I had quite a lot of fun collecting all the numbers for my data. In summary, I decided it might be wise to be able to narrow my values down to a specific region and compare the accuracy of the values to something more global. So all of the Google Webmaster Tools data used was either “United States” (my largest traffic generating region) or “Global”. I filtered the values down to “Web”. I’m still yet to decide if that was the right move, but that’s the point of this study! Google Analytics Google search engine visits by keyword and region was exported, following this I used both Google Keyword Tools (Beta and External) to get what (US) and global search volume data I could.

The data columns in my master Excel spreadsheet table looks like this:

Here’s a little screen grab of master data table:

The results

This chart shows a comparison of total visits from the United States, via Google.com to the number of clicks reported by Google Webmaster Tools. The region is set to “US” in Webmaster Tools, selecting “Web” as the preferred source of click data. (Click to enlarge the chart)

The comparison data reveals variances between a minimum of 3% and a maximum of 55% on keyphrases bringing between 1236 and 31 visits over this period. What I found quite interesting was that the error didn’t increase or decrease proportionally to the volume of the query, it just seemed to be random. I’d love to see the variances on a higher volume query set. Some terms such as “html 5 examples” were very close (3%), and the average difference across the top 20 terms was 20%.

Our next chart compares the click data provided by Google and the analytics visits from Google, globally (Click to enlarge)

The range of variance between terms that drove traffic to SEOgadget globally, and the global impression figures is much larger. Variances between -163% and 75% were observed on the top 42 keywords. Certain keywords were vastly different, “how to install ubuntu” presented a -163% difference with Google Webmaster Tools wildly overestimating or underestimating the number of clicks received. Wow. The range of difference in the global data set makes the data particulary unreliable in my opinion.

Next, I took a look at a small range of keywords and compared the impressions data for page 1 rankings to Google’s Keyword Tool and the Beta tool.

The difference is rather clear. Google Webmaster impressions data is significantly lower than the values reported by the keyword tools. I’m actually of the opinion that the impressions data from Webmaster tools is better than the keyword tools data – certainly the CTR% make a lot more sense coming from the Webmaster Tools platform. If you’ve ever tried to calculate click through rates from the keyword tool data, you’ll know what I mean.

If I had more time, there are a few things I’d like to get to the bottom of with this data, in particular working on larger query volumes and more regions to see if certain geo locations offer more convincing figures than others. Remember there’s also a need to recheck these figures against a complete month in the Google Keyword Tool, although I’m not entirely convinced the percentage differences by keyword would be all that different.

Issues aside, it felt that the data fits with the analytics figures when you’re investigating clicks and impressions by region, but be prepared for a variance of anything between 3 and 55%, possibly more.

Playing Around With Google Webmaster Tools Click Data is one of our latest posts from: SEOgadget.co.uk, UK SEO consultants helping people and organisations succeed in search.

SEOmoz have just released an exciting update to their keyword difficulty tool. The new version of the tool gathers data from the Google Adwords API and Linkscape to help get a more detailed understanding of the SEO challenge involved with targeting a specific keyword ranking. I’ve been super lucky to get an early view of the tool and in this post we’ll take a first look at it and use exported data to get a sense of how competitive some search engine rankings can really be.

Using the keyword difficulty tool

Using the tool is very easy. Just plug in up to 5 terms in the “analyse” field and select your local Google search engine. The tool is designed to allow you to compare a range of terms or drill down on one specific phrase.

The comparision report presents you with a keyword difficulty score, which is based on the data collected by the tool from its various sources. To take a look at the data, proceed to “Full Analysis”.

The keyword difficulty tool dissects the search term you’ve entered and presents you with page authority (PA) and domain authority (DA) metrics in the top 10 rankings.

I found the competitive analysis table particularly useful. The values from the table allow you to explore the top 10 rankings URL by URL, providing data on links to each domain and the ranking URL found in the search results for your query term. Depending on the search result (and its competitiveness) you can see what variables might be driving a specific result. Where Linkscape and Open Site Explorer make it possible to explore a domain URL by URL, the keyword difficulty tool allows for visibility across an entire search results page.

Using the tool to understand your search results

Using the tool is really easy, and thanks to a killer CSV export function, we’re able to get a lot more data to where it matters most – Excel. I’ve spent some time in the last few weeks pulling data out of the tool to see how far the data can inform my overall impression of what factors are driving a particular set of search results, and how competitive that search result may be. Most frequently, I found this tool provides most insight when you’re looking at domain and page level authority metrics, by ranking position. Creating a chart with the exported data is easy and can reveal quite a lot about the search engine ranking. Let’s take a look at some search results at different levels of competitiveness and see what we can learn.

How competitive is the phrase “Pallet Delivery”?

Score: 64%

“Pallet delivery” is a phrase with medium to low monthly search volume locally (3600 searches per month locally, and 3600 searches globally, suggesting little to no demand outside of the UK). Page authority and domain authority vary wildly in this chart, offering no correlation to ranking position for this term.

The term would be relatively easy to acquire a position on page 1 for, and given such low levels of authority required for a page one ranking, internal link strategies, page relevance and anchor text optimisation may be an easy win for this SERP. Positions 9, 10 and 17 are all recent additions to the SERP and have not yet been included in the Linkscape index. The ease of rapid progression on to page one of this SERP again highlights the level of competitiveness offered by this term.

How competitive is the phrase “Pivot Tables”?

Score: 49%

A phrase with low to medium local demand (2900 searches per month in February 2010) but higher global demand (12,100 searches). Overall domain authority measured across the top 20 rankings appears consistent, and the highest ranking positions are occupied by domains and subdomains of Wikipedia and Microsoft. Where domain authority is high, the ranking pages themselves carry relatively low levels of authority and low page links from independent root domains. A new page (example article on pivot tables) is able to rank in the top ten of this SERP provided that the page is published on an established domain and is able to attract a few authoritative back links.

How competitive is the phrase “Things to do in London”?

Answer: 69%

Competitiveness for the term “things to do in London“  increases with a high monthly search volume of 40,500 searches in February 2010. Overall domain authority remains consistent, and aside from the occasional ranking anomaly, page authority appears to play a strong role in the top rankings. Domain authority appears to make up for any ranking URL that has lower page authority in the top ranking positions, but is a prerequisite to having any serious positioning at all.

How competitive is the phrase “Flights” in the UK?

Score: 94%

“Flights” in the UK is an extremely competitive term where local search volume in February appears around the 450,000 searches per month mark. High domain authority and page authority is required for this SERP. High levels of inbound link diversity to the page and the domain ensure this ranking is innacessible for most, an extremely competitive search engine ranking.

How competitive is the phrase “Airline Tickets” in the US?

Score: 97%

The word “Extreme” is barely able to describe rankings in organic SERPS in the US for the term “airline tickets”. A local search volume at around an estimated 1,000,000 searches in February 2010 and more than double that value globally, ensures the highest level of SEO competitiveness for the term. The top ranking sites have upwards of 10,000 links to the ranking page and many more links to the domain overall.

While reviewing the data in this particular ranking, two results that caught my eye. AOL’s travel.aol.com (P23) and Yahoo’s travel.yahoo.com (P18) both appear to have extremely high authority domains but the pages that rank have low levels of page authority. Both sites use sub domains which is causing them both significant ranking problems. If either domain hosted their flights content on their root domains (eg: yahoo.com/flights), they may be in with a chance of a top ten ranking.

How difficult is your keyword?

The new Keyword Difficulty tool has vastly simplified extracting keyword volume data, ranking by URL and page / domain level metrics for any phrase. The obvious benefit to this tool is the speed in which you can gather the data and begin analysing. I realised that to have written this post a few weeks back, I would have needed the SEOmoz API or a URL by URL comparison from Linkscape, combined with authority metrics scraped from Open Site Explorer and rankings data from Advanced Web Ranking. I think that’s the main point with this tool – the data itself has been available to us for some time, it’s just that until now, none of us have built a tool that agrregates at this level.

You’re still going to need a tool to help understand additional variables such as anchor text distribution and unique inbound c-block IP’s, but I’m glad that as an SEO, I can invest more time into thinking about what questions I’d like to answer next, rather than spending that time wrestling with the data collection process. Awesome.

CSS Speech Bubbles by: nicolasgallagher.comSimilar Posts:

• Comparing Trust Metrics and Value Analysis to Understand Search Rankings
• My SAScon Presentation [Advanced Link Building]
• Get High Rankings by Building Authoritative, Irrelevant links?
• Using VLOOKUP to match keyword volume and rankings data
• Search engine rankings are NOT useless
• Google’s Vince Update – Brand or no Brand?
• Linkbuilding Tool Tip – SEOmoz Link Intersect + Top Pages on Domain
• SEOmoz Launch a Brand New Toy: Open Site Explorer
• Google Keyword Tool [External vs Beta] – What’s the Difference?
• How to calculate approximate traffic volume for the past 12 months in Google Keyword Tool

View the full post: Understanding Competitiveness in Search Engine Rankings [Keyword Research Tools] or read more at SEOgadget.co.uk