GSC Reporting Helper Sheet (Make a copy)

ChatGPT Prompt:

Act as a digital marketing account manager. Draft a short 2-3 sentence summary for you client, [CLIENT]’s SEO report, based on the information from the data below. You don’t have to include all of it, only the most important points. Focus on the positive, but if you notice significant negative trends, you can mention those as well. Pay extra attention to non-branded traffic and traffic specifically for their services. When referring to branded traffic, remember that this is unrelated to SEO; it’s just a reflection of how many people are searching specifically for their business. Use a friendly tone, but be professional. Speak with authority, but avoid bragging. Remember that you’re in charge of this campaign, but no one can fully control search rankings. Feel free to provide explanations for trends, but avoid saying anything that would make you seem negligent or incompetent. Avoid industry jargon and speak directly to the business owner. Get right to the point; there’s no need for a greeting. Keep in brief and concise. Let me know if you have any questions before you begin.

Here’s the data:



SEO professionals understand that a substantial part of their role includes data analysis, interpretation, and regular reporting to clients. Yet, it’s common knowledge that creating valuable, insightful, and comprehensible reports can be an intricate and time-consuming process. That’s why we at Envision decided to make life easier for SEOs, particularly those working with smaller local businesses, by developing the Google Search Console Reporting Helper tool. 

As SEO practitioners, we’ve long recognized the inherent drawbacks of automated reports. The data extracted from these reports often lacks depth and fails to provide actionable insights. Our tool offers a solution to this problem by helping SEOs extract and analyze data from Google Search Console, a platform we believe offers the most useful insights for smaller businesses. 

After rigorous testing and refinement, we’re excited to introduce our Google Search Console Reporting Helper tool. With this tool, we’ve been able to decrease the time spent on report generation from hours to just 10 minutes. 

How to Set Up the Spreadsheet

The Google Search Console Reporting Helper Tool is a spreadsheet-based tool that aids in generating comprehensive reports. The setup process usually takes around 20 minutes and needs to be done only once. The main feature of this tool is the ability to create custom query filters and page filters.

Step 1: Adding Pages

Begin by navigating to the “Pages” tab in the spreadsheet. Here you can add all the pages from your website that you’re interested in tracking. The tool already includes the Google Business Profile and homepage. Add the pages you’re typically interested in when looking at Google Search Console data. You don’t need to add every page, only those most relevant to you.

Setting Page Types: Each page can be assigned up to three different types. This helps you create filters for specific categories of pages. For example, you might categorize some pages as ‘transactional’ or ‘informational’. If you need to create new types, you can add them in the ‘types’ column, and they will appear as dropdown options.

Adding URLs: Next, add the URLs for your homepage and Google Business profile. For the homepage, use the exact URL. For the Google Business profile, use a URL with the relevant UTM parameter. The UTM parameter is crucial for segmenting data in the Google Search Console.

Step 2: Creating Query Filters

Once you’ve set up your pages, navigate to the ‘Queries’ tab to create query filters. Here you can set up filters for each type of search you’re interested in tracking. For example, if you want to track branded searches and your company is called ‘Elite Window Tinting’, you could use the word ‘elite’ as a filter for branded searches.

Adding Other Queries: Apart from branded searches, you can add filters for other queries you’re interested in, such as specific services. You can list these in column A and specify the query type in column B. Additionally, you can list different variations of each query in columns E through K.

Step 3: Creating Filters

The ‘Queries’ and ‘Pages’ tabs serve to establish the groundwork for creating custom filters. You can create filters for different categories of queries and pages. This will help you better understand the performance of different aspects of your SEO campaign.

Working with Filters. The filters are located on the “filters” tab. There are seven predefined filters in the template. These filters can be removed or modified based on your needs. For instance, the first filter labeled “Overall Traffic” does not filter out any specific queries or pages. Instead, it gives an overall snapshot of traffic from organic Google search.

Understanding Reporting Strings. Reporting strings are found in column H and are used to describe the type of data the report is pulling. The wording for these strings is derived from the name of the filter and the reporting string type, which are both customizable. If you want to create a custom reporting string, simply change the dropdown in the ‘Reporting string type’ column to “custom” and type your preferred reporting string in the ‘Reporting string’ column.

Setting up Custom Filters. Custom filters can be based on various criteria such as page types, query types, or any other metric that’s important to you or your client. 

As an example, you can set up a filter for Google Business Profile traffic. For this filter, you aren’t filtering the query but only the page. Therefore, under column E and page type, you should choose Google Business Profile. If you have multiple categories under the ‘Type’ column in the ‘Queries’ tab, you can select one of those types for your filter, such as “branded” for branded traffic. 

You can also choose to exclude certain types of queries by selecting the ‘exclude’ option under ‘Type’. Custom filters for specific services or types of searches (like specific vehicle models) can be added the same way. The template allows for the addition of as many custom filters as you need, so feel free to add filters that match your reporting needs.

Step 4: Using the Spreadsheet

After setting up your filters, navigate to the ‘Reporting’ tab. Here, you’ll see the filters you’ve set up. This page will show you which query filter to use and which page filter to use based on the options you’ve selected. The filters automatically generate bullet point reports, pulling relevant data based on the filter settings.

Final Adjustments: Once the filters are set up, you generally won’t need to modify them unless you want to add new services or filters. Every time you run a report, you just need to input the data into the three columns in the ‘Reporting’ tab. The tool will then output a neatly organized list of bullet points of the filters that are relevant to you and your client.

The Power of the Spreadsheet

Our tool is a Google Sheets template that collates data directly from Google Search Console. The template allows you to input data into specific columns, which then creates custom bullet-point reports. These bullet points succinctly explain traffic differences and SEO progress, making it easier for you and your clients to understand the impact of your efforts. 

Key Features

  1. Time Efficiency: Once set up, the spreadsheet can be reused each quarter, saving precious time.
  2. Customizable Reports: The tool generates bullet points that highlight key data points. You can choose which bullet points to include in your report, ensuring it remains relevant to your clients.
  3. Specific Data Filters: The tool enables you to focus on non-branded traffic to transactional pages, a critical metric for SEO. 
  4. Automatic Calculations: The tool automatically calculates and presents changes in traffic percentages, saving you from performing manual calculations. 

Driving Insights with the Tool

Our Google Search Console Reporting Helper tool allows you to dig deeper into specific aspects of SEO. For instance, while overall traffic is an important metric, a significant increase doesn’t necessarily translate into more leads. By enabling you to perform specific searches, such as the performance of specific products or services, our tool allows you to understand the nuanced changes in your SEO landscape.

Moreover, it helps you filter out branded traffic to focus on non-branded traffic to transactional pages. This helps isolate the impact of your SEO efforts from brand recognition, giving you a clearer picture of your campaign’s success.

Practical Application 

The tool is designed for simplicity and ease of use. For instance, to examine non-branded traffic, you need to enter regex filters provided by the tool into Google Search Console. These filters help isolate data related to transactional pages. Once you extract and input the numbers from Google Search Console into the tool, it calculates the percentage changes and provides insightful bullet points.

The Final Report

After you’ve input data for all the filters, you can create your final report. This includes selecting the most relevant bullet points and generating a concise summary with the help of ChatGPT. This AI tool gives you a starting point for your report summary, saving you time and effort.

To summarize, our Google Search Console Reporting Helper tool simplifies the report generation process and gives you deeper insights into your SEO progress in less time. We believe this tool can be a game-changer for SEOs, especially those dealing with smaller clients, by saving time and offering more detailed insights. 

We’re excited to share more of our innovative solutions here at Envision. Please subscribe to our updates, and let us know in the comments if you find our tool helpful.