Need Help? Talk to Our Experts
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:
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:
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.
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.
Google Sheets Syntax: =QUERY(range, sql_query, [headers])
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 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 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 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 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 allows you to specify a limit to the number of resultsLet’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 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…
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.
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
In the next tab, we are looking to extract all rows of data where;
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
In the next tab, we are looking to extract just the keyword and search volume where;
=QUERY(‘All Birthday Cards Keywords’!A:G,“SELECT A,C WHERE A CONTAINS ‘mum’ OR A CONTAINS ‘mother’ ORDER BY C desc”)Our Result
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.
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
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
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
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.
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:
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
For this analysis I am pulling the Referring Page URL, Referring Page Title and Link URL based on specific criteria:
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
For this analysis I am pulling the Referring Page URL, Link Anchor, Link URL, Type and Traffic based on specific criteria:
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
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
Digital Strategy Consultants (DSC) © 2019 - 2024 All Rights Reserved|About Us|Privacy Policy
Refund Policy|Terms & Condition|Blog|Sitemap