Expressions and Functions

Expressions and Functions are helpful tools to create template variables or generate custom content in keyword fields, URL fields, sitelink fields, text ad fields, and others.

Special Characters

 )  ;  =  !  <  > 
These characters have a special role in expressions. Typing them into a value field will trigger function suggestions, which you have to accept by pressing ENTER or clicking one of the suggestions from the dropdown menu. Without doing so, these characters will be regarded as plain text.
Use of expressions and functions
Example: Special Symbols versus Static Text when using the CAPITALIZE function.

Functions

Most functions have the format NAME(argument1;argument2;…), where arguments are separated by a semicolon.

AD_CUSTOMIZERS(<text>,<text>,<number>)
This function is required when using Google’s ad customizers. A list of these is available in your Google Ads account. The first argument of the AD_CUSTOMIZERS function must be the database name. The second argument must be the column of the database. The third argument is required for Crealytics’ Search Platform to know the maximum characters for the values in the database list. Please check the list in the Google Ads account, and count the characters of the longest value. Put the number into the third argument.

Ad_customizers

AD_CUSTOMIZERS_DEFAULT_VALUE(<text>,<text>,<text>,<number>)
This function is required when using Google’s ad customizers. A list of these is available in your Google Ads account. The first argument of the AD_CUSTOMIZERS function must be the database name. The second argument must be the column of the database. The third argument specifies a default text, for cases where Google is unable to match any entry in the database to your ad. This way, you can make sure your ad is always running.The fourth argument is required for Crealytics’ Search Platform to know the maximum characters for the values in the database list. Please check the list in the Google Ads account, and count the characters of the longest value. Put the number into the fourth argument. Make sure that the length of the default text is not longer than the specified length.

Use-cases for Google’s ad customizers:

  • Temporary promotions
  • Ad testing
  • Include prices, stock levels or discounts in an ad dynamically

More information and examples:

ADGROUPADVANCED(<text>)
If you opted for “broad” and not “broad modified” keywords in the campaign structure, you can create broad modified keywords with this function. The function ADGROUPADVANCED replaces each white space sequence in its argument with the string ” +” (a space followed by a plus symbol).ADGROUPADVANCED
BROAD(<text>)
If you opted for “broad modified” and not “broad” keywords in the campaign structure, you can create broad keywords using this function. The function BROAD removes the “+” in front of each word.
BROAD
CAPITALIZE(<text>)
The function CAPITALIZE makes a string’s first character uppercase.
CAPITALIZE
CAPITALIZEWORDS(<text>)
The function CAPITALIZEWORDS capitalizes the first character of every word in a string.
CAPITALIZEWORDS
+-IF
IF(<cond>;<ifText>;<elseText>)
If the first argument is true, the second argument is returned. If not, the third argument is returned.

Example: IF(name@cat=Shoes;Supershoes;name@cat)IF

FALLBACKS(<transform>;<cond>;<exp1>;<exp2>;…)
Requires three or more arguments:

  • An expression specifying how to transform the third and following arguments (may be empty).
  • A condition they must fulfill after having been transformed (may be empty).
  • A series of expressions to transform (applying argument 1) and then test (applying argument 2); the first transformed expression that passes the test will be returned; if none passes the test, NULL will be returned.

Within the transformation and test arguments (#1 and #2), the wildcard * will be replaced with the expressions to actually use (#3 and above).

  • If argument 1 is empty, the expressions will be used as is instead of being transformed.
  • If argument 2 is empty, the first expression that is not NULL will be returned.

Example:
FALLBACKS(UPPERCASE(*);MAXLENGTH(*;4);Not Lars;A long text with more than 4 characters;Lars)
LARS

KEYWORD_HASH()
The function KEYWORD_HASH returns the MD5 hash of the corresponding keyword as a 32 character hex string. Use this function for including a hash of corresponding keywords in keyword destination URLs. Don’t enter an argument into this function, but use the brackets. This function will always create a hash of the corresponding keyword combined with its match type in the following way: “[KEYWORD] [MATCHTYPE]”.

Example: For the exact keyword “shoes”, the function generates the hash for “shoes exact”.
KEYWORD_HASH

LPAD(<str>; <len>; <padstr>)

The LPAD function consists of three arguments. Imagine having IDs with a different number of digits, e.g. IDs ranging from 1 to 1000. In order to easily perform an analysis in Excel, it is useful for all IDs to have the same length, in this case 4 digits.

In the first argument, put the text or number you wish to edit. In the second argument, put a number indicating the desired number of digits/characters. In the third argument, put the digits/characters to fill in when the first argument has less characters/digits than specified in the second argument. When the length of the ID/text exceeds what is specified in the second argument, it’s cut to the specified number.LPAD

LOWERCASE(<text>)
The function LOWERCASE converts its argument to lower case.
LOWERCASE
MAXLENGTH(<text>;<number>)
The MAXLENGTH function returns true if its first argument does not have more characters than specified by the second argument (a number). Otherwise, it returns false if either argument is NULL.

Example:
IF(MAXLENGTH(This is a text;10);short;long) text
long text

Hint: MAXLENGTH can be used when a keyword should be relying on a table column that sometimes has empty fields. If keywords should only get generated when a table entry exists, use this function. It checks if the shortname column is empty. Only if it is not empty, is the keyword is created.
MAXLENGTH

MAXBYTELENGTH(<text>;<number>)
The MAXBYTELENGTH function returns true if its first argument, as a UTF-8 encoded string, doesn’t have more bytes than specified by the second argument (a number). Returns false if either argument is NULL.

Example:
FALLBACKS(;MAXBYTELENGTH(*;4);hello;hey;good morning) has 4 bytes
hey has 4 bytes

+-MD5
MD5(<text>)
The function MD5 returns the MD5 hash of its argument as a 32 character hex string.

Example:
MD5(test)
098f6bcd4621d373cade4e832627b4f6

NORMALIZEWHITESPACE(<str>)
The function NORMALIZEWHITESPACE truncates multiple consecutive white spaces to a single white space.

Example:
NORMALIZEWHITESPACE(Hello      world      !!!)
Hello world !!!

REGEX(<regexp>; <text>)
The REGEX function outputs “true” or “false”, depending on whether the regular expression (<regexp>) matches the text or variable (<text>).

Example:
Regex
This function will perform a wildcard search for the string “something” in the url parameter table column. For each field containing the string “something” or strings that contain the pattern “something” (for example: “abcsomethingdef”), the function will be “true”, otherwise it will be “false”.

REGEXREPLACE(<text>; <regexp>; <replacement>)
With the REGEXREPLACE function, it is possible to replace all regex matches (<regexp>) in the given string (<text>) with the given replacement (<replacement>).

Example:
Hello REGEXREPLACE(worl ;\s+;d)
Hello world

REPLACE(<cond>;< >;< >)
The REPLACE function is the easiest way to replace certain characters with others. The first argument contains the components that should be replaced. The second argument contains the component that should be used instead of the first. The third argument contains the content in which to search for the first argument.

Example:
REPLACE(old;new;That looks like something really old.)
That looks like something really new.

REPLACEALL(<jsonArray>;<text>)
Performs multiple replacements in its second argument, replacing each key in the JSON array specified as first argument with the corresponding value.

Example:
REPLACEALL({“ä”:”ae”,”ö”:”oe”,”ü”:”ue”,”Ä”:”Ae”,”Ö”:”Oe”,”Ü”:”Ue”,”ß”:”ss”};Öhne Ümläutchen wäre die Wült ärmer. Äber wer braucht schön daß scharfe ß?)
Oehne Uemlaeutchen waere die Wuelt aermer. Aeber wer braucht schoen dass scharfe ss?

SITELINK_HASH()
Similar to the MD5 function, the SITELINK_HASH function returns the MD5 hash of the corresponding sitelink as a 32 character hex string. Use this function for including a hash of the sitelink name in sitelink URLs. Don’t enter an argument into this function. It will always create a hash of the corresponding sitelink name.
SMARTREPLACE(<searchString>;<replacement>;<text>)
Use SMARTERPLACE to search its third argument for occurrences of its first argument and replaces all of them with its second argument. All arguments are evaluated as expressions, so they can contain field references and functions. Returns NULL if any of its arguments is NULL.

Example:
Der {#ctb01_02#} wird SMARTREPLACE({#ctb01_02#};{#ctb02_02#};Chris,) der {#ctb01_01#} wird noch viel SMARTREPLACE({#ctb01_01#};_UPPERCASE({#ctb02_01#}er); {#ctb01_01#}), selbst der Döner wird SMARTREPLACE(D;sch;Döner!)
Der Chris wird lieb, der Lars wird noch viel TOLLER, selbst der Döner wird schöner!

TIMEOFFSET(<num>)
TIMEOFFSET returns an offset calculated relative to the Unix timestamp (seconds since The Epoch). The argument is a positive or negative number. The timestamp is calculated once at the start of the program and won’t change afterwards.

Example:
TIMEOFFSET(-100), TIMEOFFSET(250)
1338998824, 1338999174

TRIM(<text>)
TRIM trims the whitespaces from both ends of the string.

Example:
TRIM(      whatever )
whatever

TRUNCATE(<str> <len>)
TRUNCATE enables you to truncate a string if it is longer than the defined length.

Example:
TRUNCATE(Hello world!!!;11)
Hello world

TODAY()
TODAY returns the current date in the format “YYMMDD” (2-digit year, 2-digit month, 2-digit day). The date is determined at the start of the program and won’t change afterward.

Example:
TODAY()
120606

UNIXTIMESTAMP()
The UNIXTIMESTAMP function returns the Unix timestamp (seconds since The Epoch). The timestamp is calculated once at the start of the program and won’t change afterward.

Example:
UNIXTIMESTAMP()
1338998924

UPPERCASE(<text>)
The UPPERCASE function converts its argument to upper case.

Example:
UPPERCASE(hello)
HELLO

URLENCODE(<text>)
To URL-encode an argument use the function URLENCODE.

Example:
http://www.example.com/URLENCODE(buy it now, save $$)
http://www.example.com/buy+it+now%2C+save+%24%24

URLENCODE(<text>)
URLENCODE2 also URL-encodes an argument. The only difference from URLENCODE is that spaces are encoded into ”%20” instead of ”+”.

Example:
http://www.project-a.com/URLENCODE2(buy it now, save $$)
http://www.project-a.com/buy%20it%20now%2C%20save%20%24%24

Operators

Operators return a boolean value (true or false).

str1 = str2
Returns true if both arguments evaluate to the same string. Returns false if they evaluate to different strings or if one or both of them are NULL.

Example:
IF(name@cat=shoes;equal;unequal)! with name@cat being shoes
equal!

str1 != str2
Returns true if its arguments evaluate to different strings. Returns false if they evaluate to the same string or if one or both of them are NULL.

Example:
IF(name@cat!=socks;unequal;equal)! with name@cat being shoes
unequal!

str1 > str2
Returns true if (the number to the left) is bigger than (the number to the right) and if neither of them is NULL.

Example:
n=4, m=2; IF(n > m ;n;m)!
n!

str1 >= str2
Returns true if (the number to the left) is greater than or equal to (the number to the right) and if neither of them is NULL.

Example:
n=2, m=2; IF(n >= m ;n;m)!
n!

str1 < str2
Returns true if (the number to the left) is smaller than (the number to the right) and if neither of them is NULL.

Example:
n=2, m=4; IF(n < m ;n;m)!
n!

str1 <= str2
Returns true if (the number to the left) is smaller than or equal to (the number to the right) and if neither of them is NULL.

Example:
n=4, m=4; IF(n <= m;n;m)!
n!

+-AND
AND(<cond1>;<cond2>;…)
Takes two or more conditions and returns true if all of them evaluate to true.
+-OR
OR(<cond1>;<cond2>;…)
Takes two or more conditions and returns true if at least one of them evaluates to true.
+-NOT
NOT(<cond1>)
Takes a condition and returns true if its argument is false.

Advanced Hints

It is possible to nest functions. This means that for example CAPITALIZE can be used in the second argument of an IF function. It is important to be very careful when nesting functions to avoid any erroneous output.
Nesting of functions enables to build complex dependencies including multiple arguments and different functions.