Marketing Cloud Intelligence provides basic functions you can use to create dynamic calculated dimensions
The functions are divided into these categories:
- Number Functions
- Text Functions
- Date Functions
- Aggregation Functions
- Logical Functions
- Type Conversion
- Content Functions
These tables summarize the available functions for calculated dimensions.
Number Functions
| 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. |
| FLOOR | FLOOR(number, significance) | Rounds the number down toward zero, to the nearest multiple significance. |
| RAND | RAND() | Gets a random number between 0 and 1. |
| ROUND | ROUND(number) | Rounds a number to the nearest integer. |
| ROUND | ROUND(number, num_digits) | Rounds a number to a specified number of digits. |
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. Returns -1 if withinText doesn’t contain findText. |
| 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 if there’s a match or, if no matches are found, returns an empty string. For example, FUZZYMATCH("ABC Company",{"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) |
Get the exchange rate for the given currencies and date. The ‘date’ variable in the formula can be 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 YESTERDAY() is a date function. |
| 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 once, the INDEXOF function returns the position of the first occurrence. 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. |
| LEFT | LEFT(text, numOfChars) | Returns the requested number of characters from the left. |
| LEN | LEN(text) | Returns the number of characters in the given text |
| LOWER | LOWER(text) | Converts all uppercase letters in a string to lower-case letters. |
| 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 letters. |
| 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'. In another example, REPLACE('Let It Be','\\s','-') returns 'Let-It-Be'. |
| RIGHT | RIGHT(text, numOfChars) | Returns the requested number of characters from the right. |
| 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. |
| UPPER | UPPER(text) | Converts all lowercase letters in a text string to uppercase. |
| 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 a specific time or date interval was added. The interval must be one of '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 the interval between the startDate and the endDate. The startDate and endDate arguments must be date objects or date strings in standard form. The interval must be one of 'yyyy' (year), 'q' (quarter), 'm' (month), 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), or 's' (second). |
| DAYPRECISION | DAYPRECISION(date) | Returns the given date with day precision, meaning the 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 must 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 must 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 the hours being a numeric value from 0 through 23. |
| TODAY | TODAY() | Returns the current system date. |
| TOSECONDS | TOSECONDS(duration) | Returns the number of seconds represented by the given duration string, with the format HH:mm:ss/HH:mm:ss.SSS. |
| YESTERDAY | YESTERDAY() | Returns yesterday’s system date. |
Aggregation Functions
| FUNCTION NAME | SYNTAX | DESCRIPTION |
|---|---|---|
| GROUPCONCAT | GROUPCONCAT(groupedDimension, keyDimension, separator) | Concatenates the values of groupedDimension by keyDimension, with the separator string between each two grouped values. This process is done uniquely, that is, the same value repeated isn’t concatenated twice. For example, GROUPCONCAT({Media Buy Name}, {Campaign Name}, ',') results in all media buy names concatenated (with no repetitions) for each campaign. |
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 is true. |
Type Conversion Functions
| 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, 'yyyy-MM-dd' --> 2014-02-25, 'dd MMM-yyyy' --> 25 Feb-2014, etc. |
| FORMATDATE_DEFAULT | FORMATDATE_DEFAULT(date) | Returns the default (yyyy-MM-dd HH:mm:ss.S) Marketing Cloud Intelligence string representation of the given date. |
| 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, '2014-02-25' ---> 'yyyy-MM-dd', '25 Feb-2014' ---> 'dd MMM-yyyy', etc. |
| 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 a 'true' value if the given dimension is included in the query. |
| CONTEXT_DIMENSION_EQUALS | CONTEXT_DIMENSIONS_EQUALS(dimension) | Returns a 'true' value if the given dimensions are exactly the context dimensions. Enter all the relevant dimensions, but the order of appearance 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 for variableName are: WORKSPACE_ID |
| QUERY_DIMENSIONS_CONTAIN | QUERY_DIMENSIONS_CONTAIN(dimension) | Returns a 'true' value if the given dimension is one of the query dimensions. |
| QUERY_DIMENSIONS_EQUAL | QUERY_DIMENSIONS_EQUAL([dimensions]) | Returns a 'true' value if the given dimensions are exactly the query dimensions. Enter all the relevant dimensions, but the order of appearance isn’t important. |