How To Clean Up Keyword Research Data in a CSV File

Your business, services and products or those of your clients need to get noticed on the web: and keywords are the way that happens. Whether it be the exact terms people use when searching for something in Google, the hashtag trends they look out for in social media, or the subject words they use to find an ebook in the Kindle store, it’s the keywords you use that help your potential customers find you.

Nowadays, keyword research is a part of a data-driven approach to your content marketing efforts. Only several years ago, keywords were stuffed into whatever webpage was being created as part of an internet real estate scramble. Now, fortunately, the landscape has narrowed. There are, however, strategic keyword tools that work well to bring more eyeballs your way when coupled with a foundation of great content.

Let’s look at how to plan your keyword research and format it into a CSV file you can put to use.

Why Conduct Keyword Research?

Keyword research is an essential business planning activity for anyone that wants to be successful online.

Strategically chosen keywords can help you get found by a greater portion of the audience who are already looking for what you have to offer. You can gain useful insights into understanding how your market audience buys products and services online by regularly carrying out keyword research. For example, when buyers are first considering a topic, they tend to choose a general keyword, like content marketing for example. As these researchers become more ready to buy the products or services they need, the keywords they use in their research become more specific: local business internet marketing or email newsletter services.

As trends evolve, seasonal events spring up, new products are released, and fresh ideas enter the zeitgeist, keywords change. In fact, keywords are always changing.

As trends evolve, seasonal events spring up, new products are released, and fresh ideas enter the zeitgeist, keywords change. In fact, keywords are always changing. Creating content that matches what people are looking for, or are hashtagging about, helps you get noticed by a bigger audience all the time.

Keywords are also essential to any online advertising strategy. How widely your ads are seen alongside search results, or how often they pop up on relevant websites, is related to the matching keyword relevance between your ads and the keywords on your site’s specific content pages.

This tutorial explains how to format and use the data results from your keyword research in your content planning. You can clean up the data from your keyword research and import the results into a spreadsheet. You can re-order and group your keywords so that you are regularly creating new content using strategic keywords that will constantly bring you a new stream of customers.

You can take action to make the most of new keyword search terms, and grow into an authority site in your industry sector, all by building a content strategy based on solid keyword research.

Tutorial Assets

To complete this tutorial, you will need:

• A Google account.
• An internet connection.
• Download this sample CSV file based on keyword research for content marketing (or you can follow along with your own CSV file of keyword research).

Keyword Basics: Using Keywords on a Website

Your main goal should be to have a set of 5-10 keywords that you use across your site. These terms should be used on your home page, and sprinkled across your other static pages, including:

• Your About page (usually the most read page of your website).
• Pages that describe your services or products.
• Pages with examples from your portfolio.
• Your contact information page.

Using a small set of keywords consistently across your website is how you are telling the search engines My site is definitely about this topic.

Then there is a secondary set of category keywords, perhaps another 10 -20 keywords that can describe sub-sectors of your industry. For example, if you provide business marketing services, you may have a section on video marketing, or public speaking services, or speech writing, or email newsletter services.

Consistently producing fresh content based on these longtail, less searched keywords helps position you to rank well for these terms.

Finally, there are the longtail keywords. Longtail keywords are search phrases that may be three or four words long. They may also mention a specific product. These are the keywords you would use on specific sales pages and in each of your blog posts. For this content, it is best if each new piece of content is focused on only one or two keyword terms.

This tutorial focuses on how to identify these longtail keywords and how to use them in planning out a content strategy. Consistently producing fresh content based on these longtail, less searched keywords helps position you to rank well for these terms. There is also a greater chance that these searchers are more interested in what you have to say, as they are looking for the specific information that you offer.

Once you rise to the top of the search engine results for several longtail keywords in your industry subsector, you are sending more signals to search engines that your website is definitely about your business topic. So, in turn, you will start ranking higher for the more competitive category and site-wide keywords that you have chosen.

As long as you are writing original, interesting content that your readers will appreciate, you do not have to worry too much about how often you use keywords in your content, but as a rough guide, for each blog post:

• Use the keyword in the title.
• Use the keyword within the first 20 words of the blog post and in the last paragraph.
• If you add any images, include the keyword as part of the alt-title description of the image.
• Use the keyword in the meta description for the blog post.
• If you have sub-headings in your blog post, use the keyword in at least one of your subheadings, if this feels natural.

You dont need to go overboard worrying about how well you use keywords in a blog post, it will get easier as you produce more content. The most important thing is to be regularly producing content in your industry sub-sector that is related to what people are wanting to find out (i.e. using the longtail keywords they are searching for).

1. Create a Simple Content Plan

Step 1. Create a Spreadsheet

Open Google Drive. You will need to enter your Google account details (this is usually your Gmail address and password).

Click on the red Create button in the left hand menu and select Spreadsheet from the drop-down menu.

Step 2. Add Worksheets

Click on the Plus symbol in the bottom left of your new spreadsheet and add an additional worksheet to your spreadsheet file.

Step 3. Add Content Plan Headings Row

Flip back to Sheet 1, and add the following columns to the first row:

• production date
• type of content
• keyword to be used
• publication date
• URL

Select View > Freeze rows > Freeze row 1.

Click on the title area for Untitled Spreadsheet and rename this file as Content Plan.

Step 4. Import Your Keyword Research

Click back to the Sheet 2 tab of your spreadsheet and select File > Import from the spreadsheet menu.

Click the Choose file button. Now import your keyword research file. If you are using our example, import our example CSV file now. Or use your own CSV file, if you compiled your own keyword research with Google AdWords keyword tool.

Choose the Replace current sheet option and allow Google Drive to identify the separator: make sure Automatic is selected. Now click the Import button.

2. Clean Up Your Keyword Research

In our CSV file example, we have imported 800 keywords related to the term “content marketing”. The data includes global search volumes and the number of monthly searches conducted in the US (“local searches”). Competition is scored between 0 and 1, with 0 = no competition and 1 as highly competitive. Data is also included for cost-per-click data, which is related to online advertising costs. When you download a CSV file from Google AdWords, it will include these fields, unless you turn them off.

Step 1. Remove Brackets

The best keyword research is only interested in exact matches. These are the terms people actually use when searching for the type of content that you may be able to provide. Google AdWords provides a number of options, including broad terms that gives an inflated picture of how many people are searching for related words. For developing content, this isn’t very useful.

In Google AdWords research tool, exact matches are displayed with square brackets around the terms like this and this: [content marketing]. You can turn on exact matches in the left column of Google AdWords research tool.

When you import a CSV file with exact matches into Google drive, the brackets will be included. Now that you have imported your data, you can remove the brackets from each row of your CSV file.

Select Edit > Find and Replace. In the Find field, type the opening square bracket ([ ). Leave the Replace field blank. Select Replace all. Now repeat this process with the closing square bracket (the ] symbol).

Step 2. Sort by Frequency of Competition, Difficulty and Volume

Select the entire spreadsheet by clicking in the grey box above row 1 and to the left of column A. The spreadsheet will now be shaded in light blue to show it is all selected.

Now, select Data > Sort range.. from the top menu.

Make sure Data has header row is checked. Sort by Competition and select A Z. Click on +Add another sort column and choose then by Local Monthly searches and select Z A. Now click the Sort button.

This will order your keyword research by the least competitive keywords. The competition score is based on online advertising data, but is indicative of all websites. In this instance, we have re-ordered your keywords starting with those that don’t have as many other online pages vying to be recognized for this keyword term.

As the list goes down, the keywords are used more aggressively by potential competitors and other websites working in the same industry. This helps you identify the “low hanging fruit,” which are the less competitive keywords that allow you to rise to the top of search engines faster for those terms. We have also ranked these keywords in order of how many times people search for them. So your best low hanging fruit are the keywords with less competition and the greatest number of searches.

3. Add Keywords to Your Content Plan

Now we’ll add these keywords to your content plan. This gives you a keyword focus to help attract readers to your website.

Step 1. Check Seasonal Trends of Key Strategic Keywords

Try using Google Trends to identify any newly emerging search terms related to your keywords. For example, we entered “content marketing” into Google Trends and looked at global trends for the past 12 months. Here are the results:

This shows us that there are no seasonal trends per se: “content marketing” is an increasingly popular search term, month after month. You can, however, see some newer keyword search terms that might not have shown up in your Google AdWords research. Look at the list of Rising terms in the bottom right hand corner.

You can also repeat this process for specific keywords from your research. Again, look for rising terms and any seasonal or annual trends. Make a note of the month where search volumes are the highest for these terms.

Step 2. Copy Search Trends into Your Content Plan

Now copy these rising search terms into Sheet 1 of your Content Plan spreadsheet. Allocate production dates based on the month prior to any seasonal increase for keywords affected by annual trends.

This will mean you will be tweeting and promoting your keyword-specific content in the month leading up to any annual increases in search volumes (see final section below on Share Your Keyword-focused Content).

Here’s how to fill it in:

• Production Date: This is a rough date that you plan to start preparing this content, you may set yourself weekly goals, for example.
• Type of Content: You can set a few standard types of content so you are making sure you use a range of content approaches to appeal to readers. This could include: blog posts, infographics, slideshows, reviews, news items, etc.
• Keyword to be used: This is the space for you to add the keyword the content should focus on.
• Publication Date: When you publish your content, you can enter the day it became ‘live’ on the web, along with.
• URL: The full URL for the published content, this will make it easy to locate it later.

Step 3. Copy Your Keyword Research – One Block at a Time

Now you can start moving your keyword research into your content plan. Flip back to Sheet 2 of your spreadsheet.

Scan down the rows from the left hand side. Click and hold the rows for keywords you don’t plan to use. When several are selected, choose Edit > Delete Rows to remove these from your spreadsheet.

Once you have deleted keywords you will not be targeting, you can select the keyword column and copy (Cmd + C) the highlighted keywords. Flip back to Sheet 1 and paste (Cmd + V) the keywords into the column titled “KW to be used.”

Step 4. Format Your Data Columns

You can set your data columns to make it easier to enter data. Select column A. Now click on the 123 button from the icon menu options and choose a date format for your entries.

Now select Column B. We want to create a dropdown list of types of content to choose from. When the column is selected, choose the little downward arrow in the right-hand corner of the column B header and choose Data validation… from the dropdown menu.

In the data validation dialog box, choose the range of cells for which data should be validated. For example, start with B2 as B1 is the header row, and choose an end number like B100 for now. Choose List of items as the Criteria and enter the types of content you will be creating, for example, we listed “blog post, slideshows, infographic, Q&A interview, news item”. Choose whether or not all your data must fit this criteria, and whether the dropdown list should be displayed in the cell. Now click on the Save button.

You can also set the Publication Date column to a date format, as with the Production Date column.

Share Your Keyword-Focused Content

Step 1. Publish Regularly

Publishing keyword-optimized content regularly is the best way to increase the likelihood that your website (or your clients) will get noticed in search results for your chosen keyword terms.

Consider adding location or industry niche terms to your keywords if you are providing services to a specific area or industry sector. Your content plan will help you stay focused on producing content every week.

Step 2. Tweet and Hashtag

When you release a new item of content – a blog post, for example – tweet about it and provide a link to the content. Make sure your tweet includes the keyword you are targeting in your content, and use a hashtag if appropriate. Repeat this for LinkedIn, Facebook, Instagram and Pinterest, or any specific social media platforms relevant to your audience.

Step 3. Create Google Alerts for Strategic Keywords

Another way to get more readers is to follow when other people use these same keywords in their content. You can set up Google or Talkwalker Alerts to monitor who else is using these keywords. When you see other content on the web, particularly blogs with comment sections or forums discussions, you can contribute your opinion and, if it feels natural and is adding value to the discussion, you can link to your keyword-specific content.

Step 4. Create Tweet Searches for Strategic Keywords

As with Google Alerts, you can also set up a search for the keyword term in Twitter to keep track of who is tweeting content about your favorite keywords. Like with blog comment pages, you can then add to these conversations and link back to your website content, where appropriate. You will also get ideas about new longtail keywords or be able to find interesting new trends about your keywords to further your position as an authority on the subject.

Step 5. Analyze Your Progress

It is worth reviewing how well you are positioned for your keywords on a regular basis. Every now and again, search your keywords to see where your content is positioned in search results. How does this compare to your competitors?

Try and get into the habit of re-entering your most strategic keywords into Google Trends and have a look at the rising searches to see if you should prioritize creating content matched to any new search term relevant to your industry.

Follow a Methodical Content Planning System

With this keyword research and content planning system, you can now methodically target any industry niche and build an authority site based on how your audience is actually looking for industry-relevant content.

Aim to repeat the keyword research at least every six months and in between, use Google Alerts, Twitter hashtag searches and Google Trends to see what new keyword terms are emerging so you can beat your competitors to developing timely content for these terms.

With a solid keyword research system in place, you will find it easier to create opportunities based on emerging trends.