Why Every SEO Should Be Using Google Sheets Query Function

Google Sheets allows us SEOs to manipulate data in a multitude of different ways – but Google Sheets combined with the power of SQL (“Structured Query Language” for the uninitiated) takes this to the next level.

Not only does the QUERY function enable us to replicate the actions of formulas such as VLOOKUPs and IF statements – they enable us to accurately and efficiently query datasets without any copy and paste errors.

I know a lot of you unfamiliar with SQL may be thinking “NOPE”  but bear with me and by the end of this blog you’ll understand the basics, have a handful of examples and a number of Google Sheets you can steal and have a play with.

The examples will focus on:

An Introduction To Query

What actually is the Query function?

QUERY is a Google Sheets formula that enables you to manipulate data sources. This function is largely considered to be one of the most powerful functions on Google Sheets and can be a gamechanger in performing key SEO tasks (and other sorts of data manipulation).

Let’s say, for example, are doing keyword research and have an export from Ahrefs related to the term “birthday cards.” In one simple query, we can extract all rows of data relating to keywords where:

• Keyword Difficulty is below 20
• Keywords are non-branded (in this case excluding keywords that contain “moonpig”)
• Search Volume is above 150
• The keyword contains a Featured Snippet
• We also want this ordered by Volume (descending).

In the first tab we would have the full export including a large number of varying quality keywords.

The second tab would have our Query formula in A1 – 

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT * WHERE B < 20 AND NOT A CONTAINS ‘moonpig’ AND C > 150 AND G CONTAINS ‘Featured snippet’ ORDER BY C desc”)

This would automatically populate the rows and columns with just the keywords matching the specific criteria we are looking for – Here are our results.

How does this relate to SQL?

SQL is a programming language used to communicate with a database. 

The QUERY function on Google Sheets allows you to use a Google Sheets version of SQL, named Syntax Query Language, which allows far more granular, accurate and speedy insights from relatively large datasets. You may have heard of SQL in relation to BigQuery – Google’s cloud based tool which allows users to query really big datasets. 

For SEOs, BigQuery comes in really handy for handling huge datasets and is useful for tasks such as log file analysis, analysing CrUX data or crawl analysis. For more insights check out this blog by one of our Senior Consultants, Dom Woodman.

What is the advantage of using Query on Google Sheets?

• Instead of having to write individual formulas for each column, QUERY allows you to import specific columns and rows based on select criteria or conditions. This saves the dramas of copy and paste errors (we’ve all been there before!)
• QUERY datasets update in real time making it easy to update sheets on the go – you can also use the QUERY results as a reference in tables and graphs etc, which can then subsequently be used on other Google platforms such as Google Docs or Slides. Updating your data will also update your data across these platforms making everything more seamless and error free.
• Queries are extremely recyclable – once you have written queries for specific datasets, you can use them again and again (and obviously adjust as you go). One example of this within SEO is when doing a backlink analysis – you can apply the same rules to a backlink export across different tabs in order to isolate URLs that reach certain criteria. See the backlink audit example below for more info. 

How to Write a Query Formula

Google Sheets QUERY Syntax

Google Sheets Syntax: =QUERY(range, sql_query, [headers])

• Range – this is the table or range you are looking to query
• Query – This is where you write your SQL query in “quotation marks.”
• Headers – this is where you can add headers (note: this is optional and it can just allocate a name for you).

Google Sheets SQL – The Basics

As mentioned earlier, the Query formula uses SQL – which has the advantage of being very logical and easy to follow. There are a few generic rules that you need to follow when using SQL. The main one being that you need to write clauses in the correct order.

Firstly, I’ll go through the basics using an Ahrefs export for the keyword “birthday cards” before jumping into some more examples. Feel free to jump straight to the examples if you fancy.

Our sample dataset consists of keywords related to the term “birthday cards” – there are a number of columns including the Keyword in Column A, Difficulty in Column B etc.

SELECT

SELECT allows you to specify which columns you wish to import.

Firstly let’s say we want to just select all of the keywords without any of the additional columns of data – basically just column A.

What we would do is create a new tab and type in our QUERY into cell A1.

The ‘range’ will stay the same across these different examples and the Query will change.

=QUERY(‘All Birthday Cards Keywords’!A:G,”SELECT A“)

Here’s our result…

WHERE

WHERE allows you to specify a condition you want to match (CONTAINS is when a cell contains specific text).

So let’s say we want to select the Keyword and Difficulty Columns (Column A and Column B) where the keyword contains the text “birthday”

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT A,B WHERE A CONTAINS ‘birthday'”)

Here’s our result…

WHERE NOT

WHERE NOT allows you to specify a condition you do not want to match.

This time let’s select all columns where the keyword does not contain the text “birthday”

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT * WHERE NOT A CONTAINS ‘birthday'”)

Here’s our result…

ORDER BY

ORDER BY allows you to  specify how you would like your data ordered – “asc” for ascending or “desc” for descending.

Let’s select the columns Keyword, Difficulty and Volume and order it by Difficulty (descending).

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT A,B,C ORDER BY B DESC”)

Here’s our result…

LIMIT

LIMIT allows you to specify a limit to the number of results

Let’s select all rows where the keyword contains the text “birthday”, let’s order it by Difficulty (descending) and limit it to the top 10 results.

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT * WHERE A CONTAINS ‘birthday’ ORDER BY B DESC LIMIT 10”)

Here’s our result…

LABEL

LABEL allows you to specify a name for a column.

Let’s just grab column A and B – and let’s label Column B “Keyword Difficulty”

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT A, B LABEL B ‘Keyword Difficulty'”)

Here’s our result…

Google Sheets Query Examples

Using QUERY for Keyword Research

The first stage in querying a large data set is to have all the raw data in one tab which can be the reference for queries in other tabs. I have named this tab “All Birthday Cards Keywords.” This may feel slightly familiar to those who read the previous section.

You can see all these examples in this Google Sheet.

In this case, I have some (very quick and unattractive) keyword research for terms related to birthday cards.

Example 1 – Isolating Branded Keywords

For this example, I am looking to pull out all the rows of data associated with keywords containing the text “moonpig.” I am also ordering this by estimated search volume.

Our Query Function

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT * WHERE A CONTAINS ‘moonpig’ ORDER BY C desc”)

Our Result

Example 2 – Isolating Keywords Matching Specific Criteria

In the next tab, we are looking to extract all rows of data where;

• Keyword Difficulty is below 20
• The keyword does not contain “moonpig”
• Search Volume is above 150
• The KW contains a Featured Snippet
• We also want this ordered by Volume descending.

Our Query

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT * WHERE B < 20 AND NOT A CONTAINS ‘moonpig’ AND C >150 AND G CONTAINS ‘Featured snippet’ ORDER BY C desc”)

Our Result

Example 3 – Isolating “Mother” or “Mum” related keywords

In the next tab, we are looking to extract just the keyword and search volume  where;

• The keyword contains “mum” or “mother”
• We also want this ordered by search volume descending.

Our Query

=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT A,C WHERE A CONTAINS ‘mum’ OR A CONTAINS ‘mother’ ORDER BY C desc”)

Our Result

Using QUERY for Crawl Data

Similarly to the KW research data, we will want to have all of the data in one tab – In this case, I have run a Screaming Frog Crawl of https://www.distilled.net, naming the tab “Distilled Crawl Data – Raw & Unedited.”

You can see all these examples in this Google Sheet.

Example 1 – Pulling Redirects and their Redirect URL

For this example, I am pulling just URLS that are 301 or 302 redirects and the subsequent redirect URL.

Our Query Function

=QUERY(‘Distilled Crawl Data- Raw & Unedited’!A:AV,“SELECT A,C,AT WHERE C = 301 or C = 302”)

Our Result

Example 2 – 404 Pages Sorted By Number of Unique Inlinks

This example requires selecting just 3 columns where the status code is 404 order by the number of unique inlinks. 

Our Query Function

=QUERY(‘Distilled Crawl Data- Raw & Unedited’!A:AV,“SELECT A, C, AJ WHERE C = 404 ORDER BY AJ desc”)

Our Result

Example 3 – Title Tags Over 60 Characters 

The purpose of this query is to extract the URLs with title tags over 60 characters ordered by length (highest first).

Our Query Function

=QUERY(‘Distilled Crawl Data- Raw & Unedited’!A:AV,“SELECT A, C, G, H WHERE H > 60 ORDER BY H desc”)

Our Result

Using Query for Backlink Analysis

Again, we will want to have all of the unedited data in one tab – In this case, we have a tab called ‘Full Backlinks Export – Distilled.net.’

You can see all these examples in this Google Sheet.

Example 1 – Analysing a Backlink Profile

For this example, I am pulling the Referring Page URL, Domain Ranking, Referring Page Title, URL Ranking and Type of Link based on specific criteria:

• URL ranking is above 40
• The links are followed links

Our Query Function

=QUERY(‘Full Backlinks Export – Distilled.net’!A:W,“Select F,C, G,D,N WHERE D > 40 AND N = ‘Dofollow'”)

Our Result

Example 2 – Analysing a Backlink Profile (Round 2)

For this analysis I am pulling the Referring Page URL, Referring Page Title and  Link URL based on specific criteria:

• The link URL is https://www.distilled.net (this will only pull in the homepage)
• The links are followed links

Our Query Function

=QUERY(‘Full Backlinks Export – Distilled.net’!D1:W1000,“select F,G,J,N WHERE N = ‘Dofollow’ AND J = ‘https://www.distilled.net/'”)

Our Result

Example 3 – Analysing Anchor Text from Non “no follow” links

For this analysis I am pulling the Referring Page URL, Link Anchor, Link URL, Type and Traffic based on specific criteria:

• The links are not “no follow” links
• The lists are ordered by the traffic descending.

Our Query Function

=QUERY(‘Full Backlinks Export – Distilled.net’!A:W,“SELECT F,L,J,N,T WHERE N <> ‘Nofollow’ ORDER BY T desc”)

Our Result

Conclusion

That’s the QUERY function – welcome to a new world without copy and paste errors ruining your day.
Check out all the examples accompanying Google Sheets – Keyword Research, Backlink Analysis and Crawl Analysis.

[ad_2]

Source link