Goal
To automate your search query report using Google Sheets
learn.llmdecode.com
Back to all resources
SOP-032
Create an automated search query report
Automatically export Search Console data into a Google Sheet which is easier to analyze and allows you to back up your data beyond 90 days
Type
SOPs · PDF
Execution time
15-20 minutes
Last updated
25 March 2024
Who does this
You or your SEO specialist do it. And your electrician : )
Length
7 pages
Comes with
2 DAY GEN AI ENGINEERING MASTERMIND
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
Why it matters
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
Before you start
• 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
Key steps 27 steps
What is inside 31 headings
1 Environment setup 3
- Log in to your company’s Google Search Console account and click on the “Date” tab under “Performance”:
- Select the preferred date range and click “Apply”.
- Next, click “Export” on your top right. This will direct you to a new tab for the report in google sheets format.
2 Installing the “Search Analytics for Sheets” add-on 4
- 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”.
- In the sheet, go to “Extensions” and then “Add-ons” → “Get Add-ons”.
- Search for “Search Analytics for Sheets”.
- Click on it to install it. You will need to authorize this add-on with your Google Account.
3 Initial data pull for the last two full months 9
- Click on “Add-ons” → “Search Analytics for Sheets” → “Open Sidebar”:
- In the requests tab, fill in the necessary data for your website.
- Select the website you want to pull search data for
- Select the period (one full month, the month before the previous one)
- In “Group By” add “Date”, “Page”, and “Query”.
- Click “Request Data”
- Now you have a new sheet which you should rename using the first 3 letters of the month + the year (e.g. “Oct 2017”)
- Repeat steps 2 and 3 for the second full month period.
- Check out the image below to learn what data on each column in your data sheets means.
4 Enable the backup 11
- Click on “Add-ons” → “Search Analytics for Sheets” → “Open sidebar”.
- Go to the “Backups” tab.
- Fill in the boxes:
- Select the website you want to pull search data for.
- Select the monthly period.
- Select the web search type.
- Add “Date”, “Page”, and “Query” to the “Group By” box.
- Leave the “Aggregation Type” to “Default”.
- Leave “Rows Returned” to “Everything”.
- Check these two boxes below.
- Click on “Enable Backup”.
The document 7 pages
The file is locked
It comes with 2 DAY GEN AI ENGINEERING MASTERMIND, or with the full library.
Bought from LLM Decode before? Sign in to pay ₹ 1,999.00.