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 comparison of data between any two defined periods. This function is available with Transformation Task, on clicking +Formula. An example use case is period over period comparison of usage metrics or support cases.
Note: This formula does not apply aggregation to the data being compared. Before applying this function, you will need to aggregate the data to the level that you would like to view it.
Period Over Period Comparison Template (Non Percent):
The following list provides descriptions of all arguments in the Period Over Period Comparison function:
- Period Type: The period type such as days, weeks, months etc..
- 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.
Note:
- Rule Run date is referred to identify the date record that qualifies as current period record.
- This is 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 be included for comparison.
- Treat Missing Data as Zero: Set this to True to treat missing data as zero or False otherwise. Missing data is either 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
Example
Consider the following sample setup with rule date - (1/9):
Period Over Period Comparison(Period ( DAYS , 2 ), Clicks , Data Date , AVG , 3 , false )
Data Set
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%