Noun Verber fucked around with this message at 05:45 on Mar 5, 2016

onemillionzombies fucked around with this message at 07:27 on Mar 5, 2016

boar guy fucked around with this message at 07:35 on Mar 5, 2016

boar guy fucked around with this message at 22:42 on Mar 8, 2016

boar guy fucked around with this message at 07:23 on Mar 9, 2016

Tools Used

Microsoft Excel (though a Google Docs spreadsheet would work just as well if you can’t afford Office)

It should be obvious to anyone that you’re going to need a spreadsheet to keep track of the potentially hundreds or thousands of keywords we might look at for a site. Actually, if you claim to do SEO for a living and you aren’t familiar with spreadsheet software and how useful it is…you probably haven’t been doing SEO for long. That’s okay, this post will be a crystal clear indicator of why anyone in SEO or digital marketing needs to learn and breathe and own Excel.

Google’s Adwords Keyword Suggestion Tool

There are many tools out there for keyword research; most of them are glorified spreadsheets using an API to pull data from this tool. We can use the data collected on the PPC side of things to look for semantically related keywords to those on our site and also get a rough- I stress the word ROUGH- estimate of the search volume during the course of a month for a particular term. Why’s that important? Well, I can rank a page optimized for a 7 word keyword phrase in first position in approximately 4-8 hours after the Caffeine update. However, if no one ever searches for that keyword phrase, what’s the point? Keyword research isn’t all about search volume and going after the most searched terms; it’s about defining a site’s niche and setting realistic goals. Also, another important point- if you have the client’s PPC data to leverage as part of the campaign, absolutely take advantage of that. Nothin’ beats hard data- not even Adwords.

semrush.com

I have a paid account to SEM Rush as I find it absolutely essential to doing keyword research. Basically, using extrapolation, SEM Rush estimates what keywords are sending organic traffic to your site, and at what percentage. As you can see for this picture, and according to SEM Rush, the branded term “Nike” is sending 6.41 percent of all nike.com’s traffic to the home page:

SEM Rush data for Nike



If you look closely you see another instance of the word “Nike” right there in the top ten- but those visitors land on the store.nike.com subdomain. It’s wholly possible (though unlikely) for one site to have one keyword sending 100% of its organic traffic all to different URLs.

Open Site Explorer

Open Site Explorer from Seattle tools vendor SEOMOZ lets you quickly evaluate your and the competition’s back links, including the number of links and the number of unique inbound domains, both critically important ranking factors. We’ll use this data after we do the first round of research to help us define what a realistic site goal looks like, and what phrases that shake out of the research we actually have a chance of ranking for.

Google Search

Uh….I don’t think I have to teach anyone reading my blog how to use Google search, though we will use the ‘intitle’ and exact match operators. Here is a of Google advanced search operators, you could spend a week alone uncovering keywords for a site once you learn how to use these. Absolutely essential!

The first step in the process is of course, when you get a client! Hopefully you’ve completed some sort of pre screening process where they lay out what their goals are for the campaign, explain how they’d like the content written, laid out any other external assets you can leverage, described a typical sales cycle, provided basic customer demographics and shared any other info that will get you intimately acquainted with their business- and that- understanding your client’s business and marketing goals- is the real first step. Anyway, part of that evaluation should be “what 3 to 5 keywords would you most like to rank for?” Frankly, the range is varied here. Some clients will give you full reign to recommend keywords to them, some will have a list of ten thousand that “they’d like us to work on” and some will have one vanity head term like “shoes” that’s simply going to be impossible to make progress on. The method given here lays out the keywords for the client but then leaves the ultimate decision up to them. Let’s get started, shall we?

Our virtual client in this case is going to be a brand new site, specializing in…disc golf. Why not? I simply picked the topic out of a hat. I’m going to assume that this client came to us with no website to draw off of but simply has the URL ilovediscgolf.com (URL is not as important as it once was for keyword research when exact match domains ruled the roost). I am going to assume this client has little to no SEO knowledge, and the only word they vaguely might want to rank for is the word “disc golf”. This is actually the ideal time to get involved in an SEO campaign, when the slate is still blank. Far too often SEO is a secondary consideration and we are brought in to “fix” an existing site after it may be too late…but I digress

The first step is simply to get a pad of paper, or an Excel sheeet, or a Word doc, or a text file, or however you like to create, and start brainstorming words that you think people who are looking for a disc golf site would key in to find a site. I’ll truncate what would normally be a list of at least 50 words for the purposes of getting done with this post some time tonight to around 10 words.

disc golf, play disc golf, disc golf information, buy disc golf supplies, buy golf discs, frisbee golf, frolf, frisbee golf courses, disc golf rules, frisbee golf forum

Throw those words into tab 1 of your Excel spreadsheet. Remember, you should be absolutely exhausting every single related word that you can before you go to the tools. Your intuition is one of your best assets in SEO- and an English degree ain’t not bad neither! I just don’t like doing free work for imaginary clients so I am keeping it very small in scope.

Then, go to the Google Adwords tool and log in. Why log in? You’ll get more suggestions. Paste the keywords you just brainstormed into the tool and hit Search. Before you go to look at the results, make sure you go to the left hand side, and under “Match Types”, un-check “Broad” and check “Exact”. You should also make sure the checkbox that says “only show ideas that are closely related to my search terms” is checked, as well. This will return something that looks like this:



The tool shows approximate relative PPC competition for each phrase, and then the approximate number of Global (the entire World) and Local (US only- since I’m in the US) monthly searches for those keyword phrases. You can also see that the system has suggested an additional 744 related keyword terms we might want to look at. That’s too many to be realistic, so we can cut the list down a bit right away. Export the file and then pick a point at which the keyword volume is too low to bother going after. This depends on how granular keywords are, among other things- a small, localized site might get a lot of benefit from going after words with only 100 or 200 searches a month; but it might not be worth a larger site’s time to go after words with such a low volume. Once you’ve culled this list to about 200 keywords- you can also manually go through all 744 if you like and remove all the branded and unrelated terms before doing the “search volume cutoff”- take those words and chuck em’ in that same first tab of your spreadsheet.

Now, go to Google Search and check who’s ranking at the top of the results page for some of your most relevant, high volume keywords. You can probably throw out sites like Wikipedia as competitors if you don’t have a gigantic budget. Write down the top 5 competitors for your 3 most desirable (preliminary) terms. Now, it’s time to go to SEM Rush, which will show you the same report we looked at for Nike; for your competitor’s sites. Again, this is just an approximation, none of the numbers given should be taken as absolute. Write down the top ten traffic driving terms for each of those websites. Repeat the process with Adwords that you used for the initial set. Throw that “clean”- meaning scrubbed of irrelevant and competitor’s branded terms- into a second tab in your spreadsheet.

At this point you have a pretty good list, maybe a thousand keywords for this imaginary site. It’s time again to cull the list; picking a cutoff point and simply dropping everything below a certain predetermined search volume, just to keep the list manageable. If you have time to go through the process I am about to lay out for all 1000 keywords, you either are in-house SEO, work from home for one client, or have a very, very understanding boss I like to go into this last step with about 200 keywords to evaluate.

Back to Excel. Start a new tab. Copy the contents of the other two tabs. Sort alphabetically, and remove all the duplicates. I also negative out misspellings, because I don’t like playing the “optimize for misspellings” game. Then, we’re going to scrub this data a little. You can eliminate the “global monthly searches” column, unless you have a client who services customers or ships products world wide. Then, add two column headers, bringing you to a total of three: “Optimized Competing Pages” and “Competitive Ratio”:

Keyword Research Spreadsheet



My next step is to go back to Google, and for each of these terms, do an intitle operator search. The goal here is to figure out how many pages on the Internet exist with the exact match phrase in their title, and the assumption I make is that if the page has the phrase in the title, it’s been to an extent optimized. So, for “disc golf” I go to Google and enter intitle:”disc golf” and then perform the search. Then, I go up the address bar and append &pws=0 to whatever URL the search result page generated, and hit Search again, just to make sure that there’s no personalization affecting anything. In this specific case, there are 2,530,000 pages optimized for that term (whoah), so that goes in column C. At the end of this process (Again, just for the initial set of 10 out-of-the-air words), your spreadsheet looks something like this:



Then, you go through and you divide the number of pages by the number of searches. This will give you an idea of what’s not being served to the User when they search- the higher the ratio, the better. If you can find a few keyword phrases with a high search volume and a low number of competing optimized pages, the keyword research has been worth it and you’ve earned yourself some Thai for lunch.

After you go through and do this, your spreadsheet will look something like this:



Bear in mind, you should really do this full process for all of those 200 basic keywords. Put your headphones on and just crank it out, you can do a hundred an hour; no problem. I like Count Basie. At the end, sort by “Competitive Ratio” and try to pare the list down to about 50 words. If any words strike you as particularly relevant but don’t look good based on raw numbers, feel free to leave them in; I’m just trying to keep this manageable. Your gut is smart and you should listen to it. Incidentally, I’d expect this overall process to reasonably take 4-8 hours, depending on the vertical and the size of the site.

Now, you have a list of about 50 keywords, their search volumes, the number of competitors, and the “Competitive Ratio”. Take those 50 or so terms and give them their own tab on the spreadsheet. Now it’s time to do one more piece of research. First, we need to add three more columns to the spreadsheet- “#1 Result”, “Inbound Links” and “Unique Inbound Domains”. Now, go to to Google and search for each of the 50 words, and copy/paste the URL that comes up into the newest column on your spreadsheet, again, cutting out stuff like Wikipedia or Nike. After THAT, head on over to Open Site Explorer and run that # 1 result for each keyword through the tool, transposing the proper number of inbound domains and inbound links for each keyword. At the end of that process your spreadsheet will look something like this (I made these numbers up for the sake of this post; none of the sites that would be competing with our virtual client have authoritative enough sites to be in the OSE index):



Now, we have a nice, neat, one page sheet at the beginning of a spreadsheet with tabs showing what we did every step of the way. We have a snapshot of information our client can use to make their choice about what keywords they want us to market- or a solid research-based foundation for what we might choose to work on. I sometimes like to take the extra step of color coding the top ten green, the middle 30, and the last 10 red after sorting by “Competitive Ratio”. I then recommend the client choose 5 of the red, 10 of the black, and 5 of the green. Everyone loves color coding, right? It’s nice and easy to understand.

There will be people who will email me saying I shouldn’t have given away this much of my process but as I stressed throughout, this is just one way of doing it. There are wholly different methods for say; a blog, or an e-commerce site. You have to develop your own process. Don’t take what you read as gospel truth; use your brain! It’s not difficult to come up with or follow a mechanical process; or even write software that would do exactly what I just described.

Without answers to “okay, now what do we do with this data”- you’re not realizing your full potential. That’s where you get to be creative, that’s where you connect with the client and that’s how you succeed. Research. Document. Implement. Document. Test. Document. Repeat. Document

boar guy fucked around with this message at 17:12 on Mar 11, 2016

boar guy fucked around with this message at 17:20 on Mar 11, 2016

boar guy fucked around with this message at 06:42 on Mar 14, 2016

<div class="local-page-heading page-navbar" itemscope itemtype="http://schema.org/LocalBusiness">
    <div class="container roto-container">
        <div class="row">
            <div class="col-lg-11 col-md-12 col-sm-11 col-xs-12 center-block roto-container-inner">
                <div class="navbar-wrapper">
                    <div class="gutterless-col col-lg-6 col-md-6 col-sm-7 col-xs-12"
                         itemprop="aggregateRating" itemscope itemtype="http://schema.org/AggregateRating">

                        <meta itemprop="ratingValue" content="5"/>
                        <meta itemprop="ratingCount" content="50"/>

<script type="application/ld+json">
{
  "@context": "http://schema.org",
  "@type": "LocalBusiness",
"aggregateRating":{"@type":"AggregateRating","ratingValue":"5","ratingCount":"50"}
}
</script>

Brut fucked around with this message at 23:30 on Mar 14, 2016

boar guy fucked around with this message at 23:21 on Mar 14, 2016