By John J. Coffey, C.P.A. and Gene Palm, www.profitres.com

Data Integration – Easier Said Than Done

Nowadays, with all of the advanced technology available at your fingertips, you would think that it would be easy to integrate data from one system into another, right?  Well, yes and no!  A serious problem arises not from the mere transfer of data from one system to another, but from the fact that this data is used very differently in each system.  Sometimes the data in these seemingly interconnected systems is used in such different ways that the results of using the same data (which should be identical) are radically different!  This can cause the credibility of each system to become suspect.

Traditional Approaches For Integrating Profit Data Into A CRM

This use of the same data in different ways is a real problem when profitability data is integrated into a CRM system.  Traditionally, there have been two basic approaches to integrate profitability into a CRM.  The first and most common approach is to use modeling outside the CRM that would allow the bank to reconcile the resulting CRM-profitability to its financial statements.  The modeling would typically generate a table of assumptions for groups of products.  These assumptions would then be loaded into the CRM by file import or manually entering the data.  In addition, the CRM’s products would then be mapped to these assumptions, which the CRM would use to compute profitability at the account level.  In total, both the modeling and the CRM appear to be tightly integrated.

However, problems arise from the fact that this does not reflect the profitability of individual accounts within the CRM, nor all of the activities for those accounts!  To do this in a meaningful way, you need to integrate the bank’s transaction data into the profitability equation.

That said, we’ve seen only a few CRM installations that successfully integrated transaction data from the bank’s core processing system.  That’s because it is difficult to get usable transaction data from the core; and, the resulting CRM-profitability becomes distorted when variable income and variable expenses from the transaction data are added into the system.  This requires complex reconciliation efforts to make the CRM-profitability usable.  As wonderful as a CRM is, it is not a comprehensive profitability system, nor should it be forced to operate like one.

Instead of trying to incorporate transaction profitability within the CRM itself, banks have instead utilized complex, time consuming, and expensive, cost accounting systems to integrate general ledger, funds transfer pricing and transaction data into the profitability equation.  The cost accounting system would then generate an export file, containing the account number and the profit for that account, for use within the CRM.  As a result, the cost accounting system and CRM appear to be tightly integrated.

However, this second approach also presents problems!  That’s because CRMs are relational databases and rely on the interaction of tables to generate useful data at a variety of levels.  If you were to use an account-level “profit append” approach, you would need to be aware of two things:

1.       The profit data in this approach is static and will not change until you import another account-level profit file.  This is fine as long as the data loaded from the core processing system and from the profit append are in sync.  However, that’s not always the case.

2.       You may lose some valuable functionality within your CRM using this approach.  Typically, the “What-If” capabilities and dynamic views of the profitability components of account or household profitability utilize data within the CRM’s profitability definition table.  This table will not be populated if you use a profit append approach and so these functions would be suppressed.

A Better Approach

We think that a better solution is for the bank to have two systems:  a profitability system and a CRM system.  The profitability system would use the bank’s financial statements, detailed general ledger and branch information, product data from the core processing system, as well as monthly transaction files; and, a comprehensive database of alternative sources and uses of funds to analyze profitability at a number of levels.  It could then export a more-detailed extract file, for use in the CRM, including things like, account number, account-level funds transfer pricing, per-account fees and costs, transaction fees and costs, provision expense; and, computed profit.  As a final quality control step, you would need to ensure that the CRM could utilize this data as well as it could utilize the profitability definition table without losing any functionality.

It’s not often you can have your cake and eat it too, but we believe that this new approach will enable you to do just that!

John J. Coffey, C.P.A. and Gene Palm are the principals of Profit Resources, a consulting company that specializes in MCIF technologies.  © Profit Resources, Inc. 2006