Too many years ago, when I was at Verizon, a key problem was lack of consistent customer data – including the basics such as name, address and other customer information.
Today’s blog explores some of the key customer data issues that persist in today’s IT systems… a sort of “Back to the Future.” The same techniques used 20 years ago are applicable – aided by the fact that commercial packages are available to assist in reporting and reconciliation.
I’ll approach this topic from the perspective of planning to implement a new customer relationship management (CRM) system and address the key topics, tools and techniques to prepare legacy data to be migrated to the new system.
WHAT IS DATA CLEANSING?
Data Cleansing is an organized approach to successfully migrate business data focused on achieving data integrity. Its benefits include increasing data quality. Focusing on data quality is intended to ensure that the new CRM system has clean and valid data. Though data quality at a company may be thought to be good, a formal approach should be used to address data integrity issues prior to migration into the CRM solution. Best practices include:
- Source to target mapping of existing data for CRM is usually performed for the design phase
- Prior to and during this data mapping exercise, data in your legacy repositories should be analyzed to identify potential occurrence of the following types of major data integrity issues:
- Orphaned/Missing Data – System and/or program errors, inadequate enforcement of Relational Integrity, data that may have been considered optional in legacy systems maybe mandatory for the new CRM.
- Invalid or Unclean data – Data entry errors, data that may have been entered in to a system prior to implementation of validation logic. Examples of these would be invalid addresses or potentially missing data.
Key Success Factor: A cleansing effort should be undertaken to get the data-to-be-converted as clean as possible prior to extraction from legacy repositories.
How to cleanse? This depends on the specific problems:
Orphaned / Missing data
- Custom programs responsible for extracting and transforming the data in to XML (for target application load), would identify these instances
- Additionally, conversion programs will generate error reports that would allow you to verify issues were resolved at the source
Invalid or Unclean data
- A variety of COTS tools that specialize in cleansing CRM related, custom programs and/or manual intervention are generally used to resolved this type of data integrity problems
- Other common integrity issues include: Incorrect , Obsolete, and Duplicate data
Typical Data Cleansing Activities to Prepare for Conversion:
- Data Discovery – Identification of all data elements within the enterprise data stores
- Data Profiling – Null & valid-values Identification, outlier analysis & other advanced profiling analyses
- Data Quality – Creating metrics which future data management policies will use to demonstrate improvement. I have often said ‘you get what you measure’… and this is true for data cleansing activities. The good news is that today’s tools provide various metrics that can be used for tracking and improvement
- Data Matching – Determination of matching values within columns of differing types, formats and naming convent
- Data Cleansing – Replacement of invalid values, deletion of nulls and bad records
Data Collection and Preparation for Loading:
- Data preparation activities are conducted to reduce the time and effort spent converting, and to ensure that the data that is transferred to the new CRM is current and meaningful to the business
- Data preparation requires assistance from various business organizations to identify and confirm the validity of any data to be migrated, and in approving data items (i.e. products) to be removed (or mapped differently)
- Data preparation will also involve resolving any data conflicts.
Data Auditing is required to perform the following tasks:
- Exception Testing, including unwanted data, duplicate data, or conflicting data
- Exception handling based upon the results from exception testing
- Field map validation
- Data validation reports
- Data comparison reports
So what are the systems needed to support your CRM conversion? The following architecture provides the ‘best practice’ view of solutions for complex conversions:
In the architecture above, the best practices include:
- Using an Enterprise Application Interface (EAI) bus to develop interfaces to extract data from legacy applications. Once these application interfaces are developed once, they can be used repeatedly. This is particularly important if your legacy system may run in parallel with your new CRM system for an extended period of time.
- Implementing a Master Data Management Model that clearly identifies systems that are primary owners of data elements as well as other systems that use the data.
- Using Extract, Transform and Load tools (ETL) to extract legacy data, store in a temporary data base where data cleansing is performed.
- After cleansing, legacy data is reloaded into legacy systems in order to improve the quality of source data.
- Steps 3 and 4 can be repeated multiple times, with quality metric reports (produced by tools) used to assess and validate that quality gates have been met.
- The same ETL tools used for cleansing data can be used in the ‘final’ extract, transform and load into the new CRM target system.
A word about tools (okay, actually several words):
Todays’ Information Data Quality tools can provide business-focused metrics that are simple and easy to disseminate to your company. For example, customer address quality has historically been a problem area. The root cause of problems is usually systems with “free form fields” where customer service representatives enter address without validation. The impact on your business is huge: mailed invoices delayed, lost, or returned—resulting in lower cash flow.
Sample Tool Output
Below are two standard reports from a commercial tool that analyzed street addresses for 3.5 million customer records.
In this case, about 8% of the street addresses were in categories 0, 1 and 2 – which indicated that 8% of this company’s customer street address information was of poor quality.
The tool can be run again after performing a number of analyses and algorithms – including street address matching against a commercial street address database (usually accessed from government postal databases or commercial delivery service sources).
Use these tools as communications vehicles to every part of your organization… to indicate you have coupled a quality improvement strategy with a few metrics that define success. For example, you could target reducing the poor quality street addresses to below 3%.
Finally, no Data Quality Improvement should be begun without a Governance program. Where does your company place on the industry-accepted Data Governance Maturity Model described below?
At the lowest level of maturity, your IT team (with or without business involvement) has developed a “tactical” approach whereby the strategy and framework are defined.
In the next level of “operational” maturity, an organization (IT plus Business representatives) successfully implements scenarios & validation of the framework.
Finally, the “strategic” and highest level of maturity indicates your company focuses on maintaining original goals achieved and fosters a corporate culture to retain implemented best-practices.
Next Up: Unlocking the Power of Big Data to Become a Next-generation Service Provider
I hope you will join me and will pass on the link to your friends and networks. Please … subscribe, send me feedback, and check back next week for the next installment. If nothing else, I promise the International Travel tips will be extremely useful!
Today’s International Travel Tip: Must-Have Technology Packing Guide (Also Known as ‘Packing for a Potential Natural Disaster’)
I will not insult your intelligence about the basics, but rather provide some helpful hints that provide for your personal safety as well as business continuity. They are the result of my experience in surviving the Chilean mega-earthquake in February, 2010 (see one of my prior blogs for more info).
- Spare batteries for your cell phone and computer (and keep them charged)
- Spare power cord to charge your wireless phone
- Small Flashlight, spare batteries
- Did I mention spare batteries?
- Laptop with Microsoft Communicator with voice calling
- Laptop with Skype for voice and/or video calling (due to some company’s corporate policies that forbid Skype on corporate laptops, some travelers carry two devices – a corporate laptop and a device with Skype)
- Copies (hard copies as well as scanned) of your passport
- Emergency travel contacts. Most companies have contracts with ‘travel assist’ vendors – not just travel agents, but companies that will arrange for quick extractions from countries if circumstances warrant.
- Emergency medical assistance contacts. Beyond the company-provided medical contacts, always obtain names and phones of at least 2 local doctors for fast emergency assistance.
One final note: while I won’t get into the merits of an iPhone vs. a Blackberry, I will point out one key difference (that is not widely advertised) that can make your life miserable in some countries. Blackberry smartphones have always had the option to let you manually select networks in foreign countries. When you select ‘manual networks’ – a list of available networks is displayed and you can select the appropriate network you prefer. This is extremely useful as you (like me) typically have a good understanding of the best local carriers in the countries where we travel.
On the other hand, an iPhone does NOT have this capability – it will automatically select a carrier in a country. This limitation has ramifications for countries such as Brazil and Chile – where the iPhone selects a carrier for good voice capabilities – yet that carrier’s data capabilities may be (based on experience) poor or ineffective. One of my team member spent hours on the phone with Verizon – and was told there was no way around this iPhone limitation. Buyer beware!