Jobs in Digital Marketing

Our first post of the New Year looks at the topic of measuring SEO indexation. Inspired by Rob’s post on Distilled, and a wish to revisit some of my previous work on the topic, I thought it might be interesting to share a method of collecting data to build a clearer understanding of page level indexation.

Image credit: Justin De La Ornellas

Hopefully by the end of this post you’ll have a few new methods to collect site index data for your own SEO studies.

Why do SEO’s need an understanding of the principles of indexation?

How hard is your website working for you? Which pages and content groups yield the most benefit, traffic wise? Are there any weak spots, groups of pages that don’t seem to be working well? How can you make changes to navigation, architecture and sometimes page layout to improve a website’s overall search engine visibility or long tail traffic performance? These are questions that should occur to an SEO on a regular basis – but coming to a reliable answer is not always straightforward.

Seeking answers to indexation and site architecture related questions is a worthy cause, but achieving a meaningful answer is a significant hurdle to overcome. All of the (excellent) resources on this topic tend to approach indexation from the perspective of analytics data, or content grouped together inside sitemaps. What about individual pages, though? I use the term page level indexation, because I’m seeking a granular, page level answer to my indexation questions.

What data sources can tell us how a website has been indexed by Google?

For me, there are a number of approximate indicators that a page (or page group) is indexed – for example, reporting on pages that receive at least one entry from Google via Analytics. You might wish to take a look at the “URLs in Web Index” report in the sitemaps section of Webmaster Tools. Savvy webmasters and SEO’s may even use multiple sitemaps to get clearer page group level insight.

Logfiles will tell you a lot about GoogleBot visits, but not indexation, so where else can we look for inspiration?

Expressing the number of pages included in a web index as a percentage, but exactly which URLs are included?

Collecting indexed pages data using the “cache” query

As a method to compliment your existing approach, you might find this methodology quite interesting. The outcome will be a page by page URL list for your site, where Google cache data, SEOmoz custom crawl and XENU data will give you a cracking starting point for you to diagnose your indexation problems. These steps involve having a Mozenda account, although you could do the same (or similar) by building your own crawler or using 80Legs.

Collect Google cache data via a proxy

Fundamentally we’re going to be executing a series of Google cache queries via a proxy. With Mozenda you have to have a method of distributing queries to Google via a proxy. even then it gets unreliable quickly if you overcook the requests. If you use a simple PHP proxy and go very, very slowly, you’ll probably be alright.

Get a URL list

For this, you’ll need a list of all of the URLs your site can generate. The easiest way to get to this list is to extract all of the URLs from your XML sitemap(s) or ask your developer. Remember that, if you crawl your site with XENU you might miss orphaned pages.

Build a Mozenda agent scraper

Your crawler needs to execute the Google cache query and should be configured to capture the URL and cache date.

http://webcache.googleusercontent.com/search?sourceid=chrome&ie=UTF-8&q=cache:[URL]

Would result with:

If no results are found, your agent needs to be able to record the alternative result. When you’re happy with the agent you’ve built, upload and run the agent. Execute this very slowly (proxy in image is a publicly available service – proceed with caution).

Mozenda running my cache agent:

Combine your new data with a few other sources

While your cache scraper is running, think about where else you could gain insight through combining your data sources. Let’s not forget we’re trying to locate pages that are not indexing. Some of the data points you could include may be;

- Click depth of URL from home page
- Internal links out from page
- Internal links in to page
- Meta robots
- X-Robots in server header
- Status code response

All of these data points can be gathered from two sources – Xenu’s link sleuth and the SEOmoz Custom Crawl tool. Xenu needs little introduction, but few know that click depth, internal links in and out of a page are part of the available data. SEOmoz’s Custom Crawl is awesome, and includes data on the server header response, contents of the X-Robots tag, meta title and rel canonical target.

Having a list of all URLs on your site, with a definitive answer on click depth, number of internal links and the Google Cache status is a very interesting piece of data to have, but (of course), it can be extended even further.

If you’re looking for a larger crawl of your site, but the same data, Adam from SEOmoz has pointed out you can get 10,000 pages + (depending on your membership level) crawled and exported from the SEOmoz Pro Account:

You can find this data via the “Crawl Diagnostics” tab in your campaign dashboard. Thanks Adam!

Content grouping

Most websites have a relatively simple approach to content types via their URL formation. This blog, for example, uses “/category/” in the URL to indicate the category content type. Paginated URLs might appear as “/page/*/”. If you’re a retail site, perhaps your product pages contain “/product/”.

By using an Excel query to group your contnet types, you’ll have the ability to get a sense of overall indexation in an area of your site, without having to group the sitemaps together. Try something like:

=NOT(ISERROR(SEARCH(“[URL CHUNK]“,Table3[[#This Row],[URL]],1)))

Where “[URL CHUNK]” could be “/page/”, “/products” or whatever. The outcome is “TRUE” if your URL belongs to a recognised group, and “FALSE” if it doesn’t.

Entries via Google to URL

With a simple VLOOKUP, you can combine traffic numbers by URL in your indexation data. This might help highlight pages that *should* have a little traffic from Google, but don’t – or at least you’ll have another point of reference for your investigations.

The end result

Here’s a screenshot of the example data I built while writing this post. You’ll see all of the data I’ve mentioned in this post, along with a number of “content groups” I found most relevant to my blog. There are some properly configured duplicated pages with SEOgadget which, I can confidently report, are not cached, nor are they generating traffic. My data tells me that the paginated URLs on the homepage, category and tag pages are properly set to noindex but that those pesky comment pages (where a blog post has more than a certain level of comments, we paginate them) are misbehaving (they should be set to noindex). Time to roll my sleeves up.

Click to enlarge…

I hope you can see from this screenshot how you might benefit from combining data into a single point to identify, diagnose and fix indexation issues on your site. Of course there are other data sources out there, and we’ve not touched on the visual aspect of representing this data, which I’m saving for another post.

In the meantime, I’d really like to hear your thoughts, particularly on the data you might choose to help diagnose your architecture and indexation issues.

Page Level Search Engine Indexation [Data & Collection Methodology] 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.

Put me in front of a Mac and it’s almost as if I never learned to use a computer. Put me in front of Google Spreadsheets and all of the time I’ve spent working with Excel feels a little like time wasted, and not in a good way. I’m just not very used to a spreadsheet that isn’t Excel.

Unafraid of a challenge, I recently decided to give Google’s (exceptional) importXML, importFEED and importHTML functions a try – the ability to fetch information from the web to retrieve the data you need. Mostly to make an interesting blog post, but partly out of envy that Excel doesn’t have this function.

Image credit: Tech109

It’s frustrating trying to get XML data into Microsoft Excel – unless you’ve got the time and patience to build some basic Macros or VBscript for your requirements. With Google Docs, it’s really easy.

A few resources

If you want to use Google Docs to extract data from the web, it would be a good idea for you to learn a little xPath. “XPath is used to navigate through elements and attributes in an XML document”, or, in simple terms, you can use xPath to fetch little bits of data contained in structured elements like <span>, <div> or links or pretty much anything, really.

Also, there are a few people who have been doing this a while, and probably have sample spreadsheets that blow some of the examples below away – but you have to start somewhere, right? If you’re already an importXML / Google Docs Ninja, maybe go and find something else to do instead of reading this post.

If you’re interested, I made a Google Docs Spreadsheet with all of the examples below:

http://bit.ly/9Fs7aF

Does anyone know?

“Does anyone know” is such an interesting search on Twitter – just combine that query with a keyword, like “restaurant” and a location for everyone on Twitter looking for a very specific, thing. Great if you happen to be trading in that thing.

Try a query like this to pull through results from the Twitter search RSS feed:

=Importfeed(“http://search.twitter.com/search.atom?q=+restaurant+%22anyone+know%22+london+OR+manchester+OR+birmingham”)

Twitter followers

A nod to Steven Foskett for this one, and particular kudos for the mention of vCard, the query for LinkedIn connections, Klout score and Alexa Rank. Nice!

Try this query: =importXML(“http://twitter.com/[your-username]“,”//span[@id='follower_count']“)

Which will give you the number of followers you have on your Twitter profile. I added together the total followers that my SEO team have (that’s the three of us) for kicks. I wonder how long it will be before someone totals up all followers counts for all UK agencies? I wonder if there’s a correlation between that data and turnover

Pull price data from the web

I think that, after some mild haranguing, Will might have purchased himself a pair of Etymotic headphones. Perhaps my pitch would have gone slightly more efficiently with a little xPath and Google Product search:

For something like this, a way smarter approach to get pricing data from Amazon would be to use their API – but you get the point with this brief example.

Get all of your (competitors) URLs from their sitemap

Try something like this: =ImportXML(“http://www.yourcompetitordomain.com/sitemap.xml”,”//url/loc”)

I mentioned doing this with Excel to find orphaned pages, but you can have a lot more fun with importXML. For one, theoretically you could go off and fetch all keywords contained in the <title> tag of each of the URLs – an instant keyword strategy!

Pull link data from Blekko

With a query like this: =ImportXML(“http://blekko.com/ws/http://seogadget.co.uk/+/links+/rss”,”//link”)

Blekko is everyone’s favourite new SEO tool, and fair enough, it is quite cool. As Blekko are happy to push their data out via RSS, we’re able to pull this data into our spreadsheets with ImportXML (to be fair this is really easy with Excel, unless you’d like to create multiple columns with different domain queries.

More Blekko – link data tables

Blekko have a feature that allows for a pretty insightful breakdown of their SEO data on your domain. If you want to pull some of that through in to Google Docs, no problem:

Try this query: =importhtml(“http://blekko.com/ws/www.smashingmagazine.com+/seo”,”table”,7)

Have fun

This wasn’t a particularly “advanced” post – I did quite enjoy the thought of what to do next with this data, though. Fetch IP addresses, WHOIS details, root domain links or keyword research data with Google Suggest, the Alchemy API, or plain scraping your competitor home pages. If you’re using importXML, I’d really like to hear how.

Anyway, as I mentioned earlier, please feel free to take the queries from here: http://bit.ly/9Fs7aF – improve them, and let me know what you did.

A little update

I got in touch with my friend Tom from Distilled to see if he wanted to contribute. He’s been out in Vegas, but came back with a tip to solve the problem of Google caching a result for around two hours at a time:

Google docs will cache a URL for ~2 hours and so if you want to crawl a URL more often than that then you need to add a modifier to the URL.

I use int(now()*1000) to generate a unique timestamp and then add that into the URL in a dummy query string. E.g.

http://www.google.com/search?q=seattle+seo+consulting&pws=0&gl=us&time=1354333

The search results won’t change when you change the time value but Google docs will treat it as a fresh URL and crawl it again.

Also – you can do lots of amazingly fancy things using Google Scripts (kind of like macros for google docs) but don’t have a huge amount of time to go into detail about that now!

Well, hopefully Tom will have time soon – thanks for contributing!

Playing Around with ImportXML in Google Spreadsheets is one of our latest posts from: SEOgadget.co.uk.

For a little while now I’ve been wrangling with an interesting problem in site architecture diagnostics. Do I have any orphaned pages on my site, and how do I find them?

Frequently, web developers will write code to generate an XML sitemap that will include all URLs in the database, linked to or no. How can we use this fact to our advantage?

Image credit: Nick Johnson

I thought I’d share a strategy using Excel to extract all the URLs on your site, comparing them to the raw export from the amazing IIS Toolkit. (PS – word is that Daniel’s writing a “how to” for IIS, including the installation…)

Import your XML sitemap URLs into Excel

Did you know you can import an XML sitemap into Microsoft Excel? It can be a little crash-tastic, but oh-so rewarding when it works. Let’s use SEOgadget’s XML sitemap as the example:

Then, pop your XML URL into the dialogue box:

I’d recommend you only do this if you have reasonably powerful machines, we rock i7′s with 4gbs of ram as a system minimum. Big sitemaps take a while!

The end result is something like this:

By the way, you could import Twitter search results via the RSS feed into Excel using the same method, if you’re into that kind of thing.

Get your IIS site crawl data

Dan’s working on his next post, so I’m not going to steal any thunder, I promise. Assuming you already have IIS Toolkit, go ahead and export the URL listfrom your site crawl:

Import the CSV export into Excel, and turn the data into a table called IIS. If you haven’t played with exporting data from IIS, you should – it’s amazing.

There’s a ton of brilliant data in the IIS export, including the contents of the server header reponse, internal links counts and the directory the page is hosted in. We’ll talk more about that later on, use for now we’re interested in orphaned pages.

Use VLOOKUP to find pages in your sitemap that have no internal links

At the end of the day, a page in your website with no internal links is very unlikely to rank. Many pages with no internal links indicates some problems with your site architecture that you’re going to need to fix. Alas, how to find them? With a trusty VLOOKUP, that’s how.

We’re most interested in the “linked by” column, which contains the total number of internal links a page has. All you have to do is pull this data into your imported sitemap XML list. Assuming you name your IIS table “IIS”, the query will look like this:

=IFERROR(VLOOKUP(XML[[#This Row],[ns1:loc]],IIS,18,0),”Not crawled”)

Why is this good?

The end result will be a list of URLs in your XML sitemap with corresponding internal link numbers. That’s cool for doing the following:

- Finding URLs in your XML sitemap with no internal links, and are as a result, “Not crawled”
- Finding URLs in your sitemap with very low numbers of internal links

Have fun, and have a great Bank Holiday weekend

Find Orphaned Pages From Your Sitemap.xml File with Excel and IIS Toolkit is one of our latest posts from: SEOgadget.co.uk, UK SEO consultants helping people and organisations succeed in search.

I’ve had two separate occasions in as many weeks where I’ve needed to answer the question: “What Are My Most Linked to Subfolders?” for a client, or for myself. I thought it would be quite fun to do a quick write up on how I answer this question using a simple Excel query, some Webmaster Tools data or Open Site Explorer.

In this post we’re going to use raw external link data from Webmaster Tools to quickly identify the most linked to subfolders on a site. You can repeat the same process using Open Site Explorer data, too.

Image credit: anne.oeldorfhirsch

Download your external linked to URL data

First, download your external links data from Google Webmaster Tools – here’s some anonymous data showing a site I’m working on and how many links their top pages have (blurring idea courtesy of DaveN)

Fun with tables, pivots and Excel queries

Do the usual “CTRL+L” to create an Excel table, I’ve called mine “GoogleLinks”.

In a new column, paste the following query:

=MID(GoogleLinks[[#This Row],[Page]],24,FIND(“/”,GoogleLinks[[#This Row],[Page]],25)-23)

This query starts at a specified point and plays back the characters in a cell until the next “/”. I’m using MID to start at a fixed position (24 = “http://www.domain.co.uk/”) and FIND to count the number of characters to the next “/”. Subtracting the count of characters in the domain from the count of characters to the next “/” gives me a column listing the folder name only next to the count of links in the data.

The end result is a little something like this chart, lovingly created using a pivot table:

Value errors

You’ll notice the #VALUE! result in the 4th column. That’s because my query gets upset if there isn’t another “/” from my MID starting point. Not to worry, try this slightly “improved” query:

=IFERROR(MID(GoogleLinks[[#This Row],[Page]],24,FIND(“/”,GoogleLinks[[#This Row],[Page]],25)-23),MID(GoogleLinks[[#This Row],[Page]],24,LEN(GoogleLinks[[#This Row],[Page]])-23))

If there’s an error, a slightly modified query kicks in, using LEN to give a count of the total characters after our starting position. The count, along with the MID query makes sure we can just play back all of the characters found in the cell. The end result looks a little better:

What’s the point of this?

In my humble opinion, there’s always been huge value in analysing your competitors top pages, and your own. When we’re dealing with a larger site architecture or we’re in need of the 10,000ft view, a count of all links by subfolder can add additional insight into the mix. I also believe that having an idea of your best linked to subfolders can help a lot with understanding strong and weak points with inbound link authority to certain content types, especially after the May Day update. That thought is for another post though.

Fun looking at the competition, or just big websites

In the meantime I’ll leave you with this fascinating view on the most linked to subfolders and pages at Google.com – data courtesy of Open Site Explorer and SEOmoz’s Top 500 list (my source of inspiration…)

This approach worked well for me – but you may have to tweak the queries slightly for it to work for you. If you have an alternative suggestion, I’d love to hear it. Let me know your approach and I’ll gladly update this post with your queries!

What Are My Most Linked to Subfolders? is one of our latest posts from: SEOgadget.co.uk, UK SEO consultants helping people and organisations succeed in search.

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