Period Over Period Comparison in Rules Engine (Horizon Experience)
This article explains the Period over Period Comparison feature in Rules Engine. Gainsight recommends that you refer to the Formula Fields in Rules Engine and then proceed with this article.
Overview
The Period Over Period Comparison function enables you to compare metrics across various time periods, such as days, weeks, or months. This feature is particularly useful for tracking changes in usage metrics, support cases, and other key performance indicators over time.
Note: This function does not automatically apply aggregation to the data being compared. Ensure that data is aggregated to the desired level before using this function.
Access the Period Over Period Comparison
To use the Period Over Period Comparison feature:
- Navigate to the Transform task in the Rules Engine.
- Click Add Formula Field. The Formula Field Builder page appears.
- From the Expression Type dropdown list, select Period Over Period Comparison.
Period Over Period Comparison Template Details (Non-Percent)
The Period Over Period Comparison function allows you to compare specific metrics across different time periods. This function requires the configuration of various parameters, which determine how the comparison is calculated. Below is a detailed explanation of each parameter:
- Period Type: The type of period to compare, such as days, weeks, months etc.
- Duration: The length of the period to be compared.
- Metric: The specific metric to be compared across periods.
- Date Identifier: This date is considered based on the Show fields (Date field) selected in the Transformation task for which Period Over Period Comparison is calculated. The Rule Run date identifies the current period record, not the current date.
- Aggregation Method: The method used for aggregation such as average, sum etc.
- Num of Prior Periods: The number of earlier periods to include in the comparison.
- Treat Missing Data as Zero: Set this to True to treat missing data as zero or False otherwise. Missing data is either an absence of the record or a null value or empty value for the metric selected. When set to:
True - Missing data is treated as zero and excluded by the Aggregation Method.
False - Missing or null values are treated as zero. - The Formula Field Builder allows users to create a Period Over Period Comparison, which is useful for comparing metrics like ARR across different time periods.
Example Calculation
Let's walk through an example to better understand the functionality of the Period Over Period Comparison. We use a sample dataset and examine how the comparison operates with a rule run date of 1/9.
| Date (Current Date to older) | Clicks |
|---|---|
| 1/8 | 1 |
| 1/7 | 0 |
| 1/6 | 5 |
| 1/5 | 3 |
| 1/4 | 8 |
| 1/3 | null |
| 1/1 | 6 |
Period Over Period Comparison calculation for the provided data set.
-
Past 2 Days AVG minus Prior 6 Days AVG = Period Over Period Comparison
Duration 2 x Num of Prior Periods 3 = 6 Prior Days.
-
For Past 2 Days (1/8 - 1/7, as the rule run date is 1/9):
1 + 0 1
-------- = ----- = . 5
2 2
-
For Prior 6 Days (1/6 - 1/1):
5 + 3 + 8 + 0 + 0 + 6 22
---------------------------- = ------- = Approx. ~ 3.67
6 6
As the record for 1/3 holds a null value and 1/2 is missing, a value of 0 is considered instead.
-
Period Over Period Comparison
.5 - 3.67 = -3.17
Alternative Calculation
The following examples use the same sample data used in the previous section:
-
Use Period Over Period Comparison Percent, (( Period Over Period Comparison / Num of Prior Periods) *100) (In this example, it’s prior 6 days)
-2.17 / 3.67 = -.5913 * 100 = - 59.13%
-
Set 'Treat Missing Data as Zero' to “True”:
For Past 2 Days (1/8 - 1/7):
1 + 0 1
-------- = ----- = . 5
2 2
-
For Prior 6 Days (1/6 - 1/1):
5 + 3 + 8 + 6 22
---------------------------- = ------- = 5.5
4 4
As Treat Missing Data as Zero is set to 'True' and the record for 1/3 holds a null value and 1/2 is missing, this record is excluded from Period Over Period Comparison calculation.
Period Over Period Comparison = .5 - 5.5 = -5.0
Period Over Period Comparison Percent = (-5.0 / 5.5) * 100 = -90.9%