Project cost overruns. Implementation delays. Processingproblems, such as missing billing statements, late commissionpayments, wrong policy information. These are just some of therisks insurers run when undertaking a data conversion project, andunfortunately, there are many ways data conversion can go awry.

“There's a lot of dirty work that needs to be involved in goinginto the data, cleaning it up, and doing the mapping between thecurrent and target format,” says Todd Eyler, vice president in theinsurance sector at Gartner. “Most of the [data conversion]problems and costs come in those areas.”

While most data conversion projects are driven by systemconversion projects–such as legacy system replacement orpost-acquisition system consolidation–there is a host of otherreasons insurers undertake data conversion. “As companies looktoward business intelligence tools and need to feed a datawarehouse more efficiently, they need cleaner data in standardformats,” Eyler adds. Complying with privacy requirements (such asconverting Social Security numbers to other identifiers), meetingformat requirements (such as for HIPAA EDI transactions),delivering data in different formats to different customer servicechannels, or even undertaking stand-alone architectural projectsall are initiatives involving data conversion that carry businessrisks.

“There also is indirect risk, such as lost-opportunity cost–theinability to link customers between systems and companies and crosssell,” says Stuart Selip, senior analyst at the Burton Group.

The good news is there are best practices insurers can employ tominimize the risks of conversion. From the experience of dataconversion experts and insurers that have navigated their ownconversion projects successfully, the following tips can help youachieve the results you're after in your next effort.

Treat the Project–and the People–as Mission Critical

“A lot of projects fail because the rationale isn't wellarticulated,” suggests Eyler. “Companies treat data conversion asdirty laundry rather than a business objective.”

In contrast, consider the philosophy of Northwestern Mutual. BobKowalsky, the insurer's vice president and chief architect,compares data architects to house foundation builders. “Even thoughthey might not get invited to the open-house party, without themthe home wouldn't be there,” he says. “We identified our dataconversion project as a strategic initiative.”

That project was an eight-year effort, beginning in 1997, toconvert data underlying the carrier's core contract administrationsystems for life insurance and disability income from Cincom'sSUPRA to IBM's DB2, running on a zSeries mainframe.

Kowalsky indicates, particularly in a multiyear project such asthis one, motivation is essential. “We focused on individualrecognition for accomplishments, and on an annual basis, we pulledeveryone together for a luncheon with the CIO,” he says. “Ourmanagers focused on team building, both between members of theNorthwestern Mutual data conversion team as well as the 'extended'team,” which included business and IT staff from impacted areas andfrom outsourcing partner InfoSys, which handled coding tasksassociated with the conversion project.

More commonly, data conversion is one part of a largerapplication project. But in those cases, as well, recognizing theimportance of the conversion component is essential to overallsuccess.

“[Data conversion] should not be a little project off to theside,” cautions Mark Christman, director of conversions for thelife and annuity division of CSC's Financial Services Group. “Youhave to assign the right number of staff members and make surethey're 100 percent dedicated to the project and also make subjectmatter experts available, both from an IT and businessperspective.”

Clean Up, Upfront

We all know trying to convert “dirty” data creates problems, yetit still is a mistake many companies make. “Companies do not doenough data conversion to understand the right approach, and as aresult, they discover how dirty their data is only when they arewell into testing–far too late to start cleaning up data,” observesJerry Kaul, chief marketing officer of Universal ConversionTechnologies (UCT). “When data cleanup becomes a critical path itemduring a data conversion, you have screwed up.”

In Manulife Financial's late 2003 acquisition of John Hancock, alarge focus of the insurer's IT staff was on data conversion issuesassociated with policy administration. However, many other areas ofthe company were faced with system consolidation issues and dataconversion challenges. For example, the human resources department,charged with bringing former John Hancock staff onto Manulife'spayroll, needed to move employee data off John Hancock's PeopleSoftplatform system to Manulife's internally developed Oracle-based HRsystem, called EPIC.

“Not being able to manage our company, manage our employees, andmaximize our return” was the risk of a failed data conversion,accord-ing to William Harmer III, assistant vice president ofcorporate human resources and distributed systems at Manulife. “Weneeded to be able to track employees and where they are both in thecompany and in the system and to make sure we were able to make theappropriate decisions with regard to restructuring the combinedorganization.”

Fortunately, Manulife had itself been a PeopleSoft customerprior to rolling out the EPIC system just months before the JohnHancock acquisition and therefore was familiar with both systems aswell as the conversion process. Manulife's data conversion issuesprimarily involved data quality. “There were discrepancies in jobcodes and salary grades as well as duplicate employee IDs andduplicate cost centers and descriptions [between the John Hancockand Manulife employee databases],” Harmer explains.

In identifying and solving data quality problems, data analysisand scrubbing tools can help with the job, but there's nosubstitute for a magnifying glass and a well-trained eye. “Findingfields with missing data or fields filled with all 9s are commonproblems, but someone needs to go in and do the analysis,” saysJohn L. Johnsen, senior manager with Deloitte Consulting.

After identifying suspect data and making repairs in the JohnHancock system, Manulife addressed many of the data redundancyissues by appending a source code to common fields between the twosystems to indicate the origin of a particular value. Rolling allJohn Hancock employees to the EPIC system took five months.

While it is most important to address quality issues before theconversion process even is begun, ensuring data quality needs to bean ongoing effort throughout the project. For instance, in 2004,Sammons Financial Group began installing CSC's CyberLifeadministration system, starting with a block of whole and universallife business it had acquired, which had been administered on ahomegrown legacy system. Sammons and CSC cleaned what data theycould identify as problematic before the conversion, then they didincremental testing to find additional problems.

“It was an iterative process of running a conversion to aneutral file, comparing the results, fixing things, and repeatingthat until we had pretty clean data going through the neutral file.Then, we repeated that process as we went from the neutral filethrough the new-business process CSC uses to convert policies toCyberLife,” says Jim McAdaragh, AVP of business systems for SammonsFinancial Group.

Assess and Address Knowledge Gaps

“My strong advice is to find someone who's done a conversionbefore, because to do one for the first time, you're going to makea lot of mistakes and the cost is going to skyrocket,” recommendsJohnsen.

Like Sammons, Great American Financial Resources, Inc. (GAFRI),was faced with the need to convert an acquired book of business:30,000 fixed annuity policies from a heavily modified ID3 system toPDMA's LifePRO, GAFRI's primary policy administration system. Theproblem, according to Dale Wilcox, GAFRI's vice president ofstrategic systems, was the company had no knowledge of the ID3system. It engaged Universal Conversion Technologies to take chargeof the data conversion aspect of the project.

“Our core competency is LifePRO, whereas one of the things UCTbrought to the table was its knowledge of different industryvendors,” Wilcox says. In addition to using UCT for consulting andproject management, GAFRI relied on UCT for data mapping and usedthe vendor's Data Conversion Architect (DCA) tool to perform thedata transformation process.

In choosing a data conversion consultant, it's advisable to usethe exercise as an opportunity for knowledge transfer for futureprojects. “The sooner you can get people trained and familiar withthe [data conversion] process, the better,” says McAdaragh. “Wethought [CSC] would do its work, drop us a gift-wrapped package onour desk, and we would move on. But we really needed to get ourpeople involved and to have them try things, have them see whatgoes wrong and how to fix it. In retrospect, we should have rampedup for that earlier.”

Also, particularly for insurers inexperienced in dataconversion, a trial run can be invaluable to help identifyknowledge and skill deficiencies as well as to select neededconversion tools. “If I want to get a handle on how hard it wouldbe to move data, I would use a low-cost [ETL] tool and simplyprototype grabbing data out of the source system and moving it to atarget database. What happened when I did that? What are theproblems with the data model? How well do I understand how the datais organized? You can get a fast read on how hard it is to convertsomething and whether the data is bad or not,” notes Selip.

Don't Overcomplicate

As in nearly any systems project, scope creep can plague a dataconversion effort. “When you get in and open up your data, thereare many other changes you are tempted to make along the way,”comments Kowalsky. “We wanted to hold the line but in a way thatwould recognize there were some improvements that made sense. Itwas a real balance.”

For example, Northwestern did take the time to build a dataaccess layer after converting the first database to make subsequentconversions run more smoothly. “We had technical access to the databefore, but we wanted to loosen the coupling between theapplication and the data even more. We built a services-orientedaccess layer so that the application is removed almost completelyfrom having to understand the physical layout of the data,”Kowalsky says.

Sometimes, resisting the urge to complicate the project can takeyou down a completely different path to a better solution. Forinstance, Allmerica Financial wanted to provide its agents accessto policy information via Allmerica's agent portal. However, italso was important to offer that information in the same printformat customers received to help agents better answer servicequestions. So, providing a field-based display wasn'tsufficient.

“The problem was customers would call up the agent and say, 'Ihave a question about my policy on page seven,' and the agentdidn't have a paper copy on hand. It could take days to get a copybefore, if the agent had to have it mailed,” says Dave Trigo, chiefarchitect at Allmerica Financial.

Allmerica initially planned to attack the problem at the datalevel by taking its AFP (Advanced Function Presentation) printstreams, converting them into XML, and reformatting them for PDFdelivery to the agent portal. This strategy would give the carrierdata that potentially could be repurposed to other uses anddelivery methods.

It quickly ran into problems with that approach. “Where weoriginally thought we had dozens of templates, we actually hadhundreds because we had tailored the templates for individualcustomers,” says Mike Clifton, Allmerica's vice president and CTO.After four months of working on the project, Allmerica realized asimpler solution was what was needed. It deployed WhitehillTechnology's TransformAFP software to convert directly AFP to PDFon the fly, when agents request a policy print via the portal, andcompleted the project in two months.

Complication of the data conversion task itself also can comefrom excessive customization of the new system. Resist the urge tocustomize, and realize the more target systems for which you needto convert source data, the harder your efforts will be.

“The functionality of our core system has been critical to ourdata conversion efforts,” says Wilcox. “We're in a unique situationwhere we administer all our products on LifePRO. As a result, wecan look at just about any [company] acquisition and put [the bookof business] on one system vs. having to decide what system toconvert to or needing to convert to multiple systems. That leads toincremental cost savings, eliminates building multiple interfaces,and lets us leverage our competency with the admin system.”

Expect Problems

Despite the best data cleansing efforts, there will be balanceproblems–when the ending value on the source and target systemsdon't match for the same transaction. Part of the solution isfinding out why–errors in the source system, rounding orcalculation differences, or undiscovered code changes made to thelegacy application over time.

The other part of the solution is planning for just what levelof “imbalance” is acceptable. In a commercial lines premiumcalculation, a rounding difference even of a few dollars might notmatter. But in an annuity, before and after cash values need to beexact.

“We identified about 20 'critical' values and fields,” saysMcAdaragh. “We decided at the outset of the project what ourbalancing tolerances were for each of those fields, and those aretracked in the reports the CSC conversion [tool] supplies.”

Plan for the Future

Insurers need to look beyond any individual data conversionproject, both to avoid creating new problems as well as to plan forfuture conversion efforts.

Avoiding–or at least more easily solving–future problems beginswith effectively documenting the data conversion process. “As yougo through the project, you make decisions, particularly regardingdata mapping. You need to have those decisions documented so thatif at some later date you question a result, you know why you madethe decision in the first place and what the impact might be if youchange it,” Wilcox says.

Planning for future conversions may mean creating or investingin a data conversion toolkit–data analysis tools to help identifywhat data you actually have vs. what you think you have, tools fordata cleansing activities such as name and address scrubbing, andautomated mapping tools. Ideally, it also means creating arepeatable conversion process.

“Over time, we basically have created a 'data conversionfactory,'” says Northwestern Mutual's Kowalsky. “The team we haveis able to identify data patterns and potential problems andreadily can create metrics around each one of these patterns toscope new project stages.”

“Our short-term goal was to move the block of business weacquired, but by the time we were done with that project, we wantedto have a Sammons conversion team that could do futureconversions,” says McAdaragh. “We learned a lot through the firstprocess that will help us deal with our [CSC] LIFE-COMM and LIFE/70conversions that are coming up in the future.”

Last, in addition to these conversion-specific strategies,avoiding the risks of data conversion requires good projectmanagement, whether the conversion is part of a larger initiativeor a project in and of itself. That means developing and managing adetailed project plan including all project-related tasks,dependencies, and critical paths; being willing to make decisions;and focusing conversion team communications on problems.

“You should focus on tasks that are behind schedule,” saysUniversal Conversion Technology's Kaul. “If you just focus on thethings you got done you were supposed to get done, it quickly canbecome unproductive self-gratification.”

In the end, even though a data conversion process may not becelebrated for its successes, it certainly can avoid being thecenter of negative attention.

“In the final years of the conversion project, we wereinvisible–45 weekends out of the year we had conversions going onand you would never know it,” Kowalsky relates. “I had to make apoint of bringing it to our CIO so nobody forgot about the work wewere doing, but that meant we were doing it well.”