So Fresh and So Clean: Tools for Maintaining Data Quality in Salesforce

Any Salesforce administrator or data analyst can tell you getting clean useable data out of a CRM is a consistent but critical struggle. Data is usually (though not always) entered by humans and sadly, few humans are perfect. They make typos, recall things incorrectly, or occasionally just get overwhelmed and give up on entering data. Yet, clean data is increasingly vital to the health of an organization. Clean data feeds into trustworthy reports for stakeholders, supports well-informed planning, and makes business process automation possible. Fortunately, Salesforce has a broad array of tools that can help to minimize threats to clean data. In this post I will share some lessons I’ve learned as a Salesforce administrator about keeping data squeaky clean no matter what challenges arise.

What is Clean Data?

There are a number of criteria by which one can define clean data, but after seven years of implementing CRMs, I have settled on four which are consistently both salient and actionable.

• Complete - Clean data is complete, meaning that it has been entered in all places it is supposed to be.
• Timely - Clean data is up to date, meaning it has been reviewed and updated in a reasonable time frame. Timely data reflects the most recent interaction with a given customer/client/constituent.
• Consistent - Clean data is consistently understood, used and entered. All people collecting and entering data have the same understanding of what the data means and how it is supposed to be entered.
• Accurate - Clean data is accurate and free of errors. 

Data that is complete, timely, consistent, and accurate can support reporting, automation, and planning to sustain a robust organization.

Principles for Ensuring Data Quality

Before delving into the tools that can be used in Salesforce to ensure that data is clean, it is important to understand a few principals that can guide their use. This is not meant to be an exhaustive list, but rather represent a few guidelines I have found useful when designing and configuring in Salesforce with an eye toward data cleanliness.

• Minimize Redundant Data Entry - Having to enter data multiple times opens the door for human error (e.g., remembering different values at different times) and can cause users to become frustrated at having to do something more than once thus hurting data completeness and accuracy.
• Limit Data Entry Options - Limiting what a user can enter limits the space for human error and ensures that their energy is focused on entering what needs to be completed. Whenever possible, only put the fields in front of a user that they need in order to complete the task at hand. Within those fields make sure that the user can only enter values that fit within the realm of the possible.
• Enable Efficient Auditing - Having an AI scan and correct data is not an option for most organizations (yet), so human auditing is still often necessary. Make auditing more efficient by identifying records that need cleaning. Enable the auditor to give their time and attention to records that need it rather than wading through every record in your system.
• Let Salesforce Do the Remembering  - According to productivity guru David Allen, “human brains are for having ideas not holding them”. Let Salesforce do the work of remembering data entry tasks and reminding users to do them so users can focus on entering complete and accurate data.

Salesforce Tools for Cleaning Data and Keeping it Clean

With the definitions and principles above, let’s take a look at some Salesforce features that can be used to ensure and maintain data cleanliness. Generally, these features fall into one of three categories; User Interface (UI), Automation, and Reporting.

User Interface Tools

The first and easiest way to maintain clean data is to design record pages with a clean and intuitive user interface that makes data entry easy and presents few opportunities for human error. 

First and foremost, make sure that fields are formatted appropriately. You can set lengths for text and numeric fields that reflect reasonable values (e.g., ages with over 4 digits are pretty unlikely). Also, if a question has a truly limited number of possible responses, use a picklist, so users are limited in their ability to enter data outside that defined range.  Where you institute picklists with “Other” values, review what those “Other” responses are periodically. If you find frequently repeated “Other:” values add those to the picklist. A great way to extend the functionality of picklists is picklist dependencies which can filter available picklist values based on responses to a controlling field on the same record. For instance, if I am documenting a referral and I select that it is to a “Legal Services” organization and I have a further “Referral Service” field, a picklist dependency can then filter that “Referral Service” field to only show legal services (e.g., pro-bono lawyer referrals, benefits advocacy, etc) rather than all possible services. Finally, It is also important to consider requiring fields that should always be entered. This can promote data completeness and cut down on human error.  That said, use this feature with caution and only for data you know users will have available when they are creating a new record. If not used thoughtfully, too many required fields can frustrate users and actually hamper data completeness.

Above and beyond designing a clean record page, two additional user interface features provide some more advanced functionality for ensuring data quality. Quick Actions make it easy to create/update new records with needed data. Creating and/or updating a new record can be overwhelming if it contains a lot of fields. Hunting for the right fields on a crowded page can lead to errors and inaction from a user hurting all aspects of data cleanliness. Quick Actions display only needed fields allowing a user to quickly create a new record. Intake/Onboarding Lightning Pages follow a similar principle but allow for updating multiple records at one time. Onboarding/intaking a new client/customer/constituent can be quite laborious, especially if that process requires entering fields spread over multiple objects. By relating various objects to an Intake record, you can use a lightning record page to surface them all in one place. For anyone using Salesforce’s Non-Profit Cloud Case Management Module managed package, this feature is standard, otherwise, it’s relatively easy to create custom with the Lightning App Builder.

Automation Tools

Salesforce’s automation tools can extend and enhance a user interface that has been designed with clean data principals in mind. Validation Rules, for example, can be used to enforce field formats and completeness in a more nuanced way than is possible with basic UI design. With Validation Rules you can conditionally require fields or limit valid values based on complex logic (e.g., requiring certain fields only when a record has reached a certain stage, status, or age). Validation Rules can also enforce complex field format requirements like phone numbers, or nine digit zip codes. Formulas are another tool that can extend field-level design for data cleanliness. Adhering to the principle of minimizing redundant data entry, Formulas make it possible to fill a field automatically from a related field. For instance, an organization might collect monthly income, annual income, and income as % of federal poverty guidelines. Two Formulas referencing that “Monthly Income” field would allow a user to enter the monthly income and then automatically calculate the remaining two fields based value thus eliminating the need for users to do math in their heads and potentially make mistakes. 

Salesforce automation tools really shine when it comes to enabling data auditing and reminding users to enter data, thus enhancing data’s completeness and timeliness. What constitutes a “clean” record in some cases can either be subjective or change frequently to reflect changing business conditions so formulating business logic to enforce data cleanliness may not always be practical. For instance, check request records may need to be coded to particular grants and accounts such that those grants and accounts get spent down to zero, but which grants are active, the amount of money left in any given account, and program spending priorities are likely to be in constant flux so setting validation rules for this kind of task is difficult. A human reviewer with the appropriate background knowledge, however, can quickly look at a record like this and gauge whether it has been entered appropriately. Here’s where Approval Processes can help by submitting new records for review and approval by an authorized user in Salesforce before that record gets finalized. Tasks and E-mail Alerts are a great way to remind users about specific data entry tasks. Tasks can be created automatically for new records reminding users to collect specific data (e.g., getting a release of information signed after an intake is complete). E-mail Alerts, on the other hand, are great for reminding users about tasks that are both time and condition dependent. For instance, each client at an organization may need a service plan, but these require some dialogue with a client and therefore can’t get set up right away. An Email Alert can be set to go to a case manager 30 days after enrollment if there is no service plan in place. Flows can be used to tie all of these features together by triggering them automatically based on timing, record conditions, or both. Finally, it is important to keep in mind that there are a wealth of AppExchange products available that can streamline and extend data cleaning functionality even further.

Reporting Tools

Unfortunately, clean UI design and thoughtful implementation of various automation tools can only go so far. None will ever completely remove the possibility of human error. Fortunately, Salesforce’s Reports and Dashboards can make robust regular data auditing simple and relatively painless. Thanks to reporting improvements in recent Salesforce releases, you can now update most field types directly from a report without having to open individual records. There are a few ways to identify records for auditing. First, set reports to filter for fields with missing values to identify and update missing data. Filter on Last Modified Date, Created Date, and Date Roll-up Summary fields, to identify records that are getting stale and require updating. Additionally, find places where you can filter on unlikely values or “outliers” to identify potential errors (for instance ages over 120 or incomes in the billions of dollars). Finally, use Dashboards to group multiple cleaning reports together for a streamlined “one-stop” data auditing experience.

Considerations Outside of Salesforce

As indicated in a few places above, there are a number of places in the process of maintaining clean data where the human element must be considered. Therefore, it is not only important to consider the toolkit available in Salesforce for maintaining clean data, but also the people and processes that interact with Salesforce. One practice that has proven particularly valuable to me has been to encourage different teams and business units to designate their own “Data Stewards”. Most teams have at least one person who is particularly detail-oriented or has a particular stake in keeping data clean. Leveraging their time and energy can increase capacity for data auditing and help to identify unanticipated data quality issues. Administrative processes also make a big difference when it comes to keeping data clean, so it is critical to put any human data auditing that must be done on a schedule. Whether it is annual, quarterly, or monthly, working data auditing into the regular rhythm of a team’s work ensures that it happens in a consistent and timely fashion. Finally, it can be helpful to step back, take a bird’s eye view of your Salesforce org and think about the extent to which it is meeting everyone’s needs. All aspects of data quality can be enhanced when all users see Salesforce as a tool for managing their own work. The more time users spend in Salesforce and the more they depend on it to manage themselves, the more comfortable they are with navigating data entry and the more of a stake they have in keeping data clean.

The tools and principles listed above are far from a comprehensive list and I am always open to learning more about keeping data clean. If you have tips, tricks, tools, and principles that you apply to keeping your data clean, I would love to hear about them. Find me on LinkedIn.