This document outlines the purpose and uses of the "Calculation" column type that can be added to tables using the Data Warehouse Manager. Below is an explanation of what SQL calculations do, why they are used, the process for creating a SQL calculation and two examples. Please note: This is currently a beta feature.
In the past, columns that were deemed "advanced" could only be done by an analyst on the Customer Success team here at Magento BI. Now all the power is in the hands of the end user, and advanced columns can be created in the form of SQL Calculation columns on the new Magento BI architecture.
The Calculation column type, now available as an option in the Data Warehouse Manager, is a same table operation that allows end users to transform the columns on a table using PostgreSQL logic. Documentation on the functions and operators that can be used in the Calculation column type can be found on the PostgreSQL website here (https://www.postgresql.org/docs/9.6/static/functions.html).
The different columns that can be created with the Calculation column are almost unlimited, but most columns can be created using IF-THEN statements and basic arithmetic, which will be used in the examples below.
Example 1 – Is customer's last order?
Most accounts have a column called "Is customer's last order?" on their Orders table in order to perform analyses on repeat purchase rates and churned customers. If your account is on the new architecture, this column is built using a Calculation column and can be seen in the screenshot below:
The "Is customer's last order?" column uses the inputs "Customer's lifetime number of orders" and "Customer's order number" aliased as A and B respectively.
Line by line, the meaning of the PostgreSQL is:
The possible values that can be returned by this column (NULL, "Yes", "No") contain non number characters, so the data type here is String.
Example 2 – Order item total value (quantity * price)
Many of our clients like to analyze revenue at the item level, slicing it by fields like product name or category. Most databases do not actually give you the revenue from a product in an order; instead they provide the quantity sold in the order, and the price of the item.
To enable product revenue analyses, most accounts have a column called "Order item total value (quantity * price)" on their Orders Items table. If your account is on the new architecture, this column is also built using a Calculation column and can be seen in the screenshot below:
In the Magento schema, the "Order item total value (quantity * price)" column uses the inputs "qty_ordered" and "base_price" aliased as A and B respectively.
The values that will be returned by this new column will be a dollars and cents, so the correct data type is Decimal(10,2).
A new Calculation column can be added to a table by navigating to Manage Data => Data Warehouse as shown below:
From here you can create a new Calculation column by following the steps below:
Looking for additional help?
Writing SQL is not included with Technical Support. However, the Magento Services team does offer assistance with the creation of columns and reports. If you’re interested in discussing your necessary Calculation columns with our Services team, or simply want more information, contact firstname.lastname@example.org.