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.
Also not sure who needs to hear this – but I’m obsessed with this Google Sheets function
— Jackie Chu (@jackiecchu) July 29, 2020
Insanely helpful for working with non-English keywords pic.twitter.com/Gi2PQGtbGD
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
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
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:
Thanks again to Jackie Chu for the inspiration. Feel free to use the sheet however you choose here.
