Expressions and Functions

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

Special Characters

 )  ;  =  !  <  > 
The following 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 on one of the suggestion from the drop-down 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 semicolon.

AD_CUSTOMIZERS(<text>,<text>,<number>)
This function is required when using Google’s ad customizers. In the AdWords account a list is deposed. The first argument of the AD_CUSTOMIZERS function has to be the database name, the second one must be the column of the database. The third argument is required for the Campaign Suite to know how many characters the values in the database list have maximally. Please check the underlying list in the according AdWords account and count the characters of the longest value. Put the number into the third argument.

Ad_customizers

ADGROUPADVANCED(<text>)
If you opted for broad and not broad modified keywords in the campaign structure, you can create broad modified keywords either with this function. The function ADGROUPADVANCED replaces each white space sequence in its argument with the string ” +” (a space followed by a plus sign).
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 every word’s first character 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>;…)
Takes three or more arguments:

  • A 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 of “shoes exact”.
KEYWORD_HASH

LPAD(<str>; <len>; <padstr>)
The function LPAD consists of three arguments. Imagine to have IDs with a different number of digits, e.g. IDs ranging from 1 to 1000. In order to easily perform analysis in Excel, it is useful that all IDs have the same length, in this case 4 digits.
In the first argument, put the text or number, which shall be edited. In the second argument, put a number indicating the number of digits/characters desired. 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 is bigger than specified in the second argument, it is cut to the specified number.
LPAD
LOWERCASE(<text>)
The function LOWERCASE converts its argument to lower case.
LOWERCASE
MAXLENGTH(<text>;<number>)
The function MAXLENGTH returns true if its first argument does not have more characters than specified by the second argument (a number). It otherwise 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 for the case that a keyword should be relying on a table column, which sometimes has empty fields. If keywords should only get generated in case a table entry exists, use this function. It checks if the shortname column is empty. Only if it is not empty, the keyword is created.
MAXLENGTH

MAXBYTELENGTH(<text>;<number>)
The MD5 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 function REGEX 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, which contains 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 replace one (<replacement>).

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

REPLACE(<cond>;< >;< >)
The function REPLACE is the easiest way to replace certain characters by 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 and 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 function MD5, the function SITELINK_HASH 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 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 function UNIXTIMESTAMP 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 function UPPERCASE 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.