Salesforce Data Quality Score Formula

During Administration Essentials (ADM-201) this week, a discussion started about data quality. A student in the class sent a direct mail piece to a large number of leads they purchased and stored in Salesforce. The mail house sent out the mail without doing any kind of address quality check and a very large number of mail pieces were returned as undeliverable. Unfortunately, neither the post office nor the mail house will refund your money. So the student asked if Salesforce could help. We had a brief discussion about some things Salesforce could do to help with the problem. Following class, the student sent me an email for clarification with their potential solution. Here is a snipped of the email:

A new field will need to be created on the Contact Object page called: Information Scorecard ####, which will provide a score between 1 and 10 (If ten fields were being counted for data). How does one count a blank field as = 1?

The new field would need to be a Formula based field on the object page..correct? The calculation would be: if Email field is Blank=1 + If FirstName is Blank =1…..n, which then provides a total in the Information Scorecard (Field).

Basically, the student is trying to create a data quality / data completeness score formula field. This topic is covered in the Advanced Administration (ADM-301) class and also in this Useful Sample Formulas document: http://www.salesforce.com/community/assets/docs/100_Sample_Formula_Fields_customization.PDF. I will cover the topic here as well.

The example formula below will create a simple formula to check data completeness on a Lead record. I created a formula field of type Percent and called it Lead Completeness. I set the decimal places to 0. This formula will give you a percent complete, 100% if the 5 fields have a value.

if(isblank(City),0,20) + if(isblank(State),0,20) + if(isblank(Email),0,20) + if(isblank(PostalCode), 0, 20) + if(isblank(Street),0,20)

This is an incredibly simple formula. In reality, you should probably be checking that the length of the postal code is at least 5, that the City is at least two, etc., etc. For writing a simple quality score, this is a good start.