Monday, March 20, 2023
  • Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Disclaimer
  • Term & Condition
SEO Tools
Get leads. Get sales. Get growing.
  • Home
  • Free SEO Tools
  • Search Engine Optimization
  • Digital Marketing
  • Marketing Strategy
  • Affiliate Marketing
  • SEO Tools
No Result
View All Result
SEO Tools
  • Home
  • Free SEO Tools
  • Search Engine Optimization
  • Digital Marketing
  • Marketing Strategy
  • Affiliate Marketing
  • SEO Tools
No Result
View All Result
SEO Tools
No Result
View All Result
Home SEO Tools

11 Google Sheets formulas SEOs should know

Admin by Admin
August 14, 2022
in SEO Tools
0
11 Google Sheets formulas SEOs should know
585
SHARES
3.2k
VIEWS
Share on FacebookShare on Twitter

[ad_1]

Sometimes the best SEO tools are free.

Look no further than Google Sheets.

While it’s not great at plotting ranking data (inverting the y-axis is always ugly), there are numerous ways to use Google Sheets for SEO.

Here are 11 of the formulas and tips I find myself using for SEO on an almost daily basis – for keyword management, internationalization, content/URL management and dashboards.


Get the daily newsletter search marketers rely on.

You might also like

Best Digital Marketing Courses: A Review of 5 Top Online Courses

7 Ways Ecommerce Businesses Use Alternative Data in 2022

What Is It & How To Do It


Google Sheets formulas for keyword management 

  • V LOOKUP
  • CONCATENATE
  • FLATTEN
  • LOWER

=VLOOKUP(text,[range to search],[column number to return],[true/false]) 

V LOOKUP (documentation)

VLOOKUP, which stands for “vertical lookup”, is arguably one of the very first Google Sheet formulas for SEO anyone learns when getting into the game.

VLOOKUP allows you to essentially combine two data sets on common values, an almost lowbrow JOIN in SQL if you will.

I generally use this formula to enrich information about keyword sets by adding search volume, PPC data or adding downstream metrics like signups. 

The end directive true/false specifies how exact you want the match to be, TRUE means not an exact match, and FALSE means exact matches only.

Tip: LOCK the range you’re searching against using $ ($E$3:$E$5 in the below example) so you can drag and carry the same formula across many rows.

=CONCATENATE(A1,A2,A3) 

CONCATENATE (documentation)

  • =CONCATENATE(A1,A2,A3) you have the option to concatenate columns
  • =CONCATENATE(A1,” I’m additional text”) or literal words and characters

Concatenate is one of the most commonly used Google Sheet formulas in SEO, and for good reason.

It can serve a variety of use cases, including creating keyword lists (concatenating two+ variables together), creating URL strings, or even bulk templatizing metadata.

As the name suggests, you can use it to simply string any amount of values together.

Just remember: if you need a space between keywords, a literal space “ “ must be added.

=FLATTEN(range1, [range2, …]) 

FLATTEN (documentation)

  • =FLATTEN(A:D) would compress all ranges in A – D in to one column

There’s a reason FLATTEN is coming after concatenate. After you’ve concatenated several thousands of keywords and a couple of hundred dollars away, you generally need to upload the keywords into your rank tracking tool’s UI or via a CSV bulk upload.

It can be tedious when you have a 20×20 block of keywords to get them into a single column so you can upload all your keywords in one go.

With FLATTEN, you essentially select the range of data you want and the output is all of your keywords in one column to make copy-pasting a dream!

=LOWER(text) 

LOWER (documentation)

This one’s pretty simple – but it can be helpful to LOWERcase all the of the keywords you’re managing (especially if you use a service provider that charges for things like duplicates) or if you’re in a case-sensitive environment like SQL.

LOWER is admittedly one of the simplest Google Sheets formulas for SEO.

The opposite (UPPER) also works, should you feel like auto-capping everything. 

=COUNTIF(range,”[text or function]”) 

COUNTIF (documentation)

COUNTIF lets you count, with accuracy, any literal text you want to match or even some numerical values that meet conditional rules.

It’s particularly useful when grouping together pages, managing an upcoming content calendar or sorting keywords on common dimensions like the page type or product they support.

It can also be used with conditions to match values, such as ones that have CPCs > $10.00 or that have a search volume > 100 searches a month. 

=SUMIF([range to search],”[condition to match]”,[range to return]) 

SUMIF (documentation)

SUMIF is similar to COUNTIF, but is helpful if you’re trying to add up an additional metric associated with the group of interest, like summing up total keyword volume opportunities by themes or search volume by page type. 

Google Sheets formulas for internationalization

=GOOGLETRANSLATE(text, [“source_language” or “auto”, “target_language”])

GOOGLE TRANSLATE (documentation)

  • source_language = two-letter language code of the source language (or “auto” for Google to guess)
  • target_language = two-letter* language code for your target language, like ES for Spanish

Ahh, one of my favorite and most loved Google Sheets hacks.

Rather than go back and forth to the Google Translate UI and risk carpal tunnel, you can bulk translate lists of keywords in seconds into one, or even multiple languages.

You even have the option to auto-select the origin language by changing source_language to “auto” to let G sheets choose for you (which usually works, usually).

Google doesn’t support translating into all “flavors” of languages (e.g., Canadian French), but supports languages like pt-pt and pt-br, as well as Chinese languages like zh-tw and zh-cn.

Google Sheets formulas for content/URL management

=SPLIT(text,[delimiter wrapped in “”])

SPLIT  (documentation)

Many times when you’re doing an analysis you might be working with data that is not in the required format you need.

There might be extraneous information that is separated (delimited) by things like commas (addresses), phone numbers (parenthesis and hyphens) and more.

While there’s a “split text to columns function” in the toolbar under “Data”, you can also split text that is delimited by a specific character, word or even spaces to individual columns with the SPLIT command directly in the sheet so you can quickly trim and tidy your keyword list.

=LEN(text) 

LEN  (documentation)

LEN is a simple Google Sheets formula for SEO you can use to simply count the characters in a line or string.

It can be most helpful when guiding people (both SEOs and non-SEOs) who are writing their own metadata, to stay within a “safe” enough character count so that it will hopefully not get truncated simply due to length.

=REGEXREPLACE(text, “regular_expression”, “replacement”)

REGEXREPLACE  (documentation)

Regexes are a powerful data mining tool when working on large websites.

If you’ve never even heard of regexes, you’ve probably not yet been challenged with an enterprise-level site.

I find myself using REGEXREPLACE most often when I’m cleaning up or trimming URLs in a sheet, where it can be helpful when I only need a path name minus domain or to manage redirects.

Google Sheets formula for dashboards

=SPARKLINE(B3:G3)

SPARKLINE  (documentation)

  • =SPARKLINE(B3:G3,{“charttype”,”line”; “color”,”indigo”; “linewidth”,2}) this version of sparkline is in indigo, with a slightly heavier weight

While BI tools like Tableau and Looker offer additional customizations, Google Sheets can be a cheap way to build simple dashboards.

The command SPARKLINE is capable of leveraging data to create simple visualizations in a Google Sheet.

A good amount of SEO and web data looks great on a time series, and Google Sheets can make it easy.

This is most helpful when you have data that is being actively updated inside of Google Sheets and need to skim 10+ trends quickly in one sheet.

A popular use case is to monitor trends like growth in several countries, campaigns or city-level basis. 

=SPARKLINE(B3:G3,{“charttype”,”line”; “color”,”[color you want]”; “linewidth”,2})

Time series/line charts

Time series is probably the most helpful for visualizing changes to traffic patterns over time and is suitable for monitoring most traffic trends and north star goals.

You can also remove the “line width” command, weight and even color for a quick and easy graph, but I find for time series I always need the line to be a little bolder and the contrasting color helps draw attention to the graph.

Column charts and bar charts
Sparkline even supports column and bar charts! Just change the chart type to column (shown below) or bar.

In more advanced use cases, most of the formulas above can be manipulated to have enhanced outputs, like automated conditional formatting or fun Unicode emoticon responses instead of nulls.

No matter how advanced you make them, using these formulas inside of Google Sheets is a great and cheap way to do basic SEO tidying work and keyword research.


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


New on Search Engine Land

About The Author

Jackie Chu is currently the SEO Intelligence Lead at Uber Technologies in San Francisco. She has deep experience in B2B, B2C and online publishing, and has led SEO and ASO efforts both in-house and as a consultant for companies like Square, Dropbox and Yahoo. In addition to doing SEO, she loves wasting money at Barry’s Bootcamp, drinking sparkling wine and hanging out with her mini Goldendoodle, Bailey.

[ad_2]

Source link

Previous Post

The key to email marketing success

Next Post

Seo Hee Ham recalls her first ever title

Admin

Admin

Blogger & Youtuber

Related Posts

Best Digital Marketing Courses: A Review of 5 Top Online Courses
SEO Tools

Best Digital Marketing Courses: A Review of 5 Top Online Courses

by Admin
December 3, 2022
7 Ways Ecommerce Businesses Use Alternative Data in 2022
SEO Tools

7 Ways Ecommerce Businesses Use Alternative Data in 2022

by Admin
November 28, 2022
What Is It & How To Do It
SEO Tools

What Is It & How To Do It

by Admin
November 23, 2022
New study: leading SaaS SEO companies in the World
SEO Tools

New study: leading SaaS SEO companies in the World

by Admin
November 18, 2022
How To Increase Traffic To Your Website
SEO Tools

How To Increase Traffic To Your Website

by Admin
November 13, 2022
Next Post
Seo Hee Ham recalls her first ever title

Seo Hee Ham recalls her first ever title

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recommended

A North Texan’s Guide to the Cult of H-E-B – Texas Monthly

A North Texan’s Guide to the Cult of H-E-B – Texas Monthly

September 22, 2022
Yoast SEO 19.5 Update Causes Fatal Errors

Yoast SEO 19.5 Update Causes Fatal Errors

August 11, 2022
► Lets Getting Rich blog

► Free SEO & Digital Marketing Tools

► SEO Tools blog

► Latest RVH News

► Find a domain and launch your site today!

Categories

  • Affiliate Marketing
  • Digital Marketing
  • E-Commerce Marketing
  • Email Marketing
  • Marketing Strategy
  • Search Engine Optimization
  • SEO Tools

Don't miss it

From Rags To Riches During The Pandemic, Teamology PR Inspires Startups To Thrive
Digital Marketing

From Rags To Riches During The Pandemic, Teamology PR Inspires Startups To Thrive

December 4, 2022
Here’s how to stop the cost of Christmas from burning a big hole in your wallet
Affiliate Marketing

Here’s how to stop the cost of Christmas from burning a big hole in your wallet

December 3, 2022
Best Digital Marketing Courses: A Review of 5 Top Online Courses
SEO Tools

Best Digital Marketing Courses: A Review of 5 Top Online Courses

December 3, 2022
10 Stars that fans want to see on “The Manager”
Search Engine Optimization

10 Stars that fans want to see on “The Manager”

December 3, 2022
Irish privacy regulator fines Facebook 265 million euros, Marketing & Advertising News, ET BrandEquity
Digital Marketing

Irish privacy regulator fines Facebook 265 million euros, Marketing & Advertising News, ET BrandEquity

November 29, 2022
7 Ways Ecommerce Businesses Use Alternative Data in 2022
SEO Tools

7 Ways Ecommerce Businesses Use Alternative Data in 2022

November 28, 2022
  • About Us
  • Contact Us
  • Disclaimer
  • Home
  • Latest Post
  • Privacy Policy
  • Term & Condition
SEO & Digital Marketing Tools

© 2022 Blog.TopSEOsuperTools.com All Rights Reserved.

No Result
View All Result
  • Landing Page
  • Shop
  • Contact
  • Buy JNews

© 2022 Blog.TopSEOsuperTools.com All Rights Reserved.

What are cookies
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent.
Cookie SettingsAccept All
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT