data hygiene

Managing data hygiene is a constant challenge for any admin, and everyone else in the company who interacts with the data in

I also believe that managing data hygiene is an imperative for any organization that invests in a CRM system, whether, or anything else. As the saying goes – garbage in, garbage out. If you are not managing data hygiene proactively that basically means you are throwing away all of the time and money you are investing into your CRM system, and if you actually do the math, it’s not cheap. With poor data hygiene, basic activities such as territory alignment, lead assignment, or even simply getting in contact with customers become a time-consuming challenge.

On the other hand, companies that manage data hygiene proactively gain tremendous value out of the data in their CRM system. Companies with good data hygiene can make good business decisions based on accurate data, and can actually reach the customers and prospects they already spent good money acquiring in the first place.

There are a wide range of causes of poor data hygiene in any database, and in fact the very term “data hygiene” is somewhat of an umbrella term that covers duplicate data, incorrect data, incorrect format (ex: US vs. USA), and incomplete data. Likewise, the remedies for poor data hygiene can be equally varied and are dependent on the cause.

In order to help diagnose, determine the scope of, and proactively manage your data hygiene problems, I recommend starting off by building a data hygiene dashboard. A data hygiene dashboard is a dashboard specifically designed to help you proactively manage data integrity on key fields that are critical for to key business activities such as the ones I mentioned above.

Here are a couple of examples of what a data hygiene dashboard setup to track completeness on key address and contact info fields might look like:

Data Hygiene Dashboard

I like this version because you can set the maximum value on the gauge to the total number of records on the table, and the gauge will show you the number of records missing the key info, and the percentage of total records. You can also set a data integrity goal using the colored thresholds. I recommend setting Green to at least 90%.

Data Hygiene Dashboard V2

I like this version, because it is a bit cleaner and easier to digest, but it’s the same data. (Tip: you can also set color thresholds for this version as well, but it won’t show you the percentages.)

Running the Reports

Running the reports for these dashboards is pretty simple. You just have to set up the filters to show all records with missing or incorrect data. If you are just looking for missing data the filter would be [Field Name] [equals] “[blank]”. When you run these reports remember to filter out the records whose integrity you aren’t worried about. For example, Junk Leads are filtered out in the example below using the Lead Status [not equal to] “Do not contact”. Here is what most of them will look like:

For reporting on records with no state or zip code, remember to exclude countries outside of the US and Canada:

Once your data hygiene dashboard is set up for your organization, you can schedule a monthly dashboard snap shot. Then make it a goal to improve your data hygiene on a monthly or quarterly basis until you reach an acceptable level by your organization’s standards. You can experiment with different approaches to accomplish your goal such as data append applications, outsourced data cleansing, validation rules, and increased security on users to prevent them from adding new incomplete records.

photo by: Nick Harris1

Sign-up for our Free Weekly Newsletter to get the best new ideas for building technology companies.

Read more: