| Function Name | Description | Example |
|---|---|---|
| COALESCE([value1, value2, ...] ) | Returns the first value from the list that isn’t empty. |
Your ‘Campaign Key’ in your original file reads ‘Campaign ID’. Adding a coalesce function to the Campaign Key field, for example COALESCE([csv[‘Campaign Key’], csv[‘Campaign ID’]]) looks for the field called Campaign Key, and if it isn’t found, Campaign ID replaces it. |
|
CODE(text) |
Returns a numeric code for the first character in a text string, according to the ASCII standard. | CODE(‘ABC’) = 65 |
| COSTTYPE(text) | Matches between the custom cost type in the source data to the custom cost type in Marketing Cloud Intelligence. |
COSTTYPE(csv['Custom Cost Type']) |
| COUNTRY_TO_ISO2(country) | Receives a text parameter that represents a country, and finds the best possible match according to ISO2, which can be displayed in the Map widget. | COUNTRY_TO_ISO2(AFGHANISTAN) = AF |
| COUNT_CHAR(text,charToCount) | Counts how many times a specific character appears in a text. | COUNT_CHAR(‘ABC_123_XYZ, ‘_’) = 2 |
| EXTRACT(text, delimiter, position) | Extracts from the given text the substring at the given position from the given text, after splitting by the given delimiter. |
EXTRACT(csv['Media Buy Name'], ‘_’,1); EXTRACT('a_b_c', '_', 1) returns 'b' Explanation: The function looks into the Media Buy Name and EXTRACTs the substring from position 1 (second position, as the index begins at 0). The positions are defined by the delimiter, in this example it’s ‘_’. |
| FIND(findText, withinText) | Returns the starting position of findText in withinText. returns -1 if withinText doesn’t contain findText, The count starts at 0. |
FIND('fall',csv['campaign']) Explanation: the function looks for the string 'fall' and returns the position in which the string starts, output - 'fall - cosmetics' returns 0 'fashion fall' returns 8 'beauty - fourth quarter' returns -1 |
| 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 for a match, or an empty string if no match is found. |
FUZZYMATCH("ABC Compani",{"ABC Company Inc","Apple Inc","IBM Company"},0.8) returns "ABC Company Inc". The threshold parameter must be a value from 0 through 1. Threshold of 1 means an exact match. |
|
FUZZYVLOOKUP(valueToSearch, searchDimension, returnDimension, threshold, workspaceLevel, {'streamfilter1', 'streamfilter2', ...}, useAutomaton) |
The FUZZYVLOOKUP function uses the value provided as the first parameter and fuzzy match it in the dimension values provided as the second parameter. After it’s found, it returns the value of the dimension used as the third parameter. Both the lookup dimension and the output dimension must be related to the same entity. WorkspaceLevel gets a true value if the intention is to perform the search in the data of all the workspace's data. Set the value to false to search it in the current data stream only. The threshold parameter must be a value from 0 through 1. threshold of 1 means an exact match. streamfilter (optional) – insert the Data Stream Names you want to apply the function to inside the {curly brackets}, instead of 'streamfilter#'. If 'searchDimension' contains more than 2,000 unique values, the function fails. useAutomaton - forces the use of Levenshtein automaton algorithm, which supports large dictionary sizes. |
FUZZYVLOOKUP("ABC Compani", searchDimension, returnDimension, 0.8) - returns "ABC Company Inc". if the list of the return dimension contains the value("ABC Company Inc", "Apple Inc", "IBM Company"). The threshold parameter must be a value from 0 through 1. threshold of 1 means an exact match. |
| GET_EXCHANGE_RATE(date, fromISO, toISO) | The function retrieves the currency rate for a specific date, and the currency rate can be used to convert the Measurements data |
The function multiplies the relevant Measurement field from the original data with the convert rate retrieved by the GET_EXCHANGE_RATE formula. The date section is filed by the 'day' CSV field. The other two sections relate to the conversion you want to perform. In the 'fromISO' section, enter the original currency, and in the 'toISO' enter the currency you want to convert to. For example: csv['Post Click Revenue']*GET_EXCHANGE_RATE(csv['Day'],'USD','GBP') |
| 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 occurrence. |
INDEXOF(csv['row'], 'Campaign ID') == 0 returns true if 'Campaign ID' was found in the first cell. The first item has position 0, the second item has position 1, and so on. |
| ISEMPTY(value) | Returns whether the value is empty or not. |
ISEMPTY(csv['Media Buy Key']) can equal ‘true’ or ‘false’ depending on whether there’s a value for Media Buy Key or not. |
|
LEFT(text, numOfChars) |
Returns the requested number of characters from the left. |
LEFT(‘ABCD_1111’,4) = ABCD |
| LEN(text) | Returns the number of characters in the given text. | LEN(‘ABCD_1111’) = 9 |
| LOWER(text) | Converts all uppercase letters in a text string to lowercase. | LOWER(‘InteLliGence’) = ‘intelligence’ |
| MATCH(valueToMatch, array) | Searches for a specified item in an array of values, and then returns a boolean result. | MATCH(1,{1,2,3}) returns true as the value '1' exists in the array. |
| 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. | PROPER(‘‘intelligence’) = ‘Intelligence’ |
| REPLACE(sourceStr, pattern, targetStr) | Replaces every match of a pattern(a regex expression) in the source string to the targeted strong. sourceStr to targetStr. |
REPLACE(csv['Campaign Name'],'-', ' ') Explanation: In the Campaign Name, takes every instance of “-” and replaces it with a space bar. Example: REPLACE(csv['media buy key'],'\\s','-') Explanation: All white-space characters for example, the space bar is replaced by a hyphen (-), from 'media buy 1' to 'media-buy-1'. //s refers to the regular expression /s |
| RIGHT(text, numOfChars) | Returns the requested number of characters from the right. | RIGHT(‘ABCD_1111’,4) = 1111 |
| SELECT(sourceStr, pattern) | Selects the first match of the pattern (a regex expression) in the source string. | SELECT('Hello B wow E Goodbye', 'B.*?E') would return 'B wow E'. |
| SENTIMENT(text) | Extracts positive/neutral/negative sentiment from the given text. |
The formula can be applied in the Mapping process or in a calculated dimension. When applying the formula insert the relevant CSV or Marketing Cloud Intelligence field you want to analyze into the brackets. |
|
SET_VAR/GET_VAR(name,value) |
Sets / gets a global variable value. |
A common use is for data files that have a certain value that appears after and applies to all the rows beneath it. if(!ISEMPTY(csv['relevant field'])) {SET_VAR('relevant Intelligence field',csv['relevant field']);return (csv['relevant field '],);} else {return (GET_VAR('relevant Intelligence field'));} |
|
SPLIT(text,delimiter) |
Split is used the same as “text to columns” in Excel to create multiple columns from values in a single field with a common delimiter. It 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. |
SPLIT('a,b,c', ',') returns an array with 3 items. Can be used for files that have multiple dimensions contained in one column that must be mapped individually. This formula can separate them into individual columns to be mapped to dimensions. |
| 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. | SUBSTITUTE(‘ABCD_1111’, ‘1111’, XYZ) = ABCD_XYZ |
| SUBSTRING(text, beginIndex, endIndex) | Returns the substring of the text beginning at beginIndex (inclusive), and ending at endIndex (exclusive). | SUBSTRING('ABCD_1111',0,4) = ABCD |
|
TARGETTYPE(text) |
Matches between the custom target type in the source data to the custom target type in Marketing Cloud Intelligence. | TargetType(csv['Custom Target Type']) |
|
TRIM(text) |
Removes all spaces from the text except for single spaces between words. | TRIM('Summer 2020 - Seasonal Sale)' = Summer 2020 - Seasonal Sale |
|
UPPER(text) |
Converts all lowercase letters in a text string to uppercase letters. |
UPPER(‘intelligence’) = ‘INTELLIGENCE’ |
|
URL_DECODE(text) |
Decodes an application/x-www-form-urlencoded string using a UTF-8 encoding scheme. | URL_DECODE (https%3A%2F%2Fwww.urlencoder.org%2F) = https://www.urlencoder.org/ |
|
URL_ENCODE(text) |
Translates a text into application/x-www-form-urlencoded format using a UTF-8 encoding scheme. |
URL_ENCODE (https://www.urlencoder.org/) =https%3A%2F%2Fwww.urlencoder.org%2F |
|
VLOOKUP(valueToSearch, searchDimension, returnDimension, brandLevel, concatValues, {'streamFilter1', 'streamFilter2',...}) |
Uses the value provided as the first parameter and searches it in the dimension provided as the second parameter. After it’s found, it returns the value of the dimension used as the third parameter. |
See more about this function here. |
| Function Name | Description | Example |
|---|---|---|
| DATE(year, month) | Returns the first of the given month in the given year. Note: Date formulas don’t allow the use of a '0' prefix (like in '08'). Be sure to insert numbers without a '0' prefix, for example, '8' and not '08'. | DATE(2020,5) = 1 May 2020 |
| DATE(year, month, day) |
Returns the date representing the given arguments. Note: Date formulas don’t allow the use of a '0' prefix (like in '08'). Be sure to insert numbers without a '0' prefix, for example, '8' and not '08'. |
DATE(2020,5,24) = 24 May 2020 |
| DATEADD(interval, number, date) | Returns the date after a certain time or date interval is added. The interval must be one of the following: 'yyyy' (year), 'q' (quarter), 'm' (month) , 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), 's' (second). | FORMATDATE(DATEADD('m',-1,csv['date']),'yyyy-MM-dd') Explanations: The output is the given date, only a month back. So if the input is '2015-09-01' the output will be '2015-08-01' Note: Date formulas don’t allow the use of a '0' prefix (like in '08'). Be sure to insert numbers without a '0' prefix, for example, '8' and not '08'. |
| DATEDIFF(interval, startDate, endDate) | Returns the number of time units specified by '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 the following: 'yyyy' (year), 'q' (quarter), 'm' (month) , 'd' (day), 'ww' (week), 'h' (hour), 'n' (minute), 's' (second). | DATEDIFF('d', DATE(2020,5), DATE(2020,5,24)) = 23 days |
| DAYPRECISION(date) | Returns the given date with day precision. For example, hours/minutes/seconds fields are set to 0. | For example: the date '12-Jun-2012 16:34' will be converted to '12-Jun-2012 00:00' Note: Date formulas don’t allow the use of a '0' prefix (like in '08'). Be sure to insert numbers without a '0' prefix, for example, '8' and not '08'. |
| 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. | DAYSBETWEEN(DATE(2020,5), DATE(2020,5,24)) = 23 days |
| DAYS_IN_MONTH(date) | Returns the number of days in a specified month. | DAYS_IN_MONTH(DATE(2020,5)) = 31 days |
| LAST_DATA_DATE() | Returns the last data date of the current data stream. | LAST_DATA_DATE() = 2020010200 |
| 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. |
NETWORKDAYS(DATE(2020,5),DATE(2020,5,24)) = 16 days |
| NOW () | Returns the current system date and time. | Using NOW() returns the date and time in this format: Wed May 27 14:55:03 UTC 2020 |
| SETHOUR(date, hour) |
Returns the given date set with the given hours. Hours are numeric values from 0 through 23. Note: Date formulas don’t allow the use of a '0' prefix, like in '08'. Insert numbers without a '0' prefix, such as '8' and not '08'. |
SETHOUR(DATE(2020,5,24), 17) = Sun May 24 17:00:00 UTC 2020 |
| TODAY() | Returns the current system date. | TODAY() returns the system date in this format: Wed May 27 00:00:00 UTC 2020 |
| TOSECONDS(duration) | Returns the number of seconds represented by the given duration string, with the format HH:mm:ss/HH:mm:ss.SSS | TOSECONDS('1:30:00') = 5400 seconds |
| YESTERDAY() | Returns yesterday's system date. | YESTERDAY() returns the system date in this format: Tue May 26 00:00:00 UTC 2020 |
| Function Name | Description | Example |
|---|---|---|
| COUNT(groupKey) | Computes the row count, optionally it can be calculated for any group aggregation provided by the groupKey parameter. | COUNT("csv['campaign']") results in the total count of rows per campaign. |
| GROUPCONCAT_FUNCTION(groupedVal, groupKey, separator) | Concatenates the groupedVal by the given groupKey, with the separator string between each two grouped values. The same value repeated won’t be concatenated twice. |
GROUPCONCAT(csv['Ad Name'],csv['Campaign Name'],'-') Explanation: This results in a Dimension value that is all the Ad Names linked to a certain Campaign Name concatenated without repetition |
| SUM(measurementName,measurementValue,groupKey) | Computes the sum of the given measurement, optionally it can be calculated for any group aggregation provided by the groupKey parameter. | SUM("clicks", csv['clicks']) results in the total sum for the clicks column. |
| Function Name | Description | Example |
|---|---|---|
| AND(condition1, condition2) | Logical AND, checks to see that both conditions are true. | AND(csv['Campaign Name'] contains ‘ABC’, csv[Media Buy Name'] contains 123) |
| IF(condition, resultIfTrue, resultIfFalse) | Returns the second argument if the first argument is true, and the third argument if otherwise. |
IF(csv['Media Buy Name'] contains ‘_DIG’, ’Digital’, ’Print’) Explanation: If the Media Buy Name field contains the substring ‘_DIG’, return ‘Digital’, else return ‘Print’. |
| NOT(condition) | Logical NOT, reverses the logic of its argument, true becomes false and vice versa. | NOT(csv['Campaign Name'] contains ‘ABC’) |
| OR(condition1, condition2) | Logical OR, checks to see if either one of the conditions is true. | OR(csv['Campaign Name'] contains ‘ABC’, csv['Campaign Name'] contains ‘XYZ’) |
| Function Name | Description | Example |
|---|---|---|
| COMPRESS(value1) |
Compresses a string using gzip and returns it in base 64. The function compresses long strings into shorter strings. When using this function you must decompress the data after it’s uploaded into the platform, using the UCOMPRESS function. When applied to short strings (roughly, below 300 characters), the function produces longer encoded strings. If the strings you’re applying the function to aren’t stable length-wise, use an IF function, for example: IF(LEN(csv['section'])>300,COMPRESS (csv['section'],csv['section']). |
|
| DECODE_BASE64(value1) | This function is used to decode strings encoded by the ENCODE_BASE64 function. | DECODE_BASE64(string in base64) = original string. |
| ENCODE_BASE64(value1) |
When using this function you’ll must decode the data after it’s uploaded into the platform. Use the DECODE_BASE64 function. When applied to short strings (roughly, below 300 characters), the function produces longer encoded strings. If the strings you’re applying the function to aren’t stable length-wise, use an IF function, for example: IF(LEN(csv['section'])>300,ENCODE_BASE64(csv['section']),csv['section']) (csv['section'],csv['section']). |
ENCODE_BASE64(string) = string in base64 |
| 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, and so on. Date formulas don’t allow the use of a '0' prefix (like in '08'). Insert numbers without a '0' prefix, for example, '8' and not '08'. |
| FORMATDATE_DEFAULT(date) | Returns the default (yyyy-MM-dd HH:mm:ss.S) Intelligence string representation of the given date. | FORMATDATE_DEFAULT(DATE(2020,5,24)) = 2020-05-24 00:00:00.0 |
| MD5(stringToHash) | Returns an MD5 hash of the given string. | MD5(Password123) = 42f749ade7f9e195bf475f37a44cafcb |
| NUMBER(value1) | Converts a string to a number. | NUMBER('1000') returns the text string ‘1000’ into the number 1000 |
| 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' |
| SHA256(stringToHash) | Returns an SHA256 hash of the given string. | SHA256(Password123) = 008c70392e3abfbd0fa47bbc2ed96aa99bd49e159727fcba0f2e6abeb3a9d601 |
| STRIP_HTML(value1) | Strip's html from a string. | STRIP_HTML(csv['Web Analytics Page Path'] |
| UNCOMPRESS(value1) | This function is used to uncompress strings encoded by the COMPRESS function. |
UNCOMPRESS(value1) UNCOMPRESS(shorter_string in base64) =original string |
| Function Name | Description |
|---|---|
| a != b | Not Equals Check. Checks to see if the values on both sides of the operator aren’t equal. For example, 'foo' != 'bar' is true. |
| a % b | Divides the number on the left by the number on the right and returns the remainder. |
| a && b | Checks to see that the values on both sides of the operator are true. |
| a * b | Multiples the number on the left by the number on the right. |
| a + b | In case the operands are numbers, adds the value on the left to the value on the right. In case the operands are strings, concatenates the string on the left to the string on the right. |
| a – b | Subtracts the value on the right from the value on the left. |
| a / b | Divides the number on the left by the number on the right. |
| a < b | Checks to see if the value on the left side of the operator is less than value on the right. |
| a <= b | Checks to see if the value on the left-hand side is less than or equal to the value on the right. |
| a == b | Checks to see if the values on both sides of the operator are equal. For example, 'foo' == 'foo' is true. |
| a > b | Checks to see if the value on the left side of the operator is greater than the value on the right. |
| a >= b | Checks to see if the value on the left-hand side is greater than or equal to the value on the right. |
| a || b | Checks to see if either the value on the left or the right is true. |
|
Java Based IF-Else |
Example: Explanation: If the Site Name contains the substring ‘Twit’, assign Site Group equals ‘Twitter’. If the Site Name doesn’t contain the substring ‘Twit’, assign Site Group equals ‘Other’. |