Better Salesforce (pt. 2): Leads & Opportunities Rating System

In the first part of this Salesforce mini-series we have focused on improving the consistency of our data. Leads that end up in Salesforce can vary in quality, though. Discovery process can reveal new facts about opportunities. That’s why we have created a custom system for rating leads and opportunities.

Goals

We created the rating system with the following goals in mind:

1. Provide an early guide for lead engagements – in other words, a high probability lead with a big budget needs more attention than a low probability lead with a small budget
2. Find out what types of leads we are receiving the most – are most of our leads low rated or high rated leads? What can our marketing department do to attract more high rated leads?
3. Correlate rating with won opportunities – do we win high rated opportunities? We should as those are the most valuable opportunities and we spend the most time on them. If we lose them, what are the reasons?

Solution

Our solution consists of a set of custom rating criteria. We update the criteria during lead qualification or while working on an opportunity. Salesforce then calculates the lead or opportunity score from the criteria.

Criteria

We use the following 5 criteria:

1. Budget range
2. Market
3. Our Experience
4. Credibility
5. Match

Each criterion can have 4 options and each option has a certain value. These values are as follows – 0.25, 0.5, 0.75 and 1. We use them later in the score calculation.

If criteria isn’t clear at the beginning, for example, the budget, we guess it and make it more accurate later in the discovery process.

Budget range

The budget range defines the range the lead will most likely fall into.

In our system the budget has a weight of 2, so the regular values are doubled.

20k+	2
10k – 20k	1.5
3k – 10k	1
1 – 3k	0.5

Market

What country does the lead come from? This criterion expresses our orientation on certain markets plus our historical experience with winning leads from certain countries.

US, Canada, UK	1
Australia, Western Europe, Israel	0.75
Eastern Europe	0.5
Other	0.25

Credibility

How credible is the person or the company behind the lead? This assumes that if someone has a serious interest in our services they use their real name. They don’t hide what company they work for and use their work email address. Anonymous leads are the least credible.

Very High	1
High	0.75
Medium	0.5
Low	0.25

Our Experience

Our experience with a particular project type or technology stack. As you can see on one of our services page, we focus on certain technologies like React or WordPress. If a project requires technology that we typically don’t use, we need to lower the rating for this criterion.

Very High	1
High	0.75
Medium	0.5
Low	0.25

Match

Finally, is this client a good match for us? Are we a good match for them? We consider things like price expectations, level of engagement, the amount of project details provided and others.

Very High	1
High	0.75
Medium	0.5
Low	0.25

Lead and opportunity score

The lead or opportunity score is calculated from the above criteria as a percentage and displayed in Salesforce as a color coded value.

Based on the score, we sort leads into the following categories:

• 91 – 100% – Excellent lead, go for it!
• 81 – 90% – Very good lead, worth the effort!
• 71 – 80% – Good lead, proceed as usual.
• 61 – 70% – Average lead, you can try.
• below 60% – Poor lead, don’t spend too much time on it.

Implementation

Let’s take a look at the implementation of our rating system. While Salesforce allows you to work with advanced formulas, the biggest obstacle to deal with is formula limits. Even without color coding, our main formula was quickly exceeding the 5,0000 bytes compile size limit.

We’ve solved this problem by creating helper custom fields and splitting calculations to more steps:

1. Create global value picklist sets
2. Define custom fields
3. Calculate custom rating
4. Set score color
5. Display final score

1) Create global value picklist sets

First, we create global value picklist sets so we can share them across leads and opportunities.

2) Create custom fields

In this step, we create custom fields for our criteria, assign global picklists to them and add them to the layout.

3) Calculate custom rating

Now, we calculate the custom rating using the formula field. The formula return type is a number with zero decimal places but it expresses percentual score.

Here’s the formula:

ROUND(
  (( 
    CASE(Budget_Range__c, "Budget_Range_1", 2, "Budget_Range_2", 1.5, "Budget_Range_3", 1, "Budget_Range_4", 0.5, -6) + 
    CASE(Market__c, "Market_1", 1, "Market_2", 0.75, "Market_3", 0.5, "Market_4", 0.25, -6) +
    CASE(Credibility__c, "Rating_Scale_1", 1, "Rating_Scale_2", 0.75, "Rating_Scale_3", 0.5, "Rating_Scale_4", 0.25, -6) + 
    CASE(Our_Experience__c, "Rating_Scale_1", 1, "Rating_Scale_2", 0.75, "Rating_Scale_3", 0.5, "Rating_Scale_4", 0.25, -6) + 
    CASE(Engagement__c, "Rating_Scale_1", 1, "Rating_Scale_2", 0.75, "Rating_Scale_3", 0.5, "Rating_Scale_4", 0.25, -6)
  ) / 6 * 100)
, 0)

The formula sums up values for particular criteria. Then it divides them by 6 because we have one criterion with weight 2 and four with weight 1.

The default value when no option is selected is -6. This prevents calculating the score when only some criteria are set, which could give us misleading results. In such a case, the sum is zero or less and we will use it for comparison in another formula.

4) Set score color

Score color is a custom text field that we use to store hex values of color depending on a calculated score. We update it using a workflow rule.

Workflow action for updating score color uses the following formula:

IF(Custom_Rating__c >= 90, "4c762e",
  IF(Custom_Rating__c >= 80, "6d9521",
    IF(Custom_Rating__c >= 70, "a98f00",
      IF(Custom_Rating__c >= 60, "d77900", "cd3333"
      )
    )
  )
)

5) Display final score

Finally, using this color coding trick, we display the lead or opportunity score. If the custom rating is less than zero (we haven’t set all criteria), we display N/A instead.

Score is a formula custom field with text return type:

IF(Custom_Rating__c > 0,
  IMAGE("https://chart.googleapis.com/chart?chst=d_text_outline&chld=" +  Score_Color__c  + "|15|l|ffffff|_|" + TEXT(Custom_Rating__c) + "%", ""),
  IMAGE("https://chart.googleapis.com/chart?chst=d_text_outline&chld=999999|15|l|ffffff|_|N/A", "")
)

Conclusion

In this two-parts series we have introduced some of the Salesforce customizations we use to improve our engagement process. These can be especially useful for companies similar to Xfive – creative agencies, marketing agencies or web development studios.

If you use Salesforce for your client engagement, keeping consistent data can save you time in the long run. A custom rating system can help you to focus on those leads and opportunities that really matter. You can adjust criteria, their values and weight according your company needs. The system also provides important feedback about lead quality to your marketing department.

Feel free to post your comments or questions below.

Drop us a line if you need professional help with integration of your digital product with Salesforce. Don’t forget to check out our other services.

—

1. Better Salesforce (pt. 1): Lead Details, Project Info, Portfolio
2. Better Salesforce (pt. 2): Leads & Opportunities Rating System