How CRM Data Updates Lead to Data Corruption
They say the road to hell is paved with good intentions. One of those good intentions is 'always keeping your data updated.'
Mon, April 23, 2012
CIO — In theory, it's best to clear all your database transactions as soon as you can, keeping the tables up to date so that you don't have to worry about data integrity or time-smear problems. It's not always that simple.
In the real world of cloud systems, you may have dozens of loosely coupled databases. The updates typically happen in a few seconds, but under certain conditions--such as weekend system refreshes or quarter-end reporting--transactions may be held up for an hour or even longer. That's the way it's supposed to work.
So in loosely coupled cloud systems, the first thing to do is make sure that any transactions have time stamps on all end-point systems, and make sure that your business logic understands what to do (or how to reconcile things) in case you have a "new" update that is actually several hours late.
OK, that's fine for the transactional tables (such as deals, payments, or transfers). But what about the core of the CRM system, which typically doesn't have much accounting content?
CRM systems are different from other enterprise software. The standards of data quality are different, both because of the data input sources (your customers or your sales reps) and the frequency of data update (some individual records may be hit several times in a day).
The highest-risk tables in a CRM are the Opportunity (particularly if its stage moves to "closed-won") and the Account (which is typically at the top of the information pyramid). So updates to these tables should be very carefully controlled, both with access control systems ("a sales rep may not modify these fields when this record is in a particular state") and with validation rules or coded mechanisms that limit the conditions under which data may be changed.
These controls need to be counter-balanced, however, with the need to respond to external systems that need to clear their transactions. For example, complex sales channels may have a need to create a "new" account that in fact already exists in your CRM system. The external system may show the account with a different name (formal legal entity versus "street name"), a different city, or other key information that is different from what you already have.
The same issue applies to an Opportunity that you might (or might not) already have in the system. At the time that the transaction comes in, there may be no way to know which version of the data is better--and sometimes both versions might need to be maintained for later reconciliation.