In the spirit of David Letterman's occasional feature "Stupid People Tricks," I thought it was time to summarize common errors that can lead to corrupted CRM records, or worse. How much worse? Read on.
Most of the opportunities for corruption come when doing data imports, data cleanup, and other mass update operations. Many of them come from using standard external tools like Excel, others come from interactions with ETL, data cleansing, and mass-update products.
So here's David Taber-man's Top 10 List of Stupid Ways to Corrupt Your CRM Data...
Number 10: Open a CSV file directly into Excel The geniuses in Redmond made Excel so easy to use, it automagically corrupts phone numbers and other large integers (turning them into scientific notation) when you open CSV files by double-clicking on them.
Number 9: Copy formulas that use relative coordinates There are several variants of this problem, but they all lead to insidious miscalculations, particularly if your formulas include error-handling. A variant of this comes when you're intending to copy cell contents all the way to the end of a row set, but the copy-down operation stops at an empty cell that you didn't notice.
Number 8: Accidentally use VLOOKUP's fuzzy match Excel's VLOOKUP function is really useful, but too often people don't read the fine print about its behavior. Nasty corruptions occur when formulas don't include the word "false" as the fourth VLOOKUP parameter.
Number 7: Put values in fields that are supposed to be pointers or references In most CRM systems, and in most object databases, many of the data items are actually references to another table's entries (e.g., "account name" in the "contact" record). While the system will immediately complain if you put long strings in those pointer fields, it may not if you put in a short string (like "Acme") where the pointer is supposed to go. Once you've saved this record, it can be tough to identify the references that point to nowhere.
Number 6: Miss the data type In strongly typed systems, it's impossible to confuse a date or a date time with an integer. But too often when working with tools outside of systems, dates can be misrepresented as integers, long integers as short floats, and similar data errors. This can cause some truly amusing messes.
Number 5: Use a deduping tool with "loose" criteria first In most CRM systems, merging duplicate records involves some complex internal operations. Consequently, merges cannot be undone. If you use a deduping tool and start with the loosest criteria first, the false-positives can easily be 50 percent of the result set...and reversing the merges is a complete nightmare.
Number 4: Sort a spreadsheet, but not include all the columns Ease of use hell, revisited. Excel's "select all" only select contiguous columns, so if you have empty columns some of your data won't be included in "all." Sort the spreadsheet, and you've corrupted every record there! Yum.
Number 3: Start working on the database without doing a full backup first This is like telling the electrician to turn the circuit breaker off - but in a rush, this mistake happens. Even if your CRM vendor has continuous backup for free, that doesn't mean data restores will be gratis. There's a local variant for this one: not remembering to do a checkpoint save (to a versioned file name) from whatever tool you're using every hour or so.
Number 2: "What system am I logged into?" For integrators who have to work across several systems in a day, this one has truly horrific implications. Update 5000 rows, but do it in production rather than the sandbox system. Or worse, make changes in client B's system that were supposed to happen in client A's. This is one of the best ways to measure the ohnosecond.