One More Great Way to Use Fusion Tables for&nbspSEO

This YouMoz entry was submitted by one of our community members. The author’s views are entirely his or her own (excluding an unlikely case of hypnosis) and may not reflect the views of Moz.

Recently Mark Cianfrani wrote on SEERInteractive.com about how Google’s Fusion Tables can be used for visualising backlinks and social media data. Google’s new interactive graphs supported by Fusion Tables simply blew me away, and I immediately wondered in what other ways this could be used.

Fusion Tables?

Google added a new feature to its Google Docs called Fusion Tables. Essentially, these are databases hosted online just like other types of Google documents. The only difference is that with Fusion Tables you can do things you simply could not do before: create Network Graphs!

Since I thought the concept of the Network Graph fits the characteristics of links perfectly, I decided to see if I could create something nice for visualising the internal link structure of a website. Although I’ve just started out experimenting with this, I’d like to show you what I came up with.

The above graph shows the internal linking structure of www.expandonline.nl. The blue dots are pages where links are placed; the orange ones are the pages that the links are to. Each dot has a roll-over function that highlights the pages it links to (in case of a blue one) or is being linked from (in case of orange).

As you may have noticed the graph looks quite nice and organised. Let’s just say, I didn’t do that. What is not visible in the screenshot is that the pages are also grouped by category. This is obviously due to the fact that they receive links from the same top pages, but all in all it looks great!

How did I do it?

It’s relatively simple to generate such an image using only XENU Linksleuth, Fusion Tables, and Excel (or whatever spreadsheet software you use). Here’s a short how to:

Crawl a website with Xenu and export the GraphFiz file.

Import the file to Excel using the Text Import Wizard. Select ‘Delimited’ and use Space as separator. You should now have the following spreadsheet:

You need to slightly tweak the Excel file to make it nice and clean. This makes sure you will not get errors while importing the data to a Fusion Table in Google Docs. First of all delete column A and column C as there is no relevant information there. Then Delete row 1 and add headers to the remaining empty row at the top. After this you might want to filter out some junk from the table such as:

Images

Stylesheets

Duplicates

Etc.

If your standard pages end in .html, it’s quite easy to grab only the .html pages only and delete the rest. Otherwise, it’s slightly more work.

Finally, I recommend adding some text to make sure the values in Column A do not show up in Column B and vice versa. Having links in both columns seems to prevent the nice color separation of link source and target and generates a messier graph in general. I add ‘S:‘ to the source and ‘T:‘ to the target. When you’re done, it should look something like this:

Save your file locally as .xlsx and go to Google Docs. Here you can create a new Fusion Table from a local file:

When your table is created you can select: ‘Experiment’ => Network Graph and BOOM! There it is!

Extras:

You can add extra columns to your spreadsheet before uploading it to Google. For example: hierarchy of the page, category, or anything you like. These can be used for filters like: Link Target < 2 levels deep. Have fun!

You can set the Number of Nodes Shown, which you can use to make the graph more like a ‘model’ or more like reality. One more great way to use Fusion Tables for SEO.

Looking forward to seeing more posts on creative ways to use this. Between this post, Marc's on SEER, and Ethan's post on link networks, that's three solid uses for this tool in two days. Can't wait for more posts like these to come forward.

I've been looking for a good way to visualize what sections of a site have poor internal linking, this certainly does the trick quickly. It's great to be able to visually show a client "Look, this section is too isolated from the rest of the website" aside from simply showing steps away from homepage in site crawl data.

I imagine there's a ton of opportunities in the weight, filter, and aggregate options as well. Drill down to specific sections of the site, or filter by page metrics after adding data with SEO Tools for Excel, etc.?

WOW! Great use! It's funny because I hadn't even considered using this for a quick internal visualization, but this is so helpful. You can really do some great things once you have your data filtered (I'm pretty terrible with Excel).

Also, quick note. I'm not sure if you know, but you can share the visualization directly by going to the "Get Link" button at the top and saving the view. https://www.google.com/fusiontables/DataSource?snapid=S507114Stnu

Skepticism made me hesitate to use Fusion Table, but after I read this blog I think I must give it a try. Nothing would lose on me if I did. Actually, I only knew a little bit about Fussion Talbe on how it works, but after I read this blog, I think I have now the reason why I should used it.

I have used Fusion Tables before, another good use I have come up with is for visualising lead data. It's useful if you have access to customer information to geo-locate the data then visualise your customer/lead data in a heatmap. Love to see other ways of using fusion tables in such an innovative way! :)