Smart Google Sheets for Your Off-Page SEO

You can use Google Sheets to automate  keyword research for SEO. The otherwise time-consuming process of researching and pinpointing the right keywords can be made a whole lot quicker and easier with some simple Google Sheets templates.

Whether you want to be able to make a quick traffic estimate for a certain keyword, identify keyword gaps, track keyword rankings, or check search volumes, there are numerous Google Sheets templates for these and more purposes, available online which you can download and start using today. Effectively, Google Sheets offer you a free SEO project management resource requiring zero project management training.

Essentially, Google Sheets templates are tables to which you can input data either by importing it from SEO tools or entering it manually, which will automatically process the input values through functions and give you a concise report or overview of all relevant information. 

If you know your way around Google Sheets and its features, you can create your own template to help you with keyword research. Otherwise, you can download the templates you need completely free in most cases.

While it can’t help you with  content writing for SEO, when used in conjunction with some SEO tools, Google Sheets can make the process of finding guest posting opportunities both quick and easy.

Below, we will give you an example of how you can make your own Google Sheets template to kick off your link building campaign in just a few clicks.

First of all, you will need to find link prospects. In order to do this, you will want to use an SEO tool, such as the Ahrefs content explorer, to find relevant pages by searching for topics related to your niche. 

To narrow your search results, make sure you set the language to English, or the language you plan to write your posts in and filter the results to only display live pages. You can also set the minimum and maximum domain rating to only view pages with a rating within the specified range.

Source: https://www.youtube.com/watch?v=bwYZGWDlQd8&t=10s

Once you have the results, export and import them into a Google Sheets file. You now have your raw datasheet. You might notice that you have a plethora of information, most of which is redundant. To parse relevant bits of information to another, more organized sheet, you can use the QUERY function.

Create a new sheet within the Google Sheets document and type a QUERY formula in the first cell to import the relevant columns. The formula should look like this:

Where column B is the Content title, column C is Content URL, column E is Domain rating, and column D is Author. You’ll want to exclude the pages that don’t have the name of the author in the Author column, and you can do this by expanding the previous formula to look like this:

Before you can proceed to search for emails, you will need to parse the Author’s name into first and last, since most email finding tools require both. Create two more headers for first and last names in columns E and F.

To parse the full names into these two columns, you can use the following two formulas:

For the first name (column E): =IFERROR(ARRAYFORMULA(LEFT(D2:D,FIND(” “,D2:D)-1)))

For the last name (column F): =IFERROR(ArrayFormula(TRIM(RIGHT(SUBSTITUTE(D2:D,” “,REPT(” “,100)),100))))

Note that by using ArrayFormula and extending the range to the entire column with D2:D, you won’t have to copy or drag the formula for each cell, as Google Sheets will perform the action for the entire column automatically.

Once you have the first and last names parsed, you are ready to look for emails. For this purpose, you can use one of the useful email finding add-ons for Google Sheets. Click Add-ons -> Get Add-ons, and find the one you want to use. Once you’ve added the tool, you can open it from the Add-ons tab.

Now that you have a bunch of emails, you can use an online email validation tool to check how many of them are actually valid. Create a new “Email Validation” sheet and type =UNIQUE( in the first cell, and then choose the column for which you want unique values – the column with the email addresses from the previous sheet. Download the sheet in .csv format and upload it to the online validation tool. Once you have the results, download them and import them to your Email Validation sheet.

Finally, you can match the validation statuses to the list of emails. Create a Validation column next to the Email column in your second sheet with parsed information, and enter a VLOOKUP function in the first cell of the new column. The formula should look like this:

Where G2:G is the column containing the email addresses in the second sheet, and !A:B are the two columns in the Email Validation sheet.

To display only valid email addresses, create a filter for the sheet and filter the validation column to only show valid results. You should end up with a sheet looking like this:

Source: https://www.youtube.com/watch?v=bwYZGWDlQd8&t=10s

You can now start reaching out to guest posting prospects! Make sure you also save this template so you can use it to speed up the process next time.

Apart from the two possible applications of Google Sheets for your off-page SEO explained in this article, Google spreadsheets can also be used for simpler tasks like organizing your workflow, keeping track of different types of data and metrics, etc.

Although not all processes of your off-page strategy can be automated, using Google Sheets to automate or at least shorten those that can, will save you a lot of time and energy and let you focus on other tasks, such as producing quality content.