Use these examples when creating calculated dimensions in Intelligence Reports Advanced to easily create fields. Each example contains an explanation with a code sample.
Required Editions
| Available in: Intelligence Reports Advanced |
Tips:
- For Equals use ‘==’
- For Or use ‘||’
- For And use ‘&&’
- For “not “ use !(expression)
Extract From a Naming Convention
You want to create a dimension based on the naming convention of an existing dimension. For example, the Campaign field is built out of the following naming convention: countrycode_brandcode_ year_campaign → “IL_CK_2022_Welcome”
Formula
EXTRACT(text,delimiter,position)
Description
Extracts the substring from the given text, at the given position, after splitting by the given delimiter. For example, EXTRACT('a_b_c', '_', 1) returns 'b'.
Example
To create a Brand Code field, use this function:
EXTRACT([Campaign],'_',1)To create a Country field use this function:
EXTRACT([Campaign],'_',0)Original Data Set
| Campaign | Email Sends |
|---|---|
| IL_CK_2022_Welcome | 50 |
| IL_CK_2022_Birthday | 25 |
| FR_AB_2022_Welcome | 70 |
Results with Calculated Dimension
| Country | Email Sends |
|---|---|
| IL | 75 |
| FR | 70 |
Extract from a Naming Convention and then Coalesce (between Mobile+Email)
For example, if you store Brand Code in the second position of Push Send Name and in the third position of Email Content Name. In this case you’re unifying two different fields into one. For example, the Push Send Name field is built with this naming convention: Send Name_Region (e.g 'Welcome_EMEA). While the Email Content Name field is built with this naming convention: 'BrandCode_Send Name_Region' (e.g 'A_Birthday_AMER) If you want to create one unified field for the Region from both Email and Push, use the example formula.
Formula
COALESCE([value1, value2, ...])
Description
Returns the first value from the list that isn’t empty.
Example
To create one field that contains Brand Code for both channels follow this logic.
var Brand_Mobile = EXTRACT([Push_Send_Name],'',1);
var Brand_Email = EXTRACT([Email_Content_Name],'',2);
return COALESCE([Brand_Mobile, Brand_Email]);Email Data Set
| Email Content Name | Email Sends |
|---|---|
| A_Birthday_AMER | 10 |
| B_Promotional_EMEA | 30 |
| B_Promotional_AMER | 15 |
Mobile Data Set
| Push Send Name | Push Deliveries |
|---|---|
| Birthday_AMER | 10 |
| Promotional_EMEA | 20 |
| Promotional_APAC | 15 |
Results with Calculated Dimension
| Region (Calc) | Email Sends | Push Deliveries |
|---|---|---|
| AMER | 25 | 10 |
| EMEA | 30 | 20 |
| APAC | 0 | 15 |
Group Values of a Dimension
You want to group the values of a field together by category. For example, you want to group all ‘Thank You’ emails and all ‘Welcome’ emails.
Formula
IF(condition,resultIfTrue,resultIfFalse)
Description
Returns the second argument if the first argument is true, and the third argument if otherwise.
UPPER(text)
Description
Converts all lowercase letters in a text string to uppercase.
Examples
To create 2 groups of emails - one for Thank You messages and one for Welcome messages:
IF(UPPER([Email_Subject]) contains 'THANK','Thank-you messages',
IF(UPPER([Email_Subject]) contains 'WELCOME','Introduction messages',[Email_Subject])) If you have multiple IF/Else cases, you can use this syntax. This is only a suggestion.
if(UPPER([Email_Subject]) contains 'THANK')
{ return 'Thank-you messages'; }
else if(UPPER([Email_Subject]) contains 'WELCOME')
{ return 'WELCOME'; }
else {return [Email_Subject];}Original Data Set
| Email Subject | Email Sends |
|---|---|
| Thanks for your purchase | 10 |
| Welcome to Northern Trail Outfitters! | 20 |
| A 20% off Welcome from NTO | 15 |
| Thanks for signing up | 5 |
| Welcome to NTO | 10 |
| Thank You for Reaching Out! | 15 |
Results with Calculated Dimension
| Grouped Subject (Calculated) | Email Sends |
|---|---|
| Thank-You Messages | 30 |
| Welcome Messages | 45 |