Using Google Sheets’ Language Translation Formula for Keyword Research

, , ,

This post was inspired by a tweet by Jackie Chu, who leads SEO at Uber. Check out her blog here and follow her on Twitter here. The Google Sheet that’s referenced in this post can be found here and modified as you see fit.

If you’ve managed a global SEO campaign before, you probably know how tricky measuring progress on keywords in languages you don’t speak is. In English, it’s easy enough for me to segment keywords, compare branded vs. unbranded, and measure high-level trends.

Let’s say you’re running a campaign for a finance company and you want to track progress on keywords related to “compliance.” If you have international traffic, there will be countless variations of the word in different languages.

Or maybe you’ve been asked to benchmark a foreign-language Google Ads campaign. Having done exactly this before blindfolded (and poorly, I’ll admit), I recommend leveraging Google Sheets to get a better grip on the data.

Caveat

I would not recommend using this to translate things like meta titles or content. This will give you a better overall view of global search trends for your web properties. It’s not intended as a substitute for human translators. Google Translate can spit out some pretty goofy interpretations.

DETECTLANGUAGE

Google Sheets: Detect Language Formula

For this Google Sheet, I’ve included a column for query language. Assuming your first column contains the keyword, input the following into the left column:

=DETECTLANGUAGE (A2)

This will populate the sheet with the language of the search queries when available. For more on DETECTLANGUAGE, go to Google’s Docs Editors Help.

GOOGLETRANSLATE

Google Sheets: Translate Keyword Formula

GOOGLETRANSLATE is one of Google Sheets’ most powerful features. With a simple formula, you can translate search queries automatically and connect them to your marketing stack. As of February 2020, Google Translate supports 108 languages.

The sample spreadsheet includes formulas for English, Chinese, Spanish, Hindi, French, Bengali, Russian, Portuguese, and Italian. Languages are specified using ISO-639-1 Code parameters. A full list of supported language codes can be found here.

If you only want to detect query languages and translate to English, just use the following formula:

=GOOGLETRANSLATE(A2, "auto", "en")

Sample spreadsheet

Your spreadsheet should look something like this when you’re done:

Google Sheets: Foreign Keyword Language Keyword Research spreadsheet sample

Thanks again to Jackie Chu for the inspiration. Feel free to use the sheet however you choose here.

Latest blog posts

Thriving as a Young Marketer in B2B Technology

Young B2B technology marketers

Like anyone my age, there are things I would’ve approached differently in my 20s that would’ve accelerated my career progression. I wouldn’t say they’re regrets but if I woke up in my mid-20s, there are plenty of things I’d do differently this second go.