How to Create Salesforce Star Rating Formula Fields for Prospect Score

May 8, 2023
Lead Scoring, Salesforce and Pardot, Salesforce Ben | The Drip

You can visually display prospect engagement or quality with a rating field on Salesforce Lead or Contact records. For example, you can take the Account Engagement (Pardot) prospect score and convert it into a 1-5 star rating.

Stars are one type of image formula field that any org can implement. It’s useful and quick to set up, so it’s good ‘bang for your buck’! Let’s first explore the benefits, then step-by-step instructions on how you can deliver this quick win.

Salesforce Star Rating Formulas: The Benefits

The reality is that the marketing and sales teams are working with plenty of ever-changing data. As prospect scores can be adjusted from 0 through to figures in the thousands (and negative, too), simply telling someone a prospect’s score is, say, 100, has little impact. What’s the context? Does this score reflect whether we believe it’s a good, average, or poor prospect?

The rating of 1-5, displayed as stars, gives any user an indication of where this prospect sits relative to all the other prospects in your database – just like the hotel rating system that compares one establishment to all the others.

As your database changes, perhaps your definition of a good/average/poor prospect also changes. The beauty of this formula is that you have control over what the star ratings means to your organization.

While this field helps communication between teams, it also improves the Salesforce user interface. While customizing Salesforce for organizations, I quickly realized how important this was. Make it eye-catching, and people will notice it.

Step 1: Identify Your Score Thresholds

There’s always groundwork to do! Ask yourself:

Should star thresholds be equally spread (quintiles)? For example, a score of 50 is one star, 100 is two stars, 150 is three stars, etc.
Or, should the stars be weighted another way? For example, a score of 50 is one star, 100 is two stars, 500 is three stars, 1000 is four stars, etc.
Should only MQLs get a star rating? If so, how do you define a marketing qualified lead (MQL)?

Decide what score a prospect must reach to be a one-, two-, three-, four-, five-star prospect. This is unique to your business, and there’s no right answer I can give you. Every Account Engagement (Pardot) Admin has free reign to adjust the out-of-the-box scoring model, and both increase and decay prospect scores – so you need to decide based on your own score data.

Step 2: Test Your Score Thresholds

You should look at the number of prospects that would hypothetically fall into each level to cross-reference your assumptions with the reality your data is showing. Use Salesforce report bucket fields to group prospect scores. Visualizing this as a donut chart will show how your proposed thresholds look.

Note: When you create the report chart ensure you use record count as the measure.

Can’t see report buckets? This feature is only available with Salesforce Enterprise edition and above. Alternatively, you can export the data and do the same manipulation elsewhere e.g. Excel.

Step 3: Write the Formula

Take the template below and insert the thresholds you decided on in the previous step:

IMAGE(
IF(pi__score__c< [insert number for 0 stars] , "/img/samples/stars_000.gif",
IF(pi__score__c< [insert highest number for 1 star + 1] , "/img/samples/stars_100.gif",
IF(pi__score__c< [insert highest number for 2 stars + 1] , "/img/samples/stars_200.gif",
IF(pi__score__c< [insert highest number for 3 stars + 1] , "/img/samples/stars_300.gif",
IF(pi__score__c<[insert highest number for 4 stars + 1] , "/img/samples/stars_400.gif", "/img/samples/stars_500.gif"
))))),
"Rating")

For example, if these are your scoring thresholds, your formula will look like the one below:

0 stars = 0
1 star = 1-49
2 stars = 50-199
3 stars = 200-499
4 stars = 500-999
5 stars = 1000+

IMAGE(
IF(pi__score__c

Step 4: Create the Image Formula Field

Go to Setup → Object Manager. Search for Contacts.
Go to Fields & Relationships in the left menu. Click New.
Select Formula.

Name your field (the other name box will auto-populate). Select Text as the Formula Return Type.

When you get to the formula editor, copy and paste your formula into the box.

Finally, tick which profiles in your org should see this field.

Step 5: Add the Star Rating to Page Layouts

After field accessibility (i.e. which users can see the field), Salesforce asks if you want to add this field to page layouts.

If you want to adjust where this field sits, go to the Page Layouts section and drag and drop where you want it. Personally, I create a new section to keep all the marketing fields data together; however, you may want the field further up the page.

Step 6: Repeat for Leads

You’ve now created this field for Contacts. Repeat steps 1-5 to create it for Leads.

Take Star Rating Further

I received a request to make the Account page more engaging, so that users could see who the most engaged Contacts working for that company are.

Add the Star Rating field to the Contacts related list on the Account page layout. It will look something like this:

Summary

Star rating means you can visually display prospect engagement or quality on Salesforce Lead or Contact records. Add this to improve the Salesforce user interface and gives any user an indication of where this prospect sits relative to all the other prospects in your database.

This Pardot article written by: