- Same table calculations
- One to many calculations
- Many to one calculations
- Handy reference map
- Advanced calculated columns
Within the Data Warehouse Manager, you have the ability to create columns to augment and optimize your data for analysis. This functionality can be accessed by selecting any table in the Data Warehouse Manager and clicking the Create New Column button.
This article describes the types of columns that you can create with the Data Warehouse Manager, along with a description, a visual walkthrough of that column, and a reference map of all the inputs required to create a column. There are three ways to create calculated columns:
Same table calculated columns
These columns are built using input columns from the same table.
An age calculated column returns the number of seconds between the current time and some input time.
In the example below, we created Seconds since customer’s most recent order in the customers table. This can be leveraged to construct user lists of customers who have not made purchases (sometimes referred to as churning) within X days.
A currency converter calculated column converts the native currency of a column to a desired new currency.
In the example below, we created base_grand_total In AED, converting the base_grand_total from it's native currency to AED in the sales_flat_order table. This column works well for stores with multiple currencies that want to report in their local currency.
For Magento clients, the base_currency_code field typically stores native currencies. The Spot Time field should match the date used in your metrics.
One-to-many calculated columns
One-to-Many columns utilize a path between two tables. This path always implies a one table, where an attribute lives, and a many table, where that attribute gets “relocated” down to. The path can be described as a foreign key--primary key relationship.
A joined column relocates an attribute on the one table to the many table. The classic example of one/many is customers (one) and orders (many).
In the example below, the Customer's group_id dimension gets joined down into the orders table.
Many-to-one calculated columns
These columns utilize the same paths that one-to-many columns do, but they point data in the opposite direction. The column gets created on the one side of the path, as opposed to the many side. Because of this relationship, the value in the column needs to be an aggregation, that is, a mathematical operation performed on the data points on the many side. There are many use cases for this, and a few are listed below.
This type of calculated column returns the count of values on the many table onto the one table.
In the example below, the dimension Customer’s lifetime number of canceled orders is created on the customers table (with a filter for orders.status).
A sum calculated column is the sum of values on the “many” table onto the one table.
This can be used to create customer-level dimensions like Customer's lifetime revenue.
Min or Max
A min or max calculated column returns the smallest or largest record that exists on the many side.
This can be used to create customer-level dimensions like Customer's first order date.
An exists calculated column is a binary test determining the presence of a record on the many side. In other words, the new column will return a 1 if the path connects at least one row in each table, and 0 if no connection can be made.
This type of dimension might determine, for example, if a customer ever purchased a particular product. Using a join between a customers table and orders table, a filter for a specific product, a dimension Customer has purchased Product X? can be built.
Handy reference map
If you're having a little trouble remembering what all the inputs are when creating a calculated column, try keeping this reference map handy when you're building:
Advanced calculated columns
In your quest to analyze and answer questions about your business, you may encounter a situation where you're unable to build the exact column you want. In these cases, we've got you covered!
To ensure a speedy turnaround, we recommend checking out the Advanced Calculated Column Types guide to see what kind of columns our support team can build. In that article, we also cover the info we'll need from you to create the column - please include it with your request.