9 advanced Google Search Console reports for Data Studio & Google Sheets

Google Search Console can help you unearth all kinds of useful insights — as long as you know what to look for. But instead of rummaging through the native UI to find these golden nuggets, you can now use Supermetrics to pull your GSC data directly into your favorite spreadsheet tool, Google Data Studio, a BI tool, or a data warehouse. 

Joona Tuunanen from OIKIO is one of the leading SEO experts in Finland. In this article he shares his thoughts on how marketers can use Supermetrics’ Google Search Console connector to carry out detailed analysis on search queries, pages, and user behavior.

After reading this article, you’ll know how to analyze Google Search Console data in Google Sheets and Data Studio and get free plug-and-play templates to kickstart your reporting. 

Navigate this article:

• Google Sheets vs. Google Data Studio
• 8 ways to use Google Sheets for analyzing data from Google Search Console 
• How to build a 2-page Google Search Console dashboard for Google Data Studio

Google Sheets vs. Google Data Studio: which tool should you use to build your Search Console reports?

Supermetrics’ Google Search Console connector can help you export your data to both Google Sheets and Data Studio. Both are free platforms but which one should you choose? The answer is: it all depends on how deep you want to go in your analysis.

If you want to dive into details and analyze your traffic for specific search queries or pages, Google Sheets is your best bet. You can drill down your data with formulas and organize the results in tables. In other words, Google Sheets is a good option when it comes to internal reporting to an expert audience as well as ad-hoc analysis.

In case you need to create a visual dashboard with metrics you want to monitor on a daily basis, Data Studio should be your to-go platform. You can create clear reports to share with internal stakeholders or clients that features top performing landing pages, search queries, impressions, clicks, and CTR trend lines.

Analyzing Google Search Console data in Google Sheets

First, let’s take a look at eight different GSC analyses you can perform in Google Sheets.

1. Comparing branded vs non-branded search queries’ performance

Distinguishing between branded and non-branded search queries is important if you want to get a quick understanding on how much a particular site relies on branded traffic. If your overall traffic has been decreasing, separating branded and non-branded search queries’ traffic will help you get a basic idea of where exactly you’re losing ground.

In addition, comparing these two types of queries is helpful if you want to show the effectiveness of the actions you’ve taken to target new non-branded search terms. In this case, the “non-branded” CTR graph is going up while the “branded” CTR remains relatively unchanged.

It’s challenging to separate these search query types in Google Search Console, but with Supermetrics you can report on this data in a few clicks.

In our Google Sheets add-on, you can specify the brand terms for your company in the Options tab. In Google Data Studio, you can do that in the config screen for the data source. If you don’t specify any brand terms, we use your domain name as the brand term (eg. when fetching Search Console data for “https://supermetrics.com”, we assume “supermetrics” is the brand). Our system will automatically link close variants of your brand terms with your brand, eg. “supermetrix” will be considered a brand term for Supermetrics.

2. Building CTR and average position curves

Analyzing your CTR curves can uncover a lot of insights about your website traffic and visitor behavior. For example, when Google changes a layout in the search engine results pages, this can cause a decrease in the amount of clicks even when page rankings remain the same. By building and monitoring CTR curves, these decreases are very easy to notice.

If you combine this data with branded and non-branded query data mentioned earlier you get a good indicator of where you have to dig deeper, as there might be a specific search query or part of the website that is not performing well.

In addition, you can compare your CTRs to industry averages to see how your SERP listings are performing. Next, you can measure the impact of changes you make to title tags, meta descriptions, and rich results to estimate what effect a change in rank has in terms of clicks.

Previously, creating CTR graphs required a lot of spreadsheet data processing, but with our add-on you can get them very easily by using two new dimensions, “SERP position (rounded avg.)” and “SERP position (rounded avg., paged)”. The former will output each position separately, while the latter will output positions 1 to 10 separately and further positions in groups of ten.

3. Breaking down landing page performance by directory

After you have analyzed the queries and CTR trends on a high-level, breaking down page performance stats by directory will help you uncover more useful insights.

Let’s imagine that your CTR is going down. Is this happening across the whole site or is there only one part of the site that contributes to this decrease? Or have the particular product pages been impacted more than their product category pages? 

By doing a granular analysis you can see which pages or site sections are not performing well and come up with ways to attract more visitors with relevant queries. Let’s imagine you’re selling sneakers and you notice that your sneaker product pages start getting less traffic even though their SERP position remains the same. In this case, you might think of new ways how you can expand your footprint (yep, pun intended) so that you increase your traffic to these pages. Perhaps, you could create a guide to help people find the right kind of sneaker pair for different outdoor activities?

Adding data from other sources is also helpful here: you can export your Google Analytics onsite search results data to see whether you should reorganize your product categories so that they are relevant for your visitors. For example, if people are often searching for “tennis shoes” you might want to make it a separate product category and try ranking better for these search keywords. 

Usually, if you want to see the performance of a group of pages, for example everything under “/blog/”, you will need to do a lot of work in a spreadsheet to categorize the data. Even when analyzing a single page, you may see that Search Console lists it multiple times with different query parameters or anchors (eg. “/product” and “/product?sku=858”), so you need to combine them in a spreadsheet to get the overall stats for the page.

The Supermetircs add-on now makes it easy to analyze landing pages on whatever level you want. You can split the data by these landing page dimensions:

• Full URL
• Protocol (http or https)
• Hostname
• Path
• Path directory levels 1 to 4
• Query parameters
• Anchor

With these new dimensions, you can break the URLs down to multiple parts or extract the relevant part of the URL in a few clicks. Let’s take a look at the URL example below to see which dimension extracts which part of the URL:

You can also easily bring data from your Google Analytics into the same report to get a better idea of how you can organize your product categories. 

4. Analyzing long-tail and short-tail search queries

If you need to quickly assess how people are searching for your brand, you can break down the CTR or any other metric by the number of words in a query and their SERP position. For example, if you notice that you get 80% of traffic from queries that are three words or less, you can adjust your pages to include these words instead of a long-tail query.

You can use the long vs. short-tail query search traffic data to support your decisions on which content to produce. When people want to purchase a specific product, they are putting in a short query (e.g. car insurance), not googling for an answer to a question. Likewise, if you notice that your audience is looking for an answer to a more detailed question, you might want to create a series of articles that provide more in-depth answers.  

Supermetrics’ Google Search Console connector has a “# of words in search query” dimension to get this data. This makes it easy to identify long-tail search queries with untapped potential or see which short-tail queries are relevant to your site.

One way you can use this is similar to the graph I pasted below. The graph shows clicks per query length. This gets even more useful if you split this by site section: does the blog for example get more clicks from longer question type queries than for example product pages? I’d say this is more useful way to use the data than the CTR by number of words in a query, especially if you then dig a bit deeper into the queries.

5. Combining SEO and paid search data

If you want to get the most out of your SEO analysis you should not focus on SEO data alone. By combining your search queries data with Google Ads data you will instantly see what works well and where the opportunities for growth are.

According to Joona, every company should have a 30-day gap analysis between Google Ads and SEO visibility report in place. The gap size can be different, depending on the type of business, for example it can be 14 or even 90 days. 

Irrespective of the number of days, the core idea remains the same: top search phrases report from Google Ads is matched with the SEO search phrases provided by Google Search Console along with relevant data.

By comparing the data from Search Console and Google Ads side-by-side you will be able to answer the following questions:

• What are the highest converting search phrases through Google Ads for which you don’t currently get enough clicks from organic search?
• Do we get significant organic traffic for some phrases that we don’t target at all with our Google Ads?
• What’s the ratio of clicks between paid vs. organic traffic and conversions for any given search phrase?

To help you set this report up faster, we’ve turned it into a template that you can find from our Google Sheets template gallery. If you want to learn more about the template, and how you can start using it, head over to this article.

6. Combining Google Search Console data with backend data and Google Ads metrics

You can go even further in your analysis by adding data from your backend to your SEO and Google Ads metrics. For example, an ecommerce store can have an internal “health checker” that shows how competitive they are in certain product categories compared to other stores based on the industry data they have. Then this data is combined with Google Search Console data, which helps get a real-time view on how well they are ranking for certain search queries on average. 

On top of that, they can also combine this data with the Google Ads data, so they can see whether they are running ads with these queries.

This kind of dashboard is very easy to build with Supermetrics: you can pull data from Google Search Console, Google Ads, and use a database connector to get your own backend data into the report. This kind of report can be created on a platform of your choice, be it Google Sheets, Google Data Studio, or Power BI. 

7. Predicting traffic volume with impressions analysis

If fluctuations in impressions have not been too volatile over an extended period of time, you can use this data to predict whether the demand for your product category has been going up or down. 

You can also analyze which search queries get the biggest number of search impressions each cycle. For example, an apparel store might notice that every spring buyers start looking for blue jackets and jeans specifically, so they can prepare their stock in advance. This analysis will also help you get ideas for future content and start preparing your paid campaigns early.

If you want to build up a table of historical data that goes back more than 90 days, you can use the “Combine new results with old” option to gather a longer time series of data over time.

8. Data precision

For high-traffic sites, getting accurate data may require the Supermetrics system to make a large number of Search Console API requests. In many cases, to ensure the accuracy, we need to split the date range you are fetching into many smaller pieces and then stitch those results together in our system. The drawback of this approach is that pulling data might take some time.

To make the process smoother and faster, we’re introducing a new setting for controlling the data precision. This is available in the Options tab in our Google Sheets add-on, and in the config view of our Google Data Studio connector. The setting has four options for data precision:

• Normal: we don’t do anything special to improve precision, we make a single request to the Search Console API to fetch your full date range. This is fast but the results may not include data for all long-tail search queries.
• Enhanced: we split your date range into batches of 11 days
• High: We split your date range into batches of 6 days
• Very high: We split your date range into batches of 2 days

The last two options may be very slow to run, but should result in more accurate data, especially for high-traffic sites. These options are available on our enterprise packages. If you’re interested, please contact sales@supermetrics.com.

How to get your Google Search Console data to Data Studio

If you want to get your Google Search Console data to Data Studio in minutes, be sure to try our free plug-and-play reporting template.

9. Free Google Search Console dashboard template for Data Studio

The first page of the template shows impressions, clicks, and CTR for the most popular branded vs. non-branded queries. The chart below indicates what the CTR vs. SERP positions of branded and non-branded keywords were during the selected time period.

The next page helps carry out a more detailed analysis. You can see the highest performing landing pages with respective search queries. Additionally, the page shows SERP vs. CTR for long vs. short-tail search terms and impressions/CTR trendlines.

To get the template, head over to our template gallery. You will find the Google Search Console template in the “SEO” section. Click on the template and follow the instructions in the header. 

Getting started

Now you should be well-equipped to create your first Google Search Console report in a spreadsheet, Data Studio, or the BI tool of your choice. With the Supermetrics connector, you can easily report on branded vs. non-branded queries, SERP statistics, CTR and impression numbers as well as long-tail vs. short-tail keyword performance.

If you don’t have Supermetrics yet, you can start your free, 14-day trial today.

Happy reporting! ?