Creating new formulas

Note: The following is considered a PLUS feature and additional licensing is required. For more information, contact your AiSP or ASI Technical Support.

System administrators can create custom scoring formulas using IQA queries. Review the following query information before creating a new formula to ensure the queries are properly built.

Types of queries and their necessary query elements

Before defining formulas in RiSE > Scoring, ensure the necessary IQA queries are built with the required criteria. Different types of queries require specific properties to calculate scores accurately:

• All queries - Every query must include a Contact Key property, or a property with an Alias of ContactKey to identify the contact for which the data is being collected. Use one of the following business objects:
  • (recommended)NetContactBasic - Use if combo records should be considered as individuals. Combo records are returned as one row and financial data is not included.
  • (not recommended)NetContactData - Use if combo records should be considered as organizations. Combo records are returned as one row and financial data is not included. This business is not recommended in other scenarios because it may cause performance issues.

  Note: A combo record is a company record that also contains a first and/or last name.

• Transaction data queries - Queries linked to specific transaction dates (e.g., purchases, donations, or event registrations) must include an Updated On property or a property with an Alias of UpdatedOn. This property ensures the correct data is retrieved for a given point in time, preventing transactions from being counted more than once when scores are calculated.
• Sampled data queries - These queries involve data without specific transaction dates, such as committee memberships or subscriptions. Scores reflect the data at the time the formula is run, and recalculating later may alter results. An Updated On property is not required.

Building the formula

Once you have defined the relevant queries, do the following to create a new formula:

1. Go to RiSE > Scoring.
2. Select Add new formula.



3. Enter a Score name and Description.
4. Click Save.



5. In the Score components area, click Add new. Enter the following information:
   1. Define a Name.
   2. Select an IQA Query. Review the necessary query elements before selecting an IQA query.
   3. Select the Score calculation of Count or Sum:
      • Count - The number of rows returned by the query will be the value weighed against the Multiplier.
      • Sum - Takes the total sum of the selected Column to sum, and use that value to be weighed against the Multiplier.
   4. (optional) If the score calculation is Sum, select a column in the IQA query from the Column to sum drop-down.

   Note: If the selected Column to sum is a custom SQL expression column, the column must be of an aggregate type, such as SUM, MAX, MIN, AVG, COUNT, or some other aggregate function expression.

   5. Select a Multiplier value that will be multiplied with the score calculated from the IQA query.

   

6. Click Save.
7. Continue adding Score components as necessary to define the new formula.
8. Make sure the formula is Enabled.



9. Click Save.

Defining score categories

Score categories classify constituents based on their scores within a specific formula. Each formula supports the creation of custom categories (e.g., Gold, Silver, and Bronze) to distinguish constituent scores according to the formula’s criteria.

Do the following to define score categories for a formula:

1. Go to RiSE > Scoring.
2. Open an existing score.
3. From the Categories section, select Add new.



4. Enter a category Name.
5. Select a category Color. When a user falls into this category, the color appears in the Engagement Score Display content item.
6. From the Criteria section, select Add new.



7. Define the Criteria that constituents must meet for this category:
   1. Basis - Select how the category is determined:
      • Score - A constituent’s total score.
      • Percentile - A constituent’s relative percentage.
      • Score components - The Score components related to this scoring formula can also be used as a determinant for the category.
   2. Comparison - Choose whether the constituent's score must beLessThan, Equal, or GreaterThan the specified Value to meet the category criteria.
   3. Value - Enter the Value required to meet this Criteria.

   

8. Select Add new to continue adding Criteria for the category.
9. Save the category.
10. Select Add new to add additional Categories to the score formula.
11. Once the categories are defined, Save the score formula.

Note: If multiple criteria are defined for a scoring formula, users must meet all criteria to fall into a category. Users are assigned the highest-ranked category they qualify for.

Next steps

After defining the score formulas, consider the following next steps:

• Calculate the scores
• View the scores
• Updating the account page to use custom scores

For detailed examples of defining scoring queries formulas, review the following:

• Creating a score formula based on event participation
• Creating a score formula based on membership participation
• Calculating yearly scores
• Locating at-risk engagement score contacts and sending them an email
• Reviewing historical scores from previous months or years