For many in the SEO industry, the migration from Microsoft Excel to Google Sheets has been sluggish.
Folks seem to prefer Microsoft’s offering when it comes to undertaking large-scale analysis, but that has started to change of late.
Google Sheets has always had some powerful selling points, namely its potential for cloud-based collaboration and handy functions like =importxml(). However, that has often not been sufficient for SEOs accustomed to the more sophisticated and reliable functions Excel can offer.
Sheets does have something extra to offer: its extensive library of add-ons. Some of these simply add functions that any Excel aficionado would take for granted, but others go further than that, perhaps giving Google’s spreadsheet platform an edge in this ongoing battle.
Downloading just a handful of these add-ons can automate quotidian SEO tasks, providing fresh insight and simply making us better at what we do.
To get started, just click on ‘Add-ons’ in the top navigation:
From here, a vast array of options is instantly available.
Below, I have selected just four of my personal favorites:
Search Analytics for Sheets
Getting accurate keyword-level data has been an increasingly uphill task for SEOs. We have become dependent on Search Analytics as a data source.
However, there are still some significant shortcomings with this platform. Leaving aside the unchangeable aspects of this, such as the two-day lag (if we’re lucky) in data and the normalized search volumes, the user interface also does us few favors.
That’s where Search Analytics for Sheets comes in.
This add-on fulfills an essential function by using the Search Analytics API to pull your information into Sheets and providing a slew of new ways to slice the data.
Some common questions we go to Search Analytics to answer, at times with difficulty due to its limited functionality, include:
- How often does my site rank for a particular query across multiple URLs?
- Google has expanded its paid advertising footprint. How has this affected my SEO CTR by device, location, and keyword?
- How is seasonality affecting my website’s performance? (The 90-day limit on Search Analytics data is a real restriction on this kind of analysis.)
By using the right sidebar options to call for your data by Query and Page, for example, the information will be pulled into Sheets directly, as in the example below:
This creates new opportunities to filter query-level performance by URL, and there are options to add extra groups to the spreadsheet such as Country or Device.
Moreover, you can also schedule automated downloads of your data, so you’ll never need to worry about that 90-day historical limit again.
Supermetrics is a very useful tool for pulling data from a wide variety of different connectors into Sheets.
Their website highlights just how many of these are now available:
The pro version does come with an attached cost of $49/month, so this doesn’t quite rank alongside the great selection of free add-ons available for Sheets.
However, Supermetrics does provide two essential capabilities. First of all, it creates an easy way to synthesize a number of data sources within one spreadsheet, which in turn allows for further data manipulation and analysis.
However, the role that Supermetrics has increasingly come to play in my daily activities is as an intermediary between platforms like Facebook and integrated reporting solutions like Google’s Data Studio.
With Data Studio now free to use worldwide, it is a powerful tool for everyday reporting and analysis. That said, it does lack integration with some important connectors, such as Facebook Ads.
By using Supermetrics, we can pull Facebook data into Sheets and then sync that spreadsheet with our Data Studio report.
The valuable solutions that it brings make Supermetrics a very helpful add-on for any SEO that wants to assess their data in the context of the wider digital marketing picture.
Google Analytics for Sheets
The Google Analytics add-on is notable primarily for its ability to create and schedule custom reports within Sheets.
Once the add-on is installed, the following options will be readily available within a user’s Sheets account:
By going to the right sidebar and setting the View and Start/End dates, along with the requested Dimensions and Metrics, data will be pulled from the selected Analytics account.
It does take a certain level of technical fluency to master its use of Regular Expressions, but users familiar with the data segmentation used within Google Analytics should have no issues compiling reports, allowing for some trial and error.
Google has produced a short but comprehensive video that will guide users through building their first report if any hurdles are encountered along the way.
Although the interface may not be the most intuitive, this add-on provides a further level of flexibility to Analytics data and will save a lot of time normally spent isolating data points, once the reports are set up and scheduled.
If that wasn’t enough, you can also combine this add-on with Search Analytics for Sheets to start uncovering keyword-level data in a lot more detail. This guide over on Moz gives a great overview of how to get started.
Text Analysis is a fantastic add-on for expediting the keyword research process, and it can also help with content idea generation.
The tool works by taking as its stimulus a selected topic, hashtag, or Twitter handle. It is then capable of pulling in and analyzing all related links, documents, and Tweets. This is powered by the AYLIEN Text Analysis API, which uses natural language processing to assess sentiment within content, for example.
It contains a range of handy functions, such as the option to summarize longer pieces of content and the ability to recognize frequently-used key phrases.
As with all such technologies, its findings do need to be taken with a grain of salt sometimes (particularly the sentiment analysis), but as a starting point for wide-ranging topic research, it is still a go-to tool.
It is worth noting, however, that this is a freemium add-on; users will have to purchase credits to analyze larger data sets.
To insert some of the typical Excel functions within Sheets, it is also worth installing Power Tools, Remove Duplicates, and Advanced Find and Replace. Although not SEO-specific, these do all serve to streamline a number of data analysis tasks.
All of these enhancements to Google Sheets, along with its in-built competitive advantage as a cloud-based solution and Data Studio integration, now make this platform a real contender when it comes to everyday SEO analysis.
Featured image: Pixabay
All screenshots taken by Clark Boyd, March 2017