Skip to main content
Gainsight Inc.

Formula Builder in Bionic Rules

Overview

This article reviews the Formula fields supported in Bionic Rules. The Formula Builder allows you to create different types of formulas so that you don’t have to create the formula on the Object directly in SFDC or MDA. The +Formula Field option can be seen only when you perform any Transformation task. Formula Builder allows you to do the following tasks: 

  • Allows you to build a numerical expression on fields with all arithmetic operators.
  • Enables you to generate a formula on the columns of a single record.
  • Enables you to generate a complex formula on multiple record sets.
  • Enables you to perform complex functions such as SUM, AVG, or AGGREGATE of a column in a set of rows.

Two types of Formulas are supported in Bionic Rules

  1. Expression
  2. Function

Supported Formulae  

  1. Numeric Formulas
  • Admin Page Views + Config page views
  • Current Score - Previous Score
  • NPS Avg * 10
  • ((Current Score - Previous Score) / Previous Score) * 100
  1. String Formulas
  • First Name + Last Name
  • CTA Comments + Custom Text
  • Convert to Upper Case (Account ID)
  • LEFT(field, substring) - extracts the left part of the string from the appearance of string character. 
  • RIGHT(field, substring) - extracts the right part of the string with the appearance of a string character.
  • POSITION(field, substring) - provides the position of the substring in a string. For example, POSITION(“doe” in “johndoe”) returns 5. Counting of the position starts with an index 1.
  • LENGTH(field) - provides the length of the string. For example, LENGTH(“string”) returns 6.
  • SUBSTRING(field, position, length) - finds the substring based on the position and length.
  1. Date Formulas
  • (Last Modified Date - Created Date) in days
  • Convert to Date time (Created Date)
  1. Period Over Period Comparison

Limitations

  • An expression cannot be added when GroupBy field is added to a task.
  • Constants in Formula Field are not considered as a token.
  • Formula time span cannot exceed 2 years. 
  • Period Over Period Comparison or Period Over Period Comparison in % formula cannot be created unless a date identifier is present.
  • Negative values are not allowed in the Function for Period Over Period Comparison and Period Over Period Comparison in %.
  • ((A-B) * (A+B)) cannot be simplified to (A-B) * (A+B).
  • Period Over Period Comparison and Period Over Period Comparison in %accept only integers, not decimal values.
  • Concat function allows only 10 arguments.
  • A maximum of only 10 formulas can be added.

Add a Formula Field

  1. Navigate to Administration > Rules Engine > +RULE (Bionic - Formula Builder Test).

  1. Click NEXT to navigate to Setup Rule screen.

  2. In Setup Rule screen, click the edit icon for the aggregated task (Transformation Task) as shown in the following image.
    Note: You can create a Formula Builder only for Transformation Task.

  1. Select the Source Object (here, Formula Builder Test) and add the required fields in the Show section.

  1. Click +Formula Builder available on the Setup Rule screen. The Formula Field dialog box is displayed.

Expression_FormulaB.png

  1. Provide the following information:

  • Output Field Label: Enter only alphanumeric and underscore characters in this field. The label should always start with an alphabetical character.
  • Output Header: This name is used as CSV Header to process action. This is view only and is generated based on the Output Field Label (but without space).
  • Decimal Places: Enter a number between 0 and 9.
  • Treat null as zero: Select this to replace any null field values with zero while performing calculations using the expression provided. Deselect this to use any null value as is in the calculations.
  • Formula Type: Select Expression or Function as required. Refer to Expression and Function to learn more about formulas available in Formula Builder.
  1. Click SAVE. Clicking RESET erases the data that you entered and allows you to refill the field with new information. 

  2. In Setup Rule, you can see the newly created Formula Field is added in the Show section as shown in the following mage.

The same field is also added in Setup Action > Action type >Field Mappings section.

Add an Expression

All arithmetic operators are considered as Expression. The primary items of Expression are:

  • Operator: *, +, -, and /
  • LHS: Left hand side Token to the operator (Currency Field or Numeric Field)
  • RHS: Right hand side Token to the operator  (Currency Field or Numeric Field)

If any LHS or RHS is missing in the Formula Field, the following message is displayed when you try to SAVE the formula.

  • Arguments: The list of arguments(Tokens) participating in the method for evaluation.
    Note: If tokens are not entered, the following message is displayed.

  • Arguments length: Max number of arguments that this method can accept. Especially in case of multi-row scenario.

Sample Expression Syntax and Flow
  • <FIELD/VALUE> <OPERATOR> <FIELD/VALUE>

  • <FUNCTION>(<FIELD/VALUE>,[<FIELD/VALUE>/<METHOD>(...)])

Add a Function

Some calculations were already available in Engagement Rules, but since they are deprecated in the 5.6 release, Gainsight now offers the Formula Builder in Bionic Rules. The following sections explain different types of functions available in Formula Builder.

String Type Functions

The following functions allow only String, Picklist, Multi-picklist data types as input arguments.

  1. Concat: Concatenates the selected arguments. A maximum of 10 arguments can be added and one string can be used multiple times.

Concatentation Functions.png

  1. To Lower Case: Converts the selected argument values to Lowercase.
  2. To Upper Case: Converts the selected argument values to Uppercase.
  3. ​​​​​​LEFT(field, substring) - extracts the left part of the string from the appearance of string character. For example, LEFT(johndoe@gainsight.com, @) will return “johndoe” as the output.
  4. RIGHT(field, substring) - extracts the right part of the string with the appearance of a string character. For example, RIGHT(johndoe@gainsight.com, @) will return “gainsight.com” as the output.
  5. POSITION(field, substring) - provides the position of the substring in a string. For example, POSITION(“doe” in “johndoe”) returns 5. Counting of the position starts with an index 1.
  6. LENGTH(field) - provides the length of the string. For example, LENGTH(“string”) returns 6.
  7. SUBSTRING(field, position, length) - finds the substring based on the position and length. For example, Substring(“johndoe”, 2, 3) returns “ohn”.

Substring.png

Date Type Functions

The following functions allow only Datetime data type as input arguments.

  • Date: Converts Date-time argument to Date.
  • DateDiff: Returns difference of the two selected dates in the selected unit.

The following functions allow only Date and Datetime data types as input arguments:

  • First Day of Calendar Quarter: Returns the first day of Calendar Quarter.
  • First Day of Current Month: Returns the first day of Current Month.
  • First Day of Current Week: Returns the first day of Current Week.
  • Last Day of Calendar Quarter: Returns the last day of Calendar Quarter.
  • Last Day of Current Month: Returns the last day of Current Month.
  • Last Day of Current Week: Returns the last day of Current Week.

Note: By default, Rule date is available in these functions.

Period Over Period Comparison Functions

The following are the other available functions:

  • Period Over Period Comparison
  • Period Over Period Comparison in %

These functions cannot be created unless a date identifier is present. Arguments are consumed inside a function.

POP.png

Example: Function syntax is as listed below. In this case, this function has a fixed number of arguments and each argument has its own context.

  • Period Over Period Comparison ( Period([ DAY / WEEK / MONTH / YEAR ], <Duration>),
  • <Metric [ field of type numeric ]>,
  • <Date Identifier [ field of type date ]>,
  • <Aggregation Method [ SUM,AVG,COUNT, COUNT DISTINCT, MIN, MAX ]>,
  • <Num of Prior Periods>,< Treat Missing Data as Zero  [ true/false ]>);

To accommodate this kind of requirement, Argument item is added. Refer to Bionic Rules Period Over Period Comparison for more information.

Statistical Functions

Gainsight introduces Statistical Functions in Bionic Rules. This is a first step towards introducing Data Science capabilities in the Rules Engine. Using Statistical Functions, you can derive insights from your datasets that will help you make more informed decisions regarding your product and customers.

  1. Correlation - The correlation function shows you how two variables are related. This function looks at the strength of the relationship between two variables and helps you make a decision based on the results.

  2. Covariance - Covariance is a type of correlation. It indicates whether there is a positive or negative correlation but does not indicate the strength of correlation between two variables.

  3. Standard Deviation and Variance - Both Standard deviation and variance measure volatility/variability. A high value indicates that the dataset is spread out; a low standard deviation means that the dataset values are close to the mean.  

  4. Variance - Variance measures how far a set of numbers are spread out from their average value.

  5. Median - Median is more useful than an average when your dataset has outliers; meaning if the dataset has high standard deviation. 

Best Practices in using Statistical Functions

  • Correlation and Median functions, if used together within the same task, may not provide accurate results if the data has nulls.

  • Add filters with not null checks: if you are unsure if the dataset has null values or not and want to use correlation along with other statistical functions within the same task, it is suggested to add filters with not null checks. This will filter out the null data records and produce accurate results.

  • Correlation/Median with other aggregations: It is suggested not to use correlation or median functions in conjunction with any other aggregation (like sum, min, max, avg) or function if your data has null values.

  • For Nulls: Use "Treat null as Zero" for all the relevant metrics. You can use formula field here to pick each of these fields and select the Treat null as Zero option.

Correlation

The correlation function shows you how two variables are related. This function looks at the strength of relationship between two variables and helps you make a decision based on the results. For example, this function lets you forecast a risk and helps you take appropriate steps.

For example, if you see the data of two variables and correlate it, if one variable goes up and the other goes down, then it is said to have a negative correlation. In this case, you can be assured that risk is not involved based on the two variables in consideration. If one variable goes up and the other goes up then it is said to have a positive correlation. In this case, you can decide that the two variables have a correlation and then take the necessary steps to mitigate the risk.

For example in Gainsight, you can correlate the number of CTAs in a high-risk category (1st variable) and the customer churn in the next 4 months (2nd variable). If the result says that they have a positive correlation, then you can take appropriate steps to understand why the customer is churning.

Following are some use cases in which Statistical Functions can be used:

  1. Are the number of support tickets positively/negatively correlated to a higher NPS score?

  2. Is health score correlated to Churn Rate or Upsell Opportunity?

Prerequisite: A field with Number datatype should be present to use this function.

Output: any value between -1 and +1. Can also be a decimal.

How to use Statistical Functions (you can use a similar approach to use the other statistical functions in Bionic Rules)

  1. Navigate to Rules Engine.

  2. Create or edit an existing Bionic Rule.

  3. Use the output of a dataset and create a Transformation task.

  4. Click + Formula Field. The Formula Field dialog appears.

  5. In the Formula Type section, select Function.
    Formula1.png
  6. In the Formula Type text box, select Correlation and select the two fields of your choice.

Correlation.png

  1. Provide a name for the Output Field Label.

  2. In the Decimal Places field, specify a number. This will be the allowed number of decimal places in the output of the function.

  3. Click SAVE. The formula field will be saved and visible as a Show field in the task. To view the results of the functions, save the task and run the rule. In the execution history, you can see the task’s output and download them.

ExecutionHistory.png

  • Was this article helpful?