DocumentationCommerceCommerce Intelligence User Guide

Advanced Calculated Column Types

Last update: Tue Jul 25 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User

Many analyses you might want to create involve the use of a new column that you want to group by or filter by. The Creating Calculated Columns tutorial covers the basics for most use cases, but you may want calculated column that is a bit more complex than what the Data Warehouse Manager can create.

These types of columns can be created by the Adobe team of Data Warehouse analysts. To define a new calculated column, provide us with the following information:

  1. The definition of this column (including inputs, formulas, or formatting)
  2. The table that you would like to create the column on
  3. Any example data points that describe what the column should contain

Here are some common examples of advanced calculated columns that users often find useful:

I am trying to order events sequentially

This is called an event number calculated column. This means you are trying to find the sequence in which events occurred for a particular event owner, like a customer or user.

Here is an example:

event\_id
owner\_id
timestamp
Owner's event number
1
A
2015-01-01 00:00:00
1
2
B
2015-01-01 00:30:00
1
3
A
2015-01-01 02:00:00
2
4
A
2015-01-02 13:00:00
3
5
B
2015-01-03 13:00:00
2

An event number calculated column could be used to observe differences in behavior between first-time events, repeat events, or nth events in your data.

Want to see the Customer’s order number column in action? Click the image to see it used as a Group By dimension in a report.

Using an event number calculated column to Group By the customer's order number.

To create this type of calculated column, you need to know:

  • The table on which you would like to create this column
  • The field which identifies the owner of the events (owner\_id in this example)
  • The field by which you would like to order the events (timestamp in this example)

Back to top

I am trying to find the time between two events.

This is called a date difference calculated column. This means you are trying to find the time between two events belonging to a single record, based on the event timestamps.

Here’s an example:

id
timestamp\_1
timestamp\_2
Seconds between timestamp\_2 and timestamp\_1
A
2015-01-01 00:00:00
2015-01-01 12:30:00
45000
B
2015-01-01 08:00:00
2015-01-01 10:00:00
7200

A date difference calculated column could be used to create a metric which calculates the average or median time between two events. Click the image below to check out how the Average time to first order metric is used in a report.

Using a date difference calculated column to calculate Average time to first order.

To create this type of calculated column, you need to know:

  • The table on which you would like to create this column
  • The two timestamps between which you want to know the difference

Back to top

I am trying to compare sequential event values.

This is called a sequential event comparison. This means you are trying to find the delta between a value (currency, number, timestamp) and the corresponding value for the owner’s previous event.

Here is an example:

event\_id
owner\_id
timestamp
Seconds since owner's previous event
1
A
2015-01-01 00:00:00
NULL
2
B
2015-01-01 00:30:00
NULL
3
A
2015-01-01 02:00:00
7720
4
A
2015-01-02 13:00:00
126000
5
B
2015-01-03 13:00:00
217800

A sequential event comparison can be used to find the average or median time between each sequential event. Click the image below to see the Average and Median time between orders metrics in action.

= Using a sequential event comparison calculated column to calculate Average and Median time between orders.

To create this type of calculated column, you need to know:

  • The table on which you would like to create this column
  • The field which identifies the owner of the events (owner\_id in the example)
  • The value field which you would like to see the difference between for each sequential event (timestamp in this example)

Back to top

I am trying to convert currency.

A currency conversion calculated column converts transaction amounts from a recorded currency to a reporting currency, based on the exchange rate at the event time.

Here is an example:

id
timestamp
transaction\_value\_EUR
transaction\_value\_USD
1
2015-01-01 00:00:00
30
33.57
2
2015-01-02 00:00:00
50
55.93

To create this type of calculated column, you need to know:

  • The table on which you would like to create this column
  • The transaction amount column that you would like to convert
  • The column which indicates the currency in which the data was recorded (typically an ISO code)
  • The preferred reporting currency

Back to top

I am trying to convert timezones.

A timezone conversion calculated column converts the timestamps for a particular data source from their recorded timezone to a reporting timezone.

Here is an example:

id
timestamp\_UTC
timestamp\_ET
1
2015-01-01 00:00:00
2014-12-31 19:00:00
2
2015-01-01 12:00:00
2015-01-01 07:00:00

To create this type of calculated column, you need to know:

  • The table on which you would like to create this column
  • The timestamp column that you would like to convert
  • The timezone in which the data was recorded
  • The preferred reporting timezone

Back to top

I am trying to do something not listed here.

Not to worry. Just because it is not listed here does not mean it is not possible. The Adobe team of Data Warehouse Analysts can help.

To define a new calculated column, submit a support ticket with details on exactly what you would like to build.

Related documentation

e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc