by Sawab Allaladé on 25/02/2020 |

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:

=IFERROR(VLOOKUP(C2,‘SEMrus Backlinks’!$A$2:$J$2830,4,false),“”)

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:

=IMPORTXML(C2,“//title”)

=IMPORTXML(C2,“//meta[@name=’description’]/@content”)

=IMPORTXML(C2,“//h1”)

=IMPORTXML(C2,“//meta[@name=’robots’]/@content”)

=IMPORTXML(C2,“//link[@rel=’canonical’]/@href”)

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:

=arrayformula(iferror(ifs(not(iserror

(find(“/es/”,B2:B))),“Spanish”,

not(iserror(find(“remoters.net”,B2:B))),

“English”)))

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(AND(C2=“Spanish”,OR(D2=“esp”,

D2=“arg”,D2=“mex”,D2=“ury”,

D2=“ven”,D2=“per”,D2=“col”,D2=“dom”,

D2=“bol”,D2=“pan”)),“Aligned”,“Misaligned”)

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):

=UPPER(text)

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:

=PROPER (TRIM(A21))

Once you hit Enter, the results will look like this:

Pro Tip 2:You can also combine the aforementioned ARRAYFORMULA with PROPER:

=arrayformula(proper(A1:A))

This way you can copy the messy text from column A, correct it and place it nicely formatted in column B.

To find out more on handy SEO-specific Google Sheets formulas, check out this informative in-depth article by BlueClaw.

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.

If you have any further question about SEO, don't hesitate to contact us, we are the best web agency in Montreal.

Sawab Allaladé

Sawab A, is the co-founder and Managing Partner at OSHARA. He is passionate about Branding and Marketing and he helps businesses grow their brand and reach new heights of success using simple but efficient web technologies and digital marketing. His expertise and ability to listen and execute bring an added value to any team.

Was this article useful?

People Who Read This Article Also Read...

Laravel is a powerful php framework that allows you to create custom web applications

How to install a Laravel web a...

By definition, a communication agency (marketing) is a company that plans and harmonizes internal and / or external communication on be...

Everything You Need To Know Ab...

Collecting data from customers is an important marketing strategy since it gives you information on what customers exactly want instead...

12 questions to ask when colle...

Did you already try udpating your joomla website and you got this error : "An error has occurred. 0 Cannot open file for writing log" ...

How to fix Joomla Error 0 'Can...

A workflow is a repeatable scheme or pattern of a sequence of steps or activities that are undertaken to realize the completion of a pa...

This is How you can Simplify C...

ETL is an abbreviation for extract, transform, and load. It is one of data integration tools used in the extraction of data from a sour...