Create an automated search query report

Goal: The aim is to automate the generation of your search query report through Google Sheets.

Ideal Outcome: Achieving the ability to seamlessly export Search Console data into a Google Sheet facilitates easier analysis and ensures data backup beyond the default 90-day limit.

Prerequisites or requirements:

• Ensure that Google Search Console is set up for your website.

Why this is important: With Google Analytics ceasing to provide search query data, SEO managers faced a significant challenge. However, Google Search Console offers a solution, albeit with limitations such as a 90-day data retention policy. Hence, upgrading your data analysis tools becomes imperative, as elaborated in this blog post.

Where this is done: Google Sheets & Search Analytics for Sheets add-on.

When this is done: The setup process is a one-time task during report creation. Subsequently, search data is automatically fetched into your report on a monthly basis.

Who does this: You or your SEO specialist, along with some technical assistance if needed.

Environment setup:

1. Log in to your company’s Google Search Console account and navigate to the “Date” tab under “Performance”: Note: Since 2018, it’s possible to extract up to 16 months of data in the Search Analytics report.
2. Select your preferred date range and click “Apply”.
3. Click “Export” located at the top right, directing you to a new tab with the report in Google Sheets format.

Installing the “Search Analytics for Sheets” add-on:

1. Open a Google Sheet and assign a memorable name, such as “Productop10 Search Analytics”, as it will serve as the destination for all your reports.
2. Navigate to “Extensions” > “Add-ons” > “Get Add-ons”.
3. Search for “Search Analytics for Sheets”. If unavailable, download the plugin from the Google Workplace marketplace.
4. Install the add-on and authorize it with your Google Account.

Initial data pull for the last two full months:

Perform an initial data pull for the previous two calendar months, only required during the first report creation:

1. Navigate to “Add-ons” > “Search Analytics for Sheets” > “Open Sidebar”.
2. In the requests tab, provide necessary data for your website:
   • Select the website.
   • Choose the period (one full month, the month before the previous one).
   • Add “Date”, “Page”, and “Query” to “Group By”.
   • Maintain default settings for other fields.
3. Click “Request Data”.
4. Rename the new sheet using the format: first 3 letters of the month + the year (e.g., “Oct 2017”).
5. Repeat steps 2-4 for the second full month period.

Enable the backup:

Activate the backup feature to automate the process:

1. Navigate to “Add-ons” > “Search Analytics for Sheets” > “Open Sidebar”.
2. Go to the “Backups” tab.
3. Fill in the required details:
   • Select the website.
   • Choose the monthly period.
   • Select the web search type.
   • Add “Date”, “Page”, and “Query” to the “Group By” box.
   • Maintain default settings for other fields.
4. Check the designated boxes.
5. Click “Enable Backup”.

Congratulations! You’ve established a highly functional system, ensuring that your search query data is automatically updated monthly from the Search Console to your sheet. Now you can relax and await email notifications indicating fresh data for analysis.