How to Automate Multilingual Email Campaigns with Google Sheets

62% of marketing teams spend more than two weeks creating one promotional email. Time is spent on design, content writing, testing, and so on. For companies that operate in foreign markets or countries with several main languages spoken, the task becomes more difficult. In this case, marketers have to create copies of the same message but in different languages, so leveraging the power of automation becomes a wise solution.

Yespo CDP has functionality that helps companies speed up multilingual customer communication up to 5 times. One of the methods for automating this process with our platform is data substitution from an external source. For this task, you have to use integration with Google Sheets.

How to Set up Dynamic Email Content Substitution in Yespo

Let's look at the process of automating multilingual campaigns using Google Sheets, following the example of the Find My Kids company.

We will set up dynamic content substitution in an email using the Apache Velocity library. Using the code editor, you need to add a #foreach loop to the element.

1. Declare a variable that contains the language parameter. In our example, this is the Locale field. Its code looks like this: %DATA.LOCALE%

The value of this variable can be found in the system on the Settings → Additional fields tab:

To declare a variable, you need to make an entry like this:

2. Create a structure that performs the following functions:

• checks that the DATA.LOCALE field has a value and is not empty;
• the found language value in the DATA.LOCALE field is used to search for the corresponding value in the Google Sheets.

In the letter code, points 1 and 2 will look like this:

Let's find out the meaning of some variables from the described code:

#foreach($!item in $!data.get('table_name')) – access Google Sheets, where table_name is the name of the source created from the table:

#if($!item.get('local')==$local) – look for the local variable in the Google Sheets, where the locale is specified, and compare it with the value of the $local variable (where we got the value from the additional field # set($local=$!item.get('DATA.LOCALE'))):

3. Create the final structure, which will display the content in the letter:

!--#foreach($!i in [0..$!mathTool.sub($!filtered.size(),1)]) #if($!i%3==0) #if($!filtered .size()-$i==1) – start a loop through the previously filtered array.

#set($phrase1=$!filtered.get($!i).get('Phrase1')) – pull out the value for the first variable, for example, the title.

#set($phrase2=$!filtered.get($!i).get('Phrase2')) – pull out the value for the second variable, for example, text.

#set($phrase3=$!filtered.get($!i).get('Phrase3')) – pull out the value for the third variable, for example, a button.

-->

– close the loop at the bottom of the letter.

Please note that the loop should be opened at the beginning of the letter and closed at the end of the letter. Opening and closing the loop must occur at the same level in the html code hierarchy.

Let's take a closer look at the meaning of some variables from the code above:

#set($phrase1=$!filtered.get($!i).get('Phrase1')) – create the $phrase1 variable.

$!filtered.get($!i).get('Phrase1') – substitute a variable from the table that contains the first phrase you need:

Next, we insert the $phrase1 into the desired place in the letter where we need to display the contents from the sheets.

In order not to visually clutter up the layout of the letter with a large amount of code, you can enter the necessary values in the html editor using the assignment syntax:

If you need to add different images to versions of the letter, you need to insert a Velocity expression into the src attribute in the code editor.

Instead of a link to the image, you should indicate:

$!filtered.get($!i).get('img'),

where 'Img' is the name of the column that contains a link to the corresponding image.

Using the syntax, you can set various conditions for displaying content. In our case, the attribute is checked in the “Locale” field, depending on which the text in the desired language is substituted:

This way, the email will consist entirely of dynamic content, and there is no need to create layouts for other languages.

Conclusion

Using these instructions, the Find My Kids app marketer managed to independently set up a multilingual newsletter announcing a global-wide promotion for 1.5 subscribers from 23 countries. We prepared 2 tables for it: one for languages with text written from left to right, the second – from right to left.

Thanks to this method, the company's marketers were able to reduce the time for designing letters from 24 to 12 hours. In addition, this tactic made it possible to avoid human errors that can occur when manually working with a large number of email copies and numerous audience segments. Discover more about this case.

Do you also want to automate some of your tasks to work more efficiently? Then write to info@yespo.io, and our specialists will be happy to advise which Yespo CDP tools meet the needs of your business best!