Calculate a Customer Health Score in Salesforce using data from Pendo

A customer health score is a metric that consolidates multiple indicators of whether a customer is likely to churn based on their engagement with your product. As well as measuring the health of the relationship with your customers, it can be useful in determining which customers would make good candidates for upselling.

Pendo has developed the Product Engagement Score (PES) as a composite measure of customer health for a single measure of user sentiment and engagement. The PES is calculated based on measures of adoption, growth, and stickiness.

You can also push some Pendo user engagement metrics into Salesforce through the Salesforce integration with Pendo, you can use our Breadth, Depth, and Frequency (BDF) framework to measure a customer’s health.

Indicators	Description
Breadth	The number of visitors using the product for a given account over a specified period of time.
Depth	The usage of five to eight key features that have been validated as leading retention indicators.
Frequency	The amount of time that your visitors are spending in your product, and how often these visitors are returning.

This article provides an example of how you can use Pendo data to build your own BDF score in Salesforce. However, a customer health score doesn't have a set formula. You can modify this example to use different criteria. 

This example provides a starting point. If you adjust your criteria, you must also adjust the Salesforce fields to capture the correct data and calculations. 

Prerequisites

• Pendo admin account permissions.
• Pendo Salesforce integration. For instructions, see Set up the Salesforce integration for Pendo Engage.
• The ability of your Salesforce admin to add new fields in Salesforce.
• Spreadsheet software to help you find your benchmarks. This article uses Microsoft Excel in its example.

Step 1. Create an account report in Pendo

In Pendo, navigate to People > Accounts > Account Reports and create a new report with the following parameters and columns:

• Set the segment to Everyone.
• Set the date range to Last 30 Days. This is important for setting up your Salesforce data mappings.
  
  
  
  
• Add the following columns:
  • Visitors, used for breadth. These are account-level visitors set up during Pendo installation, indicated with the double-person icon.
  • Time on App (minutes), used for frequency. This is account-level metadata set up during Pendo installation, indicated with the double-person icon.
  • Feature, used for depth. Choose at least one important Feature that has been validated as a retention indicator.

“Depth” can differ between companies because key features differ based on how the business is defined. We typically recommend choosing at the top five to eight features that are used by your power users. For simplicity, this example only uses one so you can repeat as necessary. 

If you don’t know what top Features to use in your health score, use Pendo Feature data to see what your power users and current customers use the most. For more information, see Tagging and viewing Features.

Step 2. Export your account report

From the report you just created in Step 1, use the CSV icon in the top-right corner of the page to export your account report. Each row in the downloaded CSV file represents an account, indicated by an Account ID.

Step 3. Prepare your spreadsheet

Create two tabs in either Microsoft Excel or Google Sheets.

• Name the first tab Account Report Data. This tab will be used for data that you copy over from your exported account report (CSV file).
• Name the second tab Formulas. This tab will be used to automatically calculate your benchmarks based on the data you add to the first (Account Report Data) tab.

Account Report Data tab

Create a column for each measure: Number of Visitors, Time on App, and Key Feature Usage. If you're analyzing more than one Feature, create a column for each Feature. Any data that you add to these columns is used by the Formulas tab that you create next.

Formulas tab

Set up the formulas tab to automatically calculate benchmarks based on any data that you add to the first (Account Report Data) tab.

We use a four-point rating system that correlates to "red" (0), "at risk" (1), "healthy" (2), and "champion" (3) and calculate breadth, depth, and frequency using a percentile formula. Using percentiles is one way to determine an even distribution across four segments. 

• 25% = red
• 50% = at risk
• 75% = healthy
• 100% = champions

You can use the 25th, 50th, and 75th percentiles to estimate where to draw usage lines in each category.

To create this rating system and automatically calculate the 25%, 50%, and 75% benchmarks, create a table for each measure. These correspond with the columns in the Account Report Data tab of your spreadsheet: Number of Visitors, Time on App, and Key Feature Usage.

• In the first column of each table, add the following: Red in the first row, At risk in the second row, Healthy in the third row, and Champion in the fourth row.
• In the second column of each table, add the following tiers: 25% in the first row, 50% in the second row, 75% in the third row, and 100% in the fourth row.
• Leave the third column blank. This column is used to calculate your benchmarks in the next step.
• In the fourth column of each table, add the points system next to each percentile: 0 in the first row, 1 in the second row, 2 in the third row, and 3 in the fourth row.

Step 4. Calculate your benchmarks

First, copy the data from the exported account report to the appropriate columns in the first tab (Account Report Data) column of your spreadsheet.

Next, populate each table in the second tab of your spreadsheet (Formulas) with calculations. You do this by adding the appropriate percentile function to the third column of each table. Note that there must be an exact match, including casing and spacing, for the name of the first tab inside the formula. In this case, the exact name of the tab is Account Report Data, and is encapsulated by an apostrophe (') on each side within the formula.

For example, the formulas for the Number of Visitors table would be as follows.

• For the first row (Red, 25%), =PERCENTILE.EXC('Account Report Data'!A:A,0.25)
• For the second row (At risk, 50%), =PERCENTILE.EXC('Account Report Data'!A:A,0.5)
• For the third row (Healthy, 75%), =PERCENTILE.EXC('Account Report Data'!A:A,0.75)

There is no formula for the fourth row (Champion, 100%) because the 100% benchmark is any value above the 75% benchmark. You can manually add numbers to the 100 percentile. For example, if the 75% benchmark is 2.5, you can add "> 2.5." as the 100% benchmark.

For each table, swap A:A for the appropriate letter representing each column in the Account Report Data tab of your spreadsheet. For example, if data for Time on App is in column B, change the formulas to =PERCENTILE.EXC('Account Report Data'!B:B,...).

Step 5. Create your Salesforce fields

Create Salesforce fields to capture Pendo data and calculate your score.

Note: You must have “Pendo_” in the API name. The Pendo Salesforce integration only writes to custom fields in Salesforce that start with “Pendo”.

Navigate to your Salesforce account and create the fields that you want to capture from Pendo and score calculation fields. In this example, you need to set up at least seven fields:

1. • Numeric field with the name Number of Visitors and API name of Pendo_Number_of_Visitors.
   • Numeric field with the name Feature Name and API name of Pendo_Feature_Name.
   • Numeric field with the name Time in App and API name of Pendo_Time_in_App.

Next, create formula fields in Salesforce to calculate scores by replacing the "#" with the benchmarks you found in Step 2 and using the Pendo data captured in the fields you created above. For more information, see Salesforce's article: Build a Formula Field.

Create a field to calculate your “Breadth” score called BDF Visitors (Field type = Custom Formula Field with type of Number and no decimals). Copy the code below and replace the # with the benchmarks you found in Step 2.

IF(Pendo_Number_of_Visitors__c > # ,
3,
IF(Pendo_Number_of_Visitors__c > # ,
2,
IF(Pendo_Number_of_Visitors__c > # ,
1 ,
0
)
)
)

Create a field to calculate your “Depth” score called BDF Feature Name (Field type = Custom Formula Field with type of Number and no decimals). Copy the code below and replace the # with the benchmarks you found in Step 2.

IF(Pendo_Feature_Name__c > # ,
3,
IF(Pendo_Feature_Name__c > # ,
2,
IF(Pendo_Feature_Name__c > # ,
1 ,
0
)
)
)

Create a field to calculate your “Frequency” score called BDF Time (Field type = Custom Formula Field with type of Number and no decimals). Copy the code below and replace the # with the benchmarks you found in Step 2.

IF(Pendo_Time_in_App__c > # ,
3,
IF(Pendo_Time_in_App__c > # ,
2,
IF(Pendo_Time_in_App__c > # ,
1 ,
0
)
)
)

Total Score (Field type = Custom Formula Field with type of Number and no decimals). Copy the code below.

BDF_Visitors__c +
    BDF_Feature__c +
    BDF_Time

Step 6. Set your report to push to Salesforce

In the account report you just saved:

1. Select Set Up in the Salesforce Push row to start mapping your Salesforce fields.
   
   
2. Map the newly created Salesforce fields and save your settings.
   
   
3. Select Sync Now in the account report to manually push changes into Salesforce for your one time test. After this, your data syncs automatically every 24 hours. Once the sync is complete, review the data in Salesforce.