Ever wanted to scrape just a handful of Google SERPs inside a Google Sheet?
Or wanted to group a bunch of keywords, to better de-duplicate them and isolate unique topics?
Well, have I got a Google Sheet for you!
My SERP Scraper & Keyword Grouping Google Sheet will do all of that for you.
Check out the initial video below;
And the latest update can be watched here;
In just a couple of clicks, you will get full SERP data for thousands of keywords, and will even get them grouped up by SERP similarity.
Nothing I have seen will give you access to RAW SERP data, so easily & efficiently – let alone process them into the groupings for you!
SERP data is now available & accessible en-masse.
No coding.
No learning how to run a Python script.
No overpriced, under-delivering SAAS.
Just paste keywords, GET SERPs.
This is what you need to do.
How to scrape Google SERPs inside a Google Sheet
1. Signup for Serper.dev and get an API key
No affiliation with them, they just have a low-cost API with an awesome response time.
2. Duplicate the Google Sheet, and add your API key into the settings
The API key goes into the API key cell, on the settings sheet of the Google Sheet.
This is where you can also edit the Google Location, or the Language, of the SERPs you’ll scrape. Tweak them as needed, or leave as is for a default US search.
Other settings on this tab include disabling the PAA & Related Keyword extraction, which may speed up processing and allow you process more keywords without overloading Google Sheets. Just enter FALSE in either of those cells.
You can also increase the batch size, which is set at 30 due to a 50-per-second query limit for Serper.dev. You should be able to increase this above 50 safely, as its not doing a batch per second, but 30 worked fine and is still fast enough.
3. Add keywords with their search volume
Add the keywords you’d like to scrape the SERPs for.
You only need the keywords for the SERPs, but you will need volume to group them. It won’t work without it.
I’d recommend exporting the volume from your favourite tool so that its consistant. You can instead just click the new “Get Search Volume” button instead though!
For this to work, you will need to add your DataforSEO API username & password to the sheet. You can find the guide on this here but you just need to signup to DataforSEO here and then access your API dashboard here for the password.
The script makes a direct call to DataforSEO with your details, and we do not store or log any of the details used, or data sent/returned.
You can use one of the location codes given on the settings page, which line up with the dataforseo location codes.
If you need a different location, you can download the full DataforSEO location list from here: https://docs.dataforseo.com/v3/keywords_data/google_ads/locations/.
4. Click ‘Extract SERPs’
Once the keywords are loaded, just click extract SERPs.
This will then process keywords in the batch size mentioned on the settings page. Serper.dev allows 50 connections a second, so I set it to 30 to keep within the limits. Increase and test if you need it faster!
Each batch will add “SCRAPED” in the status column for the keyword.
You may get a script warning. You just need to click advanced, and then allow/proceed. It may then start running, but if you don’t see anything in the ‘status’ column, it won’t be running.
The warning will look like this:
Ciick Advanced, and then click “Go to SerperScraper”
It will show your email, and not give any external access to the sheet. This script just sends some keywords data externally for the requests, and you can view the entire scraper script yourself.
Note: If the scraping times out, just click extract again. We only get 6(?) minutes to run a script, so if the scraping takes longer than that it’ll freeze up. Pressing extract will just continue from where if left off though. 2,500 keywords should process within the 6(?) minutes, so this should only be an issue with a higher amount of keywords.
How to run keyword grouping based on SERP Similarity
Click the button to run the script.
It will then process, and load up the groupings and parents in their columns.
That’s it. You’re done.
OPTIONAL: Adjust SERP Similarity setting
The optional step here is to tighten, or loosen, the SERP similarity setting.
This setting allows you to flag how many URLs you’d like to be in the serps.
0.1 is 1, 0.2 is 2, 0.3 is 3, etc.
A good starting point will be 0.4 or 0.5, but depending on your keyword set, along with what you’d like to get out of the grouping, you could adjust this to 0.2/0.3 to be broader matching, or 0.6/0.7 for even tighter groups.
Broader matching could be for higher level categorisation, or may content hub type pages.
Tighter matching is best for keyword mapping, classification, or just general keyword filtering to remove duplicates/close-duplicates.
Plenty of other uses though!
Running a competitive traffic analysis
One of the newest features of the SERP sheet is the competitive traffic analysis.
This features looks at a URLs ranking position, the search volume for a keyword, and an estimated CTR for that ranking position.
Here are the simple steps to run this analysis;
- Ensure you have search volume for the keywords, that the SERP scrape was run, and that you’ve grouped the keywords
- Press the “Estimate traffic” button
Thats it!
The script will run, process the results, and then fill out the domain performance & URL performance sheets.
Domain Performance
The domain performance tab offers a top domains, and top urls by domains views.
The top domains is just that, a list of all the domains with their total estimated traffic from the keyword data, and their market share within the keyword group.
The Top URLs by Domain will list out all the domains again, but this time will also let you see the URLs that show up for each domain, all broken down by their estimated traffic, along with their best ranking (min rank).
There are plenty of other ways to pivot this data, including keywords per URL. Just duplicate or modify these pivots and go wild with it.
URL Performance
The URL performance gives a similar view as the domains, but for the URLs.
First you get the top URLs, each with their est traffic & market share;
The next view breaks the top URLs down by each group;
A good overview to quickly see the competing URLs by group, and be able to understand your competitors at the group/topic level, and not just keyword level.
Download the sheet
Ready to get stuck in?
You can get access to the sheet right here.
Let me know if you have any issues at all, as we only have a small user base at the moment!
Uses for the sheet
Plenty of different use-cases for the sheet, and I will build out some additional how-tos in time.
- Once off SEO market analysis like this one I started
- De-duping keywords based on highly similar terms
- Isolating core content topics from large lists
- Determining word associations like fridge = refrigerator
The list goes on! Let me know your use-case.
Any issues, questions or feedback?
Would love to hear any feedback or answer any questions you have.
If you’ve got an issue, just throw in a comment and we can work through it.
I will provide some deeper-dives and some more actionable information off the back of this shortly, for now though, enjoy the sheet!
On pressing “Get Search Volumes” it saying – Exception: Request failed for https://api.dataforseo.com returned code 401. Truncated server response: { “version”: “0.1.20241227”, “status_code”: 40100, “status_message”: “You are not authorized to access this resource. See your login de… (use muteHttpExceptions option to examine full response)Details
Hi Tarun – Have you added your dataforseo API user & password to the settings? the API password is different to your account login details. You’ll also need to have money on your dataforseo account to pay for their volume data.
Hello! I keep getting this error when I click any of the buttons:
There was a problem
Script function [function name] could not be found
I can run the scripts just fine if I go straight into the Apps Script page, but would be best if I can just run it through the buttons.
On a side note — is there an easy way to remove old data so with the next crawl it only shows that data? SERPs, Related, and PAA tabs keep the previous crawls data.
Thanks!!
Sorry to hear about the problem Nate, did you get the approval message when you first run the script? You should get a little popup saying it will run and you need to approve the script. Other than that, I haven’t seen this issue yet. Maybe try re-link the buttons to the functions. You just need to enter the function name on the button. Maybe create a new button yourself, and you might not run into the isssue.
I think when you run directly you won’t get this, and thus why it works.
No way to easily clear the data yet, but will add a clear button in the next update! Should be this week.
Hi Sam!
Eternally grateful for putting together this post and signed up with DataforSEO using your link. Going to proactively reach out to serper.dev and ask them to be kind to share any revenue that signed up through your channel (or, to at least point out that they should track it). In addition to that would love an opportunity to buy you a coffee for all the time you’ve saved me and my team with scraping organic listings.
Im sure you get this a lot but my question is in regards to wether you’d be open to putting together something similar — only this time for paid Google Ad listings. Let me know, if there is anything you could recommend using for this, and, needless to say, if a custom solution is in the works, I’d be more than happy to help out with whatever I can to accelerate the process.
Kind Regards,
Vitaly
Hi Vitaly!
Appreciate the comment and the feedback. No plans for any paid ad listings, as I don’t do anything with that at the moment.
I’d recommend just using dataforSEO for the data, and you could possibly just run the script through chatgpt and give it the paid dataforseo endpoint and have it modify it all for you. Will just require a bit of a structure modification to make it work.