How to Create Salesforce Star Rating Formula Fields for Prospect Score

Share this article...

Implementing a ‘Star Rating’ field in Salesforce is a nice, quick win, that visually shows prospect engagement or quality at first glance. You can take the Pardot Prospect score field, and covert it into a Star Rating Salesforce formula field. Bringing immediate value with a simple set up, it delivers a good ‘bang for buck’!

Why use Scoring Stars?

Unless you have a tight-knit Sales/Marketing unit, then simply telling someone a prospect’s score is 100 has little impact. It doesn’t say anything about how this lead rates against the rest of the leads in your Salesforce database. It makes Prospects Score relatable to one another; just like the hotel rating system, other teams will know that a 5-star prospect is better than a 2-star prospect.

Not only does this field aids communication, but it also improves the Salesforce user interface. I realised this recently when I was customising Salesforce for a client to make their account page interface more ‘ABM-ready’. A key element was to use Scoring Stars to show where the engagement lay within that account – to view the account and identify which prospects are active, and where there is account whitespace.

If you see this having the potential to improve your Salesforce Lead, Contact, or Account pages, then read on for the tutorial.

Step 1: Identify your thresholds

There’s always groundwork to do if you want to implement something correctly on the first attempt.

Ask yourself: what do you want to score?

Should stars thresholds be equally spread (quintiles)?
Should only MQLs get a star rating? If so, how do you define an MQL?

Decide what score a prospect must reach to be a 1 / 2 / 3 / 4 / 5-star prospect.

This is unique to your business. Pardot customers have free reign to adjust the out-of-the-box scoring model, and both increase and decay prospect scores – so there’s no one answer what score you should relate to which star rating.

To test your star rating levels, you should look at the number of prospects that would fall into each level. If you’re savvy with reporting, use Salesforce report bucket fields to group Prospect Scores. Seeing this is a donut chart will visualise how realistic your proposed thresholds are. When you create your report chart, make sure you are using record count as the measure.

The 4 images below walk you through the process:


Can’t see report buckets? This feature is only available on Enterprise Edition and above. You can run an export and do the same manipulation elsewhere.

Step 2: Write your formula

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

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"

An example (if these are your scoring thresholds):

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

Your formula would look like this:

IF(pi__score__c<1 , "/img/samples/stars_000.gif",
IF(pi__score__c<50, "/img/samples/stars_100.gif",
IF(pi__score__c<200, "/img/samples/stars_200.gif",
IF(pi__score__c<500, "/img/samples/stars_300.gif",
IF(pi__score__c<1000, "/img/samples/stars_400.gif", "/img/samples/stars_500.gif"

Step 3: Create the formula field

To create a new 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), and select ‘Text’ as the Formula Return Type.

When you get to the formula editor, copy & paste your formula.

Tick which profiles in your org should see this field.


Step 4: Add the Star Rating to your page layouts

The next step following filed accessibility (ie. which users can see the field), is page layouts. Salesforce automatically asks if you want to add this field to your page – so you can click ‘Save’.

If you want to adjust where this field lands, go to the ‘Page Layouts’ section to drag and drop where you see fit. I create a new section to keep all the marketing data together and not clutter record pages.

You’ve now created this field on contacts – don’t forget to repeat for the lead object!

Take Star Rating further!

As I mentioned in the opening, this field can help your org become more ‘ABM-ready’, to see where engagement lies within each account. To get a view on your account page like the image below, add the Star Rating field to your Contacts Related List on the Account page layout:

How about extending this to Grading too? Read this follow-up post for a how-to Prospect Grading Rating.

2 thoughts on “How to Create Salesforce Star Rating Formula Fields for Prospect Score

  1. This is great. Did you end up setting one up including grading? We are likely to try to do a combo of the two… Thanks!

Add Comment