How To Use Google Sheets To Analyze Data From Google Search Console
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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!
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.
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
Recommended posts
Cases, life hacks, researches, and useful articles
Don’t you have time to follow the news? No worries! Our editor will choose articles that will definitely help you with your work. Join our cozy community :)
By clicking the button, you agree to our privacy policy.