The Prevail Case Management System: Articles

Data Conversions That Don't Suck

Pardon the title of the article. It’s a little blunt. It was designed that way to get your attention. I’ve learned that where data conversion is concerned, you have to state things very bluntly and forcefully in order for them to sink in.

Here’s a true story: I recently spoke with a client who had purchased Prevail and opted to have his data converted from his old case management system. He wasn’t happy. He called me specifically to complain that the process was taking too long, the results weren’t as good as he expected them to be, and his valuable staff time was being “wasted” by the process. I suggested to him that he re-read his data conversion contract, paying special attention to the clauses where it spelled out very specifically what the timetable was going to be, and what he, the client, would have to do in order to make things happen within that timetable, and what he could reasonably expect the results to be.

Naturally, he hadn’t read the agreement in the first place. He assumed, quite wrongly, that this whole data conversion thing was some mystical process by which us computer-types would magically transform his data from one system to another. In places where his original data wasn’t accurate, we were apparently supposed to correct it for him. In places where the data structures didn’t match up exactly, we were apparently supposed to re-write our software to make it look just like his old system. If any phone numbers were missing in his original system, we were apparently supposed to look them up for him and make sure they made it into Prevail. Oh, here’s the kicker – all of this was supposed to happen in the background, without any feedback, involvement or review from him or anyone on his staff.

Needless to say, that client had an unfortunate learning experience about how data conversion really works. He said if he’d known in advance how it was going to be, he wouldn’t have bothered having the data converted. I’m with him on that. It probably didn’t make sense for this particular client to have his data converted from the old system. We’d have all been better off if he’d just started entering new cases into Prevail and left the old ones in the old system. If you’re a cynic you might think we knew this going in, but did the data conversion anyway to make some extra money. Of course, you’d be dead wrong. We at Prevail, as well as most other case management vendors, HATE doing data conversions. I can’t speak for the rest of the industry, but I can speak with absolute certainty for my own company: We lose money on every single data conversion.

Here’s another true story: Being currently a little backlogged on data conversions, we recently elected to seek a hired-gun contract programmer to do a conversion for which we’d already agreed with the client on a price $2,500. With the number of programmers out of work these days, we figured it would be easy to find one to take on a project like this. We made arrangements to have a number of programmers view the data on our server, provided them with working copies of our application, and then asked them to bid on the project. The LOWEST bid we got was $12,000, and the highest was $25,000. Maybe all these contract programmers know something that we don’t.

The simple fact is that data conversion isn’t easy. It’s a time-intensive process that involves a lot of sleuthing and trial-and-error. It’s service that is almost always provided by the software vendor at a net loss. Why then, are we in the case management industry willing to do data conversions at all? The answer is simple: We’d rather have you buy our software than not buy it. If converting your data is what it’s going to take to make the deal happen, most vendors will do it for you. They may not be happy about it, but they’ll do it and hope for the best. As someone who has presided over literally hundreds of data conversion projects, I can tell you with some authority that the best doesn’t happen all that often. The worst, on the other hand, rears its ugly head pretty frequently.

Going back to my earlier true story, here’s where we ended up: The client was mad at me, I was mad at the client, and the conversion programmer was mad at both of us. In short, NOBODY was happy. Any scenario where everybody loses is usually a bad idea. Could this situation have turned out better? You bet. It’s certainly possible to have a data conversion that doesn’t suck. It’s just not easy. It’s mostly a matter of going into the project with your eyes wide open and understanding the reality of the process. Here’s what it takes to have a data conversion that doesn’t suck:

Realistic Expectations
You’d better know going in that your data conversion isn’t going to be perfect. Your new system and your old system almost certainly don’t handle data in the same way. If they did, you wouldn’t be looking for a new system. When converting data between dissimilar systems, the programmer has to fit a lot of round pegs into square holes and vice-versa. Here’s an example: Let’s say your old system is a flat-file system, where each time you enter a case, you type all the information about the client into a brand new record. Now let’s say you convert that database to Prevail which, like most modern case management applications, is a rolodex-based system. If you had 67 different cases with Allstate as the insurance company, Allstate is going to show up in your Prevail Rolodex 67 times. Does that mean the conversion programmer did a lousy job? Nope. He did exactly what he should have done under those circumstances. It’s neither his place nor his job to delete the redundant entries.

Let’s look at another example: Prevail has the ability to link every appointment on your calendar directly to a matter in the system, so that you can click on a link in the appointment record and it will jump right to the matter or case to which that appointment belongs. Suppose your old system didn’t have that capability. In a data conversion of this sort your appointments would convert, but none of the matter links would be there. Similarly, the Events page (containing all calendared events for that case) in every matter in Prevail would be blank. It doesn’t matter that you may have typed the client’s name in the subject line of each appointment – if your old program didn’t have a specific, unique field that links appointments with matters, no link can be established in Prevail. We can’t convert data that’s not there.

The bottom line is that you need to have a realistic understanding of how much data is going to be converted, how it’s going to show up in your new system, how long it’s going to take and how much it’s going to cost. You may well conclude that for the amount of data you’re talking about, it’s not a cost-effective transaction. That’s fine by us. If you’ve got a lot of data and don’t mind the time or money involved in a conversion, that’s fine too – just make sure your expectations are based on facts rather than wishful thinking.

Feedback, Feedback, Feedback
Converting data isn’t something we do for you and deliver when it’s done. It’s a process we go through together. Granted, we do most of the work – but the fact remains that our results are only going to be as good as the feedback we get from you. Your feedback has to be comprehensive, accurate and timely. Otherwise, your data conversion will be incomplete, inaccurate and late. Here’s how the process works:

First, we start with a copy of your data. Our conversion programmer looks at your data (which may be contained in upwards of 50 data tables), figures out how the tables relate to each other, then writes programs to extract the data from each table and plug it into the corresponding data table in Prevail. When he’s got it all roughed out they way he thinks it should be (remember, he’s guessing at this point), he’ll send the data back for you to look at in Prevail. It will then be your responsibility to review the data, write down anything that’s missing, anything that’s formatted incorrectly, and anything that’s otherwise screwed up. Remember, you are the eyes and ears of the conversion programmer. He doesn’t know how your old system works or where things are supposed to be, so expect the first pass to be pretty rough. As you give him feedback, the programmer will fix things, but he’s flying blind without your help. Give him as much detail as you can. It’s very helpful to include specific examples of things that are wrong or missing (e.g. “In our old system in the Smith vs. Jones File, ABC Insurance Company is listed as the UM Carrier, but they don’t show up anywhere in the converted data in Prevail.”) Keep in mind that the programmer frequently can’t even run your old program to see the data – he has to use the examples you give him to find the data in the tables themselves.

After you’ve written down anything that’s amiss in the first pass and faxed it to us, the conversion programmer uses the information you’ve given him to as a guideline and re-writes his conversion programs accordingly. Hopefully you didn’t wait three weeks before reviewing the data and sending your fax. If you did, you just added three weeks to the conversion timetable.

The re-written conversion programs will then be run again, and the programmer will send you back your re-converted data for a second pass review. Ideally, the second pass will fix most or all of the problems you identified in your first pass review. If it didn’t, you need to identify anything from your first-pass list that’s still not correct. If you discover anything new, put it on the list as well. Programmers work from lists. If it’s not on the list, he’s not going to change it. Period. As with the first pass, you compile your feedback into a fax and send it to us. You didn’t wait three more weeks to send that fax, did you? I hope not, because if you did, you’re now a month and a half behind schedule and you’ve got nobody to blame but yourself.

After receiving your second pass feedback and tweaking his conversion programs accordingly, the programmer will send your data back for a third-pass review. By now your data should start looking pretty decent. It’s not going to be perfect, but it’s almost as close to perfect as it’s going to get. Now it’s time to pick a go-live date and pull the trigger.

On your final pass, we’ll usually download another dataset from your old program on a Friday afternoon, then run the conversion over the weekend so you’ll be up and running on Prevail first thing Monday morning on data that was current as of close-of-business Friday.

We call the final pass “final pass” for a reason – it’s the last one you’re going to get. It’s your responsibility to review the data BEFORE THE FINAL PASS and make sure you’re comfortable with the accuracy and completeness of the information. DO NOT call us up a week after the fact, tell us you found a field that didn’t get converted, and expect us to drop everything and re-run the whole conversion. It was your responsibility to find that sort of thing long before the final pass – that’s why we have three passes in the first place. If you’re going to find things wrong with the conversion, find them in the first 2 passes where they’re easy to fix and won’t cost you anything. Find them after you go live and they’re almost impossible to fix – and it’ll cost you $100 an hour.

Intelligent Timing
If you’re doing a data conversion and also scheduling user-training, make sure you schedule the user-training around the conversion. Give yourself some float time in case the conversion doesn’t go exactly according to plan (have I mentioned yet that conversions almost NEVER go exactly according to plan?). The last thing in the world you want is to have user-training so far in advance of the data conversion being done that your end-users have forgotten everything they learned in training by the time you go live on Prevail. I like for the training date and the go-live date to be close together as possible (ideally a day or two apart). That way there isn’t much time for all that hard-won knowledge to slip out people’s heads while you’re waiting for your data conversion to be finished.

Understand the 30/70 rule
Here’s a general rule I’ve come up with for data conversions: Regardless of how much time and money are spent on a data conversion, 30% of that time and money are spent getting the first 70% of the data. The remaining 70% of the time and money are spent getting the remaining 30% of the data. This phenomenon can be summed up in two words: Diminishing returns. If you spend enough time and money, you can come up with a near-perfect conversion. Banks do it all the time. When they switch from one system to another, their accounts have to balance to the penny. Of course, data conversions like that take a year or so and the cost is almost always in six-figures. For non-financial applications like case management software, it makes more economic sense to limit the scope of what you’re converting. You’ll get up and running faster and spend a lot less money. Figure out where YOUR point of diminishing returns is and stop short of it. How much we convert is up to you. We can give specific quotes for just calendar info, just rolodex, etc. It’s often much more cost-effective to convert only enough data to get the ball rolling, then let your staff fill in the rest as you go along.

Data vs. Documents
I’ve actually had clients call up after a data conversion and want to know where their mail-merge documents from the old system went. The answer was that they haven’t moved – they’re still in the old system. Forms and merge documents aren’t part of a data conversion. Data conversion only addresses data tables and data fields. Documents are a whole different animal. Form documents can be converted, but they are almost always specifically excluded from any data conversion agreement – and ours is no exception. If you want to have your forms converted, you’ll need to make separate arrangements. Because it’s not programming and can be done by any word-processor power-user, form document conversion is much less costly than data conversion. It’s not free, though. Whether you’re doing it yourself or farming it out, make sure that your form documents are ready to go when your data is, otherwise you’ll have some very unhappy staff-members.

Economies of Scale
As a general rule, the bigger a database is, the more sense it makes to do a data conversion. Let’s say a large firm has 3,000 cases that need to be converted from their old system into Prevail, and that their data conversion costs $3,000. That breaks down to a nice, round figure of $1 per case. When you figure the vast amount of data that may be contained in each of those cases, it seems pretty reasonable. You certainly couldn’t hire temps to re-enter all that data for $1 per case. Now let’s say a smaller firm is also buying Prevail and electing to have a data conversion. Their old software is very much like that of the hypothetical large firm, except they only have 300 cases in it. Since their database is 10 times smaller than the large firm, it should only cost $300 to convert, right? WRONG!!! Despite the smaller number of records, the conversion programmer has to go through exactly the same process for the small firm as he does for the large firm. It takes just as long and costs just as much. The conversion still costs $3,000 even though the database is much smaller. The economies of scale change somewhat. Now we’re looking at $10 per case to have the data converted. Does it still make sense to do the conversion? I don’t know.

That’s for them to decide.