How to Get Most Frequent Keywords from Titles in Google Sheets

There are a lot of functionalities that Google Sheets supports, like scraping data from websites, creating semi-automated SEO workflows, manipulating big data sets, automating follow-ups for outreach campaigns, and much more. In this guide, I will introduce you to some of these in-built functions, which enable you to perform one of the most common SEO tasks – to get the most frequent keywords from a given set of titles.

Before I begin, I want you to understand how tracking keyword counts can help on-page SEO (Search Engine Optimization). When used correctly, it can help attract search engines and boost a page’s visibility in the search results. Ignoring this could keep a page from the ranking itself, thereby leading to it not appearing in the top search results. As a content owner, you don’t want to be in that situation.

Let’s jump right in.

 

 

Commonly Used Functions

There are a few different ways to do this, and they involve a combination of in-built functions. I am listing down the most commonly used functions here, some of which I will be using in the examples that follow.

FUNCTION USE-CASE
ARRAYFORMULA Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays
CONCATENATE Appends strings to one another
COUNTA Returns the a count of the number of values in a dataset
IFERROR Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent
JOIN Concatenates the elements of one or more one-dimensional arrays using a specified delimiter
QUERY Runs a Google Visualization API Query Language query across data
REGEXEXTRACT Extracts matching substrings according to a regular expression
REPT Returns specified text repeated a number of times
SPLIT Divides text around a specified character or string, and puts each fragment into a separate cell in the row
TRANSPOSE Transposes the rows and columns of an array or range of cells

 

To go through a more comprehensive guide on how to use these functions, feel free to go through some of our articles on the same.

 

 

Using the QUERY Function

The QUERY() function may be the most powerful of the functions listed above, and when combined with a few other functions, it allows us to effectively manage and manipulate data using the power of SQL. Let us take a look at exactly how, using an example.

Using the IMPORTFEED() function, I selected 25 of the day’s news titles from Google News in ATOM format (an alternative to the RSS web feed).

Frequent keywords in Google Sheets

 

Look at the formula bar for a quick refresher on how to use the IMPORTFEED() function. The first input refers to the source feed from which you want to pull the data from. The second entry, “items title”, describes what you want to pull from the feed, and then at the end, you give the number of entries you want to import. On our website, you can find a detailed article on how to use the IMPORTFEED() function and other common web scraping techniques.

Our goal here is to identify the most frequently occurring keywords in today’s news.I have navigated to cell D2 to insert my formula:

Frequent keywords in Google Sheets

 

Before I move to the result, let me break down the formula into smaller parts. I will start with the innermost function used here, which is JOIN().

  • JOIN(” “,B2:B) concatenates all entries in column B separated by a space. Note that we have given the range of B2:B to allow for updates in the number of news entries in the future.
  • SPLIT(<result from above>,” “) splits the large chunk of text from the JOIN() output into words separated by a space, and each word is treated as a separate entity in this case
  • TRANSPOSE(<result from above>&{“”;””}) converts each of the words in the preceding range into a single column entry (each word is treated as a data point within a single column of words).
  • And finally QUERY(<result from above>, query) performs the required operation. Notice that the query is enclosed within quotes. The result from the TRANSPOSE() is treated as the data on which the query will be run. Let us further break down the query into smaller portions to understand better:
    • select Col1, count(Col2) – selects two columns where Col1 is the word list and count(Col2) is the count aggregation performed on column one
    • group by Col1 – performs the count aggregation at the Col1 (keyword in this case) level
    • order by count(Col2) desc – sorts the results in descending order based on the keyword count (Col 2).
    • limit 25 – selects the first 25 rows only
    • label Col1 ‘Word’, count(Col2) ‘Frequency’ – names the result columns as ‘Word’ and ‘Frequency’

 

The output, if you have followed the steps correctly, will look like this:

Frequent keywords in Google Sheets

 

To further refine your result, you can add a few conditions:

  • Convert all of the content into UPPER case for an easy and foolproof search
  • Add a clause in the query to omit pronouns and conjunctions

The final result will look like this:

Frequent keywords in Google Sheets

 

 

Using the INDEX, MATCH and COUNTIF Functions

If you are more comfortable with common Excel functions than with QUERY(), you can use INDEX() and MATCH() to find the keyword with the most occurrences. The only caveat here is that it will fetch you the one keyword that has more occurrences than every other. And not a group of keywords with corresponding counts like we get using QUERY(). Let us look at an example.

Let’s use the same IMPORTFEED() function to get the titles of articles on Sheetaki.com.

Frequent keywords in Google Sheets

 

Once you have the titles, proceed to get the list of keywords as in the earlier example. We will use:

=TRANSPOSE(SPLIT(JOIN(” “,B2:B),” “))

to get the list of keywords as entries in a single column (Column C in this case).  Use the UPPER() function in column D to ensure that all keywords are in uppercase, ensuring that the frequency count is error-free::

=UPPER(C2)

 

Refer to our article here for a detailed explanation of how to use the UPPER() function. 

Given below is what you get once you run the two functions listed above. Our objective now is to find the keyword that occurs the most number of times across column D.

Frequent keywords in Google Sheets

 

And we achieve this by using a couple of formulas given below:

Most Common Word = ARRAYFORMULA( INDEX( D2:D81, MATCH( MAX( COUNTIF( D2:D81, D2:D81)), COUNTIF(D2:D81, D2:D81), 0)))

Frequency = ARRAYFORMULA(MAX(COUNTIF(D2:D81,D2:D81)))

 

Let us break those two down into smaller components:

  • COUNTIF(D2:D81, D2:D81) passes a set of words in D2:D81 to the range D2:D81 and counts the occurrence of each word
  • MAX() selects the highest possible count
  • INDEX() selects the word that corresponds to the word with the highest count

On applying the two functions, you should get an output as shown below:

Frequent keywords in Google Sheets

 

 

That’s pretty much it. You have everything you need to get started with keyword counts on Google Sheets. You may make a copy of the spreadsheet using the link I have attached below:

I would recommend playing around with the formulas above and other numerous Google Sheets formulas and seeing what you can come up with.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like