A good SEO strategy starts with great research. Gathering all the necessary SERP data can be overwhelming. In this video, I’ll share how you can use Google Sheets to easily extract the information you need from the search results. The best part, you can get all this information 100% free!!!
Table of Contents
Resources:
Bookmarklet Code:
Other bookmarklets: https://detailed.com/chrome-bookmarklets-seo/
To extract Titles: =IMPORTXML(URL,”//title”)
To extract Meta Descriptions: =IMPORTXML(URL,”//meta[@name=’description’]/@content”)
To extract H1s: =TRANSPOSE(IMPORTXML(A2,”//h1″))
More Google Sheet extractions: https://ahrefs.com/blog/google-sheets-formulas-seo/
Google Sheet SERP Analysis Template: https://docs.google.com/spreadsheets/d/184mMyL-__veJ8f4D2SElLsAkl2PjIlEBcGIDiqYnyEM/edit?usp=sharing
Video Transcript:
One of the most important aspects of SEO strategy today is 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 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.
Understand What Is In the SERP
One of the fundamental practices of a good SEO strategy is understanding intent and also understanding what the SERPs look like and comparing the information that Google is pulling 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. One of the things I advocate for all the time is 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 Ask questions, and then a number of links, as well as a Local Pack. There’s quite a bit of information 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 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 what 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 in a Sheet, and then use Google Sheets to do the rest of the pulling of the data for you, using some extractions.
Easier Than It Sounds
It’s not as scary as it sounds. It’s a lot easier to do than it sounds. And it can be 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.
Once you get this information into Sheets, it’s 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 of things that we’re going to do. The goal of this video is to make sure that it’s completely free. So you’re actually not 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.
Create a Bookmarklet
I’m going to have you create a bookmarklet. It’s not as scary or hard as it sounds and we’ll walk through that process first so that you can extract this information. So what this bookmarklet will do, so right now, I have it installed here. It’s called Extract URLs. You can name it whatever you want. Is it 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 Ask.
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 copy and paste this into a Google Sheet and start working with it. But how did I get this to work? Well, I used a bookmarklet, which is right here.
Now, if I hit edit, you’ll see that there is a line of JavaScript code right here that’s triggering this. You can simply open a page, like google.com and I can go ahead and take this URL here and drop it into this bookmarklet. Then you need to take the code, 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 pretty standard. What you would do is take this code and you go over to the bookmarklet you dropped. So google.com, which I don’t really need this one. And right-click it, and I click edit.
Where it says URL you replaced that 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, pulls in immediately.
Now, if you want to pull 100 results, you’ll just need to change your settings, and your search settings, and you can pull all the way up to 100. And now it’s going to show 100 search results. It’s going to extract 100 URL’s straight from your search results. So whatever you set the settings here to, and whatever’s on the page, it’s going to extract that for you. And 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 hit URL in here because we’re going to start doing some analysis.
Time to Do Analysis
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 organize it. You want to get rid of these columns, you can go through and delete all the spaces, that way it looks a little bit nicer. I’m going to go ahead and do that now. Now that we’ve removed these unwanted rows, we can get into analyzing the search results themselves.
The first thing I like to look at is SERP features. I’ll add a column for SERP features and I’ll go back to the search results and I’ll take a look at them. The first thing I’m going to see here is Moz, beginner guide to SEO. Notice underneath here, we’ve got site links. 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. I’ll annotate this for myself. Again this process is a little bit manual, but this is really important because it helps you understand the search results.
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? That’s the organic listing here, but notice here, we’ve also got it down here. These four links are the People Also Asked boxes. This is why, again, you want to walk through it. I’ve got what is SEO. I’ve got four people who also asked links. 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 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 SERP features are so important because they can 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 here into the people also ask, 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. These are definitely important parts of the searcher’s journey because it can help you target people with specific questions.
Jumping back to the search results, we’ve got the “what is SEO.” We have the Search Engine Land 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 Wikipedia. We’ve got WordStream. Again we 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. I have to annotate these as local packs. This is important so, again, you know all the different opportunities that you have here in search. We’ve got Wikipedia, we’ve got WordStream. 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. There’s a little bit of back and forth when you’re doing this early on, just to make sure you know what’s happening. We’ve got some top stories here, entrepreneur.com, Search Engine Journal, before we get to search engine watch.
A Manual Process
Here we go. We have some top stories. Again, we want to make sure that we’ve annotated those top stories. This will be the most tedious part of the process. You got the Wikipedia up here, and this was 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. We definitely want to add that in as well.
This is a manual process. The SERP feature part is 100% a manual process, but it’s an important process because you can see this is one query. Typically we’d say, okay, we’ve got 10 blue links in the search results. As you can see here for one query, we’ve gotten over 22 different links on this page. 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 and knowledge panel, and if we look at 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. There’s a ton of competition. There’s a ton of real estate that maybe we didn’t even realize was there.
Working With Import XML
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. We want to extract some information that can be helpful to us. We know that title tags are important. We know that H1 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. Instead of now going through here and copy and pasting title tags, meta descriptions, and looking at each page and pulling each one, we can use Google Sheets to help us do that.
Now we’re going to use something called import XML. This is going to allow us to extract the titles from these pages and put it right here into our sheet without us having to do any copying and pasting. I’m going to start by hitting the equal sign and start typing out import. You’ll see import XML is one of the options. We want to find the URL first.
It’s easy to do that. We go ahead and click A over here and then hit comma. Now we need to give it an X path query. There’s a lot of ways to find these, but some of them are pretty standard across the web. A title tag is one of those. You’re going to open your quotations here. Then inside of that, you’re going to do forward slash, forward slash, and lowercase title.
Sometimes this will happen. If I go ahead and pull this down, I’m going to create an error because up here we’ll create an error because it had information to pull down further. I still want to be able to pull this 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 hit paste special and paste the value only. And now you can do the process again, starting here. Again, we would do import XML, the path, once again, open and close, go back to it. Title.
The Moz pages are doing this quite frequently, which is kind of funky and frustrating. You might have to do a little bit of finagling depending on what you’re working on. But normally if I pull all the way down, usually it will 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 pulled that down, what it’s done is it’s given me every single one of these titles for each one of these pages. I will have to go back now and extract these ones specifically, because again, see it can’t 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, so for these Moz ones, we can absolutely do that. It will solve that problem for us, at least for right now, until we head to the next step. I’m going to do that, that way, I don’t have to go copy and paste.
A little bit of my history. I don’t have a background in learning how to do Excel. Honestly, 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 helped speed up the research process when it comes to understanding the search results. 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 of that. 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.
Create Description Column
There’s a number of other things now we can extract as well. We can do the description. We can use the H1 tags. There’s a lot of cool things we can do. 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 so that way you don’t have 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. Here’s an article. It’s the 10 sheet formulas every SEO should know. This article talks about a number of sheet formulas that we can use in one of the sections, section four, 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 going to look at. This allows us to use something that somebody has already created and make our lives a lot easier.
Now we’re going to go ahead and create a column and call it description. We’re going to 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 take this right here, copy it, and paste it. Make sure you close that and hit okay. Now, cool thing, if you double click this button, it’ll pull it all the way down and extract all those meta descriptions.
Now, some of these it might not have been able to because it didn’t actually have one, like Neil Patel doesn’t have a meta description. Neither does SEO tech pro. Neither did the Wikipedia page. In some cases they actually might, it’s just that they might not allow us to scrape that data.
Don’t Forget the H1 Tag
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 targeting. It gives us a little bit more context on the page itself. Again, we want to 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 closed. Once again we’ve got two H1s on this page. In case this happens, we’re going to 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. 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.
Look at It All Together
Now what we’ve done, we’ve pulled our URLs. We’ve looked at the SERP 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 are talking about when it comes to this query? 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 correct in what I’m saying and be trustworthy enough that Google will grant me 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 in order to make sure that mine is clickable. Some of the things that Moz is doing here is Google SEO best practices. They have it all in caps, trying to entice that click. We’ve got some that might be a little more in-depth that go a little deeper into it, like right here when we talk about the SEO, the technical part of the SEO guide, and understanding responsive design and the technical aspects. 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 could 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 simple formulas in order to extract information, to make our lives a whole lot easier.
I’m 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 head start, but I’m also going to share on our website; if you go and head over there to the blog, we’re going to be sharing the exact code right there within the top so that you can 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, 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’ve covered today, and until next time, Happy Marketing.
