Derived fields

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

NOTE

You must have the Select package or higher in order to use the functionality described in this section. Contact your administrator if you’re unsure which Customer Journey Analytics package you have.

To use the template, you have to specify the correct parameters for each function listed as part of the rules in the template. See Function reference for more information.

Specifications

Use case 1

You want to define rules to identify various marketing channels, by applying cascading logic to set a marketing channel field to the proper value:

• If the referrer is from a search engine and the page has a query string value where cid contains ps_, the marketing channel should be identified as a Paid Search.
• If the referrer is from a search engine and the page does not have the query string cid, the marketing channel should be identified as a Natural Search.
• If a page has a query string value where cid contains em_, the marketing channel should be identified as an Email.
• If a page has a query string value where cid contains ds_, the marketing channel should be identified as a Display Ad.
• If a page has a query string value where cid contains so_, the marketing channel should be identified as a Paid Social.
• If the referrer is from a referring domain of twitter.com, facebook.com, linkedin.com, or tiktok.com, the marketing channel should be identified as a Natural Social.
• If none of the above rules are matched, then the marketing channel should be identified as Other Referrer.

In case your site receives the following sample events, containing Referrer and Page URL, these events should be identified as follows:

Data before

Derived field

You define a Marketing Channel derived field. You use the CASE WHEN functions to define rules that create values for the based on existing values for both the Page URL and Referring URL field.

Note the usage of the function URL PARSE to define rules to fetch the values for Page Url and Referring Url before the CASE WHEN rules are applied.

Data after

Use case 2

You have collected several different variations of search within your Product Finding Methods dimension. To understand the overall performance of search vs. browse, you must spend a great deal of time combining the results manually.

Your site collects the following values for your Product Finding Methods dimension. In the end, all of these values indicate a search.

Data before

Derived field

You define a Product Finding Methods (new) derived field. You create the following CASE WHEN rules in rule builder. These rules apply logic to all possible variations of the old Product Finding Methods field values for search and browse using the Contains the phrase criterion.

Data after

Use case 3

As a travel company, you would like to bucket trip duration for booked trips so you can report on bucketed lengths of trips.

Assumptions:

• The organization is collecting trip duration into a numeric field.
• They would like to bucket 1-3 day durations into a bucket called ‘short trip’
• They would like to bucket 4-7 day durations into a bucket called ‘medium trip’
• They would like to bucket 8+ day durations into a bucket called ‘long trip’
• 132 trips were booked for a 1-day duration
• 110 trips were booked for a 2-day duration
• 105 trips were booked for a 3-day duration
• 99 trips were booked for a 4-day duration
• 92 trips were booked for a 5-day duration
• 85 trips were booked for a 6-day duration
• 82 trips were booked for a 7-day duration
• 78 trips were booked for an 8-day duration
• 50 trips were booked for a 9-day duration
• 44 trips were booked for a 10-day duration
• 38 trips were booked for an 11-day duration
• 31 trips were booked for a 12-day duration

Your desired report should look like:

Data before

Derived field

You define a Trip Duration (bucketed) derived field. You create the following CASE WHEN rule in rule builder. This rule applies logic to bucket the old Trip Duration field values into three values: short trip, medium trip, and long trip.

Data after

More information

Customer Journey Analytics uses a nested container structure, modeled after Adobe Experience Platform’s XDM (Experience Data Model). See Containers and Segment containers for more background information. This container model, albeit flexible by nature, imposes some constraints when using the rule builder.

Customer Journey Analytics uses the following default container model:

The following constraints apply and are enforced when selecting and setting values.

Constraints

A

Values you select within the same If, Else If construct (using And or Or) in a rule must originate from the same container and can be of any type (string , numeric , and so forth).

B

All the values you set across a rule must be from the same container and have the same type or a derived value of the same type.

C

The values you select across If, Else If constructs in the rule do not have to originate from the same container and do not have to be of the same type.

Specifications

Use case 1

You do have a CSV-file that includes a key column for hotelID and one or more additional columns associated with the hotelID: city, rooms, hotel name.
You are collecting Hotel ID in a dimension but would like to create a Hotel Name dimension derived from the hotelID in the CSV file.

CSV-file structure and content

Current Report

Desired Report

Data before

Derived field

You define a Hotel Name derived field. You use the CLASSIFY function to define a rule where you can classify values of the Hotel ID field and replace with new values.

If you want to include original values that you have not defined as part of the values to classify (for example Hotel ID AMS789), ensure you select Show original values. This ensures AMS789 is part of the output for the derived field, despite that value not being classified.

Data after

Use case 2

You have collected URLs instead of the friendly page name for several pages. This mixed collection of values breaks the reporting.

Data before

Derived field

You define a Page Name (updated) derived field. You use the CLASSIFY function to define a rule where you can classify values of your existing Page Name field and replace with updated correct values.

Data after

More information

The following additional functionality is available in the Classify rule interface:

• To quickly clear all table values, select Clear all table values.
• To upload a CSV file containing original values for When values equal and new values for Replace values with, select Upload CSV.
• To download a template for creating a CSV file with original and new values to upload, select Download CSV template.
• To download a CSV file with all original and new values populated in the rule interface, select Download CSV values.

Specifications

Use case

You currently collect origin and destination airport codes as separate fields. You would like to take the two fields and combine them into a single dimension separated by a hyphen (-). So you can analyze the combination of origin and destination to identify top routes booked.

Assumptions:

• Origin and destination values are collected in separate fields in the same table.
• The user determines to use the delimiter ‘-’ between the values.

Imagine the following bookings occur:

• Customer ABC123 books a flight between Salt Lake City (SLC) and Orlando (MCO)
• Customer ABC456 books a flight between Salt Lake City (SLC) and Los Angeles (LAX)
• Customer ABC789 books a flight between Salt Lake City (SLC) and Seattle (SEA)
• Customer ABC987 books a flight between Salt Lake City (SLC) and San Jose (SJO)
• Customer ABC654 books a flight between Salt Lake City (SLC) and Orlando (MCO)

The desired report should look like:

Data before

Derived field

You define an Origin - Destination derived field. You use the CONCATENATE function to define a rule to concatenate the Original and Destination fields using the - Delimiter.

Data after

Specifications

Use case 1

You want to prevent counting duplicate revenue when a user reloads the booking confirmation page. You use the booking confirmation ID at the identifier to not count the revenue again, when received on the same event.

Data before

Derived field

You define a Booking Confirmation derived field. You use the DEDUPLICATE function to define a rule to deduplicate the Value Booking for Scope Person using Deduplication ID Booking Confirmation ID. You select Keep first instance as Value to keep.

Data after

Use case 2

You use events as a proxy for campaign click-throughs with external marketing campaigns. Reloads & redirects are causing the event metric to be inflated. You would like to deduplicate the tracking code dimension so only the first is collected and minimize the event overcounting.

Data before

Derived field

You define a new Tracking Code (deduplicated) derived field. You use the DEDUPLICATE function to define a rule to deduplicate the Tracking Code with a Deduplication scope of Session and Keep first instance as the Value to keep.

Data after

Specifications

Use case

You have received some malformed values for your external marketing channels report, for example email%20 marketing instead of email marketing. These malformed values fracture your reporting and make it more difficult to see how email is performing. You want to replace email%20marketing with email marketing.

Original Report

Preferred Report

Data before

Derived field

You define an Email Marketing (updated) derived field. You use the FIND AND REPLACE function to define a rule to find and replace all occurrences of email%20marketing with email marketing.

Data after

Specification

Use case

You would like to lookup the activity name using the activity id collected when your customers clicked on a personalized banner shown through Adobe Target. You want to use a lookup dataset with Analytics for Target (A4T) activities containing activity ids and activity names.

A4T lookup dataset

Derived field

You define an Activity Name derived field. You use the LOOKUP function to define a rule to lookup the value from your collected data, specified in the Field to apply lookup field (for example ActivityIdentifier). You select the lookup dataset from the Lookup dataset list (for example New CJA4T Activities). Then you selecting the identifier field (for example ActivityIdentifier) from the Matching key list and the field to return from the Values to return list (for example ActivityName).

More information

The Lookup function is applied at report time to the data retrieved by Customer Journey Analytics from the lookup dataset you have configured as part of your connection.

You can quickly insert a Lookup function in the rule builder, already containing one or more other functions.

1. Select Schema fields from selector.
2. Select Lookup datasets.
3. Select your lookup dataset and find the field you want to use for lookup.
4. Drag and drop the lookup field on any of the available input fields for a function (for example Case When). When valid, a blue box, labeled + Add, allows you to drop the field and automatically insert a Lookup function before the function you dropped the lookup field on. The inserted Lookup function is automatically populated with relevant values for all fields.
   

Specification

Use case

You would like to convert all collected product names into lowercase for proper reporting.

Data before

Derived field

You define a Product Names derived field. You use the LOWERCASE function to define a rule to convert the value from the Collected Product Names field to lowercase and store that in the new derived field.

Data after

Specification

Use case

Due to inflation you want to correct the revenue numbers of ingested CRM data with 5% inflation.

Data before

Derived field

You define a Corrected Annual Revenue derived field. You use the MATH function to define a rule that multiplies the original Annual Revenue number with 1.05.

Data after

More information

To create a formula:

1. Simply start typing in the Formula field and numeric fields that match what you type will appear in a popup menu. Alternatively, you can drag and drop a numeric field from the available fields in the left pane.
   

2. Add the operand (for example * to multiply) followed by another field or a static value. You can use parenthesis to define more complex formulas.

3. To insert a static value (for example 1.05), type the value and select Add x as a static value or Add -x as a negative static value from the popup menu.
   

4. A green checkmark indicates whether your math formula is valid, otherwise you will see a warning and the message Invalid formula expression.
   

There are some important considerations when working with static numbers in the MATH function:

• Static values need to be associated with a field. For example, using the MATH function with only static fields is not supported.

• You cannot use the raise to power operator (ˆ) on a static value.

• If you are using multiple static values in a formula, these static values should be grouped using parenthesis for the formula to be valid. For example:

  • This formula returns an error.
    

  • This formula is valid.
    

Use the Math function for hit-level based calculations. Use the Summarize function for event, session or person scope based calculations.

Specification

Use case

You would like to create a dimension made up from the page name field and the call reason field with the intent of analyzing the journey across channels.

Data before

Derived field

You define a Cross Channel Interactions derived field. You use the MERGE FIELDS function to define a rule to merge the values from the Page Name field and Call Reason field and store that in the new derived field.

Data after

More information

You must select the same type of fields within a Merge Fields rule. For example, if you select a Date field, all other fields you want to merge have to be Date fields.

Specification

Use case

You would like to understand what the next or previous value is of the data that you receive, taken into account repeat values.

Data

Example 1 - Handling include repeats

Example 2 - Handling include repeats with blank values in data received

Derived field

You define a Next Value or Previous value derived field. You use the NEXT OR PREVIOUS function to define a rule that selects the Data received field, select Next value or Previous value as Method, Session as Scope and set the value of Index to 1.

More information

You can only select fields that belong to the Visit or Event table.

Include repeats determines how to handle repeating values for the NEXT OR PREVIOUS function.

• Include repeats looks and the next or previous values. If Include Repeats is selected, it will ignore any sequential repeats of next or previous values from the current hit.

• Rows with no (blank) values for a selected field will not have next or previous values returned as part of the NEXT OR PREVIOUS function output.

Specification

Use case

You would like to grab a potion of a URL and use that as a unique page identifier to analyze traffic. You use [^/]+(?=/$|$) for the regular expression to capture the end of the URL and $1 as the output pattern.

Data before

Derived field

You create a Page Identifier derived field. You use the REGEX REPLACE function to define a rule to replace value of the Referring URL field using a Regex of [^/]+(?=/$|$) and Output format of $1.

Data after

More information

Customer Journey Analytics uses a subset of the Perl regex syntax. The following expressions are supported:

You can use these sequences in the Output format any number of times and in any order to achieve the desired string output.

Specification

Use case 1

You collect voice app responses into a delimited list in a single dimension. You would like each value in the list to be a unique value in the responses report.

Data before

Derived field

You create a Responses derived field. You use the SPLIT function to define a rule to use the Convert to array method to convert the values from the Voice App Response field using , as the Delimiter.

Data after

Use case 2

You collect voice app responses into a delimited list in a single dimension. You would like the responses from the first value in the list into its own dimension. You would like to put the last value in the list into its own dimension.

Data before

Derived field

You create a First Response derived field. You use the SPLIT function to define a rule to take the first value from the Responses field from the left of the response , as the delimiter.

You create a Second Response derived field to take the last value from the Responses field by selecting From the right, 1 as the Delimiter and 1 as the Index.

Data after

Specification

Use case

You would like to categorize Add to Cart Revenue into three different categories: Small, Medium, and Large. This allows you to analyze and identify the characteristics of high-value customers.

Data before

Assumptions:

• Add to Cart Revenue is collected as a numeric field.

Scenarios:

• CustomerABC123 adds $35 to their cart for ProductABC, then separately adds ProductDEF to their cart for $75.
• CustomerDEF456 adds $50 to their cart for ProductGHI, then separately adds ProductJKL to their cart for $275.
• CustomerGHI789 adds $500 to their cart for ProductMNO.

Logic:

• If Total Add to Cart Revenue for a visitor is less than $150, set to Small.
• If Total Add to Cart Revenue for a visitor is greater than $150, but less than $500, set to Medium.
• If Total Add to Cart Revenue for a visitor is greater than or equal to $500, set to Large.

Results:

• Total Add to Cart Revenue for $110 for CustomerABC123.
• Total Add to Cart Revenue for $325 for CustomerDEF456.
• Total Add to Cart Revenue for $500 for CustomerGHI789.

Derived field

You create an Add To Cart Revenue Size derived field. You use the SUMMARIZE function and the Sum Summarize method with Scope set to Person to sum the values of the cart_add field. Then you use a second CASE WHEN rule to split the result in the tree category sizes.

Data after

More information

Use the Summarize function for event, session or person scope based calculations. Use the Math function for hit-level based calculations.

Specification

Use case 1

You collect product data, however that data contains hidden whitespace characters which fragment reporting. You would like to easily trim any excess whitespace

Data before

Derived field

You create a Product Identifier derived field. You use the TRIM function to define a rule to Trim whitespace from the Product ID field.

Data after

Use case 2

Your data on page names collected includes some erroneous special characters at the end of the page name which must be removed.

Data before

Derived field

You create a Page Name derived field. You use the TRIM function to define a rule to Trim special characters from the Name field using the Special characters #?%&/.

Data after

Use case 3

You collect data including a storeID. The storeID contains the abbreviated US state code as the first two characters. You want to only use that state code in your reporting.

Data before

Derived field

You create a Store Identifier derived field. You use the TRIM function to define a rule to Truncate from right the storeID field from String end to position 3.

Data after

Specifications

Use case 1

You only want use the referring domain from the referring URL as part of a marketing channel’s set of rules.

Data before

Derived field

You define a Referring Domain derived field. You use the URL PARSE function to define a rule to fetch the host from the Referring URL field and store that in the new derived field.

Data after

Use case 2

You want to use the value of the cid parameter of a query string in a Page URL as part of the output of a derived tracking code report.

Data before

Derived field

You define a Query String CID derived field. You use the URL PARSE function to define a rule to fetch the value of the query string parameter in the Page URL field, specifying cid as the query parameter. The output value is stored in the new derived field.

Data after