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

For SEOgadget clients, most of the SEO work they receive from me is delivered at the end of a heavily data driven process. If you’re feeling a shift towards data driven SEO too, then the chances are using pivot tables and charts in Excel is a near daily part of your SEO consulting activity.

Image by: Incase Designs

At some point we all have to up our game, especially with Excel and general analysis skills, so at the SEOmoz Pro Training Seminar late last year, I gave a step by step tutorial on how to make a beautiful chart based on an Excel Pivot Table.

Almost 6 months after the presentation I’ve finally gotten to tick another blog post off my ideas list: “how to make a pivot table in Excel”.

The chart above is the finished article showing search volume (or analytics entries) and ranking on the secondary axis. It’s my favourite SEO chart, and feels especially useful for keyword research presentations. Here are the main steps to making the chart above:

Gather your data and create a Master table

Pull down some keyword research data from Google Keyword Tool, the Search based Keyword Tool or your usual source of keyword volume information and paste the exported CSV into a (Master) Excel Table. You don’t have to use tables, but I recommend you do – amongst other reasons, tables are less work for your processor, less work for you and can be fun to name. Next, you need to run your keywords through your favourite rankings checker (mine is Advanced Web Ranking) and put the exported CSV into another table. Excel 2007 pivot tables demand that the data for the pivot comes from one table (until Excel 2010 is commercially launched, I’m sticking with 2007) so let’s do a simple VLOOKUP to pull the rankings data through into your Master table.

Use VLOOKUP to pull the rankings data into your keyword data master table

Use VLOOKUP when your values are located in a column to the left of the data that you want to find, says Microsoft. I’ve written before about the power of this Excel query so, if you’re new to it’s use, try this post on how to use VLOOKUP for matching keywords and rankings data.

Note in my screen grab that there’s the occasional missing value in the rankings columns? That’s because there are no values for that particular keyword in the rankings (raw) data table. If you say, wanted a zero value to appear instead, you could use an IFERROR and nest the VLOOKUP inside the new query. For the time being, we don’t need to complicate things too much.

Create a Pivot Table on a new sheet

Now we have all of our data nicely arranged in one place, let’s get to the fun part. We’re going to add a pivot table to a new sheet. You can add the chart later if you like, but I always add them both at the same time by selecting “Insert > PivotTable > PivotChart“.

Next, a window will appear that looks like the one below. Hopefully, you’ve taken note of your table name (visible via “Design > Table Name:”), though if you don’t know what the table is called it will almost certainly be called “Table1″!

When you click “OK”, you’ll be presented with a blank Pivot Table “field list” and a Pivot Chart “filter pane” on the right of your screen and a very blank looking space on the left called “PivotTable1″.

Add axis fields, values, column labels and filters

If you’re new to Pivot Charts, you’re about to experience a bit of a penny drop moment. We’re going to look at which items of data should be placed where and you’ll see very quickly how a pivot table works.

The PivotTable Field List uses drag and drop functionality to enable you to populate those little white squares with values. As you add values, the table on the left begins to form. Start by picking up your keywords by dragging the keywords (KWs in my screenshot) field into the “Axis Fields” box. Next, drag and drop your search volume figure into the “Values” box. Provided you’re looking at “Sum of KW’s” and not “Count of KWs”, your table on the left will start to make a lot of sense:

You’ll very quickly notice that you’ve created a thing of beauty. A pivot table with all of the keywords in your list and all of their corresponding search volume values. I call this the pivot-table-penny-drop-moment. Having all of your values in a pivot table might not be what you intended, though and as you can see, in my table I have some pesky “-1″ values to clear away. To do so, we need to apply a “Report Filter” by dragging the data point we wish to filter into the correct section:

You can filter by any value in your master data table, which allows for some serious charting! Follow the black arrow to the filter drop down and “Select Multiple Items”. You’re now free to clear out any irrelevant data from your table. Finally, drag down the rankings values into “Values” and you’ll have a pivot table, with keywords, search volume and rankings. Now to make a graph!

A pivot chart is born

You’ll already have the pivot chart right in front of you (mine’s just missing from the screen shots). It may look a little rough around the edges though, so let’s make it look a lot nicer than this:

First, we need to organise the keywords by search volume so we can look at our chart as a tail graph. Highlight your search volume data (the column you’d like to sort in volume order) and select “Data > Sort“.

This will improve matters slightly, but there might be a few too many keywords in the chart. Try filtering out the lower volume terms, at least for the time being.

Sort out the rankings by keyword

We’re really very close to being done. The only remaining challenge is to arrange the rankings in such a way that they make sense, visually. You should never compare fundamentally different types of values on the same chart axis, so lets create a secondary axis for the ranking values.

First, you need to select and format the rankings data series. You could use your right mouse button on the chart and select “format data series”, but that’s fiddly and unnessecary. Instead, select your chart and navigate to the “Format” pane. You’re looking for the “Current Selection” drop down on the far left hand side of the screen. Select the drop down and click your rankings data series, now, select “Format Selection”.

We’re going to place the data on a secondary axis, and change the chart type to a line chart. Finally, we’ll remove the lines in the rankings chart to leave only the markers.

Dealing with the secondary axis and changing chart type

My ranking charts use a reversed secondary axis to place position 1 rankings at the top of the chart. To be able to do this, we’ll need to edit the secondary axis. Right click on the secondary axis in your chart, and choose “Format Axis“. Setting your minimum value to “1.0″ will exclude all of the zeroes in your rankings data and setting a maximum of say, 15 would exclude any ranking higher than 15th. You choose the range that’s right for you.

Finally, check “values in reverse order” and you’re almost done.

Finishing touches

One tiny point left to do, we should change the chart type so our secondary data makes a little more sense. Select the rankings data bars and navigate to “Design > Change Chart Type”. Select the line graph option with visible data markers in the line. Now, take out the line colour from inside “Format Data Series > Line Colour” and you’re done. Here’s the chart I reproduced while I was writing the blog post:

And there you have it. How to make a pivot chart and table in Excel. Have fun making your own!Similar Posts:

• Using VLOOKUP to match keyword volume and rankings data
• How to calculate approximate traffic volume for the past 12 months in Google Keyword Tool
• Playing Around With Google Webmaster Tools Click Data
• My SAScon Presentation [Advanced Link Building]
• Comparing Trust Metrics and Value Analysis to Understand Search Rankings
• Using Tables in Microsoft Excel 2007
• Understanding Competitiveness in Search Engine Rankings [Keyword Research Tools]
• Google Keyword Tool [External vs Beta] – What’s the Difference?
• How to resize a Windows Vista partition
• How to remove the Sandisk U3 Launchpad

View the full post: How to Make a Pivot Table and Chart in Excel or read more at SEOgadget.co.uk

In my SEOmoz pro session last week I spent some time explaining the benefits of using Tables in Microsoft Excel. Gone are the days of broken formulas that once worked, and extending your cell range references every time you add new data in a spreadsheet.

Using this technique isn’t all that different to using cell references, and the outcome is a more agile and robust Excel, with an ability to manage your data faster making for a more time efficient experience. What’s not to like?

What are Tables?

From Microsoft Office Online [Overview of Excel tables]

A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.

That seemed really obvious! Let’s drill down a little deeper.

What’s the actual difference between data stored in an ordinary worksheet and a table?

There’s no difference at all. The benefit of using tables are how cell references change, and how they seem to suit keyword research methodologies and other SEO applications particularly well. Let’s look at an example to get the point across.

First, take a look at the table itself. By highlighting your data in a worksheet, and pressing CTRL-L, you can add a little polish to a quite ordinary data set.

Before:

After: [Highlight, CTRL-L, Select "My data has headers"]

Apart from the obvious differences in formatting, tables become quite powerful for two reasons. First of all, a formula applies to an entire column immediately, not just in the cells you apply the formula to. Enter a VLOOKUP into cell D2 and the entire column marked “September” will perform the calculation. Great for big worksheets.

The formulas change significantly

We don’t like cell references, especially when they apply to a large array of data. Why? Because if you add more data, you have to manually adjust each cell reference. This process introduces bugs and errors quickly. Using a VLOOKUP as the example, let’s take a look at how formulas change the way they refer to cell ranges, other tables and columns.

This is our “pre-table” VLOOKUP. You can see cell references “A2″, cell ranges “$A$2:$E$11″ and worksheet references “‘KW rank’!”:

=VLOOKUP(A2,‘KW rank'!$A$2:$E$11,5,FALSE)

This is the same query written in a table:

=VLOOKUP(Table1[[#This Row],[Keywords]],Table5[#All],5,0)

Where “Table1[[#This Row]” is our new cell reference, “[Keywords]” is the Column name in which the data we’re looking for is stored, and “Table5[#All]” is the table in which we’re looking to find a match and pull through the value from column 5.

Become more agile in the time it takes to drop a penny

Though it takes a small period of time to adjust yourself to this way of thinking, tables are great for keyword research agility. Imagine the scenario – you’ve nearly finished your analysis and you remember there’s a keyword type missing from the original data set. If you’re using tables, you can extract your data from Google Keyword Tool and paste the raw CSV straight into Table5. All of the VLOOKUPs in our example would automatically update as Table5 adjusted itself to accommodate the additional lines of data.

Tables save us time and effort and they’ve been around for a while. As I said in my presentation, they’ve changed the way I work with Excel for the better. Next time you’re working with a spreadsheet, try selecting your data and pressing CTRL-L. It takes a while for the penny to drop, but once it does, there’s no turning back. Good luck!

Photo: In Case Designs

Similar Posts:

• Using VLOOKUP to match keyword volume and rankings data
• How to calculate approximate traffic volume for the past 12 months in Google Keyword Tool
• Trism on the iPhone
• Implementing named anchors to improve your SERPs
• SMX London 2008 – Day 1 “High Fives”

SEOgadget is an SEO Agency specialising in helping people and organisations succeed in search.

Using Tables in Microsoft Excel 2007

On the 24^th September 2009, Google announced a revision of their Keyword Tool, the imaginatively titled “Keyword Tool (Beta)”.

As Barry reported that morning at Search Engine Roundtable,

Google has a beta version of a new keyword tool available in the AdWords console. To get to it, login to adwords.google.com, go to a campaign, click on opportunities (if you have that tab), then on the left bar, click on keyword tool. A “beta” link should be available for you to click on in the top paragraph.

SEO’s all agree, the data from Google’s Keyword Tools and other sources should be taken with a pinch of salt, and I definitely agree with that too. Particularly in the US market, the Local, Global and Trend data sets just don’t feel right on certain keywords.

Has Google improved the situation in their new tool? We’ll let Google tell you about their fancy new interface and get right on with what’s important. The data.

Evaluating Google Keyword Tool Beta

From the start, let me tell you that this post does not conclude with a “this tool is right” kind of an answer. What it does do is compare new with old, and it’ll tell you where the differences are. With an understanding of those differences, you can make your own decision about which source makes more sense. I’ll give you my personal opinion on this, but, I’ve only reviewed one sector in the UK market, so the conclusion could be different in different geo locations.

For the record, I looked at local search data in the UK market, ignoring the global figures. All values relate to August 2009 comparing the data exported from the Beta tool. Variances are expressed as the percentage difference of the beta tool compared to the old tool.

Observations

1) The largest volume generating keyword in the dataset had a variance between the data sources of -242% – equating to a difference of more than 14 million searches per month. Beta significantly under reported at the head of the data, compared to the original tool.

2) With the largest volume generating term removed, Google beta continued to under report by as much as -230%, but did not over report until the 28th keyword, with a search volume of approximately 76,000 searches per month.

3) After the 28th keyword, beta begins to demonstrate a smaller negative and eventually frequently occuring positive variance further down the data set. An anomaly is visible at keyword 36, where beta strongly disagrees with the old keyword tool. From this point the variance favours the beta keyword tool, with more volume data available in the beta keyword set for long tail search queries.

4) Much further along the tail (a selection of 40 keywords with a volume between 140 and 1700 searches per month), the beta tool under reported compared to the original keyword tool data set, with the average variance around -35%:

5) More data (results with a volume figure) was acquired using the Beta Keyword tool in the same keyword list, making the Beta tool a better tool for long tail keyword data. Beta keyword tool has improved data exports with more rows of data and actual numbers for monthly trends, instead of those dreaded ratios.

6) Keyword Tool (External) and Keyword Tool (Current, signed in to adwords) are exactly the same.

Conclusion

It’s very difficult to draw a conclusion by simply comparing the two data sources. There are obvious differences, and my personal opinion is that the beta tool is a step up from what we have now. The data exported (as a CSV) contains more usable values from the outset, and there are powerful categorisation features available in the user interface.

Keyword data evaluation is not easy. My recommendation would be try this for yourself, pulling actual rankings and Google referral data from your analytics tool to benchmark the numbers. My theory is that you’ll see some consistency between organic CTR% on a more accurate dataset, by keyword category / group. That’s a different blog post though.

Image credit: Pasta Boy Sleeps

Similar Posts:

• How to calculate approximate traffic volume for the past 12 months in Google Keyword Tool
• Google Keyword Tool – Volume data is permanent
• Using VLOOKUP to match keyword volume and rankings data
• Demand for SEO Jobs in 2008
• Keyword Research artistry at SMX Advanced 2009

SEOgadget is an SEO Company and blog founded by SEO Consultant Richard Baxter.

Google Keyword Tool [External vs Beta] – What’s the Difference?