The Smarter Way to Convert Your Fundraising Data

Over the past 20 years JCA has seen the fundraising software industry evolve; from DOS to Windows, from client/server to ASP to SaaS, from data silos to enterprise solutions that connect directly to constituents over the Internet.  At JCA, we understand that no matter how well a software package functions and no matter how sophisticated the features, a bad data conversion will render the system useless and undermine user confidence.  From our experience in managing implementations, we have seen that the same vendors who have built best-of-breed software products often have unsophisticated data conversion procedures.  This need in the market inspired us to develop internal conversion software with the sole purpose of converting fundraising data.

When I started at JCA more than 10 years ago, there were four consultants responsible for all aspects of data integration projects, from meetings with clients to conversion programming, training and report writing.  As you might guess, we each had our own methods and preferred tools for completing the conversions. It was especially difficult for us to help each other on data conversion programming since we each maintained our own script libraries and frequently used different programming applications – a couple of us used FoxPro, others used MS Access, and occasionally various command line applications were thrown into the mix (remember this was a long time ago).

As the company started to grow, we decided it would be useful to consolidate all of our conversion programming and devise a methodology that would provide a common language and tool set. We had several goals for this project:

• Encourage more collaboration via software and methodology
• Enable less technically experienced consultants to assist with the conversions
• Establish a library of database and table structures for fundraising and ticketing applications that we encountered frequently

The result of this endeavor was an application we call FIDO.

Initially we developed FIDO using MS Access as a front end to MS SQL Server 2000 databases. We recently reinvented FIDO as a .Net windows application written in C# with an SQL Server 2005/2008 back-end.

When working on a data conversion of any complexity, substantial data cleanup is inevitable.  Most fundraising systems have similar high-level functionality (name, address, gift, pledges, etc.) but each software typically stores data very differently from the next.  FIDO was designed to allow every field, code and value within the source system to be reconfigured, recoded, moved or deleted  to feed the target system in a way that fits the needs of fundraisers.  In my experience, simple field-to-field mappings represent only a tiny subset of the work required.  A successful conversion may demand the reconfiguration of nearly every detail in a database, and FIDO helps JCA achieve this objective.  Here are some examples:

Use Case: Fields that change meaning over time.   For example, if Finance implemented a new general ledger with a revised chart of accounts ten years ago, the coding on new gifts in the fundraising system may be altered but the history cannot be changed.  Staff turnover and lack of documented policies can result in inconsistent data as staff take their best guess when adding data.  Additionally, staff may re-purpose fields in an effort to track new or different types of fundraising efforts.  All too often, the historical data is not changed as it either becomes too complex to alter in the fundraising system or the institutional knowledge no longer exists to properly interpret the old data.

Solution:  FIDO will map one field to multiple locations based on individual codes, and a code can also ‘trigger’ a series of actions.  A good example involves the handling of ‘deceased’ coding, which often appears anywhere from being in a mail code (‘Do Not Mail – DECD’) to an address type or to an actual flag or other indicator.   In fact, some users have been known to replace the actual street address with the word ‘Deceased.’  In all of these cases, FIDO can search for each variation of ‘deceased’ in the data – even in a long text or memo field – and accurately remap the data.  Conversion rules in FIDO can be set to trigger any number of actions in the conversion: assign the proper codes in the new system, uncheck mail flags as needed – even produce an exception report for the Research Department to confirm before final conversion that the individual is, in fact, deceased.

Here are more examples of what we can do with FIDO:

• Perform an analysis of a client’s source data by providing table, field and code statistics

• We can map data from any source to any destination system. FIDO has a simple non-technical interface for “mapping” the source data to the target system (field mapping).  We can include transformations, one-to-one, one-to-many and many-to-many mappings.

FIDO translates the data mapping into SQL programming. The person mapping the data conversion does not even need to program!

• FIDO contains embedded SSRS conversion reports from both the data source’s perspective and the target system’s perspective.
• FIDO provides a reliable and repeatable way to manage the data conversion. We can run the entire data conversion over and over by simply pushing a button.
•  We’ve designed a repository of database schemas and application requirements for each system we convert.

FIDO is indispensible for our conversion projects. It provides a common language for all of our consultants and programmers. It allows for a logical division of labor – client-facing consultants can do the mapping, and behind-the-scenes programmers can translate that mapping into the more technical SQL programming – all in the same tool. If necessary, multiple consultants and/or programmers can collaborate on the project, mitigating misunderstandings.  Best of all, we’ve greatly reduced the effort required by all parties to complete a data migration project.

FIDO has been our trusted conversion companion for many years.  In the end, though, the tool alone does not make for an excellent conversion – our people do.  At JCA our programmers have a deep understanding of how fundraising works.  I was membership manager at Carnegie Hall before joining the JCA team, and many at JCA previously worked at nonprofits.   At many software vendors, data conversion gets staffed by the least experienced of the technical staff, while more experienced engineers work their way to software development.  But here at JCA data conversion is core to our business.  If you are doing a data conversion, don’t settle for anything less than excellent data conversion tools and experienced conversion consulting.

Catherine Tarbox is Senior Programmer at Jacobson Consulting Applications, Inc. (JCA)
JCA provides strategic consulting to the world’s leading nonprofits.