How do you keep track of all of those keywords? How can you possibly manage to sort through a thousand keywords to find the best one? How can you turn all of this raw data into an SEO strategy that actually works?
While there certainly are some guides out there that insist that the best keyword research tool for digesting all this information is some expensive piece of software (or some secret proprietary method), the reality is that all you need is a simple keyword research spreadsheet.
While there certainly are websites out there that will “pick” the best keywords for you, the best content strategies spring from keyword research that is a little more hands-on — meaning that it’s up to you to comb through the data to find what keywords work best for your business.
Here’s the step-by-step guide on how to use it and below is our keyword planner you can use to do your research.
1. Do your keyword research.
Before you can can write a blog post that converts, before you decide on a content strategy, and — certainly — before you start writing, the first thing you need to do is research. After all, you can’t break data down if you don’t have it! Make sure you’re thorough with your research, too. The more competitors you can research, and the more keyword categories you can investigate using AdWords and Google’s keyword tool (or whatever your research tool of choice is), the better off you’ll be.
2. Export the data.
Most SEO tools will allow you to export the raw data into a .CSV file. If you’re planning on using the keyword spreadsheet template, having the raw data is essential to being able to break it down into something useful.
If you are using Google AdWords to find keywords, click the ‘Download’ button to export the data:
If you are using SEMrush to find keywords, the ‘Export’ button to download the data.
3. Put it in the keyword spreadsheet.
Regardless of what site generated your keyword data, it will always be organized by the same variables: CPC (cost per click), search volume, position, and competition. You should easily be able to highlight the individual columns and copy/paste them into their respective columns in the keyword planner spreadsheet.
If you’re using the spreadsheet, then make sure to pay attention to what tab you’re pasting the data into. Try to keep your data organized — separate your competitors’ keywords into individual sheets, and make sure to keep any keywords you’ve found independently (in other words, keywords your competitors aren’t ranking for) in their own sheet. By splitting up this information, you’ll easily be able to compare specific keywords (and make sense of what strategy your competitors are using).
4. Analyze the data.
It’s important to note here that this process will be slightly different for every person or business. Likewise, the amount of “analysis” you will have to do will vary dramatically depending on the size of your enterprise. If you’re a brand new startup with no real web presence entering into a market that doesn’t really exist yet, you probably aren’t going to be playing with a ton of keywords. Similarly, if you are a global cloud hosting company, you’re probably going to spend several weeks combing through the data.
If you’ve already stuck the data into the keyword planner, you’ll probably notice that it automatically color-codes the level of competition each keyword has. High competition keywords are in red, medium ones are in yellow, and low competition keywords are in green.
The competitiveness of a keyword is highlighted in the spreadsheet for a pretty good reason: generally, high-competitive keywords are the ones most important to your competition (and they’ll likely be incredibly important for you).
What they can tell you is where you should start looking to find a keyword that might produce results for your business. “Cloud computing” might be a high competition keyword, for example, but a longer-tail keyword, like “cheap hybrid cloud computing” might not be. Something to remember here is that a good content strategy isn’t just about stealing away traffic from your competitors’ websites. While you want to target some of their keywords, you also want to find keywords that no one has touched yet.
Take a look at every column. What are the most valuable keywords by volume? What are the most valuable keywords by CPC? Take a note of which are the most competitive. Do you notice trends? Are there certain root keywords that are present in multiple keywords? Keep in mind that just because you’re starting to analyze the data, that doesn’t mean that you are done playing around with AdWords, SEMrush, Moz, or whatever keyword tool you use. If you see some terms multiple times among your batch of keywords, go see which ones that might not be being targeted (and that add that data into your spreadsheet).
How do you decide what a good keyword is and what a bad keyword is for you? The short version is that you have to decide some cutoffs for yourself. For example, do you have a large enough presence to target high competition or high volume keywords? Are you aiming to generate organic traffic for a blog, or are you trying to sell a very specific product or service? All of these questions should inform what keywords you target.
5. Create a content strategy.
Keywords are useful — but only if they are put to use in a solid, thought-out content strategy. You can’t toss keywords randomly into blog posts — you have to think up ideas that creatively (and accurately) use the keywords that you’ve researched.
You want people to find your website because it has information that is useful to them — not because you’re just bombarding them with keywords.
A good method here is to find 50 keywords that work for your business, then try to build 10 pitches based on those keywords (and remember you need to use the keywords in the title!) For example, if you find the keyword “creative marketing team,” you could write a pitch for a post titled, “Building a Creative Marketing Team: What You Should Know.” Not only does that pitch contain the keyword in the title, but it’ll naturally include the keyword in the post without much effort. Even better, it’d be a good idea to twist that title into something that’s likely to convert by targeting people who are looking to purchase whatever it is you are selling (so, for example, using the title “Hiring a Creative Marketing Team: X Things You Should Know”).
Remember, though — a good content strategy isn’t just built around a month or two of content, nor is it structured around a single round of keyword research. Ideally, you should be continuously looking for new keywords, and you should always be looking to improve your content strategy. After all, if you start ranking for certain keywords, you’d be wasting your time trying to continuously target them (and on the flip side, if you aren’t successfully targeting a certain set of keywords, it might be a good time to rethink how you’re targeting them). You also should be ranking for multiple keywords so you get a good idea of what you can realistically rank for (and what type of keywords your content does best with).
6. Use your content strategy to guide blog posts.
You need to know how to write a blog post that converts. You can have perfect keywords, incredible pitches, crazy-strong titles, and a content strategy that is absolutely killer — but if you don’t actually turn all of that hard work into content, it won’t matter.
It’s not enough to just write a few pieces of solid content, either — the more content you can pump out, the better. Just make sure you aren’t sacrificing quality for the sake of quantity.
Starting in on keyword research can feel like a daunting task. Depending on the size of your business, you might be dealing with anywhere from tens of keywords to thousands of them: and in either case, it’s going to take a lot of analysis to figure out what will work for you and what won’t.
While an SEO keyword spreadsheet template might not seem like the best keyword research tool when you’re just starting out, once you’re swimming in CPCs and SERPs, you’ll be thankful that all of that data is properly organized