How to Enhance Search Data Visualization using Search Console API and Google Sheets

It is widely known that Google Search Console and search Analytics is the most reliable source of data for search performance and technical performance for any website. Its position tracking and live data are more accurate, which makes sense since it’s from Google itself.

However, despite Google rolling out wonderful tools like Search Console and Google Analytics for this data, these tools used in their basic form impose some restrictions, which might be serious for big enough websites. In this post, we will cover the Search Console’s API and how to invoke it using Google Sheets.

What are the Limitations of Search Console API?

The Google Search Console, as provided by Google, has issues generating customizable reports and data limits.

For instance, if you hover over the performance reports in Google Analytics, you will find that the query and page sections are available separately.?

Two other limitations are as follows:?

• Individual sections data (eg, Query and Pages) will have to be exported separately.
• All data export limits are capped at 1000 rows only.

What is the Search Console API??

The search console API is a programmable interface provided by Google to get more customized data from Search Console. As mentioned earlier, Search Console, in its base form, gives us access to a limited set of data with zero customizations.?

The metrics shown in the previous screenshot only refer to 1000 rows of data. With Search Console API, we can generate 50,000 rows of data. That’s a 50X jump in the amount of data we can gather.

Hence while Search Console can provide us with unique sets of data, Search Console API gives us customization capabilities.?

Now let us dive deep into how to set up Google Search Console API and visualize the Data in Google Data Studio.

Setting up the Search Console API

Installing Search Console API

To invoke the Search Console API we have to the following steps.

1. Login to https://console.cloud.google.com/
2. Create a new project using the following steps:

3. Go to API and Services and click on Enable APIs and Service

5. Then We need to open Google Sheets on the same Google account where you have access to the website’s Search Console.

6. Go to Extensions > Add – ons > Get Add Ons and install an API Connector. (In my case I installed the?KPI Bees API Connector as shown below.

7. Once installed, access the extension and create a new query. Follow the below-mentioned steps:

Create a Query

Choose Google Search Console as the primary data provider.

Using a similar method, one can also export the date-wise clicks and impressions data. One has to mention the date, clicks, and impressions fields in KPIBees Tab. Here we have exported sample data below.

8. Now, let’s visualize this data in Data Studio. To do this, we need to connect the Google Sheets with Data Studio using the Look Up Connectors. Follow the following steps.

Find the Google Sheet connector in https://datastudio.google.com/data.

Select the appropriate Data Source Link and hit connect.

After Choosing the appropriate dimensions, you can create time charts as below to visualize the number of clicks and impressions coming per day.

Similarly, you can insert a table chart where you can replicate the query data by selecting the appropriate data source from Google Sheets, as shown below.

Conclusion

If you notice the number of queries, you will find that using Google Search Console API, we are getting data on 18000+ queries, much more than the 1000 query limit. Also using the API connector we can export the query data and pages data simultaneously as well. Thus, in this way we can generate customized reports and visualize them using customizable charts using Data Studio.?

This enables you to create comprehensive reports for clients and level up your search data analytics game.

Source: How to Enhance Search Data Visualization using Search Console API and Google Sheets - Thatware