Not even a few days after we published our keyword research guide, the questions started to flow in. 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.
Wait, what? The best keyword research tool is just a spreadsheet?
Yes — and we’re not talking about some complicated, algorithm-filled wonder, either. While there certainly are websites out there that will “pick” the best keywords for you, at TCF, we feel 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:
Do your 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 (our keyword research guide might be helpful for you!). 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.
- Export the data. Most SEO tools will allow you to export the raw data into a .CSV file. If you’re planning on using our 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 here to export the data:
If you are using SEMrush to find keywords, click here to export the data:
- Put it in the 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 our SEO keyword spreadsheet template.
If you’re using our keyword 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).
- Analyze the data. Before I get into the analysis, 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. Likewise, 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 our SEO keyword spreadsheet template, 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 our 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). That doesn’t mean we’re telling you to target them exclusively, though — high competition keywords, while worth targeting, are usually not worth focusing on exclusively.
What they can tell you, though, 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? I suggest checking out our keyword research guide, but 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.
- 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 — or if your content marketing strategy is working for you). 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).
- 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. Want some extra help? Here are some other tools that we find helpful:
BuzzSumo — BuzzSumo allows you to quickly search a domain (or keyword) for relevant social media posts. With BuzzSumo, you can see which titles are doing well — and on what platforms.
Moz — Moz provides a handful of keyword research tools, all of which are useful (and many of which are useful long after you’ve done your first round of keyword research).
SEMrush — SEMrush is our favorite tool for SEO research. With SEMrush, you can see what keywords certain domains are ranking for (or the other way around). It’s fast, easy to use, and it gives you easy access to all the information you need for effective keyword research.
Still struggling trying to find keywords that work for your business? Contact us! The Content Factory can help you with every step — from finding keywords and analyzing competitors, to writing your content and making sure that your customers see it.
Have a question about the spreadsheet — or SEO in general? Tweet us! @ContentFac.