Using Google QUERY for SEO insights

In this blog post, we’ll be looking at how we can use the QUERY function in Google Sheets to pull out quick SEO insights from traffic and keyword datasets, either for our own analysis or for a presentation deck.

We’ll be creating quick, well-formatted tables to paste into reports or presentations, and learning how to organise data in a useful way for spreadsheet analysis.

If you just want to see the live example, see here: https://docs.google.com/spreadsheets/d/1CMeDbO3fh6yBMCRZ2JrXuGza24vtlyTTYHYsBpVJfCI/edit?usp=sharing

What is the QUERY function?

The QUERY function in Google Sheets is a built-in function that allows us to (as it says on the tin!) query a table based on a wide range of parameters and filters. We can use it to pick top 10s, bottom 10s, and much more, and is a useful way of working with data, particularly if you hate pivot tables – and this writer doesn’t blame you for that!

Here’s the basic structure of the formula in an example:

=QUERY(data!A:C, "select A where B>0 order by C desc limit 10")

This example formula will output 10 items from column A, ordered by column C descending, where B is greater than zero. 

You might ask – why use this function instead of normal filters on headings? For me, I end up using QUERY regularly due to:

1. Typing out a formula is faster than clicking the multiple of times needed to add filters, select each heading, apply the correct filters, repeating for each
2. Google Sheets’ filtering workflow (as someone who mainly worked on Excel) is a bit less intuitive and slow, having to check/uncheck the filters

We can leave the data untouched, which some people might find more tidy (like me)

What is a useful SEO insight?

A useful SEO insight is something that can meaningfully lead to a good outcome, either through informing strategy or uncovering underlying performance issues. In SEO, we work with a large array of metrics, often spread across a huge number of tracked keywords and landing page URLs. 

A good SEO can interpret these data and make decisions on a strategy based on them and deliver results as a result.

I also emphasise the importance of moving quickly away from heavy data analysis and onto actual implementation, actual driving of results – that means one of the key ongoing challenges of a modern SEO is:

“How can I spend less time analysing data and more time impacting results?”

This blog post will hopefully get us thinking about how we can use built in functions, like QUERY, to achieve that.

Organise your data – keywords

To deliver useful insights, we need good data. For keyword data, we need to be able to pick out the most important keywords. To do this, let’s include some sort of weighting by search volume.

Let’s look at our starting data. Keywords, Categories, Search Volume, Rank Before & Rank After:

The dataset has some 974 keywords and 100 category-subcategory variations, so it’s a good size and needs some work. Let’s start with some basic processing to prepare the data for our analysis.

Let’s calculate the visibility score before and after, using a typical CTR curve. No need to be too scientific here, a generic curve will do for the type of insights we are building.

How to calculate keyword visibility score

I typically calculate visibility score like this:

=$C2*VLOOKUP(D3,ctrs!$A:$D,4,true)

Where

• $C2 == the Search Volume (which we are multiplying by the click-through rate for the current ranking position)
• D3 == the rank position
• ctrs!$A:$D == our CTR model (column A contains ranking positions 1-30, column D contains the click-through percentage for that ranking position)

Let’s look at our columns now, where columns F:I are new:

I’ve included here a ‘Rank Change’ and ‘Vis Change’ columns, which is the difference before and after. (I think some calculate their rank change the literal opposite way to me, but for me + == positive i.e. ‘we gained position’ and – == negative i.e. ‘we lost position’).

Creating concatenated helper columns (optional!)

If we want to be particularly clever, we can add another column which might help us later on! Knowing that I am creating a deck or a quick insight for an email or similar deliverable, I can create something helpful in a final column:

"“"&A2&"” ("&TEXT(C2,"#,#")&" searches PCM)"

This formula simply joins up the keyword (A2) and its monthly searches (C2) so we get something that looks like this:

We can go further, by adding the ‘Rank After’ (E2) and ‘Rank Change’ (F2) to include lots of interesting data in a single cell:

"“"&A2&"” ("&TEXT(C2,"#,#")&" searches) #"&E2&" ("&TEXT(F2,"+#;-#;=")&")"

Which looks like this:

You might see how this could be useful later on already. #

There are a couple of TEXT functions in this formula: We need these when joining up formatted numbers in order to maintain the desired number format – a very useful function if you are building dashboards in Sheets.

Example Google QUERY functions for SEO

Let’s go ahead and deploy a QUERY function now. In a new sheet, let’s go ahead and enter this anywhere we like:

QUERY('example data'!$A$1:$J$975,"select A order by I desc limit 10")

Which gives us this:

Let’s look at the QUERY function inputs:

• ‘example data’!$A$1:$J$975 == our keyword data from the data preparation steps;
• “select A == select our column of keywords;
• order by I desc == order by ‘Vis Change’ column, descending;
• limit 10″ == limit to 10 rows of output.

Simply put, what we have here are the top 10 growth keywords by visibility change: The top keyword (“keyword 5”) drove the biggest visibility impact for this campaign. 

Alternatively, we could simply grab our newly formatted column J instead:

QUERY('example data'!$A$1:$J$975,"select J order by I desc limit 10")

Which gives us this:

Here we have some simple information we can paste into a deck, or an email summary, to pass on the performance overview for our best-growing keywords in the campaign.

There are loads you can do at this point to advance your analysis, but this should give you a healthy starting point to run with.

Are you interested in learning more about Google QUERY? Get in touch with one of our SEO experts.