How do I effectively use my Google sheets to manage my SEO tasks?
1. 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:
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.
2. 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.
3. 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:
4. USE SIMPLE GOOGLE SHEETS FORMULAS TO FORMAT
Those who like to work quickly and leave ironing out the formatting for later, are likely using the copy/paste option a lot. If this is the case, the best way to deal with messy text formatting in the cells is by using Google Sheets Formulas.
Four (4) functions to help you with this include:
UPPER – change all to uppercase
LOWER – change all to lowercase
PROPER – capitalize each word
TRIM – remove extra spaces within the data text
To use these Google Sheets Formulas, simply type in the following (we’ll use the UPPER formula in our example):
NOTE: Instead of “text” in the brackets, you can also insert the cell number, for example (A1). Or you can simply type in =upper() and while your cursor is placed within the empty brackets, you can just click on the desired cell(s). The same principle goes for other Google Sheets Formulas.
Pro Tip: You can also combine PROPER and TRIM formulas into the same function and get two functions for the price of one, like this:
Once you hit Enter, the results will look like this:
Pro Tip 2:You can also combine the aforementioned ARRAYFORMULA with PROPER:
This way you can copy the messy text from column A, correct it and place it nicely formatted in column B.
5. LEARN HOW TO MAKE A GRAPH IN GOOGLE SHEETS
In order to make your relevant data come to life so it is easier to convey what your SEO efforts are all about, you will want to create and customize graphs (or charts) in Google Sheets.
This can be done in 3 easy steps:
1. Select the cells you want to include in your graph
2. Click “Insert” from the dropdown menu and select “Chart”
3. Once the chart pops up, you can customize it via the Chart Editor located on the right-hand side of the screen.
The Chart Editor window appears each time you create a chart, and has two main tabs:
Setup – here you can choose the chart type and the data range you want to include in your visualization.
Customize – here you can modify your chart’s appearance.
If all the data you want to show adds up to 100% – we suggest using a pie chart; if you want to compare data over a certain period of time – use histograms or line graphs, etc.