Our data isn’t very good, people don’t trust it.
I’m always cleaning data. When will it end?
Why do organizations get to the point where they don’t trust or can’t act on their data? Often the reason is poor data quality and integrity. How does that happen? There are a lot of reasons but the primary reason is that data integrity requires attention. Without attention, it falters. It’s not a “once and done” effort and everyone has a role in it. However, there are actions you, the Salesforce administrator, can take that will help improve your data integrity. Ultimately it is your responsibility to drive this effort and do what you can to make it easier for your users to comply.
Here are a few tips and tricks for maintaining data integrity and data quality that I have learned both as an administrator and a consultant.
Validation rules are a simple way to make sure that your data meets certain standards before a record is saved. Examples of data validation are verifying that the close date when posting a donation is this month, or validating that there is a value in the amount field when the donation stage is set to Closed/Won.
There are a few cautions with validation rules.
Salesforce runs field validation rules BEFORE creating records submitted via Web-to-Lead and only creates records that have valid values. So, let’s say you have a web-to-lead form that requires only email address and you have a validation rule on Leads in Salesforce that requires first name, last name and email address. If someone interested in your organization only provides an email address on the web form, the lead will not be created in Salesforce. The validation rules requiring first and last name will prevent the lead from being created.
Validation rules are executed before workflow rules. Therefore, when you have a workflow rule that updates a record, the validation rules do NOT get triggered by the update. For example, let’s say any contact records with Joe as the owner must be in the Northwest region – no exception. A validation rule requires region to be Northwest if the Joe is the contact owner. Now let’s say you also have a workflow rule that will set the region to Southwest if the state is Arizona. A contact record is created with Joe as the owner, Northwest as the region, and the state of AZ. It passes the validation rule because it is in Northwest and Joe is the owner. Now the workflow rules execute. Your workflow rule will change the region to Southwest because the state is AZ. You now have a record with Joe as the owner but in the Southwest region. You also have a workflow rule that will set region to Southwest if the state is AZ.
Using picklists (pre-defined drop down lists) can help you keep your data clean. For example, one organization had a field that identified how the person heard about the organization. It was just a text field. It was fine when one person did all the data entry because he consistently entered the data the same way. Now, they have more people entering the data and no clearly defined rules. A source of website was entered as website, web-site, web, and internet. A picklist of pre-defined values could have avoided the difference in data entry.
Another favorite is field dependencies. This helps to keep data clean because it gives you only the choices that are appropriate based on another field. For example, if you have ten programs but only a selection of them are offered in specific cities, you can have a field dependency between city and program. When the city is selected, only the programs available in that city are available for choosing. This helps reduce the ‘noise’ of a long list of choices and avoids incorrectly entering a program that is not available in a specific city.
This is an interesting one. If a field is required, then well, it is required. Think carefully about how you use this. In some cases it may make sense, but in other cases, it could actually prevent you from entering good data. For example, if your organization has decided that they want to increase their email communication and therefore decide that email is required, then what do you do when a person doesn’t have an email address (yes there are a few people that don’t) or, more likely, that the person doesn’t want to give you their email address? You still want to enter that person in your system. I would not make email a required field in this case, but instead use the next tip – exception reports – to help identify where you are missing email addresses.
Exception reports show records that MAY have invalid data. They allow you to “catch” potential dirty data that can’t be prevented using the methods described. When I was a Salesforce administrator, I had quite a few exception reports for situations like the one above, where we wanted email address but did not want to require it. Exception reports were set up as scheduled reports to go to the contact owner to remind them of people who didn’t have email addresses. We could also use this report at the management level to see if there was a pattern in where we were not getting emails and determine if we wanted to take a different approach in that area.
Another example — we had a person who typically entered information for the Southwest (SW) region but occasionally she entered data for the Southeast (SE) region. Regions were a picklist but sometimes she would select the wrong one. A validation rule to make sure she chose SW would not be appropriate, as sometimes she did need to choose SE. We created an exception report that was scheduled and sent to her once a month before month-end to review those entries that she made using SE, to ensure that they really should be SE.
I had a pretty good user manual. New users looked at it in the beginning, but honestly once they became familiar with the system, they didn’t use it as often. Documentation is still important – it stays in your organization if you or another key staff member leave. Your documentation should (1) be focused on what you put here — keep it relevant and to-the-point. Include a chart of data entry standards. Also, a picture is worth a thousand words — use screenshots liberally (2) Keep it up-to-date as you make changes to your system. (3) Refer to it often so that others become familiar with it too.
You can never stop training your users. Train them as they start but also have periodic training sessions with them. You can conduct sessions in advanced topics, Q&A sessions, or just re-train them on what they learned before. Mixing it up with experienced staff and newer staff can be beneficial for all. New people will often ask “why” when the experienced people may have gotten used to doing something a specific way.
The exception reports mentioned above can also be used to help identify where you may have a training gap. In one case, one of our exception reports showed us clearly that one person was not entering data correctly. The exception report helped us catch it early and provide the training needed. Only a few records needed to be updated versus hundreds if we had not caught it and taken action when we did.
As a Salesforce Administrator (or any CRM administrator) you need to be diligent in ensuring you have data integrity. Using some of these tips and tools within Salesforce and staying on top of it is critical.
What tricks have you used to help maintain your data integrity?