by David Taber

13 tips for managing the data in

Jan 11, 2017
CRM SystemsDatabase AdministrationEnterprise Applications

When family is visiting for the holidays, you need to have a credible u2018urgent projectu2019 to keep you in blissful solitude. Hereu2019s what I did over my Christmas vacation. is increasingly used as the system of record for many aspects of the customer relationship.  While this falls under the category of “duh,” too often the data in the system has not been managed as a strategic asset.  With every change of leadership in sales, marketing or customer support, there is typically a blurring of semantics.  Consequently, the meaning of a “contacted lead” or a “stale opportunity” may depend on when it was entered. So reports trying to compare performance over time can be meaningless, or even outright lies.

Last month, we recommended an 11-point audit for the metadata in a (SFDC) system.  Now it’s time to look at the data, to identify which parts of the system are going to contain junk instead of information.

Here are some quick metrics we use when first evaluating an SFDC database for its completeness, quality and fidelity.  It’s not that any one of these by itself is an explosion ready to happen, but each contributes to technical debt and the eventual budgetary and data corruption surprises.  So here, in no particular order, are some rules of thumb we look out for in an SFDC instance and why they matter:

  • No more than 5 percent of any object’s records are owned by an inactive owner.  In SFDC, a record can have only one true owner and if it’s the wrong person, the right person may not be able to see or edit it.  Further, records owned by inactive users can throw all kinds of fun data permission errors.
  • At least 90 percent of Leads and Contacts that are flagged as “valid” have some contact information that looks valid.  In most cases, this will just be email or phone, but you should include in the calculation the people who just have a snail-mail address or LinkedIn/Facebook ID. 
  • At least 98 percent of “valid” contacts should have the Account field filled in.  Why?  Because without an Account specified, the system categorizes the Contacts as “private” and nobody besides the original creator can even see them.  This causes code and user-experience errors that can be so easily avoided.  Create an Account by the name of “### UNKNOWN – DO NOT DELETE ###” and tag all the private contacts in the system with that account name.  Now that these contacts are visible, the users can do the data cleanup in due time.
  • All objects that declare record types should have zero records without the record type populated.  This situation occurs when record types are enabled after the system was deployed, so historical data has the newly-required record type blank.  This causes lots of loopholes and trapdoors for workflows, validation rules and code…and unfortunately, the system UI doesn’t enforce population of the record type on record edits.   So, just clean this up with administrative tools.
  • Fewer than 10 percent of Opportunities and Cases are overdue.  While this really should be 0 percent, you can’t expect perfection at all times.  That said, overdue (past the close-by or resolve-by SLA date) records are important indicators of process and people problems, so you want to make sure the data in these areas is clean.  Typically, you’ll want to create a new status picklist value (along the lines of “closed—timeout”) and set all the overdue records to that value.  Double-check that your sales and support business processes aren’t using “overdue” in a meaningful way.  Rare, but it can happen.
  • Fewer than 5 percent of Opportunities and 10 percent of Cases are “insta-close” (created and moved to closed status on the same day).  These records indicate that some of the users are not using Opportunities correctly (or are gaming the system), or that they are prematurely closing Cases that are not in fact resolved.
  • Fewer than 5 percent of Tasks/Events/Activities have been “open” more than a year.  There’s nothing wrong with using Tasks to remind users of action items, but if there are a ton of them that are long overdue, any reports or alerts based on these records become a source of noise.
  • Fewer than 10 percent of closed-won Opportunities have no associated Activity records.  This is a clear indication of information-hiding by the sales rep and is an abuse of the system.
  • Fewer than 25 percent of all Opportunities have no updates (i.e., the Create time stamp is the same as the Updated/Modified time stamp).  Ditto the information hiding and abuse observation above.
  • At least 90 percent of your active users logged in during the last month.
  • At least 75 percent of your active users created or updated a record during the last month.
  • No more than six active users have full administrative privileges in the system.  This is a “data issue” that points to severe metadata and system governance problems.  Use delegated authority and new profiles to trim the powers of administrative users, particularly those who do not work for IT.
  • The overall system has at least 10 percent spare capacity for data and files.  This can become a real challenge if you have integrated a marketing automation system into SFDC, as the emails and event records that can be automatically generated can become truly prodigious. 

Your mileage may vary

Depending on your company’s target market, sales model and sales cycle, there are a dozen other metrics to examine, but there’s no fixed “allowable percentage.”  Compare closed-lost, closed-won and still-open deals for deal size, number of days in the pipeline, number of days in current stage, and number of deals with $0 value or no products defined. 

Look carefully at any deal that represents more than 5 percent of the company’s total sales for the year. If you’re using forecasting, examine the forecast table to evaluate participation rate, revisions and accuracy trends.  Evaluate your Case pipeline by “support level” to identify bottlenecks and clumsy hand-offs.