Use functions when creating a calculated measurement to make your functions more dynamic.
The functions are divided into these categories:
- Number
- Text
- Date
- Aggregation
- Logical
- Type Conversion
- Context
Number Functions
Returns a numerical value.
| Function Name | Syntax | Description |
|---|---|---|
| ABS | ABS(number) | Returns the absolute value of a number. |
| CEILING | CEILING(number,significance) | Rounds the number up away from zero to the nearest multiple significance. |
| EXP_TREND | EXP_TREND(metric) | Computes an exponential trend line for the given metric. |
| FLOOR | FLOOR(number,significance) | Rounds the number down toward zero to the nearest multiple significance. |
| LINEAR_TREND | LINEAR_TREND(metric) | Computes a linear trend line for the given metric. |
| LOG_TREND | LOG_TREND(metric) | Computes a log trend line for the given metric. |
| MOVING_AVG | MOVING_AVG(metric, windowSize) | Computes a moving average with the given window size. If windowSize is null or negative, a fully cumulative average is computed. |
| POWER_TREND | POWER_TREND(metric) | Computes a power trend line for the given metric. |
| RAND | RAND() | Gets a random number between 0 and 1. |
| ROUND | ROUND(number) |
Rounds a number to the nearest integer. For example: ROUND(4.2) =4 ROUND(4.6) =5 |
| ROUND | ROUND(number,num_digits) | Rounds a number to a specified number of digits. |
| TREND_SLOPE | TREND_SLOPE(metric, windowSize) | Computes the linear trend slope in the given window size. If windowSize is null or negative, a fully cumulative average is computed. |
Text Functions
| Function Name | Syntax | Description |
|---|---|---|
| CHAR | CHAR(number) | Returns a character specified by a number. |
| COALESCE | COALESCE([value1, value2,...]) | Returns the first value from the list that isn’t empty. |
| CODE | CODE(text) | Returns a numeric code for the first character in a text string. |
| COUNT_CHAR | COUNT_CHAR(text,charToCount) | Counts how many times a specific character appears in a text. |
| EXTRACT | EXTRACT(text,delimiter,position) | Extracts from the given text the substring at the given position, after splitting by the given delimiter. For example, EXTRACT(‘a,b,c’,’_’,1) returns ‘b’. |
| FIND | FIND(findText,withinText) |
Returns the starting position of findText withinText. For example, FIND('World', 'Hello World') returns 6. FIND('aoc', 'Hello World') returns -1. |
| FUZZYMATCH | FUZZYMATCH(valueToMatch, array, threshold) | Searches for a string in an array of values that potentially match using duplicate-detection algorithms that calculate the similarity of two streams of data. Returns the matched string from the array or an empty string if no matches are found. For example, FUZZYMATCH("ABC Compani",{"ABC Company Inc","Apple Inc","IBM Company"},0.8) returns "ABC Company Inc". The threshold parameter must be a value between 0 and 1. A threshold of 1 means an exact match. |
| GET_EXCHANGE_RATE | GET_EXCHANGE_RATE(date, fromISO, toISO) |
Gets the exchange rate for the given currencies and date. The date variable in the formula can be either a mapped date field or a date function. For example: where [Day] is a mapped date field.
where DATE('YYYY','MM','DD') is a date function.
where |
| INDEXOF | INDEXOF(array, findText) |
Searches the array for the specified item, and returns its position. Returns -1 if the item isn’t found. If the item is present more than one time, the INDEXOF function returns the position of the first occurence. For example, INDEXOF(csv['row'], 'Campaign ID') == 0 returns true if 'Campaign ID' was found in the first cell. Note: The first item has position 0, the second item has position 1, and so on. |
| ISEMPTY | ISEMPTY(value) |
Returns if the value is empty or not. For example, ISEMPTY(‘ab’) returns false. ISEMPTY(‘’) returns true. |
| LEFT | LEFT(text,numOfChars) | Returns the requested number of characters from the left. For example LEFT(‘abcde’, 2) returns ‘ab’. |
| LEN | LEN(text) |
Returns the number of characters in the given text. For example LEN(‘abc’) returns 3. |
| LOWER | LOWER(text) |
Converts all uppercase letters in a string to lowercase letters. For example, LOWER(‘AbC’) returns ‘abc’. |
| MATCH | MATCH(valueToMatch,array) | Searches for a specified item in an array of values and then returns a boolean result. For example, MATCH(1,{1,2,3}) returns true because the value '1' exists in the array. |
| PROPER | PROPER(text) | Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase. |
| REPLACE | REPLACE(SourceStr,pattern, targetStr) | Replaces every match of a pattern (a regex expression) in sourceStr to targetStr. For example, REPLACE('All-The-Best', '-', ' ') returns 'All The Best'. REPLACE('Let It Be','\\s','-') returns 'Let-It-Be'. |
| RIGHT | RIGHT(text,numOfChars) | Returns the requested number of characters from the right. For example, RIGHT(‘abcde’, 2) returns ‘de’. |
| SELECT | SELECT(sourceStr,pattern) | Selects the first match of the pattern (a regex expression) in the sourceStr. For example, SELECT('Hello B wow E Goodbye', 'B(.*?)E') returns 'B wow E'. |
| SEMTIMEN | SENTIMEN(text) | Extracts a positive, neutral, or negative sentiment from the given text. |
| SPLIT | SPLIT(text,delimiter) | Splits a text string into an array by using a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string. For example, SPLIT('a,b,c', ',') returns an array with three items. |
| SUBSTITUTE | SUBSTITUTE(text,oldText,newText) | Substitutes newText for oldText in a text string. Use SUBSTITUTE when you want to replace specific text in a text string. |
| SUBSTRING | SUBSTRING(text,beginIndex,endIndex) | Returns the substring of the text beginning at beginIndex (inclusive) and ending at endIndex (exclusive). |
| TRIM | TRIM(text) | Removes all spaces from the text except for single spaces between words. For example, TRIM('Hello World') returns 'Hello World'. |
| UPPER | UPPER(text) | Converts all lowercase letters in a text string to uppercase. For example, UPPER(‘abC’) returns ‘ABC’. |
| URL_DECODE | URL_DECODE(text) | Decodes an application/x-www-form-urlencoded string using a UTF-8 encoding scheme. |
| URL_ENCODE | URL_ENCODE(text) | Translates a text into an application/x-www-form-urlencoded format using a UTF-8 encoding scheme. |
| VALUE_FROM_JSON | VALUE_FROM_JSON(JSON string, key) | Returns the value for a given key in a JSON string. If the key doesn’t exist or the JSON is empty, an empty string is returned. |
Date Functions
| Function Name | Syntax | Description |
|---|---|---|
| DATE | DATE(year,month) | Returns the first day of the given month in the given year. |
| DATE | DATE(year,month,day) | Returns the date representing the given arguments. |
| DATEADD | DATEADD(interval,number, date) | Returns the date after which a certain time/date interval has been added. The interval can be one of the following: 'yyyy' (year), 'q' (quarter), 'm' (month), 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), or 's' (second). |
| DATEDIFF | DATEDIFF(interval,startDate,endDate | Returns the number of time units specified by interval between the startDate and the endDate. The startDate and endDate arguments can be date objects or date strings in standard form. The interval can be one of the following: 'yyyy' (year), 'q' (quarter), 'm' (month), 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), 's' (second). For example, DATEDIFF('m', PARSEDATE('12/22/2021','MM/d/yyyy'), PARSEDATE('10/18/2021','MM/d/yyyy')) returns -2. |
| DAYPRECISION | DAYPRECISION(date) | Returns the given date with day precision,so hours, minutes, and seconds fields are all set to 0. For example, the date '12-Jun-2012 16:34' is converted to '12-Jun-2012 00:00'. |
| DAYSBETWEEN | DAYSBETWEEN(startDate,endDate) | Returns the number of days between the given startDate and endDate.The arguments can be date objects or date strings in standard form. |
| DAYSINMONTH | DAYSINMONTH(date) | Returns the number of days in a specified month. |
| NETWORKDAYS | NETWORKDAYS(startDate,endDate) | Returns the number of workdays between two dates, excluding weekends.The arguments can be date objects or date strings in standard form. |
| NOW | NOW() | Returns the current system date and time. |
| SETHOUR | SETHOUR(date,hour) | Returns the given date set with the given hours, with hour being a numeric value between 0 and 23. |
| TODAY | TODAY() | Returns the current system date. |
| TOSECONDS | TOSECONDS(duration) | Returns the amount of seconds represented by the given duration string in the format HH:mm:ss/HH:mm:ss.SSS. |
| YESTERDAY | YESTERDAY() | Returns yesterday’s system date. |
Aggregation Functions
All aggregation functions look at a data range, as opposed to a single row. You can specify the data range you want the calculated measurement to consider by defining the set of rows that the function operates on, which is called a window function.
You can define a window function in two ways.
- Use GroupByDims within your function, for example SUM([metric],[[groupByDims]])
- Use a syntax that lets you choose in which row the function begins to apply and where it stops, such as SUM(metric, first_index(), last_index())
If you don’t define the window function, aggregation functions consider all the data included in the query.
| Function Name | Syntax | Description |
|---|---|---|
| ACC | ACC(metric, [groupByDims], orderByDim, sortOrder) | Gives an accumulative sum of the metric grouped by the dimensions, ordered by the orderByDim with the given sortOrder (‘ASC’ or ‘DESC’). For example, ACC(‘Clicks’, [‘Media Buy Key’], ‘Day’, ‘ASC’). |
| ACC | ACC(metric, orderByDim, sortOrder, first_index(), last_index()) | Gives an accumulative sum of the metric ordered by the orderByDim with the given sortOrder (‘ASC’ or ‘DESC’). The window is defined as an offset from the current row. Use first_index()+n and last_index()-n for the offsets from the first and last row in the partition. If the last two parameters are omitted, the entire partition is used. |
| ACC_SUM | ACC_SUM(metric) | Computes an accumulating sum for the given metric. Unlike ACC, this function does not require you to insert a sort order. |
| AVG | AVG(metric, [groupByDims]) | Computes the average of the given metric grouped by the supplied dimensions. |
| AVG | AVG(metric, first_index(), last_index()) | Computes the average of the given metric. The window is defined as an offset from the current row. Use first_index()+n and last_index()-n for offsets from the first and last row in the partition. If the last two parameters are omitted, the entire partition is used. |
| CORREL | CORREL(metric1, metric2, [groupByDims]) | Calculates the Pearson Product-Moment Correlation Coefficient for two sets of values. Set the appropriate precision when creating a calculated measurement using correlation. |
| CORREL | CORREL(metric1, metric2, first_index(), last_index()) | Calculates the Pearson Product-Moment Correlation Coefficient for two sets of values. The window is defined as an offset from the current row. Use first_index()+n and last_index()-n for the offsets from the first and last row in the partition. If the last two parameters are omitted, the entire partition is used. |
| COUNT | COUNT(metric, [groupByDims]) | Counts the number of rows (grouped by the supplied dimension list) in which the given metric exists. |
| COUNT | COUNT(metric, first_index(), last_index()) | Counts the number of rows (grouped by the supplied dimension list) in which the given metric exists.The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| COUNT | COUNTDISTINCT([dimensions], [groupByDims]) | Returns the number of unique appearances of dimension value combinations within the supplied context dimensions. If no groupBy dimensions are supplied, the row context is used. |
| DENSE_RANK | DENSE_RANK([groupByDims], orderField, sortOrder) | Computes the dense rank grouped by the dimensions, ordered by the orderField with the given sortOrder (‘ASC’ or ‘DESC’). If groupByDims is omitted, it is calculated over the entire range. |
| FIRST | FIRST(metric, [groupByDims]) | Returns the first value (considering the results’ date dimension) of the given metric, grouped by the supplied dimensions. |
| FIRST | FIRST(metric, first_index(), last_index()) | Returns the first value (considering the results’ date dimension). The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used.. |
| GROUPCONCAT | GROUPCONCAT(groupedDimension,keyDimension, separator) | Concats the values of groupedDimension by keyDimension, with the separator string between each two grouped values. If a value is repeated, it’s not concated twice. For example, GROUPCONCAT({Media Buy Name}, {Campaign Name}, ',' ) results in all media buy names concated (with no repetitions) for each campaign. |
| LAST | LAST(metric, [groupByDims]) | Returns the latest value (considering the results’ date dimension) of the given metric, grouped by the supplied dimensions. |
| LAST | LAST(metric, first_index(), last_index()) | Returns the latest value (considering the results date dimension). The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| LIFETIME | LIFETIME(metric, [groupByDims]) | Computes the lifetime aggregation of the given metric grouped by the supplied dimensions. |
| LIFETIME | LIFETIME(metric, first_index(), last_index()) | Computes the lifetime aggregation of the given metric. The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| MAX | MAX(metric, [groupByDims]) | Computes the maximum of the given metric grouped by the supplied dimensions. |
| MAX | MAX(metric, first_index(), last_index()) | Computes the maximum of the given metric.The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| MEDIAN | MEDIAN(metric, [groupByDims]) | Returns the median (the value lying at the midpoint of the frequency distribution of all the values) of a metric across all records. You can use median only with numeric fields. Null values are ignored. |
| MEDIAN | MEDIAN(metric, first_index(), last_index()) | The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used. (optional). |
| MIN | MIN(metric, [groupByDims]) | Computes the minimum of the given metric grouped by the supplied dimensions. |
| MIN | MIN(metric, first_index(), last_index()) | Computes the minimum of the given metric. The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| PERCENTILE | PERCENTILE(metric, number, [groupByDims]) | Returns the percentile value of the given metric corresponding to the specified number. The number must be between 0 and 1 (inclusive), for example, 0.66, and must be a numeric constant. |
| PERCENTILE | PERCENTILE(metric, number, first_index(), last_index()) | Computes the percentile value of the given metric corresponding to the specified number. The number must be between 0 and 1 (inclusive), for example, 0.66, and must be a numeric constant. The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| RANK | RANK([groupByDims], orderField, sortOrder) | Computes the rank grouped by the dimensions, ordered by the orderField with the given sortOrder (‘ASC’ or ‘DESC’). If groupByDims is omitted, it is calculated over the entire range. |
| STDEV | STDEV(metric, [groupByDims]) | Computes the standard deviation of the given metric grouped by the supplied dimensions. |
| STDEV | STDEV(metric, first_index(), last_index()) | Computes the standard deviation of the given metric. The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| SUM | SUM(metric, [groupByDims]) |
Computes the sum of the given metric grouped by the supplied dimensions. For example, SUM(‘Clicks’, [‘Media Buy Key’, ‘Creative Key’]) returns the sum of clicks per media buy key and creative key. |
| SUM | SUM(metric, first_index(), last_index()) | Computes the sum of the given metric. The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used (optional). |
| TOTAL | TOTAL(metric, [groupByDims]) |
Computes the total of the metric grouped by the supplied dimensions, according to its defined aggregation function. For example, if a metric m has aggregation type MAX - TOTAL(‘m’, [Dimensions]) == MAX(‘m’, [Dimensions]). |
| VAR | VAR(metric, [groupByDims]) | Computes the variance of the given metric grouped by the supplied dimensions. |
| VAR | VAR(metric, first_index(), last_index()) | Computes the variance of the given metric. The window is defined as offsets from the current row. Use first_index()+n and last_index()-n for offsets from the first or last row in the partition. If the last two parameters are omitted, the entire partition is used. |
| first_index | first_index() | Returns the number of rows from the current row to the first row in the partition. |
| index | index() | Returns the index of the current row in the partition. |
| last_index | last_index() |
Returns the number of rows from the current row to the last row in the partition. |
Logical Functions
| Function Name | Syntax | Description |
|---|---|---|
| AND | AND(condition1, condition2) | Logical AND, checks to see that both conditions are true. |
| IF | IF(condition,resultifTrue,resultifFalse) | Returns the second argument if the first argument is true, and the third argument if otherwise. |
| IFERROR | IFERROR(someExpression, number) | Returns the someExpression if it’s a valid number. Otherwise, returns a number. |
| NOT | NOT(condition) | Logical NOT, reverses the logic of its argument: true becomes false and vice versa. |
| OR | OR(condition) | Logical OR, checks to see if either one of the conditions are true. |
Type Conversion
| Function Name | Syntax | Description |
|---|---|---|
| COMPRESS | COMPRESS(value1) | Compresses a string using gzip and returns it in base64. |
| DECODE_BASE64 | DECODE_BASE64(value1) | Decodes a string from base64. |
| DOUBLE |
DOUBLE(value1) |
Converts a string to a double. |
| ENCODE_BASE64 | ENCODE_BASE64(value1) | Encodes a string to base64. |
| FORMATDATE | FORMATDATE(date,pattern) | Returns the string representation of the given date according to the supplied pattern. For example, (Fri Dec 22 00:00:00 UTC 2021) returns 22-Dec-2021. |
| FORMATDATE_DEFAULT | FORMATDATE_DEFAULT(date) | Returns the default (yyyy-MM-dd HH:mm:ss.S) Marketing Cloud Intelligence string representation of the given date. For example, DATEADD('d',3,PARSEDATE('12/22/2021','MM/d/yyyy')) returns (Mon Dec 25 00:00:00 UTC 2021). |
| MD5 | MD5(stringToHash) | Returns an MD5 hash of the given string. |
| NUMBER | NUMBER(value1) | Converts a string to a number. |
| PARSEDATE | PARSEDATE(dateString,pattern) | Returns the date object specified by the given dateString using the given pattern. For example, PARSEDATE('12/22/2021','MM/d/yyyy') returnsFri Dec 22 00:00:00 UTC 2021. |
| SHA256 | SHA256(stringToHash) | Returns a SHA256 hash of the given string. |
| STRIP_HTML | STRIP_HTML(value1) |
Strips HTML from a string. |
| UNCOMPRESS | UNCOMPRESS(value1) | Uncompresses a base64 string using gzip. |
Context Functions
| Function Name | Syntax | Description |
|---|---|---|
| CONTEXT_DIMENSION_CONTAINS | CONTEXT_DIMENSIONS_CONTAIN(dimension) | Returns true if the given dimension is included in the query. |
| CONTEXT_DIMENSION_EQUALS | CONTEXT_DIMENSIONS_EQUALS(dimension) | Returns true if the given dimensions are exactly the context dimensions. Enter all relevant dimensions, but the order isn’t important. |
| FilterEndDate | FilterEndDate() | Returns the end date of the query filter. |
| FilterStartDate | FilterStartDate() | Returns the start date of the query filter. |
| GET_ENV | GET_ENV(variableName) | Returns the value for the given environment variable. Possible values are: WORKSPACE_ID. |
| QUERY_DIMENSIONS_CONTAIN | QUERY_DIMENSIONS_CONTAIN(dimension) | Returns true if the given dimension is one of the query dimensions. |
| QUERY_DIMENSIONS_EQUAL | QUERY_DIMENSIONS_EQUAL([dimensions]) |
Returns true if the given dimensions are exactly the query dimensions. Enter all the relevant dimensions, but the order isn’t important. |