How to create a great keyword research and analysis for SEO

These is one of the reasons why many customers turn to MADE It Digital. Our services include, among other things, a technical website audit, but in order to help the customer to set up a comprehensive SEO strategy and website optimization, one of the basic things that should be done is a great keyword research and analysis.

It will help us, as well as the client, to get a glimpse into the world of his business, as well as reveal how his offer is perceived by customers, how they think when they are looking for his products and services. At the same time, find out where the biggest content weaknesses of the website are, and ultimately bring the customer more of his desired conversions.

• What is keyword research?
• What is it used for?
• What is a keyword?
• High-quality keyword analysis
• The most common mistakes
• The process
• Collection of ideas
• Data collection
• Data cleaning
• Clusterization
• Categorization
• Creating output

What is keyword research and analysis?

Keyword research and analysis is one of the pillars of an effective content strategy, which can be used mainly by SEO and PPC specialists, but also by web developers who can use it to design a better structure of a website.

Keyword analysis is the process of finding and analyzing search terms that people enter into search engines to find specific information, services or products. Keyword analysis can reveal the queries to target, the popularity of those queries, the difficulty of positioning for those queries and much more.

A great analysis should not be just a list of search queries, they should be categorized according to a logical key that will make a better overview of the business. In our process, we try to analyze the user's thinking during the search. In addition to the product, is the user interested in the brand, seller, location, color, or other (often specific to the given segment) attribute of the product or service? A classification analysis of keywords often gives us a very nice answer to these questions.

According to an Ahrefs study, many website owners had never donne even a basic search term analysis, and this is one of the reasons why 90.63% of websites on the Internet get no traffic from Google search engine. (Source: https://ahrefs.com/blog/keyword-research/)

What exactly will you use the analysis for?

High-quality content on a website is a key factor for search engine algorithms, the old saying goes that content is king.

Although it might seem that you only use keyword analysis for new content ideas, this is not the case. It is also often used in various other activities:

• Optimization of current content
• Creation / modification of the website architecture
• Identification and setting of filters
• Basics for creating a content strategy
• Queries for creating content for individual landing pages
• Creation of new landing pages
• Information if proper landing pages are created
• Queries for managing PPC campaigns
• A complete audit of the current state of SEO
• Identifying additional SEO opportunities
• Names and descriptions for products and services
• Optimization of meta tags (Title and description)
• Optimization of H tags
• Analysis of a competition
• Analysis of a locality
• Uncovered search terms
• Irrelevant pages for search terms
• Increasing  website traffic
• Internal linking structure

What is a keyword?

A keyword (or a focus keyword)  is a one-word or multi-word phrase that an Internet user enters into a search engine in order to find important information, a product or a service. So it is the way to your potential customers from search enngines.

Keywords can be short (short-tail) consisting of a maximum of three words, which are more competitive, or longer (long-tail) consisting of four or more words.

What does keyword analysis look like?

From the experience of our clients, they often say that they receive analysis from other companies, which is only a list of keywords, monthly volume, seasonality and price per click for PPC campaigns, it is unclear and without explanations or without SEO strategy.

Keyword analysis ≠ copy-pasted data from research tools

 

 

A high-quality analysis consists of several steps, and the time it takes to create it depends on the volume of relevant data for the client's website. We can say that for smaller websites and online stores the average time to create an analysis is 30-40 hours, for larger projects with thousands of products it can be 80-100+ hours.

The most common errors in analysis

If you have decided to create a keyword research and analysis yourself and you do not have enough experience, avoid the following mistakes:

• Too few relevant search terms
• Inflating the dataset with zero search phrases
• Bad or no categorization
• Absence of data in clear charts
• Incorrect or no interpretation of the collected data for the client
• Graphical visualization of useless values
• Absence of all datasets (also deleted irrelevant data for the needs of PPC campaigns)
• Absence of landing pages for individual terms
• Uncleaned dataset that contains typos, irrelevant data, etc,

Keyword analysis step-by-step

As we have already mentioned, the analysis consists of several steps:

1. Collection of suggestions for keywords
2. Data collection
3. Data cleaning
4. Keyword clusterization
5. Categorization / classification
6. Creating output for the client

Collection of suggestions for keywords

If your website has been up and running for a while, it probably appears for certain keywords in the search engine. It is ideal to have Google Search Console (GSC) and Google Analytics (GA) implemented on your website, from which you can find suggestions that are relevant to your business.

However, if you don't have GSC or GA on your site, or you see few suggestions, you can use Google's Search Suggest.

When determining the basic keywords, it is also necessary to think about the fact that your website may not contain the right words, so it is appropriate to do a quick analysis of what the competition is displaying for. It is also advisable to use your own ideas and check them in the search engine - whether they are searched for in a way you know them, or if you need to use a different way / synonym that the search engine offers you.

Keyword research tools:

• Google Search Console
• Google Analytics
• Google search engine (Search suggest and related terms)
• Seznam search engine (Search suggest and related terms for Czech republic)
• Keyword planner in Google Ads
• Ahrefs
• Keywordtool.io
• Answerthepublic.com (most frequent questions asked on the internet)

Data collection

The list of keywords obtained from the previous tools will provide you with the basic terms that your customers or users are searching for. However, there are certainly many more, especially long-tail keywords, that may not have been shown to you.

That is why SEO specialists in the Czech Republic and Slovakia use 2 popular tools not only for keyword research - Marketing Miner and Collabim, in which you can use their functions for keyword suggestions (+ related terms in Marketing Miner).

If you lack data from GA or GSC or a specific service or product you are offering, we recommend to "play around" with the Search suggest and related terms in Google search engine, where you can enter any terms related to what you offer (although the algorithms of Google and other tools  year by year, they don't always offer all suggestions for keywords).

Related terms on Google

Marketing miner

After registration, click on "Create report" in the upper right part.

In a next step, you can select all the options that gives you even more relevant keywords that you might not have thought of, i.e.:

• Suggest
• Keyword Suggestions
• Related Search

In the next step, enter all the keywords you have prepared to the field, click on "Import data" and "Analyze data":

The report will be processed in a few seconds to minutes, depending on the volume of your data.

Marketing miner will create a report for all three options you selected. Just click on "Export" -> "Merge and download Excel”.

The downloaded Excel file will offer you data in 3 different tabs - we will merge all keywords together.

 

Collabim

In the project, click on "Keywords" -> "Add new keywords"

Select "Keyword suggestion" (if you have a website in the Czech Republic, you choose between Seznam or Google, in our case for Slovakia we can only see Google), we enter the keyword and click on "Get keyword suggestions".

We will apply this procedure to all the keywords we have prepared.

In the table that appears, we focus the column "Google search average, free match". We will copy the entire table and delete the column "Google search last month, loose match" so that we have the relevant average data for 12 months.

Data cleaning

Whether you have chosenn Marketing Miner or Collabim, these keywords need to be cleaned of duplicates and suggestions that are not relevant to your business. You can do so in Google Excel, Google Sheets or in the Open refine tool.

Google Excel

Removing duplicates is very simple, just mark both columns (the column with the search term and the volume) and click on "Data" -> "Remove duplicates".

Cleaning data in Excel is tedious, especially if we have a relatively large dataset, for which we recommend using Open Refine tool. You can either delete the data manually row by row or create a filter over the data, search for irrelevant terms and delete these rows.

Google Sheets

Removing duplicates is similar to Excel: you mark both columns and click "Data" -> "Data cleanup" -> "Remove duplicates".

Data cleaning is similar to Google Excel.

Open Refine

Removing duplicates and irrelevant terms in Open Refine is more difficult, we must first create a new project and insert data into the project. You can copy-paste the data manually, or you have several options to choose from. Next, we will name the project for easier orientation in projects and click on "Create project".

First, we must sort the terms alphabetically so that the same expressions are below each other. You can do this by clicking on the arrow in the expression column -> "Sort..." -> "a-z" (it's automatically set, you don't need to select anything at all). We have to confirm this sorting permanently: "Sort" -> "Reorder rows permanently".

You almost certainly have many duplicate expressions below each other now. In the expression column, click the arrow -> "Edit cells" -> "Blank Down". This will leave you with only the first expression from the duplicates, the others will become empty lines that we need to remove: "Facet" -> "Customized Facets" -> "Word Facet".

On the left side of Open Refine we will find the "Facet / Filter" option, where we have to find the expression (blank). We click on it -> "All" -> "Edit rows" -> "Remove all matching rows". 

Cleaning the dataset in Open refine works similarly to duplicate removal, i.e. we click in the column with expressions on the arrow -> "Facet" -> "Customized Facets" -> "Word Facet" (if we didn't delete duplicates or we closed the "Facet / Filter" field), we will find in the list of words the ones we don't want (we can use select multiple of them by the Include option on the right side of the expression) and then click again on "All" -> "Edit rows" -> "Remove all matching rows". And we are done!

PS: it is a good idea to save the removed irrelevant terms, you will use them mainly as exclusion list of terms in PPC campaigns.

After removing these terms, we go through the keyword search tools again to get the average monthly searches for these relevant terms.

Clusterization / combining similar terms

If we did not get rid of duplicates in Open refine, but in another way, open "Open refine" -> "Create project" -> your prefered data import method, in our case Clipboard (Copy-Paste).

Then click on "Create project".

 

There will be many similar terms in the dataset that will show the same results in the search engine, leaving them all is misleading. They differ only in diacritics (if your language uses one) and word order, but it is the same expression, so they should be combined into one, e.g. men's black t-shirt, black men's t-shirt, t-shirt black men's and so on.

In the column with expressions, click on "Edit Cells" -> "Cluster and edit". A new window will appear with all expressions that can be combined. It is possible to do this one-by-one, where you can choose the term that remains (for example, according to the search volume), or you can mark all of them at once by "Select All" and approve "Merge Selected & Re-Clucter" at the bottom.

However, clusterization created new duplicates, which need to be removed, but we have to merge the search volumes of these terms together:

• in the term column, click on the arrow -> "Sort..." -> "a-z"
• "Sort" -> "Reorder rows permanently"
• arrow -> "Edit cells" -> "Blank Down"

This reordered the terms for us, but we need to merge the volumes together:

• in the volume column, click on the arrow -> "Edit cells" -> "Join multi-valued cells"
• in the volume column, click on the arrow -> "Edit cells" -> "Transform..."
• enter this formula in the newly opened window: "forEach(value.split(','),v,v.toNumber()).sum()"
• confirm by "OK"

Categorization / classification of keywords

(We at MADE It Digital do categorization in Open Refine)

This part of the analysis is the longest, but it is the most important. Categorization is actually a logical dividing of terms into groups according to a certain key. You can find these groups  by going through the dataset.

For example, the terms "Men's winter jacket North Face black XL in New York" and "Women's summer clothes Trendyol S in LA", the groups are: Product/type of clothing, color, size, gender, feature, brand and location.

It is a good idea to add "Modifier" (sales terms such as sale, discount, for sale, etc.) and "Other" to the groups, where you can add everything term  you are not sure about or there are only few terms and it is useless to create XYZ groups for all of them .

Once we have the groups thought out, we need to add them to Open Refine, each in a separate column:

• in the column with terms, click on the arrow -> "Edit column" -> "Add column based on this column"
• in the newly opened window, write the name of the group in "New column name" and enter the word "blank" in "Expression"
• confirm "OK"

We will do this for all of the groups of terms.

Grouping the keywords

To divide words into groups, we need to open 2 different types of windows on the left in Open Refine (the first is the term itself, the other are groups):

• first we click in the column with terms on the arrow -> "Facet" -> "Customized facets" -> "Word facet"
• for each column with a group, we proceed in the similar way: click on the arrow -> "Facet" -> "Text facet"

Next, the distribution of keywords takes place::

• in the first window, click on the term we want to include (for several terms at the same time, click on "Include" on the right of the term)
• find the appropriate group to which we want to put the term in
• if the term is not included in any group, it is displayed as "(blank)"
• at "(blank)", click on "edit" and enter the name of the group
• if we have several terms clicked at once, we can reset them with "Reset All", otherwise just click on the next term in the list

We will continue in this way until all terms are divided into their categories. Then export the categorization to our preferred format, for example Excel.

Creating the output for a client

For most professionals, such as SEO or PPC specialists, this look of a categorization would be sufficient, where they can filter data through filters in Google Excel or Google sheets, but for clients it is advisable to create an output with visualizations and a summary of the analysis.

Graphs, especially Bar and Pie graphs, are among the clearest visualization elements.

You can create outputs in Excel, Google Sheet or Data Studio, where you can further work with the data or edit it.