13. Analytics Pro functions and operators
A major part of creating expressions in Analytics Pro is the functions and operators you can use in them. The functions and operators are divided into a few basic categories:
- Mathematical: Number-related functions.
- String: Word- and letter-related functions.
- Dates: Date- and time-related functions.
- Logical transformation: Includes boolean (true or false) functions and comparison operators.
Mathematical functions and operators
Función | Sintaxis | Objetivo |
abs | abs(value) | Returns the absolute value of value. |
ceiling | ceiling(value) | Returns the smallest integer greater than or equal to value. |
exp | exp(value) | Returns e to the power of value. |
floor | floor(value) | Returns the largest integer less than or equal to value. |
ln | ln(value) | Returns the natural logarithm of value. |
log | log(value) | Returns the base 10 logarithm of value. |
mod | mod(value, divisor) | Returns the remainder of dividing value by divisor. |
power | power(base, exponent) | Returns base raised to the power of exponent. |
rand | rand() | Returns a random number between 0 and 1. |
round | round(value, num_decimals) | Returns value rounded to num_decimals decimal places. |
sqrt | sqrt(value) | Returns the square root of value. |
Operators for any Analytics Pro expression
Operador | Sintaxis | Objetivo |
+ | value_1 + value_2 | Adds value_1 and value_2. |
- | value_1 - value_2 | Subtracts value_2 from value_1. |
* | value_1 * value_2 | Multiplies value_1 and value_2. |
/ | value_1 / value_2 |
Divides value_1 by value_2. |
String functions
String functions operate on sentences, words, or letters, which are collectively called “strings.” You can use string functions to capitalize words and letters, extract parts of a phrase, check if a word or letter is in a phrase, or replace elements of a word or phrase.
Función | Sintaxis | Objetivo |
concat | concat(value_1, value_2, ...) | Returns value_1, value_2, ..., value_n joined as one string. |
contains | contains(string, search_string) | Returns Yes if string contains search_string, and No otherwise. |
length | length(string) | Returns the number of characters in string. |
lower | lower(string) | Returns string with all characters converted to lowercase. |
position | position(string, search_string) | Returns the start index of search_string in string if it exists, and 0 otherwise. |
replace | replace(string, old_string, new_string) | Returns string with all occurrences of old_string replaced with new_string. |
substring | substring(string, start_position, length) | Returns the substring of string, beginning at start_position, consisting of length characters. The start_position starts at 1, with 1 indicating the first character in the string, 2 indicating the second character in the string, and so on. |
upper | upper(string) |
Returns string with all characters converted to uppercase. |
Date functions
Date functions enable you to work with dates and times.
Función | Sintaxis | Objetivo |
add_days | add_days(number, date) | Adds number days to date. |
add_hours | add_hours(number, date) | Adds number hours to date. |
inutes | add_minutes(number, date) | Adds number minutes to date. |
add_monthadd_ms | add_months(number, date) | Adds number months to date. |
add_seconds | add_seconds(number, date) | Adds number seconds to date. |
add_years | add_years(number, date) | Adds number years to date. |
date | date(year, month, day) | Returns “year-month-day” date or null if the date would be invalid. |
date_time | date_time(year, month, day, hours, minutes, seconds) |
Returns “year-month-day hours:minutes:seconds” date or null if the date would be invalid. |
diff_days | diff_days(start_date, end_date) | Returns the number of days between start_date and end_date. |
diff_hours | diff_hours(start_date, end_date) | Returns the number of hours between start_date and end_date. |
diff_minutes | diff_minutes(start_date, end_date) | Returns the number of minutes between start_date and end_date. |
diff_months | diff_months(start_date, end_date) | Returns the number of months between start_date and end_date. |
diff_seconds | diff_seconds(start_date, end_date) | Returns the number of seconds between start_date and end_date. |
diff_years | diff_years(start_date, end_date) | Returns the number of years between start_date and end_date. |
extract_days | extract_days(date) | Extracts the days from date. |
extract_hours | extract_hours(date) | Extracts the hours from date. |
extract_minutes | extract_minutes(date) | Extracts the minutes from date. |
extract_months | extract_months(date) | Extracts the months from date. |
extract_seconds | extract_seconds(date) | Extracts the seconds from date. |
extract_years | extract_years(date) | Extracts the years from date. |
now | now() | Returns the current date and time. |
trunc_days | trunc_days(date) | Truncates date to days. |
trunc_hours | trunc_hours(date) | Truncates date to hours. |
trunc_minutes | trunc_minutes(date) | Truncates date to minutes. |
trunc_months | trunc_months(date) | Truncates date to months. |
trunc_years | trunc_years(date) | Truncates date to years. |
Logical functions, operators, and constants
Función | Sintaxis | Objetivo |
case | case(when(yesno_arg, value_if_yes), when(yesno_arg, value_if_yes), ..., else_value) | Allows conditional logic with multiple conditions and outcomes. Returns value_if_yes for the first when case whose yesno_arg value is yes. Returns else_value if all when cases are no. |
coalesce | coalesce(value_1, value_2, ...) | Returns the first non-null value in value_1, value_2, ..., value_n if found and null otherwise. |
if | if(yesno_expression, value_if_yes, value_if_no) |
If yesno_expression evaluates to Yes, returns the value_if_yes value. Otherwise, returns the value_if_no value. |
is_null | is_null(value) | Returns Yes if value is null, and No otherwise. |
The following comparison operators can be used with any data type:
Operador | Sintaxis | Objetivo |
= | value_1 = value_2 | Returns Yes if value_1 is equal to value_2, and No otherwise. |
!= | value_1 != value_2 | Returns Yes if value_1 is not equal to value_2, and No otherwise. |
The following comparison operators only can be used with numbers and dates:
Operador | Sintaxis | Objetivo |
> | value_1 > value_2 | Returns Yes if value_1 is greater than value_2, and No otherwise. |
< | value_1 < value_2 | Returns Yes if value_1 is less than value_2, and No otherwise. |
>= | value_1 >= value_2 | Returns Yes if value_1 is greater than or equal to value_2, and No otherwise. |
<= | value_1 <= value_2 | Returns Yes if value_1 is less than or equal to value_2, and No otherwise. |
You also can combine Analytics Pro expressions with these logical operators:
Operador | Sintaxis | Objetivo |
AND | value_1 AND value_2 | Returns Yes if both value_1 and value_2 are Yes, and No otherwise. |
OR | value_1 OR value_2 | Returns Yes if either value_1 or value_2 is Yes, and No otherwise. |
NOT | NOT value | Returns Yes if value is No, and No otherwise. |
Logical constants
You can use logical constants in Analytics Pro expressions. These constants are always written in lowercase and have the following meanings:
Constante | Significado |
yes | True |
no | False |
null | No value |
Note that the constants yes and no are the special symbols that mean true or false in Analytics Pro expressions. In contrast, using quotes such as in "yes" and "no" creates literal strings with those values.
Logical expressions evaluate to true or false without requiring an if function. For example, this:
if(${field} > 100, yes, no)
is equivalent to this:
${field} > 100
You also can use null to indicate no value. For example, you may want to determine if a field is empty, or assign an empty value in a certain situation. This formula returns no value if the field is less than 1, or the value of the field if it is more than 1:
if(${field} < 1, null, ${field})
Combining AND and OR operators
AND operators are evaluated before OR operators, if you don’t otherwise specify the order with parentheses. Thus, the following expression without additional parentheses:
if (
${order_items.days_to_process}>=4 OR
${order_items.shipping_time}>5 AND
${order_facts.is_first_purchase},
"review", "okay")
would be evaluated as:
if (
${order_items.days_to_process}>=4 OR
(${order_items.shipping_time}>5 AND ${order_facts.is_first_purchase}),
"review", "okay")