Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

How CRM data updates lead to data corruption

David Taber | April 24, 2012
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 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.

So our recommendation is that you purposefully create a duplicate record, with pointers to the record you believe is already there. When the record is created, an alert should be sent to your sales operations or accounting department for subsequent evaluation and data reconciliation. Typically, the new record will be made a child of your existing record in a master-detail relationship, so that the outside system can continue to update the "copy" of the data that it likes&and your accounting system can get the roll-up data it needs to balance the books.

 

1  2  Next Page 

Sign up for Computerworld eNewsletters.