“Measure what is measurable, make measurable what is not.” – Galileo
One of the very first custom functionality features that a Salesforce manager should implement into their instance is a couple of formulas used to measure data quality. These formulas are important in that they will allow a manager to get a high level overview of what the data quality looks like within their instance, and set baselines for improvement.
The key metrics that this post is covering are what are known as ‘Data Completeness’ scores. As completeness is subjective to the organization as well as to the type of records measured, this post will only provide a couple common examples.
An example for Account data would be to measure what the completeness is across all records for key attributes such as:
- Account Name
Using these attributes which would likely be important to any organization, we can develop a simple formula to calculate a score for the data completeness of each record. Once we have the formula in place we can then obtain a holistic view of all of our Account data, which we can roll-up into a summary report for a good measure of where we are, and how far from there we are.
Below is an example formula which gives a score of 20 for each of the key attributes on Accounts, if the record is complete it should have a total score of 100.
Account Data Completeness
IF(ISBLANK(Name),0,20) + IF(ISPICKVAL(Industry,””),0,20) + IF(ISBLANK(Website),0,20) + IF(ISBLANK(Phone),0,20) + IF(ISBLANK(ShippingAddress),0,20)
An example for Contact data would be to measure common key attributes such as:
- First Name
- Last Name
- Account Name
This formula is a little different in that it gives a score of 25 for the Last Name (a required field) and 15 points for each other attributes so that if each of the 6 attributes have a value, we get a satisfying score of 100.
Contact Data Completeness
IF(ISBLANK( FirstName ),0,15) + IF(ISBLANK(LastName),0,25) + IF(ISBLANK(Phone),0,15) + IF(ISBLANK(Email),0,15) + IF(ISBLANK(Title),0,15) + IF(ISBLANK(Account.Name),0,15)
The formulas themselves are pretty basic, if the attribute is empty it contributes 0 points to the overall score, but if it is not empty then we give it a score.
Without having the key attributes that are most important to your organization you are left with an incomplete picture. Putting a couple of formulas like this in place will allow your organization to measure the quality of the contributions made by team members, by departments, as well as by regions.
I would even advise scheduling a refresh of the report to team members listing those records which are incomplete as a follow up to ensure they are conscientious about the necessity to offer records which are whole.
This soft enforcement of data quality is definitely a nice alternative to making each attribute required.