Google Sheets is undoubtedly a very powerful tool for SEO.
The amount of Add-ons & features that Google Sheets offers outweigh the productivity that you achieve with Excel.
In this article, we will learn about Google Sheets QUERY formula & why is it so important for SEOs to learn this.
What’s more, I will also explain how ChatGPT can help build powerful QUERY formulas that will make your life easier.
What is QUERY Formula anyway?
Google Sheets QUERY formula acts like an SQL or BigQuery.
But wait a minute, what does SQL mean here?
SQL (Structured Query Language) is kind of like a programming language which is used to clean, manipulate and analyze large volumes of data. When you work at a large enterprise you may get an SQL database that contains business information like sales by region with various parameters which may contain 500,000 or 1M rows of data. This amount of data simply can’t be handled via Excel. This is why tools like SQL or BigQuery come to save the day.
Now for example, to clean & analyze the data you want to figure out the sales from the North American Region where the annual revenue per city is greater than $50,000 and does not have a case where people returned the goods or asked for a refund. Now there are SQL Query Syntax that can help you retrieve this particular piece of data within seconds if you correctly formulate your QUERY & run it.
Refer to the screenshot below to see how it works in SQL
Coming back to Google Sheets QUERY Formula
Google Sheets QUERY formula works exactly like SQL, you can have raw data in let’s say tab1 and in tab2, cell A1 you can type the =QUERY formula which will pull the data from tab1 based on your conditions.
The main reason why I find it so handy is because I don’t want to waste a lot of time adding filters where the browser tab freezes now and then & then I copy it to the next tab manually.
It’s a pain having to handle data this way. If you are dealing with Enterprise SEO project data then the data is bound to be more in quantity you may end up wasting several hours in this manual process.
Now there is no need to learn & memorize the QUERY formulas we are living in the age of GPT & other LLMs.
ChatGPT would help you with writing the QUERY formula and you can use the formula to get what you want.
Below I am sharing some examples of how you can use it.
1. QUERY Select All Basic Formula
This is the most basic QUERY/SQL formula where * (asterisk) stands for all.
This is why when I add this command I get all the data from the raw tab.
In your Google Sheet Raw data tab, you can select the whole table and using named ranges you can name it anything you want. The name in our case is “table”.
If you don’t know how to name the ranges then refer this guide by Google
=QUERY(table,"SELECT *")
2. QUERY Intermediate Formula Example
=QUERY(table, "SELECT * WHERE O = 'Closed with explanation' AND (I = 'VA' OR I = 'CA' OR I = 'NY')")
In this example, I wanted data from all the columns where column O is titled “Company Response to Consumer” whose value I wanted as “Closed with explanation” Additionally I wanted to include only those rows from column I that contained the following row values VA, CA, NY.
In a nutshell, what I wanted was to get rows whose response to the consumer was closed with an explanation and were from the following states of the country VA, CA, NY.
Now I didn’t manually write this QUERY ChatGPT helped me write my query.
Here is the prompt that I used 👇
As you can see in the screenshot above how easily ChatGPT helped me with the QUERY Formula that worked. It’s all about how easy to understand your prompt is.
3. QUERY Formula with More Conditions
=QUERY(table, "SELECT * WHERE O = 'Closed with explanation' AND (I = 'VA' OR I= 'CA' OR I = 'NY') AND (D = 'Using a debit or ATM card' OR D = 'APR or interest rate')")
Here is another example from the same table.
Here in a nutshell I procured data where I wanted data about consumer complaints that are closed with explanation, are from the following states VA, CA, NY and issue was specifically the following using a debit or ATM card or APR or Interest rate.
So in essence we applied 3 conditions to get the data we wanted.
We can utilize the capabilities of ChatGPT to add layers of conditions to get refined data that will save our time.
Possible SEO Use Cases?
Now this begs the question how does learning this help SEO at all?
It greatly helps us do SEO because we can form similar kinds of queries for different tools like SQL and BigQuery.
In BigQuery GSC & GA4 data are usually stored. We can use the QUERY formula to extract the conditional data that we need for our analysis.
Let’s suppose you performed a crawl on Screaming Frog for a large website & you exported the inlinks report using the QUERY you can add conditions & extract data where inlinks are from the content location where the target page subfolder contains a certain path or you can also identify internal links flowing through nofollow or noreferrer attribute.
There are so many use cases that I can think of, but what I am trying to conclude here is that learning this skill will massively save your time & you don’t even need to recite these formulas GPT will literally help you with forming these formulas.
Kunjal Chawhan founder of Decode Digital Market, a Digital Marketer by profession, and a Digital Marketing Niche Blogger by passion, here to share my knowledge