Most SEOs are familiar with popular tools such as Majestic, Screaming Frog, Keywordtool.io, Google Trends, and many others. These tools all serve a specific function of SEO and often come with a premium price tag. But, very few SEOs are taking advantage of one of the most powerful, free SEO tools out there - Google Sheets.
For those that are unfamiliar, Google Sheets is a free spreadsheet program that allows users to create and edit spreadsheets. It’s essentially a free cloud version of Microsoft Excel. In fact, a majority of the same functions and formulas that are used in Excel work just the same in Google Sheets. And because this is a Google product, Google Sheets can actually communicate with other Google products such as Google Analytics!
So how can you best use this wonderful tool in order to take your SEO game to the next level? Let’s take a look at some different scenarios that can help you improve your site rankings.
1. Web Crawling using ImportXML
Google Sheets has a few really nice formulas that can actually crawl the back end of a webpage and return specific data for you. While there are free tools that are already built to do this, this method offers you a few features that are not present in those other tools. For starters, the free version of popular web crawlers only allow you to crawl up to 500 pages. With Google Sheets, there is no limit. Additionally, you can program Google Sheets to grab any piece of data from the backend of the site. Other web crawling tools limit you to their built-in features. Let’s say that you want to import a list of all of the images that don’t contain Alt Tags. You could program Google Sheets to find that information for you using the ImportXML formula.
The ImportXML formula requires two inputs: the URL, and the XPath of the data that you want returned. XPath can be found by right clicking on an item to inspect the element, and then right clicking on the element code to copy the XPath.
Once you have found the XPath, you can insert it into the formula to return your desired value. The next step is to get a list of URLs that you want to crawl. You can either use the XML sitemap, or use a Google site search to show all pages of the website that are indexed by Google and import that page list into Google Sheets. Now you can pull specific data from all pages or desired pages of the site instantly. This can be useful for both analysing a competitor website, as well as cleaning up your own website.
2. Generating Google Analytics Reports Automatically Across Multiple Accounts
As mentioned before, Google Sheets is able to communicate with other Google products including Google Analytics. There is a great, free Add-on that makes it very simple to pull data and generate reports that run automatically. Search for Google Analytics in the Add-ons menu and click on the first result called Google Analytics. After you have added this Add-on, you can start generating reports.
To generate a report, select Create New Report under Google Analytics in the Add-ons menu. You’ll need to grab the view-ID for each of the analytics accounts that you want to run reports for. Then select the metrics and dimensions that you want to pull.
Using this Add-on, you can pull almost any metric or dimension from your Google Analytics reporting, and have them all in one spreadsheet and ready to analyze. This can be a huge time saver for SEOs with a larger client load. At Page 1 Solutions, we use this Add-on to pull traffic, page views, mobile traffic, and a few other metrics for a very large number of clients all at once. This allows us to monitor SEO health very quickly without having to look into each client’s analytics one by one. If Google has updated their search algorithm, we can quickly identify what impact the algo updates had. We can also analyze how users are interacting with our sites across different verticals, and then optimize accordingly.
3. Backlink Research and Outreach
SEOs know the power a good backlink profile can have on your rankings. But, they also know how time consuming it can be to find those high quality sites, and reach out to those resources in order to obtain a backlink. With Google Sheets, you can quickly identify a list of websites, extract an email address, and keep track of the outreach efforts.
Now you’ve got a list of 100 links that are ranking for keywords related to your website. Obviously, not all of them are going to be valuable. In order to determine which backlinks you should pursue, you need to identify the PageRank. The higher the PageRank, the more valuable the backlink will be to your website. To identify PageRank for a bulk of URLs, try using this handy, free spreadsheet tool from Techlila.com: Bulk Google PageRank Checker. Paste all of the text links that you attained above into this tool, and then sort by the PageRank.
Once you have those URLs all sorted, you need to find a way to reach out to the websites that have sufficient value. You can weed out sites like Wikipedia, Facebook, or the like which won’t be able to provide you with what you need. Instead, focus on high authority niche sites. Using the ImportXML formula, you can attempt to extract email addresses in bulk from those URLs that you deem worthy. This can also be done using other tools such as Screaming Frog. Doing this within Google Sheets helps you to easily create a database of contacts that you can make notes in and maintain as a starting point with your outreach efforts.
Remember, when you are reaching out to these other websites, it is important that you are providing value for them too. If your sitel provides additional information that would be useful to the users of their sites, then you are adding and providing value to them. If you are not providing value to their users, then you are not likely to earn a backlink.
There are lots of smaller, less time-consuming projects that you can do with Google Sheets that can help you in your daily work.
You can use conditional formatting to identify duplicates in a list of data. This helps you see if any two pages are using the same Page Title or Meta Description. You can also use user-created Add-ons to remove duplicates from a list in order to be left with only unique values.
The Concatenate function is useful for combining different strings of data into one string. Using this function in conjunction with some VLookups, you could create a JSON Schema code generator for local business data and videos.
The LEN function will allow you to calculate the length in characters of a string of text. This can help you determine if your meta tags are the proper length.
PowerTools is an Add-on with a split function (similar to text-to-columns in Excel) that will allow you to split a string of data in a single cell into multiple cells. This makes it very easy to separate the file path from the main domain in a set of URLs. This can be very helpful for bulk uploading a list of 301 redirects.
Sorting tables of data, such as your auto-generated Google Analytics reports, can make it very easy to see which of your sites are receiving the most traffic, or have the highest bounce rate. Now you know where to focus your attention and resources much faster than manual data review.
Google Sheets: Your New Best Friend
There are almost no limits to what this tool is capable of. And there are endless online resources that can assist you in creating the right formulas to get the data that you want. Once you start using Google Sheets, it will quickly become one of the most powerful tools in your arsenal. From simple functions such as calculating length of a string of text to more complex projects such as generating Schema code, Google Sheets can be an SEO’s best friend.
-Paul Duncan, SEO Specialist