Lead Scoring by Data Quality ⭐⭐⭐⭐

The problem:

Bad data plagues us all. In all seriousness, if you don’t have bad data then we need to talk so you can tell me your secrets…

Poor data quality has many consequences, here are just a few:

❌ Poor user adoption

❌ Lack of executive buy in

❌ Unreliable reports and dashboards

❌ Slower on-boarding of new employees

❌ Inefficient or unsuccessful marketing campaigns

Poor data quality makes your job a lot harder. How can you convince users that Salesforce is a central system that must be used when you’re showing them records with missing information? It makes Salesforce look unloved and indicates that it’s not a priority.

Poor data quality makes your user’s jobs a lot harder too. Imagine setting up a list of leads for a new business development user, only for them to discover 50% of the mobile phone numbers are missing? Or trying to run an email marketing campaign and finding you only have email addresses for 30% of your contacts?

✔ Unhappy user 

✔ Unhappy Admin

✔ Unhappy business

There are many tools out there designed to help you improve your data quality and complete missing information but these usually come at a cost. So what can you do if you know your data is bad but you don’t have the budget to pay someone to fix it?

The first step is identifying the bad data. Once you know how bad the situation is, you can decide if it can be tackled by you and your users or if you really do need to call in the big guns.

In this post we’ll focus on weeding out bad leads based on a data quality score and star rating. You can apply this to any object that needs data quality analysis by creating a unique data quality score based on the fields that are most important to your business.

The solution:

In this tutorial we’ll create 2 custom fields on the Lead object. The first field will be a number based on how many relevant fields are completed. The second will be a star rating based on the previous score. So, the higher the score the more stars. Simple, right?!

1. Lets start by creating a formula field that gives our lead a total score out of 100, based on which fields are completed
2. Navigate to Setup > Object Manager > {Leads} > Fields & Relationships
3. Create a new field:
   • Data Type = Formula
   • Field Label = Data Quality Score
   • Formula Return Type = Number
   • Decimal Places = 0
   • Treat blank fields as zeroes
   • Enter a description and help text (so your users understand what fields are driving the score)
   • Enter the following formula but use ‘Insert’ to ensure you use the correct API name:

     IF(LEN( FirstName ) = 0, 0,15) 
     + IF(LEN( Company ) = 0, 0,15) 
     + IF(LEN( MobilePhone ) = 0, 0,15) 
     + IF(LEN( Email ) = 0, 0,15)
     + IF(LEN( Title  ) = 0, 0,10)
     + IF(LEN( City ) = 0, 0,10)
     + IF(LEN( Country ) = 0, 0,10)
     + IF(ISPICKVAL( Industry,""), 0,10)

What is this formula doing?

The LEN function returns the number of characters in a specified text string. In layman’s terms, it’s counting the characters in our field, so if First Name is empty then the number of characters will be 0.

Our formula then says, if the number of characters is 0 then add 0, otherwise add 15.

4. Make the field available to all relevant profiles and I recommend adding it to the page layout
5. Next create another new field, this time to display a star rating
   • Data Type = Formula
   • Field Label = Lead Quality
   • Formula Return Type = Text
   •  Treat blank fields as blanks
   • Enter a description and help text
   • Enter the following formula but use ‘Insert’ to ensure you use the correct API name:

     IF(Data_Quality_Score__c >= 90, IMAGE("/img/samples/stars_500.gif", '5 star'),
     IF(Data_Quality_Score__c >= 75, IMAGE("/img/samples/stars_400.gif", '4 star'),
     IF(Data_Quality_Score__c >= 55, IMAGE("/img/samples/stars_300.gif", '3 star'),
     IF(Data_Quality_Score__c >= 35, IMAGE("/img/samples/stars_200.gif", '2 star'),
     IF(Data_Quality_Score__c >= 15, IMAGE("/img/samples/stars_100.gif", '1 star'),
     IMAGE("/img/samples/stars_000.gif",'0 stars')
     )))))

What is this formula doing?

Our formula is checking the data quality score and then, depending on that score, allocating a number of stars.

The first line of the formula says: if the Data Quality Score is equal to or greater than 90, display the 5 star image. The wording at the end of the line e.g. ‘5 star’, is what the system will display if for some reason it cannot display the image.

Our formula then checks the other criteria e.g. equal to or greater than 75, equal to or greater than 55 and so on.

The final part of our formula is the ‘else’ statement. This means that if our score does not fall into one of our set categories (in this example if the score is less than 15), display the 0 star image.

6. Make the field available to all relevant profiles and add to the page layout

---

The result:

You can add the new lead quality fields to lead records, list views and reports:

And easily identify leads with good or bad data quality:

Good 👍

Bad 👎

---

Gotchas & hints:

*You don’t have to use the same fields as me! Choose fields that are most important to your business.

*Don’t include fields that are always required/required on the page layout/required by validation rules! If you have different page layouts/record types you may need a more complex calculation to determine the data quality score based on record type.

*Your data quality score should add up to 100. You could change this to add up to 5 or 500 but my point is be sure you know what it adds up to and change the image formula accordingly.

*You could use ISBLANK instead of LEN in the data quality score formula.

*Make sure your image formula is structured with highest score to lowest. More on why in a future post!

*If you have bad data, as well as identifying it, start preventing it. I’m talking about required fields on the page layout, validation rules and more. Otherwise bad data might flow in faster than you can fix it…

---

Useful links:

Salesforce Help: Sample Image Link Formulas

Shell Black: Using Image Formula Fields

Shell Black: Create a Data Quality Lead Score Using Formulas

AppExchange: Lead Scoring by Salesforce Labs

AppExchange: Data Quality Analysis Dashboards by Salesforce Labs

Trailhead: Data Quality

---

Resources:

Data Quality Formula:

IF(LEN( FirstName ) = 0, 0,15) 
+ IF(LEN( Company ) = 0, 0,15) 
+ IF(LEN( MobilePhone ) = 0, 0,15) 
+ IF(LEN( Email ) = 0, 0,15)
+ IF(LEN( Title  ) = 0, 0,10)
+ IF(LEN( City ) = 0, 0,10)
+ IF(LEN( Country ) = 0, 0,10)
+ IF(ISPICKVAL( Industry,""), 0,10)

Star Image Formula:

IF(Data_Quality_Score__c >= 90, IMAGE("/img/samples/stars_500.gif", '5 star'),
IF(Data_Quality_Score__c >= 75, IMAGE("/img/samples/stars_400.gif", '4 star'),
IF(Data_Quality_Score__c >= 55, IMAGE("/img/samples/stars_300.gif", '3 star'),
IF(Data_Quality_Score__c >= 35, IMAGE("/img/samples/stars_200.gif", '2 star'),
IF(Data_Quality_Score__c >= 15, IMAGE("/img/samples/stars_100.gif", '1 star'),
IMAGE("/img/samples/stars_000.gif",'0 stars')
)))))

---