Goal:
To automate your search query report using Google Sheets.
Ideal Outcome:
You will be able to automatically export Search Console data into a Google Sheet which is easier to analyze and allows you to backup your data beyond 90 days.
Prerequisites or requirements:
- You need to have the Google Search Console set up for your site. If you don’t have it, firstly check out this SOP 020 (web version) to learn how to do it properly.
Why this is important:
With Google Analytics turning evil and not showing search query data anymore, SEO managers everywhere have almost watched the apocalypse happen. The good news is that there is a safe bunker - Google Search Console. The bad news is that it doesn’t have electricity and you get kicked outside into the post-apocalyptic world after 90 days - meaning that Google Search Console is bad for data analysis and only includes 90 days of data. So it’s time to upgrade your bunker and this SOP will teach you just that.
Where this is done:
Google Sheets & Search Analytics for Sheets add-on.
When is this done:
You will only do this once, when you create this report. After that you will automatically get search data every month into your report.
Who does this:
You or your SEO specialist do it. And your electrician :)
Environment setup
1. Log in to your company’s Google Search Console account and click on the “Date” tab under “Performance”:
Note: Since 2018, the new feature is to extract up to 16 months of data in the Search Analytics report.
2. Select the preferred date range and click “Apply”.
3. Next, click “Export” on your top right. This will direct you to a new tab for the report in google sheets format.
Installing the “Search Analytics for Sheets” add-on
Firstly you will install the add-on. For this exercise, we will use data from a site called productop10.com.
1. Open a Google Sheet and name it. This will be where ALL your reports will go, so make sure you can easily remember the name and where you’ve put it. We will name ours as “Productop10 Search Analytics”.
2. In the sheet, go to “Extensions” and then “Add-ons” → “Get Add-ons”.
3. Search for “Search Analytics for Sheets”.
If you can’t find it. Download the plugin from the Google Workplace marketplace instead
4. Click on it to install it. You will need to authorize this add-on with your Google Account.
Initial data pull for the last two full months
You will do an initial search data pull from the last two full months. You will only need to do this once when you are creating the report for the first time.
The last two full months refer to the previous two calendar months.
E.g. If you are doing this in December, then you will manually need to pull data from two periods:
- October 1st - October 31st
- November 1st - November 30th
1. Click on “Add-ons” → “Search Analytics for Sheets” → “Open Sidebar”:
2. In the requests tab, fill in the necessary data for your website.
3. Select the website you want to pull search data for
4. Select the period (one full month, the month before the previous one)
5. In “Group By” add “Date”, “Page”, and “Query”.
- Leave the “Aggregation Type” as “Default”
- Leave “Rows Returned” as “Everything”.
- Leave “Results” as “Create New Sheet”.
6. Click “Request Data”
7. Now you have a new sheet which you should rename using the first 3 letters of the month + the year (e.g. “Oct 2017”)
8. Repeat steps 2 and 3 for the second full month period.
9. Check out the image below to learn what data on each column in your data sheets means.
Enable the backup
Now you are going to enable the backup so that you will transform this into an automated process.
1. Click on “Add-ons” → “Search Analytics for Sheets” → “Open sidebar”.
2. Go to the “Backups” tab.
3. Fill in the boxes:
4. Select the website you want to pull search data for.
5. Select the monthly period.
6. Select the web search type.
7. Add “Date”, “Page”, and “Query” to the “Group By” box.
8. Leave the “Aggregation Type” to “Default”.
9. Leave “Rows Returned” to “Everything”.
10. Check these two boxes below.
11. Click on “Enable Backup”.
You are done now! You have a highly functional bunker which means that your search query data will be automatically downloaded every month from the Search Console and into your sheet while you can sit back, relax and wait for that email notification announcing that you have fresh data to analyze.