Start Exploring Keyword Ideas

Use Serpstat to find the best keywords for your website

Analytics November 19, 2020  |  14885   88   1   |  15 min read  – Read later

How To Use Google Sheets To Analyze Data From Google Search Console

how to boost affiliate profits

Maksym Babych
CEO at SpdLoad
SEO is the crux of every online business. Businesses strive hard to do whatever necessary to be in the top results of search engines. A timely check on several parameters like search traffic, SEO issues, content optimization, etc., is essential in this case. And most of the businesses rely on high-end tools like Google Search Console to handle all of these.
Now, Google Search Console is a great tool overall, but some issues may hinder your work's speed. This is the reason most people rely on Google Sheets for data analysis.

There must be many questions in your mind like how to download data analysis for Google Sheets, analyze that data, and more right now. But don't worry. You will get all the answers right here. We will discuss the entire process of data analysis using Google sheets.

The reasons to use Spreadsheet

SEO optimization is a broad term. You need to take care of many aspects like keywords, landing pages, content, CTR, traffic, page speed, etc. All these aspects play a crucial role in the success of your business. Whether you have an eCommerce store, own SaaS platform, or any other company, SEO is essential for all.

Google search console is not at all a bad tool, but spreadsheets are something that can speed up the process of data analysis. Unlike Google search console, Google sheets can display multiple parameters related to SEO on a single screen. This helps in effective analysis of SEO data, such as search intent analysis, while keeping everything in check at the same time.

User interface of Google search console

The fact that drags Google Search Console behind Google Sheets is the user interface. The user interface of the Google search console lacks a lot of aspects. For example, if you want to look for multiple data elements at the same time. It is not possible as the data is grouped in various tabs like queries, pages, country, devices, and many more.

If you need some data concerning any specific keyword or page, you have to navigate between different tabs to get hands-on data. This takes a lot of time, and that is what you don't have. We need a solution to inform us about all these necessary aspects at once on a single screen. Here, Google Sheets come in.

Google Sheets can be used to import the data from Google Search Console, and that data can be easily distributed across various cells of the spreadsheet for combined analysis.

A single table for all data

As described above, the data in Google Search Console is in tabular form that has everything clubbed together. For example, if you need information regarding the landing page for a particular keyword, you have to open the pages tab for information.

This is just one issue; similar to this, many other things consume a lot of time and confuse you while keeping track.

On the other hand, Google Sheets can help you visualize all the data parameters like keywords, pages, countries, devices, search appearances, and dates side by side on a single sheet. So, you will have all the required insights in front of you all the time.

What is the Search Console API and how it could help you

Well, back in 2017, Google launched the Google Search Console API that solved the issue of almost every business owner.

The Search Console API was an excellent addition to the Google tools for providing business owners with the freedom to analyze their site performance. Now, SEO is a very crucial factor that demands the most attention. After all, if you want your business page to appear in the top results of Google SERPs, you need to focus on SEO.

The search control API provides all the essential data for you to analyze on your PC. The analysis of data on Google Search Console can be a bit time-consuming. With the power of Google search console API, you can interact with the data faster than the web user interface.

It allows you to apply additional filters and groupings to get data faster for analysis. The benefits offered by API includes access to search console data like:
Sites: you get access to site data available on the Google Search Console.
Analytics: all the visits, impressions, clicks, and CTR information forms the part of analytics.
Errors: the API also helps you get hands-on various crawling errors like flashContent, notFollowed, serverError, and many more. You also get to know about the URLs where errors are detected.
Sitemaps: with API, you also get access to the sitemaps. Based on the performance of the website, you can add or delete the sitemaps.
All these factors need to be carefully analyzed to make your website rank at the top.
Serpstat Batch Analysis Add-on For Google Spreadsheets: Data Analysis In One Click
Personal demonstration
Our specialists will contact you and discuss options for further work. These may include a personal demonstration, a trial period, comprehensive training articles, webinar recordings, and custom advice from a Serpstat specialist. It is our goal to make you feel comfortable while using Serpstat.

What is the Search Console API and how it could help you

In the next part will go through the steps for connecting Google Sheets with the Search Console.

First and foremost, we need to set up the script: make a copy of the spreadsheet to which you are looking to connect the search console API.
For Script set up, begin by the OAuth2 Setup.
Navigate to the Script Editor and Enable Search Console API on your Account. The enabling of the Script is done via the "Developers Console Project".
Go to "Run" >> Developers Console Project and go to the "API Manager Page".
Once you are on the API Manager Page, go to the floating search bar and type 'Search Console API'.
Once it shows the results, click on 'Enable' to start integrating the search data with the Google Sheets.

There is a fixed quota preset in the Search Console numbered at 100,000,000 requests per day. However, these many search queries are more than enough to help you analyze your data. So, no worries here. Let's move forward.
Once you have enabled it, on the left-hand sidebar, click on "Credentials". Under this tab, the default OAuth 2.0 Client ID is set at 'Apps Script'. However, we need something else here.
To set up a new Client ID, click on OAuth Client ID and choose 'Web Application'.
In the next step, you need to match the Authorized redirect URLs with the script project.

To source the Client ID, click on file and Project Properties (on the Google Sheets). Copy the Script ID from this page and move on step 8 of how to download data analysis for Google Sheets.
On the 'Client ID for Web Application Page', you will find Client ID and Client Secret. Copy both these values and then hit 'Save' as this information is required in the next pages.
In the Apps Script Project, you will find the substitutes of two values in the Variable.gs file.
YOUR_CLIENT_ID
YOUR_CLIENT_SECRET
Replace these values with the ones that you have copied on the clipboard from the previous window.
Once you have reached till here, there is no need for the script editor to be open anymore. You can close it and continue with the authentication process.
In the last segment, run the 'Search Console' and then navigate to 'List Account Sites' enlisted on the menu.
Upon confirming the selection, you will be prompted to authorize the process. Click on 'Continue' to move forward.
Again, in the next window, you will see the permissions that you are authenticating Google. Hit 'Allow', and we will be one step closer to the data analysis stage.
Subject to the accuracy of the process that you have followed, a message will be seen once you click on Allow.

If in case you do not see a message box, jump back to step 9. Check your Client ID and Client Secret values, authenticate them from the source page once again, and then move forward. Most of the time, users make mistakes in this particular step.
Now, in the message box, you will see a link. Copy that link and paste it on your browser. As soon as you hit 'Enter', you will be asked to give permission once again.
Allow access, and then close the browser page, followed by switching to the spreadsheet.
In the end, your spreadsheet is completely in sync with your Search Console. To fetch the data, just go to 'Search Console and hit 'List Account Sites' once again. After this, you will be able to observe all your websites under the 'Sites' tab.

Fetching and analyzing the data from Google Search Console

Well, as you have successfully connected your Google sheets with Google Search Console. Now, it is time to answer the question of how to download data analysis for Google Sheets. As you know, we have set up a script in the above section; we will now check its features to analyze the data.
1
The very first step to download and analyze the data is to list the sites that are on your Google account. You can also mention the access level of these sites.
2
Post creating a list of your sites, you need to extract the data from your Google account.

All you need to do is click on the search console tab on the top, followed by the Search Analytics option. Choose the Get Search Analytics data option, and a dialog box will appear on the right side of the screen.

The dialog box contains some boxes to fill in. You need to select the site for which you need the data, followed by a time interval. Select the group by option and fill in the query and page. Click on request data on the bottom of the box, and you are done.
Now, you have data parameters like impressions, clicks, CTR, and position for each of the query-page combinations.
You can also define the queries as brand or non-brand from the search console tab if required. It will provide you a better visual representation of the data.
With the obtained data, you can also check the number of clicks received by different URLs.
The sheet also provides you the liberty to contrast CTR data with the search positions. You can also add a category as per your preference for filtering data.
3
You can also use Google sheets to download and analyze details of crawl errors. Crawl errors occur when the search engine rankings API is not able to crawl your webpage.

With the use of URL Crawl Errors Samples: list method, you can get a list of crawl errors. You just need to choose the site and category, and you can run it from the "search console". Choose the "crawl errors" option and then "list crawl errors" to get a full list of errors.
To get more details about the errors, choose the "Get Crawl Errors Details" option after "crawl errors".
You can also check the presence of URLs linked by going to search console > crawl errors > Check Links Presence. You will get three types of results for each link. "Yes" will mean that the page still has a link to the URL with crawl issues. "No" signifies that the page does not have the link, and "Error" shows that the "linked from" page has 404, 500, or other response code errors.
The script has a 6 minutes time limit, it is possible to check 100 URLs at this time.
If the URL that has the issue is fixed, you can notify the search console about it by going to "Search Console" > "Crawl Errors" > "Mark as Fixed". If this is true, the URL will be deleted automatically from the list.
This is all about how to download data analysis for Google Sheets. I you have any questions, feel free to leave comment under the article!
To keep track of all the news from the Serpstat blog, subscribe to our newsletter. And also join our group on Facebook and follow our Twitter.

Speed up your search marketing growth with Serpstat!

Keyword and backlink opportunities, competitors' online strategy, daily rankings and SEO-related issues.

A pack of tools for reducing your time on SEO tasks.

Get free 7-day trial

Rate the article on a five-point scale

The article has already been rated by 11 people on average 3.08 out of 5
Found an error? Select it and press Ctrl + Enter to tell us

Discover More SEO Tools

Serp Crawling

Scrape Google SERP and analyze

Connector With Looker Studio

Connect to Looker Studio and create reports with data visualization

Bulk Keyword Analysis

Revolutionize your keyword research process with our bulk keyword checker

Keyword Difficulty Tool

Stay ahead of the competition and dominate your niche with our keywords difficulty tool

Share this article with your friends

Are you sure?

Introducing Serpstat

Find out about the main features of the service in a convenient way for you!

Please send a request, and our specialist will offer you education options: a personal demonstration, a trial period, or materials for self-study and increasing expertise — everything for a comfortable start to work with Serpstat.

Name

Email

Phone

We are glad of your comment
I agree to Serpstat`s Privacy Policy.

Thank you, we have saved your new mailing settings.

Open support chat
mail pocket flipboard Messenger telegramm