Google Sheets at SEO specialists’ service – part II

Welcome again to another one of our mini-talks on TOP Google Sheets functions that every SEO specialist (or Content specialist) must know. What you’ll find below are another 5 solutions to brighten and improve every marketing strategy. And if for some reason you missed our first article, feel free to catch up here: ”Google Sheets at SEO specialists’ service – part I”. Done? Want more? Let’s go then!

Find and replace

A simple and multifunctional non-formula. It allows you to find a particular character, text or even a space and replace it with any value (or delete it).

Press Ctrl+H (or Command + Shift + F on Mac) to open a very intuitive panel dedicated to this feature. Now you can request that the sheet replace your current domain name, przykladowa-strona.pl, with nowa-strona.pl. Optionally, you can also indicate a specific scope of these “actions”, e.g. column A only.

What do we need it in SEO for?

• to remove or replace specific characters and texts;

• to adjust key data between reports from different sources (e.g. adjusting URLs in GA reports);

• during migrations, e.g. to replace the names of specific directories;

• to correct addresses from “http” to “https”, after implementing the SSL certificate;

• for quick modification of metadata, e.g. change of location, brand or CTA.

Tip: If you have a strong suspicion that your keyword column has extra spaces (double spaces between words or spaces after the last phrase), a better solution is to use TRIM. It will only leave the regular spaces between words, removing all unnecessary ones.

=TRIM(text)

Tip 2: Remember! When you use “Find and replace”, you overwrite your data in the spreadsheet. If you plan to keep it, read on.

SUBSTITUTE function

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

It’s an alternative formula to ”Find and replace”, which allows you to keep output data. Let’s say you’re working on the migration of the UltralightOutdoorGear website onto a new domain – from the ultralightoutdoorgear.co.uk/ directory to ultralightoutdoorgear.com/uk/. And here comes in handy the SUBSTITUTE formula:

What do we need it in SEO for?

• It has a similar use as “Find and replace”, we choose it when we care about preserving the output data;

• ideal for creating a redirect map

Tip: An alternative to our alternative SUBSTITUTE formula is the REPLACE formula. It also preserves the output, but differs in how it indicates the characters to replace. The string to be replaced here is not defined as specific text, but as a numeric string of characters.

=REPLACE(text, position, length, new_text)

It’s used much more rarely, but I’ll just leave it here anyway!

IMPORTXML function

=IMPORTXML(url, xpath_query)

It’s such a quick way to extract data from a given URL, without running a crawler! This formula is based on XPath queries but you don’t need to have an XPath certificate to use it. Read on to find some readymades for the most frequently extracted data.

Example time! Let’s say you want to check whether the meta data prepared by you has been implemented on the website. Let the formula return the title and description for each of the URLs:

What formulas can you use?

• “//h1” – extraction of H1 headers, if the page contains more than one first row header, they will also be returned in the sheet;

• “//title” – as in the example, you extract the meta title for the indicated URL;

• “//meta[@name=’description’]/@content” – you get your current meta description;

• “//link[@rel=’canonical’]/@href” – returns a link set as canonical for a given address;

• “//meta[@name=’robots’]/@content” – returns the contents of the robots tag;

• “//@href” – you get a list of all links placed on the page;

• “//a[contains(@href, ‘domain_name.pl’)]/@href” – as above, but you extract only internal links leading to other subpages within the same domain;

• “//a[not(contains(@href, ‘domain_name.pl’))]/@href” – contrary to the previous one, you get a list of external links.

What do we need it in SEO for?

• to check whether the links applied on the website have the appropriate indexing parameters;

• to verify canonical addresses;

• to check if the new meta and H1 proposals have been properly implemented on the site;

• while constructing the best meta version for a given key phrase, to be inspired by the current TOP 10 results;

• generally speaking, for a quick check of individual addresses, without running a crawler

COUNTIF function

=COUNTIF(range, criterion)

It allows you to count the cells that meet the specified criterion. Perhaps you want to count the number of phrases containing “Warsaw” and compare it with the target phrases “Wrocław”? ¡No hay problema! You can also rely on numerical criteria, e.g. count the number of keywords on which our domain ranks in the TOP 10:

Tip: Why go with just one criterion?^^ Use the formula COUNTIFS to define more conditions and extract even more accurate data. Need an example? Let’s count the number of phrases in the TOP 10 again, but this time we want them to be only phrases containing “jacket”.

=COUNTIFS(range1, criterion1,[range2, …], [criterion2, …])

SUMIF function

=SUMIF(range, criterion, sum_range)

It has got a similar syntax to the previous function. However, its purpose is to return the sum for the selected criterion. Continuing our example, let’s count the estimated traffic generated by the TOP 10 phrases:

Tip: As in the case of COUNTIF, also with the total, you can use a variant with multiple criteria and refine our data. You will use the SUMIFS formula here. How much traffic was generated by TOP 10 phrases containing “jacket”?

=SUMIFS(sum_range, range1, criterion1,[range2, …], [criterion2, …])

What do we need it for in SEO?

• to report the number of items in any TOPs, also taking into account specific groups of phrases or directories;

• to count URLs in individual directories of the website;

• to count potential traffic/clicks from groups of phrases or URLs.

No more filtering and selecting, ladies and gentlemen! Formulas allow you to enter a new level.

Summary

Another five ticked off the list! SEO specialists have got quite a toolkit at their disposal and there is still so much to be discovered… That’s why I’m already working on the final episode of this trilogy. Spoiler alert! Only UNIQUE formulas and EXs coming up. Stay tuned!