Visualize Your Backlinks with Google Fusion Tables. Data can be boring. I’m sorry, but it’s true. Don’t get me wrong, I love a good spreadsheet as much as I love a good novel, but whenever I can get away with it, I’d much rather just see the movie. Visualization and presentation have never been more important then they are today. The amount of data that we collect, process, and analyze is overwhelming.
Have you ever peeked into the backlink profile for a domain that has over a million links? In typical Google fashion, Google quietly rolled out a brand new “Experimental” version of their Fusion Table application a few weeks ago. This is what 15,000 links looks like Who said that competitor backlink research couldn’t be fun? You can view the completed Network Graph and Fusion Table here. Want to SEE who is linking to you? 1. For this experiment, I gathered the backlink profiles for four companies in the SEO industry, SEER Interactive, Distilled, SEOMoz, and Zamolution (who rank #1 for “Philadelphia SEO Company”). 2. 3. 4.
BOOM. Keyword Research - Using Categories to Make Your Process More Actionable. The author's posts are entirely his or her own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz. One of the earliest and arguably most important parts of the SEO process is keyword research. Keyword research helps you answer that all important question, "In what quantities do people use search engines to find the products and services on my website? ". Your research process will ultimately govern the method you use to structure your website, inspire your content strategy and kick start your link building campaigns. So, it's a bit of a shame that some SEO's don’t like doing keyword research. It’s data intensive, requires some heavy lifting with Excel, and, let’s be honest, at times feels a bit like guess work.
This is a "give it up" post For several years now, I have used a methodology for our client keyword research that I believe adds deeper, actionable insight in to the decision making process. What you need to know first That’s it! Yay! All of it. Excel and Google Docs: Tools for the Ultimate SEO Dashboard. I love SEO tools. I’m sort of a pack-rat when it comes to Google Docs tools and Excel add-ins. I went absolutely nuts when Tom Critchlow posted his Google Docs SEO tutorial on distilled. Since then, I’ve rolled a few tools of my own and collected pretty much anything to do with SEO and spreadsheets since then.
I’m a firm believer in bootstrapping. As an SEO consultant, the less I have to spend on tools, the more I can reinvest in my company and grow the business. Before we get started, be sure you have an SEOmoz/Linkscape API key. First off, I want to show you a tool I concocted after reading Tom’s tutorial. Long Tail Keyword Prospecting Long Tail Keyword Prospecting - Make a Copy Long Tail Keyword Prospecting - Instructions (right here!) The basic idea behind this tool is to quickly explode your keyword list with long-tail goodness. You’ve got four columns here. Ta-da! Content Strategy Generator Content Idea Generator - Make a Copy Content Idea Generator - Instructions Oh, holy crap. Competitive Analysis in Under 60 Seconds Using Google Docs. Faced with a new client, and having established a list of keywords they need to target, you want to evaluate the competition to find out what sites are dominating the SERPs for these keywords.
However... being an SEO you're a busy guy (or gal), and you need it done right now. I've built a Google Docs tool to automagically do exactly that and this post will walk you through it. The basis for this tool comes from a report in this linkbuilding post on YOUmoz which contained a neat little 'SERP Saturation' report. I don't know how Stephen made his snazzy looking report (he's now shared a few details in this comment), but in response to a few people asking about his I thought I'd put together a tool.
Here is Stephen's report: Cool, eh? 1. So, what's the report all about? You can just duplicate the Google Docs spreadsheet I provide below, and change almost any of this to add, modify or take away as per your needs. 2. You must configure it the first time you use it: 3. 2) Open the 'Report' sheet. Excel functions for fetching Google Analytics data | AutomateAnalytics.com. How to Build an Advanced Keyword Analysis Report in Excel - YouMoz. Analyzing keyword performance, discovering new keyword opportunities, and determining which keywords to focus efforts on can be painstaking when you have thousands of keywords to review. With keyword metrics coming from all over the place (Analytics, Adwords, Webmaster Tools, etc.), it’s challenging to analyze all the data in one place regularly without having to do a decent amount of manual data manipulation.
In addition, dependent on your site’s business model, tying revenue metrics to keyword data is a whole other battle. This post will walk you through a solution to these keyword analysis issues and provide some tips on how you can slice and dice your data in wonderful ways. With Microsoft Excel, we can create a report with all the keyword data you will need, all in one place, and fairly easy to update on a weekly or monthly basis. Then with all this data we can easily categorize segments of it to more quickly determine the better performing sets of keywords. The Impression That I Get. Playing Around with ImportXML in Google Spreadsheets. 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. 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.
Does anyone know? Twitter followers Have fun. Tips and Tricks – Excel and SEO: Word Search Formula | Big Sushi Blog. The following Excel formula is useful for keyword research, perhaps one of the most important steps in search engine optimization. You should always conduct keyword research prior to writing content for your website. By identifying relevant keywords with high traffic and low competition, you stand a greater chance of attracting visitors interested in your content. The word search formula enables us to sort a spreadsheet depending on whether words exist in an adjacent cell. Think of it as a full text search on a column. But instead of just seeing which cells match, we can also interact with the results.
Search Matching Words Using Excel This formula will find the existence of a word or words in a cell: =IF(ISNUMBER(FIND("social media",A2)),"true","false") How to Use the Excel Word Search Formula Create a new column next to the column containing the words you are searching for.Click the cell next to the cell you want to search and paste the formula as shown in the screenshot below. Using Excel for SEO – the Grand Collection of Tips. You probably know that I am a big Excel fanatic (though not an expert). To me, Excel has always been the ultimate SEO, guest blogging and productivity tool. I’ve been collecting Excel tutorials for years and this post lists the most useful (yet, the least geeky) of them: no matter which SEO task you have come across, chances are you’ll find one of the following tutorials handy: 1. Export Any Data to Excel Any well-known keyword research or traffic analytics tool has the “Export-to-CSV” feature and a CSV file is easy to open in Excel – so converting your data into Excel shouldn’t be a problem.
If you still you need some examples, I did a post quite some time ago listing many ways to export your backlink data to Excel; for instance: With Yahoo! 2. 1. This post by Richard Baxter on creating a pivot table and a beautiful chart using Excel offers a step-by-step tutorial on how to re-organize your data to run various types of analysis. 2. 3. 3. 1. Now use the macro: 2. Before: After: Microsoft Excel for SEOs. Table of Contents Excel for SEO is a guide we wrote up to help SEO professionals get meaningful information from mountains of data. This guide was originally written to be read from start to finish, as some examples are worked on through different lessons. That said, you should be able to jump ahead if you feel like you’ve got the basics down. Please report any errors or confusing stuff to one of the Twitter accounts on the right and we’ll be sure to fix it right up.
Feel free to download the XLS with the data from the examples. Completed the training? Introduction SEOs have been getting into our industry from all sorts of past careers — web designers, developers, marketers, business people and those that “just fell into SEO”. Over the last few years our field has become even more data-driven than in the past thanks to tools like Open Site Explorer, improved Google Webmaster Tools and Analytics, Majestic SEO, Raven, and many others. Oh! Lesson 1: Basic Tasks Concatenate Text to Columns Voila! P.ost. How To Build Agile SEO Tools Using Google Spreadsheets.