How to do a quick search intent analysis with Google Sheets [+ FREE Template]

Learn how to use the REGEXMATCH formula to perform basic search intent analysis across your keywords.

Search intent tries to answer one question:

Does your content answer the question people are searching for?

You can have the best content in the world, but if it doesn’t answer the question, people are not going to engage with your page. Not to mention Google’s not going to rank it highly. Why would they? It’ not useful.

The solution: understanding search intent.

What is search intent?

Quick Navigation

Search intent is the reason people are searching for a keyword. There can be a few reasons someone might be searching something on Google:

an answer to a question
to find specific website
looking to buy something

Google has got pretty good at understanding the intent of searches. And seeing as Google wants to rank pages higher that answer a users query, it’s an important consideration to make when creating content.

4 Basic types of search intent

There are a few distinct types of search intent:

Informational intent – People looking for information, they’ll use words like: how, what, why, when etc.
Navigational intent – People trying to find a specific website, they’ll use words relating to the service/product you offer and maybe your brand name.
Transactional intent – People looking to buy stuff, they’ll use words like: buy, cost, pricing, cheap etc.
Commercial investigation – People researching prior to making a purchase, they’ll use words like: best & vs (in order to compare products) etc.

Using REGEXMATCH to determine search intent

Manually adding search intent labels across all your keywords can be time consuming. But not with REGEXMATCH and Google Sheets.

Let’s see it in action for a fictional estate agents website (more on that later):

Pretty cool right.

All you need to get started is the keywords you want to analyse (in a sheet) and this formula:

How to use the sheet

To get it setup…

What you’ll need:

Some keywords to analyse
A tiny bit of patience
A Google account (obviously) so you can make a copy of this template:

Grab the Search Intent Analysis Sheet

📝 Note: for the purpose of this walkthrough, let’s imagine you are doing search intent analysis for a real estate agent who serves the region of Westeros. Someone’s got to.

Step 1 – Setting up the REGEXMATCH formula

This can be a bit fiddly. Let’s break down the formula into it’s individual components so you know how it works.

This REGEXMATCH formula is made up of four IF statements. Let’s look at each of them and what it does.

Transactional intent:

This looks in cell A2 (where you keyword is) and adds a label. If A2 contains any of the transactional intent keywords, the transactional label is added.

Informational intent:

if(REGEXMATCH(A2,"how|How|what|What|why|Why|when|When|why|Why|can|Can|do"),"Informational",

This looks to see if your keywords contain any informational related words (usually question modifiers) and then adds the label.

Commercial Investigation intent:

if(REGEXMATCH(A2,"best|Best|vs|Vs"),"Commercial Investigation",

This looks to see if your keywords contain any commercial investigation related words and then adds the label.

Navigational intent:

if(REGEXMATCH(A2,"real estate|rentals|estate agent|for sale"),"Navigational",

This can be a fiddly bit to setup. The words you add will be dependent on what your site does.

Remember how I said we’d image we were researching keywords for a Westeros based estate agent? This bit is why.

You want to add the core services/products and brand names here.

So in this case, it would be something like rentals (one of the service offerings) & estate agent (the industry term).

Other intent

"Other"))))

Sometimes there won’t be a clear search intent label to add. This bit of the formula marks any keywords that don’t fit into these labels as ‘other’

Step 2 – Apply some conditional formatting

To make it easier to interpret the data, I like to add some conditional formatting.

Here’s what my setup looks like:

Gotta love a pretty spreadsheet.

Step 3 – Spread the formula around

Drag your formula down and enjoy all the colourful search intent analysis labels appearing.

Psssst. Hey you. Don’t forget to sign up to the newsletter for more content like this. Or don’t, that’s totally cool too. 👌
[yikes-mailchimp form=”3″]

The elephant in the room:

Is classifying search intent by Navigational, Transactional, Commercial Investigation & Informational the best method?

No.

Analysing search intent across these four buckets is useful, albeit basic.

Search intent resources

Want to go deeper into search intent analysis? This article (and spreadsheet tool) only skims the surface of search intent and offers a basic solution. If you want to dig deeper, you’ll want to read these:

Don’t forget to grab a copy of the sheet here to help you work out search intent. Also, checkout the directory of Google Sheets templates to help you automate other SEO tasks.