I love Google sheets. 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 sheets for:
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:
You can copy the sheet here to use as a reference for your own migration validation.
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:
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:
You can view and copy the Google sheet with the functions example here.
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:
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.
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.
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.
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.
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.
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:
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.
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:
If you liked the previous simple but handy use of Google sheets for SEO, check out these great guides for even more uses across many other scenarios:
There are also a few fantastic bigger Google sheets resources Websites that you should definitely check out:
Do you know of any other Google sheets resources? If so, share them in the comments, I would love to try them out!
Accelerate your ecommerce product pages SEO analysis with the "The Ecommerce Product Page SEO Validator"…
Assess the potential traffic risk of Google's SGE for your own site top queries with…
Learn about the 3 different type of SGE snapshots, the level of risks toward the…
From the uncertainty of Google generative search to the unpredictability of core updates or Web migration…
Take a look at how the SERP changes with Google's SGE Snapshots for top Black…
First test of the new Google Search Generative Experience and how could it impact user…
Ver comentarios
Hi Aleyda,
Thanks for your awesome post. Super helpful, just added it to our must read list for new colleagues.
As we haven't spread the word outside Germany yet, you might don't know our onpage monitoring tool based on Google sheets. We created it to automize checking e.g. canonical tags, robots instructions, ... on a regular basis. Just define what the script shall look for, set the expected value and get notified if something breaks. You can find the tool here => https://www.deptagency.com/download/quickly-detect-onpage-errors-free-onpage-monitoring-tool/
Best regards,
Stephan
Thank you for sharing the time saving ways to use Google Spread sheet. I had created content creation management spread sheets with Dashboard and individual files for every writer. It reflects two ways to keep both the files updated. It was amazing to create it.
WOW! This is great Aleyda! Thanks for sharing, will be incorporating your resources to my toolset!
I like the no 3 tip. I actually described a similar case but I don't check backlinks. I do check occurance of a query in headers, title tags or in the content.
Exactly what I need right now. Great extra tips thank you for sharing.
Treasure!
Superb insightful blog post, aleyda!!
Loved the things mentioned with proper steps and up to date information. Its really helpful when we have to work on 2 or more SEO projects at the same time. Grateful for your help.
Have a great day