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 uses Breadth, Depth, and Frequency (BDF) to measure a customer’s health.  

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.
• Include the following columns: Visitors (used for breadth), Feature (used for depth), and Time in App (used for frequency). For the Feature column, choose at least one important Feature that has been validated as a retention indicator.

Note: Columns are based on account level metadata fields setup at Pendo installation. 

The Feature column is used for "depth" and refers to tagged Feature usage. “Depth” can differ between companies because key features differ based on how the business is defined. We typically recommend choosing 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.

Step 2. Find your benchmarks

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

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

By finding the 25th, 50th, and 75th percentiles, we can make an educated estimate of where to draw our usage lines in each category.

To create your rating system:

1. Download the account report you created and open Microsoft Excel.
2. In a new Excel sheet, create the following chart tiers and your point scoring system for each of the columns in your account report. 
   • Tiers: 25%, 50%, 75%, and 100%
   • Points system: 0, 1, 2, and 3
     
     
3. Use the percentile function in Excel to find the number of accounts in each percentile. Save these to help you create your Salesforce formula fields.
   • 25% =PERCENTILE.EXC(accountList!B:B,0.25)
   • 50% =PERCENTILE.EXC(accountList!B:B,0.5)
   • 75% =PERCENTILE.EXC(accountList!B:B,0.75)
   • 100% = anything above 75%

Step 3. 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
)
)
)

Create a field to calculate your overall score called 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 4. 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 Note 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 completed, review the data in Salesforce.