Clustering search queries

Lists of keywords may become very huge. Thousands of words.

Example: ods icon

We have to:

  • Remove duplicates.
  • Delete phrases with low frequency.
  • Exclude junk keywords.
  • Cluster the keywords.

We will use LibreOffice Calc.

Removing duplicates

  1. Select column A.
  2. Date / More filters / Standard Filter
  3. Condition A = Not EmptyOptions / No duplicates

    Options / Copy results to B1

  4. Delete column A
  5. Go go A1 (Ctrl + Home)

Deletion of phrases with low frequency

A lot of parsed keywords have such low monthly searches that they are not worth paying attention to.

Usually the frequency for each phrase and each region is obtained via special services (paid ones).

But for our purposess Keyword Planner by Google will suffice.

What a low frequency is? It depends on the project. It is more an art or intuition than a science. One just has to decide for himself: this phrase will not attract traffic to my project.

Remove single words

Formula for column B:

=IF(LEN(A1)=0,0,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)

It calculates the number of words. For empty strings it shows 0. Otherwise it counts the number of spaces and adds 1 to calculate the number of words.

  • Sort by column B.(Select columns A and B, Data / Sort Ascending)
  • Remove single words.
  • Remove column B.

Cluster key words

Rough list of keywords is not useful for us at all.
We need something like this: ods icon

There are some means of automatization of this task. But the best quality of clustering is achieved manually.

General idea of manual clustering in spreadsheets is like this:

  • Select all cells with the most general word.
  • Move the selected cells to another sheet.
  • Repeat the algorythm as many times as necessary.

One has to work pretty quickly otherwise he will just get stuck.
How you cluster your words is a matter of personal preference.
One possible way in LibreOffice:

  1. In B1 use a forumla to check if the word is in A1
    =ISNUMBER(SEARCH("nail", A1))
  2. Copy the formula to all the cells of column B
  3. Sort by column B
  4. Move necessary cells from A.
  5. Repeat the algorythm

Another possible way:

  1. Ctrl + H
  2. Find: nail
  3. Click on the sheet
  4. Ctrl + C
  5. Del
  6. Click on the destination sheet
  7. Ctrl + V
  8. Click on the source sheet
  9. Repeat the algorythm



Rate article
Add a comment