en
English Español Deutsch Français Italiano Português (Brasil) Русский 中文 日本語
Submit post
Go to Blog

Weekly Wisdom with Ross Tavendale: GSC Query Tutorial

Weekly Wisdom with Ross Tavendale: GSC Query Tutorial

Ross Tavendale

Mining GSC: Using Query Language to Build Powerful Reports and Gain Instant Insights

Modified Transcript

When an SEO consultant joins Type A Media, the first thing we get them to do is get really good at spreadsheets. So, that includes everything from VLOOKUPs, to index match, to pivot tables — you name it. I want you to be able to get big clumps of data in, and very quickly be able to analyze them.

The great thing about using Google Sheets to do something called QUERY, which is kind of like a pseudo-SQL type of language, and I am gonna show you all the SEO tips and tricks I have got using QUERY inside of Google Sheets to make your data analysis life way, way easier.

So, I think we have all been in a position where we get a bunch of search console data in, and we can see our keywords, but the thing is when you get 5,000 keywords, it is kind of hard to actually work out what to do with this data.

Using a Table: How it Works

I mean, there are some obvious things, right? I mean, you could stick it in a table. That is relatively straightforward, so select the entire thing and stick it in a nice little filler table. And then we can know, you can start filtering through it, which is fine. You know, you have got filter by condition, so give me everything that is greater than 10,000 impressions, so I can see all the high-end stuff, and that will chop the data down.

Maybe I just wanna sort it by topic or something similar. So, in this example, these people sell lighting.So maybe I wanna see "text contains". Maybe I wanna see all the black products. Okay, there is not enough there. And when it comes to impressions you have to make changes again; can you see how this is not an ideal way to manipulate data in a table?

It doesn't really tell me anything, and even when I do get down to the things I wanna see, it is still really cumbersome and it physically changes how the data looks. I don't want that. I want my data to be preserved, and in one place. I want to be able to manipulate it almost as if I am creating a database.

QUERY

QUERY is something which is a pseudo-SQL style language which you can use inside of Google Sheets, andI am going to show you how to use it, so I am going to put this data back the way we found it, and we are going to jump into the next sheet to show you how you can very quickly get insights into massive datasets.

This is the way we found it, as we have exported it from search console. We then jump into QUERY language itself. So, what do we do? First and foremost, "=query(". So, the first thing we need to do is actually tell it what we want to query, and in this instance, we want to query our search console.

Now we actually need to give it a command (2:35 in video). What do we wanna do here? I need to give it the parentheses, and I essentially tell it, "Well, I want you to select everything in the table, which is a wildcard." That's the asterisks. "I only want you to select it where column A contains "black" because that is an easy differentiator. The term black."

We will close the parentheses and the brackets and hit enter, and before you know it, those 5,000 are now down to 81, and it is all the black variants of the product. And my original dataset is completely intact, with nothing hitting it.

Finding Position

Okay, so getting that data is all very well and good, and now what I really wanna do is kind of make it a bit more useful. Maybe I actually wanna see everything where the average position is actually on page 1. So, in this instance, all we do is go back into it, and we just say, "And, show me everything where column B is less than 10." Just like you do in a table, but really dynamically.

So, in terms of very quickly looking at, perhaps, a product range, and working out the places where you are already ranking well, and you want to start pushing it a little bit more, this is probably the place that you want to start looking. These are the target keywords that you wanna start optimizing and pulling in. Now, that is all very well and good, but what if we want something that's even more dynamic than that, but we don't need to constantly be pushing through tons and tons of formula?

Additional QUERY Computations

Okay. Well, let me take you to the next page of this. So, you are gonna notice here, that we have got a bunch of kind of top line pieces of information. So, the great thing about QUERY language is that you can actually do computation inside of it.

So, whereas before we just selected wildcards like everything, where A contains a keyword, now what I can do is say F it contains, let's say, a product variant. In this case, say people sell lamps or different colors of lamps and lampshades. And what it will actually do for me is it will average up absolutely everything. So, inside of this massive Google Search Console data that we have exported, I can get it to show me F (turned into a product variant) — it is a white lampshade. Or pertaining to a particular product range, I can get it to average out my position, my click-through rate, my clicks, my impressions, etc.

So I can actually art seeing a top-level view of product groupings inside of search console, which is really useful; and the way I do that is really super simple. All I am doing there is saying, again =query, and then it is GSE, which is my search console down here, and I am saying select the average of B where A contains ... and you see this thing here...

Weekly Wisdom with Ross Tavendale: GSC Query Tutorial. Image 0Timestamp: 5:50

It is referencing the cell that has the colour "white", and what we can do here is say, "let's change that to the color black" and everything changes, because what it is now doing is looking for the color black.

You can also do certain product categories. For example, show me all the pendants, and all of a sudden all this stuff starts changing. So, you can very quickly query datasets and do computation on them literally with a couple of keystrokes instead of building out really overly complex sheets, and nested formulas, and arrays, and all that stuff.

More Computations

Now, this is all very well and good. But what about if I want to have this run but also, my initial query, what if I want it to reference? How do I change this data? Well, really simple. Let me show you.

Weekly Wisdom with Ross Tavendale: GSC Query Tutorial. Image 1Timestamp 6:42

So, as before, =query, and then it is GSE A-E, which of course is referencing Google Search Console, and it says select star, which means wildcard, which means everything, where A contains, and then we have got this thing. It is like parentheses, ampersand, and then a reference to the datasheet and A2, and then that closing out. So, let's actually see what happens when we run that.

What it is doing is it is actually referencing this pendant keyword here, and it is giving us the data we want just about pendants. So, what happens if we then go in and perhaps change that to maybe a color variant? We get our top line numbers changing, which is great.

Also, it changes to black. You can start seeing how you can start to use this to very quickly manipulate data. Instead of doing your usual pivot tables, and constantly filtering things, and cutting the data out. We have been able to do this and get the exact data we need while preserving our core search console data so we can always refer to it, and always go back to it.

You can use this for building reports, mining data, or doing any kind of basic analysis, and just as a final recap, all you need to do is =query. You are then selecting the dataset that you are actually querying, and you are opening parentheses. It is really important that these two parentheses open and close everything.

Then, you are saying "select", and telling it what to select.

Weekly Wisdom with Ross Tavendale: GSC Query Tutorial. Image 2Timestamp 8:00

You can give it different columns, you can give it the entire thing. And then you give a condition. Select everything where A contains my keyword. Really simple. And if you want to keep appending things on, that is really easy as well. We just put and, and "add on" and "add on".

So, for example, let's say I want to say where A contains the keyword black, that will be the same, 'cause it's the same query. And then if I want to say, "And the D>10 ", and what that is done is it's taken everything that's not on page one. It said show me where D is greater than 10, so show me where my average position isn't on page one, so it's chopped it down even more. 

Weekly Wisdom with Ross Tavendale: GSC Query Tutorial. Image 3Timestamp 8:56

You can literally just keep doing that, and doing that, and doing that.

Weekly Wisdom with Ross Tavendale: GSC Query Tutorial. Image 4

And it will just keep chopping the data down, chopping the data down, chopping the data down. So, in this instance, when I am saying and D is less than 10, or E is less than 10, what we can actually do, instead of actually putting it in the formula, what we should probably do is link it, so if it is less than the average, or if it is more than the average, I want to see that so I can work out, "Well, if my click-through rate, or my impressions, or my clicks, are lower than the average for these keywords, I probably need to optimize them."

But I can do that in literally a heartbeat as I have just shown you. Hope you found this one useful, and for any comments, please leave them down below and I wil jump into the comments and answer any questions you have got about using QUERY.

There you have it. That's using QUERY to save a bunch of time on your data analysis, done.

Ross Tavendale
Superstar

Knows everything… well, almost.

Ross first business was selling potpourri door to door in a mining village in the west of Scotland. Quickly learning the value of proper market research and understanding the audience he started working for big box agencies where he cut his teeth working on clients like Boots, Etihad and TripAdvisor. He now runs an independent agency Type A Media with his team delivering SEO services and world-class memes to SMEs and FTSE 250 businesses.
Share this post
or

Comments

2000
Hugo Scott
Helper

An experienced member who is always happy to help.

This is pure gold - thanks Ross!
It makes me wonder what other gems may be hidden away in the entrails of Google Sheets.
Paul Lovell
Pro

Asks great questions and provides brilliant answers.

Thanks for sharing your knowledge Ross, these tips and trick you are sharing are true time savers and is helping the industry look at things from a different angle to get a true view of what issues might get missed or overlooked
Newcomer

Either just recently joined or is too shy to say something.

This comment was deleted.
Ross Tavendale
Superstar

Knows everything… well, almost.

SP
SPAM ^^
Anton Shulke
SEMrush

SEMrush employee.

Ross Tavendale
A lot of spam on your post, Ross. You are famous! Spammers won't just waste their time for spamming where no one is:)
Newcomer

Either just recently joined or is too shy to say something.

Great article, Ross Tavendale! Given the details that you've provided, this article is very useful as we adopt more BigQuery with sheets. Thank you!
Ross Tavendale
Superstar

Knows everything… well, almost.

Jeannie
Glad to hear it Jeannie. Big Query is taking it yet another step beyond. Very quickly getting to a stage where excel is redundant.
Newcomer

Either just recently joined or is too shy to say something.

I think this is an informative post and it is very useful and knowledgeable. Thanks a lot for keeping great stuff. I hope that you continue to do your work like this in the future
Ross Tavendale
Superstar

Knows everything… well, almost.

johan
Thanks Johan. What part did you find the most informative? I like to take the feedback in the comments to make subsequent episodes.
Newcomer

Either just recently joined or is too shy to say something.

Ross Tavendale
It the presentations that you used in excel. after reading your post I started using same method and format, that you showed me .
Newcomer

Either just recently joined or is too shy to say something.

This comment was deleted.
Ross Tavendale
Superstar

Knows everything… well, almost.

Hitesh Parmar
Weird spam, is weird.

We're a community of SEOs dude - don't you think trying to sneak a link into your comment is a bit 2002?

If you get some time, I recommend watching the Show Me the Links webinars with Julie Joyce (https://www.youtube.com/watch?v=h6H7ERFY2EU) so you can get some better link building tactics than spamming comments.
Jason Barnard
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

columnist
Wow. I just got an insight into how much time I have been wasting messing around collecting GSC data and messing around with MySQL...
I can juist write my queries into Google Sheets. Duh !
Thanks Ross !
Ross Tavendale
Superstar

Knows everything… well, almost.

Jason Barnard
Thanks Jason. If you're already using mySQL this is going to feel very easy for you. Have a look at Big Query integrations for big data sets and let me know what you think. We're totally in love with the sheets model.
David Sayce
Helper

An experienced member who is always happy to help.

Ross does it again, great post. This is the sort of thing that saves hours each week and something everyone involved with SEO can use.
Learning a lot from these!
Ross Tavendale
Superstar

Knows everything… well, almost.

David Sayce
Thank you kind sir. All about saving those hours so I can go for beers with you at London SEO events - hope to see you at one soon :-)
David Sayce
Helper

An experienced member who is always happy to help.

Ross Tavendale
That's the correct answer! I need to spend some more time on some of these areas; optimisation and efficiencies = more SEO Beers! See you soon!
Dido Grigorov
Helper

An experienced member who is always happy to help.

Ross Tavendale's tutorials are totally top notch! I love the insights and how well is explained evertything. In todays Semantic SEO grouping and reporting when it comes to keywords and overall search performance is more important than ever! You can check your current positions and think how to improve them, or you can even find some phrases your website has ranking you have never thought about. I'll definitely use that in my everyday work! Thanks ross a lot!
Ross Tavendale
Superstar

Knows everything… well, almost.

Dido Grigorov
You're too kind Dido - thanks for the feedback.

Hope you find the sheet useful. I'm going to create a free open repository of all the SEO Google sheets, tips, tricks and hacks so the community can share them all.
CILT South India Project
Newcomer

Either just recently joined or is too shy to say something.

Good one Ross. Very intriguing article thank you for sharing.
Ross Tavendale
Superstar

Knows everything… well, almost.

CILT South India Project
Cheers amigo!
Newcomer

Either just recently joined or is too shy to say something.

This is really interesting. At first I thought, but doesn't data studio do just that?
Then I saw the potential for semantic query search.
Have you tested this with more data points? For example against URLs these queries rank for and the keyword metrics of each keyword?
Ross Tavendale
Superstar

Knows everything… well, almost.

Webfirm
Hey Webfirm

Yes, the sheets we use at Type A Media are fully featured monsters that drill down to give site wide insights. The great thing is that you can actually run computation inside of query so not only am I filtering columns in a data set, I'm running calculations on them and getting multiple outputs. In the end the big challenge is finding a "hook" to string all the data together. For example, the URL, a phone number, etc.
Pro

Asks great questions and provides brilliant answers.

The Weekly Wisdom section is really boosting. It must be getting a lot of engagement.

Excel is really good for managing everything. Going to mine GSC using this one soon enough Ross.
Ross Tavendale
Superstar

Knows everything… well, almost.

Sahil Kakkar
Always happy to provide a boost :-)

Getting your data into sheets to "roll your own" solution is key.
Amit Raj
Newcomer

Either just recently joined or is too shy to say something.

Need to implement this. Working with large data sets can be a headache sometimes.
Great article Ross!
Ross Tavendale
Superstar

Knows everything… well, almost.

Amit Raj
Cheers Amit. I can see SEO moving more and more to this level. Learning database languages and being able to query large data sets is a must. Another one we are using at the moment is Jupyter Labs (https://jupyter.org/) it helps get small output samples for large datasets - very useful for log file analysis and pattern recognition.
Enthusiast

Occasionally takes part in conversations.

Hi Ross! In your video / transcript you say "It is really important that these two parentheses open and close everything." but you're highlighting the quotation marks, not the parenthesis. There are two sets of quotation marks, too.
Ross Tavendale
Superstar

Knows everything… well, almost.

Monica - temp
Yes I think I’m getting bogged down in semantics.

All queries are in quotation marks and the values in parenthesis

Well spotted
Enthusiast

Occasionally takes part in conversations.

Ross Tavendale
Thank you! I catch these things as part of my work. But, I wanted a sanity check to be sure that the formula *was* quotes and not parenthesis. It obviously worked!
Ross Tavendale
Superstar

Knows everything… well, almost.

Monica - temp
Anytime Monica. Hope it's useful in their day to day.
Enthusiast

Occasionally takes part in conversations.

great tutorial ! Thanks you for sharing !
Ross Tavendale
Superstar

Knows everything… well, almost.

Jimmy
Cheers Jimmy!
Tristam Jarman
Helper

An experienced member who is always happy to help.

Another great tip here Ross. Great for chopping up data in half the time. Definitely too much time is spent manipulating data and not analysing it.
Ross Tavendale
Superstar

Knows everything… well, almost.

Tristam Jarman
Thanks Tristam- appreciate the daps on Twitter too.

We work a 4 day week at Type A Media so cutting out time is a massive deal to us. Thanks for taking the time to comment
Harry Sanders
Helper

An experienced member who is always happy to help.

Mmmm loving the DBM talk - something that more SEO specialists should take about! We use a lot of Google sheets and would recommend these kinds of queries to people looking to get a bit more creative with their reporting. There is a lot of great stuff you can do with this as a foundation ;-).
Ross Tavendale
Superstar

Knows everything… well, almost.

Harry Sanders
Cheers Harry. It would be great to get a central repository going of all the sheets people are making in the industry
Milosz Krasinski
Expert

Provides valuable insights and adds depth to the conversation.

No doubt about it. Google docs as a whole and any sort of API and queries things will be in more value for the next coming years . Good point ! - when you deal with a large data you need to work on something visually easy digestible. Nice one Ross - remember you used to teach me these tricks and half of them I already forgot.
Ross Tavendale
Superstar

Knows everything… well, almost.

Milosz Krasinski
Thanks Milosz - looking forward to seeing some of your creations :-)

The real brains at Type A Media is Michelle- she is the queen of Google Sheets
Peter Mead
Master

A veteran community member.

Hey nice tutorial @Ross! Some really handy info here for people to pick up on :)
Ross Tavendale
Superstar

Knows everything… well, almost.

Peter Mead
Cheers Peter - thanks for taking the time to comment
Enthusiast

Occasionally takes part in conversations.

I have been using Google Sheets for a while now and been using mostly VLOOKUP, etc. But I didn't know Google sheet has these databases like queries. This would make things easier to do when it comes to dealing with huge data. Thanks for explaining this.
Ross Tavendale
Superstar

Knows everything… well, almost.

Chris Wagner
No worries Chris. The real power comes when you start to use BigQuery with sheets- you can pull millions of cells instead of the max 2mil. It’s now directly integrated with sheets.

Send feedback

Your feedback must contain at least 3 words (10 characters).

We will only use this email to respond to you on your feedback. Privacy Policy

Thank you for your feedback!