How to Use Google Sheets for SERP Analysis

Get 300 checks per monthabsolutely FREE!

No credit card needed. No strings attached.

One of the most important aspects of seo strategy today is really understanding...
00:00
one of the most important aspects of seo strategy today is really understanding the intent of the query and the results that are shown based on that intent in this video i'm going to show you how you can use google sheets to analyze the search results and extract important information that you can use to better formulate your strategy before we get into content i just want to say thanks for watching this video and if you found our content helpful please hit the subscribe button we create new content each and every week to help you with your digital marketing and seo strategies as i said in the opener we're going to walk through how we can leverage google sheets to do serp analysis i'm going to show you how to pull all the links from the search results and put them into a google sheet and then how we can use a few simple functions in order to extract information that can be extremely helpful when it comes to developing a powerful search strategy so let's go so one of the fundamental practices of a good seo strategy is actually understanding intent and also understanding what the serps look like and comparing the information that
Google's pooling and showing in the serps and then understanding how...
01:18
google's pooling and showing in the serps and then understanding how we might need to optimize our page so that we have a better chance of actually ranking so one of the things i advocate for all the time is actually looking at the search results understanding what the search results page looks like so for instance this term seo we've got a knowledge panel here we've got books right over here we've got tons of images up here in the knowledge panel we've got the number one listing here we've got people also asked questions and then a number of links as well as a local pack there's quite a bit of information just within this one search result now it can be overwhelming when you come here and you go what do i do with this information how can i see it in a way that will maybe help me analyze it a little bit better and actually use it in my seo strategy and this is where leveraging google sheets is really powerful but how do we get all this information into sheets and then how do we extract the information we need in order to help us make better decisions well that's we're going to be walking through in this video today i'm going to
Show you exactly how you can get these urls off of the search results put them...
02:19
show you exactly how you can get these urls off of the search results put them in a sheet and then use google sheets to do the rest of the pooling of the data for you using some extractions now it's not as scary as it sounds it's actually a lot easier to do than it sounds and it can be really helpful when you're building your strategy so you understand the terms and the structure of what your competitors are using and how you can use that back in your strategy now once you get this information into sheets it's actually much easier to see the title tags and the meta descriptions and maybe some of the concepts that they're using to allow themselves to earn those powerful results now there's a couple things that we're going to do um the goal of this video is to make sure that it's completely free so you're not actually going to have to buy any tools or use anything that's going to cost you money you're going to be able to do it free extract it straight from search results put it into a google sheet and start to work with it there now i'm going to have you just create a bookmarklet it's not as scary or hard as it sounds and we'll walk through that process first so you can actually extract this information so what this bookmarklet will do so right now i actually have it installed
Here it's called extract urls you can name it whatever you want...
03:20
here it's called extract urls you can name it whatever you want is it's going to pull all the urls here and put them in just a list all i do is click it and there it is so as you can see i have all of the urls even the urls that are behind the people also asked so we've got these search results and like you said all i did was click this button and it extracts all the urls for me i can easily just copy now and paste this into a google sheet and start working with it but how did i actually get this to work well i used a bookmarklet which is right here now if i go ahead and go ahead and hit edit you'll see that there is a line of javascript code right here that's actually triggering this you can simply just open a page like you know google.com and i can go ahead and take this url here and just drop it in to this bookmarklet then you just need to take the code which which will be available on our website i'll make it available there i didn't write the code i've got it from another seo but this code is is is pretty standard
Uh and what you would just do is take this code and you go over to the bookmark that you...
04:20
uh and what you would just do is take this code and you go over to the bookmark that you just dropped so google.com which i don't really need this one right i right click it and i click edit where it says url you just replace that with the with the script that is from over here so again edit we'll go ahead and copy this go back here click edit and paste new extractor and that's it now it will work so once again i go back here i click my new extractor it does it for me just pulls them immediately now if you want to pull a hundred results you'll just need to change your settings in your search settings and you can pull all the way up to a hundred and now it's going to show 100 search results it's going to extract 100 urls straight from your search results so whatever you set the settings here to whatever is on the page it's going to extract that for you once you have this list you can easily take it once again and copy it and then we'll go over to google sheets
And paste it in i like to leave the top one open because you're going to want to...
05:27
and paste it in i like to leave the top one open because you're going to want to go ahead and hit url in here because we're going to start doing some analysis now that was an easy way to extract the urls as you can see we've got all of those urls here from search and we can start to do some analysis on it now that we have our list over here one of the things we can do is is organize it if you want to just get rid of these columns you know you can sim you can just go through and delete all the spaces that way it's looks a little bit nicer so i'm going to go ahead and do that now all right so now that we've removed these these unwanted rows now we can really get into analyzing the search results themselves the first thing i like to look at is serp features so i'll add a column for search features and i'll actually go back to the search results and i'll take a look at them so the first thing i'm going to see here is moz beginner guide to seo notice underneath here we've got site links so we've got four different site links right under this beginner guide to seo if i go back to this link here we have the beginner guide to seo and we've got the four
Site links right underneath it so i'll go ahead and annotate this...
06:31
site links right underneath it so i'll go ahead and annotate this for myself again this process is a little bit manual but this is really important because it really helps you understand the search results so there we go we have the four site links we continue to walk through these search results and we see okay what's next we've got what is seo search engine optimization 2020 what is seo so that's the organic listing here but notice here we've also got it down here so these four links are actually the people also axed boxes so this is why again you want to walk through it i've got what is seo i've got four people also ask links so there's still information right here in the search results i'm not going to open these because when you do that it expands and expands and expands but i'm going to go ahead and now tag these as people also asked we've already gotten eight links here before we even saw the second search result that's why these cert features are so important because they can really change the positioning of your site even if you're not showing
Visibly right now on that first page if you've been able to push yourself up...
07:33
visibly right now on that first page if you've been able to push yourself up here into the people also asked that's a huge jump you've actually jumped the results here for the what is seo and google is expanding and adding more and more people also asked to a number of search results we've seen that recently rise so these are definitely important parts of the searchers journey because it can help you really target people with specific questions all right so jumping back to the search results we've got the what is seo we have the search engine lan article here we've got the support article we've got neil patel we've got some of this local pack here we've got the wikipedia we've got word stream again we just want to make sure that everything is annotated the way that it should notice here that it's even pulled in the local pack here so i have to go ahead and annotate these as local packs this is important so you again you know all the different opportunities that you have here in search we've got wikipedia we've
Got word stream and again we want to make sure that we don't have any other site...
08:34
got word stream and again we want to make sure that we don't have any other site links that might possibly be showing up here in the results so there's a little bit of back and forth when you're doing this early on just to just to make sure you actually know what's happening we've got some top stories here entrepreneur.com search engine journal before we get to search engine watch so here we go we have some top stories so again we want to make sure that we've annotated those top stories so this will be the most tedious part of the process you've got the wikipedia up here and this was the the site link within the search results here but you've also got the final one which actually pulls over here and this is the knowledge panel so we definitely want to add that in as well so this is a manual process the cert feature part is 100 a manual process but it's an important process because you can see this is one query and typically we'd say okay we've got 10
Blue links in the search results well as you can see here...
09:48
blue links in the search results well as you can see here for one query we've gotten over 22 different links on this page now some of them are site links some of them are people also ask some of them come in the form of a local pack some of them come in the form of top stories in knowledge panel and if we look at uh the knowledge panel you can see there's more information too we talk about ranking cost and tips and basics and all of these expand where there's more and more information underneath each and every one of these so there's a ton of competition there's a ton of real estate that maybe we didn't even realize was there but now we've got to understand what type of sites what type of content is google considering how should we structure our pages how should we target the different keywords that we're looking at and we want to do this for each one of our terms as we go through it now we want to extract some information that can be helpful to us like we know that title tags are important we know that h tags are important we know that meta descriptions can influence click-through rate but can also tell us a little bit more about the page itself
So instead of now going through here and copy and pasting title tags...
10:48
so instead of now going through here and copy and pasting title tags meta descriptions and looking at each page and pull the h1 we can use google sheets to help us do that so now we're going to use something called import xml and this is going to allow us to actually extract the titles from these pages and put it right here into our sheet without us having to do any copy and pasting i'm going to start by hitting the equal sign and start typing out import and you'll see import xml is one of the options now we want to find the url first so it's easy to do that we just go ahead and click a over here and then hit comma now we need to give it an xpath query there's a lot of ways to find these but some of them are pretty standard across the web and a title tag is one of those so you're going to go ahead and open your quotations here and then inside of that you're going to do forward slash forward slash and lowercase title so sometimes this will happen if i go ahead and pull this down i'm actually going to create an error because up here will create an error because it had information to pull down further so i still want to be able to pull this
You know further down my site without getting these errors...
11:50
you know further down my site without getting these errors if this happens to you there's an easy way to fix this where you would just take this first one you hit copy you paste special and paste the value only and now you can do the process again starting here so again we would do import xml the path once again open and close go back to it title so the moz pages are doing this quite frequently which is kind of funky and frustrating so you might have to do a little bit finagling depending on what you're working on but normally if i pull all the way down usually we'll be okay like in this case right here the moz articles definitely have extra titles and it could have to do with the way that their guides are set up they might have multiple titles there for some reason but as you can see as i just pulled that down what it's done is it's given me every single one of these titles
For each one of these pages now i will have to go back now and extract these...
12:50
for each one of these pages now i will have to go back now and extract these ones specifically because again see it can expand because of rewrite another thing you can do if you want to make sure that this doesn't happen is you can use something called transpose if you put transpose at the front of this what it'll do is it says instead of pushing that data down push it to the side and so for these mods ones we can absolutely do that and it will solve that problem for us at least for right now until we we head to the next step so i'm gonna go ahead and do that that way i don't have to actually go copy and paste now just a little bit of my history i don't have a background in learning how to do excel honestly i'm not this is not something that i would have said i would ever be good at but by practice and trying these things over and over again i've been able to get much better at it and it's really helps speed up the research process when it comes to understanding the search results now again if i want to get rid of these extra things over here i can hit copy
Paste special values only and that will go ahead and take care...
13:54
paste special values only and that will go ahead and take care of that so even with that little bit of a hiccup in the process that was a lot faster than me going page by page trying to understand exactly how i was going to achieve this now there's a number of other things now we can extract as well we can do the description we can use the h tags there's a lot of cool things we can do now there's a great resource that was created by semrush and it's extremely helpful and i will also make sure that we link to this as well because this is going to give you some shortcuts that way you have to don't to figure it out yourself i'm a huge believer in finding things that other people have already created and using them to make our lives a lot easier so here's an article it's the tin sheet formulas every seo should know this article talks about you know a number of sheet formulas that we can use in one of the sections section 4 talks about scraping data and importing it using xml which is exactly what we've been doing this is going to give us a cheat sheet
For things like meta description which is the next one we're gonna look at so...
14:55
for things like meta description which is the next one we're gonna look at so this allows us to use something that somebody's already created and make our lives a lot easier so now we're gonna go ahead and create a column and call it description we're gonna follow the same process as we did before import xml we still need the url just this time we're going to be looking at the meta description so you can just take this right here copy it and paste it make sure you close that and hit ok now a cool thing if you just double click this button it'll actually pull it all the way down and extract all those meta descriptions now some of these that might not have been able to because it didn't actually have one like neil patel doesn't have a meta description needed as seo tech pro i need to do the wikipedia page in some cases they actually might it's just they might you know not allow us to scrape that data and the last thing we might want to look at would be the h1 tag
Why the h1 tag typically this is where people put their core topics their keywords that they're...
15:59
why the h1 tag typically this is where people put their core topics their keywords that they're targeting it gives us a little bit more context on the page itself so again we want to go ahead and import xml click there hit our comma we can go back to this as well this one's a pretty easy one the h1 it's just the same as the title but h1 i'll just do copy and paste here and then close once again we've got two h1s on this page so again in case this happens we're going to go ahead and add what we learned before and add the transpose this makes sure that if there are multiple h1 tags they're going to get pushed to the side and as you can see a number of these pages do have multiple h1 tags there's not a huge issue with that but again it can help give us more information so now we've done we've pulled our urls we've looked at the cert features which we've done manually but then we pulled titles meta description and h1 tags all into a google sheet now we can go through these pages and look at some of the opportunities look at the title tags
That are ranking what are some of the things that people...
17:00
that are ranking what are some of the things that people are talking about when it comes to this query so the query of seo we've got a beginner's guide beginner's guide beginner's guide we've got what is seo this is very top of the funnel content as you can see for a query that's extremely broad do-it-yourself tips seo explained what is seo so people are asking questions answering questions to these basic ideas of what is seo how does it play a role what should i be doing we talk about search engine optimization from wikipedia and understanding it as an entity and what it means from a deeper standpoint as you can see most of this content is very much top of the funnel but i can use this now to inform the type of content i need to create because if i want to be considered ranking i have to have expertise i have to be an authority i have to be you know correct in what i'm saying and be trustworthy enough that google will grant me a higher visibility but i also need to make sure that i'm matching the intent and typically what google is saying when it comes to this term of seo it's a very top of the funnel intent i
Can look at the meta descriptions and understand how can i structure these...
18:00
can look at the meta descriptions and understand how can i structure these in order to make sure that mine is is clickable you know some of the things that moz is doing here is google seo best practices they have it all in caps you know trying to entice that click we've got some that might be a little more in-depth that go a little bit deeper into it like right here when we talk about the the seo the technical part of the seo guide and understanding responsive design and the technical aspects so these can also give us ideas of concepts we might want to cover it needs to be unique it needs to be creative it needs to be standing out but now instead of looking at the search result and going wow there's so much going on here i can pull it all into google sheets and i begin to do my analysis this allows me to collaborate with my team we can highlight different things we can ask questions together this is a really cool tool and it's all online and we use really simple formulas in order to extract information to make our lives a whole lot easier i'm actually going to share this sheet with you and i'm going to put the codes in it in the first line for you give you a little bit of a a head start but i'm also going to share on our website if
You go and head over there to their blog we're going to be sharing the exact code...
19:00
you go and head over there to their blog we're going to be sharing the exact code right there within the top so that you can just easily copy and paste it and use it yourself there's a number of other things that you can do check out some of the other formulas here if you're curious but this is by no means the extent of everything there's a number of other great tools that you can use for web scraping if you're really interested and you're in seo and you haven't used screaming frog yet check that out that's probably one of the best tools on the web it's something that we use all the time here at our agency let me know if you have any questions on what we covered today and until next time happy marketing [Music] you

Related Topics

Get 300 checks per month absolutely FREE!

No credit card needed. No strings attached. 👍