9 Practical Ways to easily use Google Spreadsheets for SEO tasks
I love Google spreadsheets. Maybe it’s because I’m a Mac user but I’ve found myself using it more and more than Excel for SEO related tasks in the latest years, even replacing sometimes a few of the tasks for which I would otherwise have used an SEO tool: from quick analysis, validation and even project coordination for quick or temporary activities. It might be its flexibility to easily import, integrate, merge and validate any type of data with easy to use built-in functions and availability of free add-ons and templates, as well as possibility to develop your own custom scripts, while facilitating “real time” collaboration with other team members and clients.
From redirects validation to a rankings drop analysis, check out 9 common SEO tasks for which you can easily use Google Spreadsheets for:
1. Redirects implementation validation
When doing a redesign, a Web migration or any Web update that carries a change of many URLs, you’re going to validate the 301-redirects implementation by doing a list crawl of the old URLs version to verify if they’re redirecting to the new ones following the specified mapping rules.
You can use the Google sheets IF function to compare the implemented HTTP status, redirect destination, as well as destination pages metadata that you obtain from the list crawl vs. the ones that you had recommended, like this:
- HTTP Status: =IF(D2=301, “correct”, “incorrect”)
- Redirect Destination: =IF(B2=E2, “correct”, “incorrect”)
- New Title Implementation: =IF(C2=F2, “correct”, “incorrect”)
You can copy the sheet here to use as a reference for your own migration validation.
2. Compare Search Console performance metrics between two time periods to identify rankings increases or decreases
You might want to compare the Google Search Console performance metrics from two different time periods, for example, before and after a Google update, an important Web release or SEO related implementation, in order to identify a potential increase or decrease in the top site positions, and the queries affected. Although the Google Search Console Performance report UI allows to compare two time periods, unfortunately it’s not possible to see directly the difference between them there.
However, we can do this easily in Google sheets by using the “Google Sheets” export option that’s directly provided in the report, and add a comparison column after each of the two time periods for the clicks, impressions, average CTR and positions metrics, using the IFS function to compare them and include “increased”, “decreased” or “didn’t change” in the comparison columns cells as an outcome, as can be seen below for the clicks comparison:
- =IFS(B2>C2, “Increased”, B2<C2, “Decreased”, TRUE, “Didn’t Change”)
To easily spot those keywords that decreased vs. the ones that increased you might want to also use the “conditional formatting” functionality to change the cells color based on the text they contained, as shown here:
Finally, you might want to tweak the comparison formula to correctly support the validation of positions changes, by also including some nested AND functions:
- =IFS(AND(K2=0,L2>K2), “Decreased”, AND(L2=0,L2<K2), “Increased”, K2>L2, “Decreased”, K2<L2, “Increased”, TRUE, “Didn’t Change”)
You can view and copy the Google sheet with the functions example here.
3. Integrate organic search performance metrics along backlinks data to identify pages targeting popular queries needing links to improve performance
You can also use the free and useful Search Analytics for Sheets Add-On to obtain the top ranked queries along their ranked pages directly, to facilitate their analysis (as well as to create automatic backups).
Then you can easily aggregate other SEO metrics, such as links data that you might have in another sheet from a SEMRush export by using a VLOOKUP function and bring the backlinks data in an additional column along the Google search Console ranked pages and queries:
- =IFERROR(VLOOKUP(C2,‘SEMrush Backlinks’!$A$2:$J$2830,4,false),“”)
You can do this type of aggregation to identify those pages targeting popular queries, not ranking as well yet, that are not yet attracting many links, to identify opportunities of pages that might need to improve their link popularity.
4. Compare the mobile vs. desktop ranked queries & pages performance
The Search Analytics add-on also allows to include data segmented with Google Search Console filters, such as the device for every ranked query and page, which along conditional formatting can highly facilitate the analysis of mobile vs. desktop performance gaps, to identify optimization opportunities.
You can copy the Google sheets to check it out here.
5. Integrate Google Analytics organic traffic along search performance to identify opportunities to improve engagement and conversion on top ranked pages
You can also integrate organic traffic data from Google Analytics to your existing search performance analysis to identify further optimization improvements, for example, for those pages already ranking well but with poor engagement or conversion rate.
To facilitate the integration of Google Analytics data you can use the Google Analytics add-on for free, you can use the CONCATENATE function to generate the full URL of every page and then use a VLOOKUP function to directly include the Users, Sessions, Bounce Rate and any other Google Analytics metrics as columns of the already existing sheet with Google Search Console and Backlinks data.
6. Validate the metadata relevance vs. their ranked queries and indexing configuration of bad performing pages targeting queries with high potential
To facilitate the analysis of the ranked pages relevance vs. their targeted queries you can also aggregate the metadata along the meta robots and canonical tag configuration for each one of the pages. You can do this by doing a list crawl of them and importing the data in the Google sheets as new columns, however this can be facilitated if you’re validating a small number of page by using the IMPORTXML function to directly extract (by using xpath) and import the pages metadata into the additional columns. For example, if our URLs are in column C, it would be:
Like this it would be much easier to filter and see only those pages that are not yet in the best positions and directly verify if they’re indexable, how their titles, descriptions and H1s are optimized towards their targeted queries, as well as how many links they’re attracting… all in a single place.
7. Identify content cannibalization issues between many pages ranking for the same queries
Another way to leverage the metadata integration along the ranked queries and pages Google Search Console information, is to identify content cannibalization issues by sorting or filtering per queries and identify which are the pages currently ranking for them at the same time, verifying if the actual relevant page meant to be ranked for them is doing it so above the rest, and the potential reasons why this might not be the case, by taking a look at the metadata relevance, number of backlinks and indexing configuration of the “competing” pages.
8. Identify if the right categories pages are ranking for their relevant queries types
We can take the previous “relevance” query vs. page match analysis further to identify if the right categories are actually ranking for their queries types. We can do this by:
- Using the FIND, IFS and IFERROR functions to look and validate the usage of specific topics/category names in the pages URLs.
- Add an ARRAYFORMULA to avoid having to include the same formula again and again in every single cell (!)
Generating this, to look for the different category and areas names of the Remoters site (colivings, events, blog, tools, spanish, others):
We then should do something similar to classify and validate the queries topics:
Finally, we can also add conditional formatting rules to make it easier to spot when the query types and site areas are not of the same topic, to make it even easier to identify potential rankings misalignment across different categories:
We can then further use conditional formatting to color in red the misaligned rankings to make it easier to spot them.
9. Identify potential ranked pages international misalignment issues
We can also use a similar approach when we have multi-country or multi-lingual sites to identify international Web rankings misalignment issues by also importing the country of the rankings via the Search Analytics Add-on along the queries and pages. By doing this we can spot pages belonging to the Spanish version ranking in English speaking countries for which there’s already relevant content in English, or pages belonging to the Mexico version ranking in Spain, for which there are other pages to be ranked, etc. that we will want to prioritize to implement hreflang annotations.
In this case, I use it to validate the first scenario, pages belonging to the Spanish version not ranking in Spanish speaking countries by using the IFS and nested IF functions to first identify if the page belonged to the Spanish or English version:
and then validate if there was a misalignment vs. the identified country of the rankings provided through the Search Analytics Add-on, tagging as “aligned” if it’s a Spanish speaking country and “misaligned” otherwise:
Looking for more Google spreadsheets for SEO?
If you liked the previous simple but handy use of Google spreadsheets for SEO, check out these great guides for even more uses across many other scenarios:
- Free SEO Monitoring with Google Sheets
- 21 Google Sheets Formulas For SEO To Speed Up Your Workflow
- How to Automatically Categorise Keywords for the Buyer’s Journey
- Google Sheet: PAA (‘People Also Ask’) – Extract and Tidy
- 10 Google Sheets Formulas Every SEO Should Know
There are also a few fantastic bigger Google spreadsheets resources Websites that you should definitely check out:
- Using Google Sheets For Custom SEO Tools, with many tutorials and ready to use resources for SEO within Google Docs: from Extracting URLs From XML Sitemaps In Google Sheets to Semrush API Library In Google Sheets (Google Scripts)
- Sheets for marketers, an aggregation of free Google Spreadsheets templates and resources to use across many different types of SEO, social and analytics related activities.
- RankTank, offering tools developed within Google Spreadsheets that can connect also with third-party tools for data.
Do you know of any other Google Spreadsheets resources? If so, share them in the comments, I would love to try them out!