How To Get An Automated Report On Search Queries

Top Tips!

1. To follow this blog successfully, you will need to make sure that you already have an account for Google Search Console.

 

Benefits 

• You can ensure that you have your search query data safely secured. 
• This is a process you will only need to do once, as after you have set everything up, you will automatically receive a report on a monthly basis. 

 

Set-Up

 

• You need to be logged into the relevant account for Google Search Console.

 

Add-On Installation – “Search Analytics For Sheets” 

1. To begin, you need to install the add-on.
2. Now you will need to open a new Google Sheet – give it a name.
   • This is where you’re going to keep all the reports stored.
3. Click on “Add-ons” and then “Get Add-ons”
4. You need to input “Search Analytics for Sheets” into the search bar, and then install it – you’re going to need to authorise this installation using the relevant Google account.

 

First Data Pull 

This will be taking into account data from the last two whole calendar months – this is something you’re only going to need to do one time, during the time you are working on forming your report the first time. 

• Think of it this way, if you were pulling data in September, then the two calendar months you’d look at would be:
  • 1st July – 31st July
  • 1st August – 31st August

 

1. You’re going to need to select these in the following order:

• Add-ons
• Search Analytics for Sheets
• Open Sidebar

 

2. You will see the requests tab – this is where you’re going to need to input the relevant data for your site. Follow these steps:
   • Choose the site that you’re intending to get data from
   • You’re going to need to click on the relevant time-frame (a full month, the month before the prior one)
   • You need to add the following in the “Group By” section:
     • “Date”
     • “Page” 
     • “Query”
   • Here are some sections you shouldn’t change:
     • “Aggregation Type” = “Default”
     • “Rows Returned” = “Everything”
     • “Results” = “Create New Sheet”
   • Now you need to select “Request Data”
3. You’re going to need to add another sheet – give it the name of the initial three letters relating to the relevant year and month – i.e. July 2019
4. Do the same for the next full month – do this by repeating stages 2 and 3. 
5. Now we’ll explain what the different data columns mean:

• • Column 1 – Date the Data Search was conducted 
  • Column 2 – Google URL Ranked 
  • Column 3 – Google Ranked Search Term (for the URL) 
  • Column 4 – Estimation – how often the search result was clicked
  • Column 5 – Estimation – how frequently the page appeared in search results
  • Column 6 – Estimation – the percentage of the amount of times the page was selected vs. how many times it actually appeared in search results.

• • Column 7 – Estimation – URL’s position when appearing in search results 

 

 

Ensure You Set Up A Back-Up 

To set this up properly as an automated process, then this means you’re going to need to ensure you have a back-up enabled. 

1. You’re going to need to select these in the following:

• • Add-ons
  • Search Analytics for Sheets
  • Open Sidebar

2. You need to select the tabs labelled “Backups”
3. Now you’re going to need to input information into these boxes:
   • Choose the site you’re looking to get data results from 
   • Now you’re going to need to select the relevant month range
   • For the search type, click on “web” 
   • You need to add the following in the “Group By” section:
     • “Date”
     • “Page” 
     • “Query”
   • “Aggregation Type” = “Default”
   • “Rows Returned” = “Everything”
   • Ensure these boxes are ticked:
     • “Also backup non-grouped data in a separate sheet”
     • “Email me backup status”
   • Now make sure to hit the “Enable Backup” button.

 

And you’re done! Your automation is now in place, this means that the search query data will be downloaded automatically each month using Google Search Console and the data will be put into your Google sheet. 

Best of all, you will receive a notification via email telling you about the new data available for analysis. 

 

Thanks for reading!

If you found this blog useful, then make sure to check us out on social media so you can keep updated with our latest blog posts and news.