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.

Download Our Free SEO Keyword Research Template

Our keyword research template can help you organize your keywords as you find them — and help you turn them into a content and SEO strategies that works for your business. Get yours here:

Here’s the step-by-step guide on how to use it:

1. Do your keyword research.

Before you 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.

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 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:

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 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).

4. 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. 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 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.

"high competition keywords, while worth targeting, are usually not worth focusing on exclusively." - The Content Factory

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. Check out our post on keyword research and analysis for an even deeper dive!

A graphic illustration of people working on tablets and laptops around a giant tablet with the title "blog"

https://www.freepik.com/premium-vector/business-people-around-huge-tablet-with-blog-page_5562920.htm#query=blog&position=8&from_view=search&track=sph

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.

"Find 50 keywords that work for your business, then try to build 10 pitches based on those." - The Content Factory

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).

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. 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 — that's why we're affiliate partners with them! 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. Want to give it a try? Click here to land a free 7-day trial of SEMrush!

Want More Info? Sign Up for Online SEO Training!

What? You can learn the arcane secrets of SEO wizardry in the comfort of your home or office? That's right — we're happy to show you our secrets! We offer digital training in SEO and content writing for marketers and entrepreneurs who want to drive more organic traffic to their site and win more business. Get your feet wet with our free webinar on fixing 9 expensive SEO mistakes, then sign up for our comprehensive SEO course!

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.

Are you a lady in SEO? We've got a killer Facebook group you'll want to check out ASAP! You've got questions, we've got answers – with a lot of actionable advice and support from other women slaying it in the industry.

By Guest Contributor

content strategy, keyword research, keyword research tool, SEO

  1. Thanks for effective SEO strategy! 

    Moz and Semrush is very helpful tools for seo, but additionally i use Serpstat. This is such a powerful tool for analyzing competitors or keywords research. Would you, please, check it? Thanks!

    1. Hey,

      Could you recommend me a tool that has real updates on the regular basis? Lots of services just CLAIM to have that, but if you check yourself, many of them (SEMrush, for example) received last update of their bases in Dec, 2015! I KNOW, RIGHT!

      I've used Serstat for some time. So far so good. But I quess they should put more effort as their share on the market is raletively low.

  2.  Fantastic article. Adwords is definitely an unmatched discovery platform for keywords. I typically start in much the same way you have but then import the list into Moz.com's (paid but free trial available) keyword diffuculty checker. This gives a ranking on difficulty and allows me to narrow down the list to the easy hits. I then use answerthepublic.com or keywordtool.io to find the long tail keywords from the list and again process them through moz. Once I have my hit list down I run full reports and begin the process of rebuilding better versions of the top ranked page. I run a lead generation consulting agency and have seen this strategy executed successfully by several clients with little to no SEO experience! It really works to rank and generate traffic and leads!

  3. Hello,

    Long tail keywords, like you correctly opined, drive more targetted traffic and in the long run, converts far better.

    However, long tail keywords (and their numerous variations) also help bloggers and information marketers avoid Google penalties for keyword stuffing as an article that properly follows this standard appears natural both to readers and search engines.

    …while being also, very highly optimized!

    Be certain to make the day great!

    Always,
    Taposh

  4. Nice article Kari! Do you think that keyword research will change in future, as SEO tools start evolving? 

  5. Hey,

    Could you recommend me a tool that has real updates on the regular basis? Lots of services just CLAIM to have that, but if you check yourself, many of them (SEMrush, for example) received last update of their bases in Dec, 2015! I KNOW, RIGHT!

    I've used Serstat for some time. So far so good. But I quess they should put more effort as their share on the market is raletively low.

  6. Great post!
    For those of us whose bread and butter mainly consists of keyword research, tools described in this article are indispensable. SEMRush, SERPstat, Moz are my favorite for keywords research and competitors research.

  7. Kari,
    Great post. Another tool that has pretty fresh updates for your keyword research data is Ahrefs. Similar to SEMRush, but has other metrics as well and has a site specific keyword difficulty feature in Beta now that can help you gauge which keywords to go after first, for the biggest effect.

  8. Great article! I’ve just started using Moz and I think its great. I’m still having a problem getting my better ranking keywords to fit into sentences (that make sense) but I think that will improve with practice! Also think you made a great point about using long tail keywords.SEMRush seems a little pricey for me at the minute but thanks again for the article.

Comments are closed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}