0

I'm newby in the ampscript world and need you help! For a mail for different countries I have a translation dE with several languages (column = language) and the main DE with customer records. In order to personalize the mail I need to filter the mobile phone country code from the main data extension and mapp it to the translation DE since not all customers have a language! How to realize this in ampscript: if mobilePhone beginns with '+44' the language = 'EN' elseif mobilePhone beginns with '+49' the language = 'de' and so on..

3
  • Hi Ash, welcome to the community. Can give some more information about the outcome you are trying to achieve. Do you just need to output the language as "EN" or "DE" and so on, did you need parts of the 1 email to change based on the language, or did you have multiple emails built and you want the EN email to go to the EN customers? Oct 14 '19 at 10:28
  • Hi Cameron, I use variables in my email calling from the translation DE depending on the column language of the customers DE. Until now it worked very good with: SET @target_rows = LookupRows("DE_Translations","Language", @Language) Now we have the challenge that some customers have null as language and i need to show the language depending on the mobile phone country code of the customers
    – ash
    Oct 14 '19 at 12:07
  • Thanks Ash - that's what I needed to know. I'll respond with my answer. Oct 14 '19 at 12:09
1

You can write an IF Statement in AMPScript to generate a list of applicable language codes.
Assuming your customer's phone data is based on this List of mobile telephone prefixes by country or this List of country calling codes, then the following example will work:

%%[
SET @mobileprefix = Substring(mobilePhone,1,3)
IF @mobileprefix == "+44" THEN
SET @Language = "EN"
ELSEIF (@mobileprefix == "+33" OR @mobileprefix == "+32") THEN
SET @Language = "FR"
ELSEIF @mobileprefix == "+49" THEN
SET @Language = "DE"
ELSE
SET @Language = "EN"
ENDIF

SET @target_rows = LookupRows("DE_Translations","Language", @Language) 
]%%

In the above sample, France and Belgium are combined using an OR statement for the Langauge FR as an example. The default language for customers without a phone number can be set using an ELSE statement - currently set as "EN" in the above sample.

This should work for your use case, however I would encourage you to make the "Language" value a static attribute/value for each customer in your "Customers data extension" using SQL.
This will be more efficient than rendering it in AMPScript every time you send an email, and will give you the ability to let customer's specify their "preferred language" in the future.

References:
AMPScript Substring
OR Statements in AMPScript

8
  • thank you Cameron! I'll try to implement your solution and then let you know.
    – ash
    Oct 14 '19 at 12:36
  • Hi Cameron, it works, great :-) that with the SQL query and static value is a good idea, but I can not use unfortunately, because we already have several thousands of old customers who have no preferred language. But for the future we will make a required value out of it. Once again many thanks
    – ash
    Oct 14 '19 at 14:18
  • Hi Ash - you could run an Update query in SQL that would do exactly what your AMPScript is doing, but save the Language Preference outcome back into the Data Extension for later use. Oct 14 '19 at 22:42
  • Hi @Cameron, after several tests today Unfortunately, I founf out that this solution works only if the country code consists of 2 numbers, e.g. +44 or +55, but not if this consists of 3 numbers as e.g. Taiwan +886 or Ireland +353. I guess with this solution for the problem with the different number of country codes it' done :-) <code> %%[ IF IndexOf (@mobileprefix, '+ 353') == 1 THEN SET "@"Language = "EN" ELSEIF IndexOf (@mobileprefix, '+ 39') == 1 THEN SET "@"Language = "IT" and so on ... ]%% </code> Maybe you can still have a look on it. Thanks in advance!
    – ash
    Oct 15 '19 at 12:19
  • Hey Ash - you can just change the Substring function to be Substring(@mobile,1,4). By changing it to 4, it will return the first 4 characters, so “+###”. I hope that solves your issue. Oct 15 '19 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.