Period Over Period Comparison in Rules Engine
IMPORTANT - Articles Impacted due to 6.42 July CS Release
Due to the v6.42 July, 2024 release, this article has been impacted. Steps, images, and playable GIFs in this article will soon be updated to reflect the latest changes.
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 formula does not 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 (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 period type such as days, weeks, months.
- Duration: The length of the period.
- Metric: The metric which is compared for the selected 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.
- 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 the absence of the record or a null value or empty value for the metric selected. When set to:
- True - missing data or null value is considered as 0 (zero)
- False - the record for missing data is excluded by the Aggregation Method
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.
|
Data 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, 0 is considered instead.
- Period Over Period Comparison
.5 - 3.67 = -3.17
Alternative Options
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%