Avoiding data conversion hell

Why don’t my report counts show the same numbers on your new system as they did on my old?

Ugh, do I ever hate to have this question come up in a project review meeting. It is surely my candidate for the question consultants least like to hear from a client.

And this question usually follows by a few weeks or months a close runner up question, Can you convert all my old data into the new system. Clients who ask this and consultants who answer yes are looking for trouble for themselves.

This has been on my mind as our team approaches the end of a current and some recent projects whose conclusion has been directly linked to resolving data ‘errors.’ Sometimes you find unexpected joy, such as last winter when a conversion from a really old Raisers Edge version to Giftworks provided historical donation totals that matched entirely. Not to be a pessimist, but yup, I was surprised.

In our hearts if not our experience, we all know that data conversion gaps occur way more often. Here are some thoughts for “both sides” to keep in mind in approaching that next software upgrade.

First, as a background philosophical thought, if an organization’s data looked just right in its old system, would you still need to upgrade or replace? Business practices change and the old ways no longer work. We generally welcome change and we can expect staff to look forward to operating in new forward-looking ways. Yet fitting the old data to the new may be, as a colleague said yesterday may be mixing apples and oranges. The old and the new may never align again.

If you do convert piles of old transaction data or program enrollment data, and the new system has involved strategic planning and incorporation of new business rules, accept that reports will look different. Embrace the new and appreciate it.

Second, conversely on the human or philosophical side of things, you have to make an estimate of how much staff not just “hate the old way” but really are ready to “embrace the new way.” I think of a data conversion some years ago where staff continued to run mailing lists from their old system months after they were doing data entry in their spiffy new system, with its way cooler list generator. Hostage syndrome. They had grown hostage to the features that bore down on them in the old system. So if things ARE going to look different, you need to prepare everyone for it 100% or life will be difficult.

Part of the way to do this is to make sure staff at all levels of the organization are in the room when you discuss data migration. It is not sufficient to just look at reports and lists and to talk with the managers who use them. Let everyone hear how admin and program staff actually enter data, the compromises they make, the guerrilla warfare they may be waging against existing data validations to put what they want where they want it. This may be sobering to senior management and support realism about the conversion.

Third, consider if you can just leave historical data where it is. If you have been paying for a proprietary on-line data system that you want to stop paying for, you may have no choice but to extract old data and put it somewhere it can still be used. If not, why not just continue keep the old system available to run historical reports and start fresh with the new? Two pending conversions to Salesforce to replace a collection of spreadsheets and old Access databases have been way pleasant from a decision to not incorporate years of messy and unaligned old data.

Deciding not to convert may be a major policy decision if senior management or the board is used to a certain kind of review. I don’t underestimate it. But it is worth pursuing the question at the outset, before anything starts.

Fourth, consider converting just top level data, such as all primary demographic and status information for current contacts and leaving aside in the old system. Start with just what you know to be clean and essential.

Fifth, a variation on the third, fill in complex and messy old data by hand over time. As new cases or activity occurs for a contact, fill in or correct the gaps in their data from the old system. Meanwhile, don’t try to run multi-year historical reports on the new system

Sixth, if the data is messy, clean it up parallel to and prior to switching to the new system. My experience: most of the time, it is going to be easier to correct messy data either in its present home or in some convenient way station. In another conversion to Salesforce, where the data now resides in Filemaker, we have agreed to freeze data entry for a bit, and pull the data into a combination of Excel or Access where it will be more susceptible to bulk manipulation and checking.

Seventh, tell the consultant everything you can. If you are relatively new and the system is not well documented, find the staff members who have been there long enough to explain quirky things lost in the mists. In a current conversion from a proprietary undocumented membership system to CiviCRM, we have had one gotcha after another. For example, the current system shows five membership levels. The new system will have the same five membership levels. The paper forms show five membership levels. We checked at the beginning. Great! Easy! Except a faction of the members, in their actual extracted data showed a sixth membership level. And CiviCRM importer chokes on those records so it has to be addressed. OK, we figured this out, but if you add up field by field the consultant detective work to resolve things like this, you suddenly have blown a reasonable data migration budget.

Eighth, agree to leave data conversion out of proposal bids and do it on a time and materials basis only. This will enable everyone as the inevitable problems arise to make pragmatic decisions. Yes, it is worth it to write a script to fix this problem. No, it is not worth it, leave it alone. Or, yes, we need this and organizational staff will fix it ourselves or hire temps to do it.

I regularly resolve not to submit proposals where I have to include data conversion in the cost estimate, and I regularly find myself unable to keep that commitment. Even if you allot a few hours as part of a preproject assessment, it is unlikely you will find all the problems. A current project that is not exactly a system migration, but instead will need to enable staff to use purchased business data in a Drupal-based research and organizing framework. The data is good, used nationally and expensive! It should be clean say the developers! Yet even here we find quirky things. What should we make of a crucial research field which sometimes is stored as an actual dollar amount and sometimes as a coded list of dollar values. Since this is an ongoing data feed and not a one-time data conversion, we need to address it so that data searches will work on both kinds of cost valuation figures. And it’s just not realistic to expect to find all this stuff before you get pretty deeply into it.

Nine, if you are the consultant, you often, not always, do a trial conversion, leave it on a development site for a while for testing, and then do the final conversion to go live. It is really important to carefully document the conversion steps. In one really messy conversion, we ran, tested and refined the 40 or so separate conversion scripts several times before deciding the soup was ready. Different people contributed to the scripts. Having the scripts catalogued and organized lessened the pain of having to rerun them. Plus even a year later, it was possible to go back and find the script that massaged this messed up status category or performed some other task when the client asked about “what happened to the people with interest code x”?

Ten, hmm. I am leaving a slot for you, veterans of data conversions. Your thoughts? I could certainly use them…

Meanwhile, this post derived from a suggestion from fearless editor Laura Q to write about the question consultants least like to hear from clients. When I posed this to my co-workers here at Database Designs, I got so many suggestions, I first thought of doing a “top ten” and decided to just start with my “favorite.” This could turn into an occasional series, and to balance, my colleague Mimi suggested one on questions consultants MOST like to hear from clients.

Beyond Spreadsheets: Give reporting its due in software planning

Database Designs isn’t just about databases but I still spend a lot of time thinking and strategizing about them. Recently, I have noticed that, with some exaggeration, you could divide up much of the population of database managers into those trying to get data out of spreadsheets and those trying to get data back into them. From dust to dust, from spreadsheets in to spreadsheets out, data collection seems like burdensome toil for many. Maybe those flying closest to the sun, with large budgets and staff, truly escape, but most of us still struggle.

On the one side, no matter how powerful one’s database or CRM, administrators find themselves regularly battling users who keep their real data separately in spreadsheets. Not the official data, yet what counts day to day. At a 2009 Nonprofit Technology Conference session on tech planning, a speaker commented that a good technique is to just walk around and see what users are actually using at their desk, regardless of the organization’s prioritized software systems. Yup, that struck a chord.

On the other side, in evaluating systems, its easy to focus on the processing workflow, data collection fields, interactive usability and get to “reporting” last. Under-budgeting for reporting and data exchange is an easy trap to fall into.

Absence enough attention to these things, your staff's data world may remain spreadsheet driven. Here’s some thoughts.

In software planning and selection, tie every feature discussion back to reporting. Reporting today is not just neat formatted lists and labels. It’s also spreadsheets, mailmerge, email list sync, mobile and beyond. One of the reasons I have continued to have affection for Microsoft Access is its powerful reporting system, to which its adherents then layer on additional utility over the years. If you have Office, you have a pretty useful tool at hand, even if your data sits in SQL Server or on the web in MySQL or other databases. It shouldn’t be that hard anywhere.

Salesforce has a great reporting tool once you get used to it. CiviCRM, long dogged by absence of output mechanisms, now is on the verge of addressing this. And it doesn’t have to be in big, complex systems. And definitely check out the truly smart “Smart Lists” component to Mission Research’s GiftWorks software. As a reporting tool builder, I'm envious.

Going further, staff fundamentally do not want to enter the same data twice. If they do, it’s more likely because they can’t make the actual lists they want than that important data collection fields are missing. Even if you have made sure you have the right tools, it is so easy to short time for customizing the reporting features or in training on reporting. Adding another custom field or two to a web page typically takes a lot less time than getting it into appropriate search pages or output templates. You have to consider all of it in planning for reporting. An easy way to protect yourself is to include reporting elements in each phase of a projects implementation, instead of having a giant reports phase at the end.

Third, there is life beyond spreadsheets. Some of the most exciting stuff at the 2009 NTC had to do with using free and low cost tools for visualizing data. Visualizing data can mean any framework that enables the information you want to organize come alive in context. It can be putting it on an interactive map. At the 2009 NTC, Peter Black showed some done for the Environmental Defense Fund (edf.org) such as this not-so-fun exploration of sea level rise: Or , check out Google motion charts and the work it is based on at gapminder.org.

Data visualization is a whole separate topic. I’ll just say that thinking creatively with today’s tools about visualization is also part of how to break out of the dust-to-dust, spreadsheet in to spreadsheet out framework for data , and to perhaps generate greater enthusiasm for and quality of data collection.

Avoiding technology project failure

Usually, we’re all so focused on the good stuff we want to do, it’s hard to step back and consider when and why Information Technology projects go wrong. While success rates vary by project size, company size, project nature and so on, technology researchers at Gartner still figure that about half of corporate technology initiatives fail. Pretty scary, if you aren’t familiar with these statistics.

Luckily, there are folks like Michael Krigsman who pay attention. Michael writes the ZDNet blog on “IT Project Failures,” and you can follow it at http://blogs.zdnet.com/projectfailures.

Michael and Lisbeth Shaw, friends of mine from Boston, founded Asuret (asuret.com) to provide tools and consulting services to increase technology project success rates. With fellow zdnet blogger Paul Greenberg, Michael recently organized a virtual town meeting of technology strategists which I was fortunate enough to take part in. (Check out Paul Greenberg’s book and articles on CRM--customer/contact relationship management--at http://blogs.zdnet.com/crm/ and http://the56group.typepad.com/)

The experience of those in the discussion confirmed Paul’s statistics about corporate CRM projects. The big theme, cultural and collaboration issues predominate over direct technical or formal project management issues as sources of CRM project failure. Implementation of a new system may succeed on its own terms and still fail when users vote with their feet and fail to embrace it. They keep using their private Excel spreadsheets or personal email lists instead of new systems. This is important to think about when day in and day out, we may instead worry about the pace of technology innovation, how hard it is to get the latest and greatest things to work as expected and so on. These are all true, yet its the social and cultural factors which have the highest risk.

Insufficiently involving users from the beginning appears, both in statistics and in experience on the call, as a critical factor when a technically successful project still winds up abandoned or drastically recast. If not involved from the beginning, users won’t bother later on. While this may seem a truism, in my own experience, you may think you are involving users at the right level and to the right degree only to discover months later that you need to go back.

Smaller projects and organizations have limited budgets and limited staff time for concentrated planning. The danger here, which apparently affects large technology projects just as much, is that management focuses on choosing features and functions and selecting a vendor ahead of an effective, well understood, and widely accepted strategy.

Those that know me, know I like to consider where for profit and nonprofit technology planning issues converge and can benefit from each other and where they diverge. In the nonprofit world, we may see technology implementations responding to tight grant funding cycles and too quickly focusing in on formal RFP processes. These pressures may be the particular reason behind inadequate work on culture and strategy despite nonprofit emphasis on them in general.

The Asuret town hall provided a great opportunity to step back and consider these issues with folks from around the country working on pretty diverse projects. To follow the discussion on your own, check out the blogs mentioned above and resources on the Asuret site, which I hope to comment on in a follow up article.

New Years Resolution Idea - Clean Data

As I've been considering my own New Years Resolutions, I started thinking about what one client was thinking about for 2009. I was asked to put together my thoughts for a data maintenance ‘ritual’ for their new Salesforce system to ensure that they keep their data clean. Fantastic!! It's no new news that working out and eating healthy help you function in life better; similarly, it's old news that clean data helps your organization operate more effectively. Nevertheless, the beginning of the year serves as a new reminder to get in shape. I thought I'd share my quick list culled from various sources here. Would love any other suggestions.

Daily/Weekly

• Back-up database. Set up an automated backup to be generated that can be download weekly.
  
• Duplicate check. Check “Demand Tools” reports regularly to check for duplicates and other redundant data.
  
• Scan for junk leads. Doing regular scans for junk Lead records that are filled with gibberish values from sources like online forms.
  
• Check For & Tackle Incomplete Records. While most CRMs can validate or require certain data fields, it’s not always easy to ensure a value for every field at the time when a record is generated. For example, if staff import a list of event attendees with only name, title and phone numbers, it has very little value if it needs to be used in an email or direct mail campaign. While appending missing data is may require a lot of manual effort which will feel time consuming, it is a necessary evil to ensure that you have ‘actionable’ data.
  
• Returned mail. Update contact records when mail is returned. [Determine policy…what happens? Alert to owner? Purge? Phone call?]
  

Weekly/Monthly

• Run executive reports weekly/monthly. Use key organizational reports to spot poor data (as well as poor performance). Data that doesn’t match expectations are either an indicator of poor data management or poor performance by the individual.
  
• Scan communications lists. Review lists (reports) that School Volunteers will use for communications (email, invitation lists, etc.). Is there missing data? Is data in the right format?
  
• Run exceptions reports monthly. Run reports run monthly to find records with incorrect picklist values.
  

Quarterly

• Post email cleaning. Use tools in VerticalResponse to identify Returned Mail or Bounced emails so that bad lead records can be updated/purged.
  
• Delete or archive old data. Organizations merge, get acquired or shut down, contacts change addresses, change jobs, move within an organization …CRM data does expire. This is an area which is not easily automated and requires investment of time and energy. The more regularly you check for expired data, the healthier your data will be. According to one source, a database unchecked for an entire year can see as much as 30% expired data.
  
• Data enrichment. Regularly ask what additional data in each record would help staff do more and have better insight. Adding political campaign contributions? Adding annual revenues of Community Based Organizations or Foundations?
  

Social Networking meets CRM

What does Social Networking have to do with Constituent Relationship Management (CRM)?

Here are a couple of examples nonprofits are working on:

1) A health advocacy organization set up a Ning site to support collaboration and knowledge sharing among like-minded organizations. The site has grown to about 300 members and is the single largest source of “new blood” into the collaborative effort. However, in order to ensure that all the Ning members get the monthly eNewsletter and announcements of important events from Constant Contact, the organization has to cut and paste every new registrant into its database and again into Constant contact.

2) A volunteer organization decided to create “snaggable” widgets that would allow volunteers to post their own data about volunteering on social networking sites like Facebook, MySpace, or blogs. The organization believes this would create much more visibility amongst the volunteers’ friends and family than trying to attract traffic to its original volunteer portal idea. The trick, exposing the volunteer data and messaging through widgets or mini-Open Social applications.

The takeaway message: Social networking tools are merely new mechanisms that need to share data just like any other tools. If you’re delving into social networking, keep asking how you’ll manage the data and communications relative to other tools that you’re using for fundraising, events, and day-to-day interactions. Look for ecosystems in which multiple vendors work together.

I looked at the Ning site and was pleased to see that it had a decent and well-documented API that will allow the organization to automatically gather new registrant data into an open database like Salesforce, CiviCRM, or Convio. Convio, Kintera, and Salesforce among others have started to develop social networking widgets for activities like fundraising and advocacy campaigns that feed data back into their respective repositories. These kinds of ecosystems are great!

Volunteer Management Software – A Cautionary Tale

A friend recently asked for some quick advice on Volunteer Management software that a local Obama campaign could use.

I easily fall victim to this kind of request, just as my friend and many nonprofits fall victim to it: asking for a technology silver bullet for a specific problem prior to defining the problem in its larger context. As a victim, I did a quick Google search to find VolunteerHub, Volgistics, and Volunteer2, which all at first glance seemed to have surprisingly solid features and pricing.

Then BAM! I realized my error: my Silver Bullet Pill solution would cause more pain for this group down the road. I started thinking about the fact that many volunteers are donors…which meant that the organization would have a different donor database. Now that would be bad!! And then I thought, many of those volunteers may work for companies or community-based organizations that might be recruited to host issue-focused events. Would that cultivation process be stored in yet a third repository (or Excel sheet), because the Volunteer Management system isn’t built for this? Etc, etc, etc.

The result: A data quagmire that would impair the organizations’ effectiveness down the road. Well, for a campaign only in operation for a couple more months, maybe this is not the end of the world, but for an average nonprofit, it’s a big problem.

The message to nonprofits: When shopping for software, think about how the data in this particular application will interact with data living (really or potentially) in other places. Don’t buy an application that just focuses on one point of pain without thinking about the bigger picture. Like eastern medicine, look at the larger context. Sometimes a pill might be the best solution…but understand why.

The message for vendors: Get out of the data vacuum – it’s hurting nonprofits in the long run. Either: 1) build your best-of-breed application on top of an open, extensible platform like CiviCRM, Salesforce, SugarCRM, Microsoft CRM, etc.; or 2) build a open extensible platform with open APIs and develop a broader vendor ecosystem that provides plug-in components to create “whole solutions” for nonprofits.