Google SERP Result checker

Problem we are solving:

Any Digital Marketer today needs to know where their site ranks on Google or just look at the competitive landscape for the ‘search term’. And ideally be able to just see this in an Excel Spreadsheet and for it to happen in seconds. This blog shows you just that. Using Excel’s incredibly powerful Addin called Power Query we solve for this in seconds – once the Query is setup.

What you need?

  1. Excel 2013 or Above
  2. In Excel 2016 Power Query is already included but in 2013 you would need to Install Power Query for Excel (here)

Let’s get started

The first step is to Write a Function in Power Query that will extract all URLs from the Google Search Results Page and filter only the Search Results. This is done easily using the code below.

This tutorial is made using Excel 2013 so some screenshots might different in later versions.


In order to write a Function, follow these simple steps.

  1. Under the Power Query Tab click on From Other Sources and choose Blank Query
excel1
  1. This will open the Query Editor Screen. Rename the Query Name to ‘fnGoogleResults’.
excel2
  1. After this go to the Advanced Editor section and Copy the below Code. While we shall not try to explain this code in this blog, at a high level, the code makes a list of all the URLs on the page. In the case of Google the Search Results start with ‘/url?q=’. The code filters only those URLs that start with this prefix, giving you just the Google Search Results.

(SourceURL as text) =>

let

//Get HTML source

Source = try Text.Lower(Text.FromBinary(Web.Contents(SourceURL))) otherwise “”,

//Function to find each link

GetLink = (Counter as number, AttributeDelimiter as text) =>

let

CurrentLink =

Text.BetweenDelimiters(

Source,

“href=” & AttributeDelimiter,

AttributeDelimiter,

Counter

)

in

if CurrentLink=””

then

{}

else

List.Combine({

{Text.Clean(Text.Trim(CurrentLink))},

@GetLink(Counter+1,AttributeDelimiter)}

),

//Call function

Output1 = GetLink(0,””””),

Output2 = GetLink(0, “‘”),

final_Output = List.Distinct(List.Combine({Output1,Output2})),

Output = Table.FromList(final_Output, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

TheEnd = Table.SelectRows(Output, each Text.Contains([Column1], “/url?q=”) and not Text.Contains([Column1], “webcache.googleusercontent.com”))

in

      TheEnd


  1. Once done, you will see an Input section like below. Test your Code by pasting the following Google Search query: https://www.google.co.in/search?q=best+SEO+tips. This should give you the search results on the 1st page of Google for Google India. You can change the Search term to your desired country and try.
excel3
  1. Hit, Invoke and you have your Google Search Function ready. That’s it. The hard work is done.
excel4
  1. Now we need to create a List and Invoke this function across multiple Search Terms. For this create a simple Table like below. In the Search Term column insert the Search terms you want and the Google Search URL is created automatically using this formula: =”https://www.google.co.in/search?q=”&SUBSTITUTE([Search Term],” “,”+”)
excel5
  1. Next we need to pull this Table into Power Query. For this Select the above Data, and under the Power Query menu click on ‘From Table/Range’
excel6
  1. This would automatically open the Query Editor screen. As before Rename the Query to something sensible like ‘Google Data’.
excel7
  1. Now Under Add Column, goto Invoke Custom column and call the Function ‘fnGoogleResults’ we made above. And hit ok.

excel810. The Search Data shows up in a table. Behind this table is the Search Data we want. Click on the Arrow Icon at the Top Right Corner to Reveal the actual Search Data.

excel911. And just like that you have the first page results for your Search Terms.

excel10

12. Finally we shall clean the Search Results to get Clean URLs. Firstly, we shall Replace /url?q= the Blank. To do this just Right Click on the new Column and click on Replace Values. Finally you can also Split the Results by ‘&sa=u’. Remove the extra column and you have clean Results as below.

excel1213. Now Goto the Home Button and just Load the Results the Results to a New Table / Sheet.

14. Now you can just add multiple Search Terms and get the Search Results instantly


Simple Enhancements Possible to make better.

  1. Setup rules to Ignore Domains like Youtube.com, wikepedia etc from the Search Results
  2. Create a simple function to get the Domain Name from the Search

If you are looking for sample Excel Sheet please do get in touch with rohan@cashkaro.com.


SEO Made Easy With Power Query – More Useful Blogs

How to Check if a URL is 404?
Get Twitter Followers using Power Query

📢 Hungry for more deals? Visit CashKaro stores for best cashback deals & online products to save up to ₹15,000 per month. Download the app - Android & iOS to get free ₹25 bonus Cashback!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

twenty − seven =