Magento Business Intelligence Help Center

Using the Sequential Comparison Calculated Column

[Note: This feature is in beta]

This document outlines the purpose and uses of the "Sequential Comparison" calculated column available in the "Manage Data -> Data Warehouse" page. Below is an explanation of what it does, followed by an example, and the mechanics of creating it. 

Explanation 

The "Sequential Comparison" column type finds the difference between consecutive events. The most common type of "Sequential Comparison" column is the "Seconds since previous order" column. There are 3 inputs needed for this column: 

  1. Event Owner: This input determines the entity for which rows are grouped. For example, in the "Seconds since previous order" column, the event owner is the customer, because we want to find the number of seconds since the previous order of the same customer. 
  2. Event Date: This input enforces the sequence of events. In the case of "Seconds since previous order", the column containing the timestamp of the order should be the "Event Date". This input is always a timestamp. 
  3. Value to Compare: This input is the actual value to be compared. It subtracts the previous row's value from the current row's value. Hence, a column finding the time difference between successive orders of a customer is called "Seconds since previous order". This input does not have to be a timestamp. A non-timestamp example is to find the difference in order value between successive orders of a customer. 

Example

event_id 

owner_id 

timestamp 

Seconds since owner's previous event 

1 

2015-01-01 00:00:00 

NULL 

2 

2015-01-01 00:30:00 

NULL 

3 

2015-01-01 02:00:00 

7200 

4 

2015-01-02 13:00:00 

126000 

5 

2015-01-03 13:00:00 

217800 

 In the above example, "Seconds since owner's previous event" is the "Sequential Comparison" calculated column. For the "owner_id" = A, it first identifies a sequence based on the "timestamp" column, and then subtracts the previous event's "timestamp" from the current event's timestamp. In the 3rd row in the table – the 2nd row for "owner_id" A – the value of "Seconds since owner's previous event" is the number of seconds between '2015-01-01 02:00' and '2015-01-01 00:00:00'. This difference equals 2 hours = 7200 seconds. 

For this calculated column type, the row corresponding to the owner's first event has a NULL value. 

Mechanics 

Here are some instructions on creating an "Event Number" column: 

  1. Navigate to the "Manage Data -> Data Warehouse" page
  2. Navigate to the table on which you want to create this column
  3. Click the green "Create New Column" button at the top right of the screen 
  4. Select "Same Table" as the Definition Type (if the columns you want to compare are not on the same table you may need to relocate them)
  5. Select "SEQUENTIAL_COMPARISON" as the Column Definition Equation
  6. Choose the inputs, as explained above: 
  7. Event Owner 
  8. Event Date 
  9. Value to Compare 
  10. Filters can also be added to exclude rows from being considered. The excluded rows will have a NULL value for this column. 
  11. Provide a name for the column at the top of the page and click "Save"
  12. The column will be available to use immediately

SEC_new.png

For any questions, please feel free to reach out to the support team at support@rjmetrics.com. 

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

Comments

Powered by Zendesk