Magento Business Intelligence Help Center

Advanced Calculated Column Types

 

Many analyses you might attempt 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’s a bit more complex than what the Data Warehouse Manager can create.

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

  1. The definition of this column (including inputs, formulas, or formatting)
  2. The table that you'd 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’m trying to order events sequentially.

We call this an event number calculated column. This means we’re trying to find the sequence in which events occurred for a particular event owner, like a customer or user.

Here’s 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.
Click for a closer look!

To create this type of calculated column, we’ll need to know:

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

back to top

I’m trying to find the time between two events.

We call this a date difference calculated column. This means we’re 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.
Click for a closer look!

To create this type of calculated column, we’ll need to know:

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

back to top 

I’m trying to compare sequential event values.

We call this a sequential event comparison. This means we’re trying to find the delta between a value (currency, number, timestamp) and the corresponding value for the owner’s previous event.

Here’s 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.
Click for a closer look!

To create this type of calculated column, we’ll need to know:

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

back to top

I’m 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’s 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, we’ll need to know:

  • The table on which you would like to create this column
  • The transaction amount column 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’m 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’s 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, we’ll need to know:

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

back to top

I’m trying to do something not listed here.

Not to worry - just because it isn’t listed here doesn’t mean it’s not possible. Our team of Data Warehouse Analysts has you covered.

To define a new calculated column, just contact us with details on exactly what you'd like to build.

RELATED:

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk