Mapping Functions in Marketing Cloud Intelligence

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)

COSTTYPE(csv['Custom Cost Type'])

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('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("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.

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')

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(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.

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

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.

TARGETTYPE(text)

TRIM(text)

UPPER(text)

Converts all lowercase letters in a text string to uppercase letters.

UPPER(‘intelligence’) = ‘INTELLIGENCE’

URL_DECODE(text)

URL_ENCODE(text)

Translates a text into application/x-www-form-urlencoded format using a UTF-8 encoding scheme.

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.

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'.

Returns the number of workdays between two dates, excluding weekends.

The arguments must be date objects or date strings in standard form.

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'.

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

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’.

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']).

COMPRESS(LoremIpsumissimplydummytextoftheprintingandtypesettingindustry
LoremIpsumhasbeentheindustrysstandarddummytexteversincethe1500swhenanunknownprintertookagalleyof
typeandscrambledittomakeatypespecimenbookIthassurvivednotonlyfivecenturiesbutalsotheleapintoelectronic
typesettingremainingessentiallyunchangedItwaspopularisedinthe1960swiththereleaseofLetrasetsheetscontaining
LoremIpsumpassagesandmorerecentlywithdesktoppublishingsoftwarelikeAldusPageMakerincludingversionsofLoremIpsum)
 = H4sIAAAAAAAA/01Qy07DMBD8JXoAiSPHSkXiFzbOtlnF3rW86wb/PZMgWi7RxPY8L9a4nKv3Iu5Sah5zL2UEf4ddY+HaREP0RjrHqOwc+5/
o3D3auDzYC/nErGD83bkHSNTmhyDfubloYrw6vb68+LawknZd1TY9nLiF2Uo3ypkHAsASIp4alSnzLBFWaGU6slROUlgnMM6BAN7bXe48q4VpHlf
ghEi9CfvUg7IbnDNThZMBpGimkv4VQx0SBWB3UAU5Rte0EE7mc2zk1WrP1MSRZq97en9DEYkFuO3izna9cDSA8IXxSabxq/rcqyIuwQXtCg
7bkTSPXWhmX8Nq7VMWX8ByDLERxGXlj4x1v8D8xAxYLOWOHLdjWVO8fFr8AM3IW0rbAQAA

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']).

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'.

For example:

'2014-02-25' ---> 'yyyy-MM-dd', '25 Feb-2014' ---> 'dd MMM-yyyy'

UNCOMPRESS(value1)

UNCOMPRESS(shorter_string in base64) =original string

Java Based IF-Else

if(Boolean_expression){
//Executes when the Boolean expression is true
}else{
//Executes when the Boolean expression is false
}

Example:

if(Site Name contains ‘Twit’){
Site Group = ‘Twitter’;
}else{
Site Group = ‘Other;
}

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’.