How To Count Keywords in Page Titles (using Google Sheets)

Last Updated on July 1, 2021 by Jake Sheridan

In this very short guide, we’ll run through a method for calculating the most frequent keywords from page titles using Google Sheets.

Find Content Formats Based on Page Titles in the SERPs

The other day I saw this tweet:

It got me thinking: this is a great way to quickly understand the intent of a keyword… without diving into a full scale search intent analysis.

There are a few ways this process could be useful:

  • Sometimes you just need to check the type of content you are creating is on the right track.
  • Or maybe a writer has asked you if the article they are creating is positioned correctly.
  • Or maybe you just like seeing what kind of content is ranking in the SERPs?

Want to do something similar? It’s dead simple

Let’s go:

How To Count Frequent Keywords from Page Titles (in Google Sheets)

Before we get started, to use this process you will need:

*NOTE: please make a copy of the Google Sheets template. If you request access, you will be ignored 😊

Step 1 – Copy the sheet

Make a copy of the Page Title Frequency Sheet. You should have a blank sheet:

The sheet is setup to have 100 pages titles dropped in. If you want less, that’s fine.

If you really want to drill down to the intent of a keyword, using the top 10 – 15 titles in here is probably enough to be useful. The sometimes dropping in the full top 100 from the SERPs can be useful for understanding the intent across a range of ranking sites.

Step 2 – Get page titles

Now you want to Google a keyword and grab the page titles for each URL.

You can do this a couple of ways:

  • Use Screaming Frog
  • Use a Chrome Bookmarklet
  • Ahrefs lets you export SERPs
  • Manual write down the page titles on paper and then fax them to yourself 🤷‍♂️

Whatever your method of choice is, use that.

Step 3 – Paste them into the sheet

Now click into Cell B2 and paste them into the sheet.

Voila! Now you’ll have a frequency count of the terms in the titles:

 

To the right, I’ve copied in the different words to clue you in on what Google believes users are trying to accomplish (learn, do, buy).

These are colour coded and thanks to a little conditional formatting, highlight when they occur in the WORDS column.

For spreadsheet geeks, here is the formula that is calculating the most frequently occurring terms:

=ARRAYFORMULA(query(TRANSPOSE(SPLIT(JOIN(" ",B2:B)," ")&{"";""}),"select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 25 label Col1 'WORDS', count(Col2) 'FREQUENCY'",0))

If you want to understand how this formula works, I’ll explain it below. If you can think of nothing worse, please skip this section and go do something less nerdy with your life.

If you are still reading this, sorry you are a nerd. (Hey there👋 )

Here’s how the formula works:

  • JOIN concatenates all entries in column B separated by a space.
  • SPLIT turns the text into words separated by a space (each word is treated as a separate entity).
  • TRANSPOSE converts each words in the range into a single column entry.
  • QUERY does the magic:
    • select Col1, count(Col2) – selects two columns where Col1 (word list) and Col2 (count aggregation) is done
    • group by Col1 – count aggregation is done at Col1 (keyword)
    • order by count(Col2) desc – sorts results in descending order based on the keyword count in Col 2
    • limit 25 – picks only the first 25 rows
    • label Col1 ‘Word’, count(Col2) ‘Frequency’ – adds ‘WORD’ and ‘FREQUENCY’ as column headers

What’s next?

From here, you can at a glance see the types of content formats that Google is choosing to rank.

As Jonas points out:

Sometimes keywords straddle intent, so it’s not perfect. But, it does help guide your topic clusters so you create content for each stage of the journey.

There you have it, a quick (and hacky) method for understanding search intent a little better using frequency counts and a simple Google Sheets template.

If you’ve got any modifications to this sheet/process, or more uses cases, Tweet me and I’ll add them to this article.