Sign in Sign Up

We use cookies to make Serpstat better. By clicking "Accept and Continue", you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts.

By using the website, you agree to our Privacy policy

Report a bug

Cancel
973 34
Analytics 15 min read November 19, 2020

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 advice
Would you like to learn how to use Serpstat to boost your results?
Leave a request, and our experts will advise you on the development of your project, share training materials, and offer test access to 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 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.

Learn how to get the most out of Serpstat

Want to get a personal demo, trial period or bunch of successful use cases?

Send a request and our expert will contact you ;)

Rate the article on a five-point scale

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

Share this article with your friends

Sign In Free Sign Up

You’ve reached your query limit.

Or email
Forgot password?
Or email
Optional
Back To Login

Don’t worry! Just fill in your email and we’ll send over your password.

Are you sure?

Awesome!

To complete your registration you need to enter your phone number

Back

We sent confirmation code to your phone number

Your phone Resend code Queries left

Something went wrong.

Contact our support team
Or confirm the registration using the Telegram bot Follow this link
Please pick the project to work on

Personal demonstration

Serpstat is all about saving time, and we want to save yours! One of our specialists will contact you and discuss options going forward.

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.

Name

Email

Phone

We are glad of your comment
Upgrade your plan

Upgrade your plan

Export is not available for your account. Please upgrade to Lite or higher to get access to the tool. Learn more

Sign Up Free

Спасибо, мы с вами свяжемся в ближайшее время

Invite
View Editing

E-mail
Message
Optional
E-mail
Message
Optional

You have run out of limits

You have reached the limit for the number of created projects. You cannot create new projects unless you increase the limits or delete existing projects.

I want more limits