No one likes surprises during an association management system (AMS) implementation, and surprises during the data migration or conversion process are among the least welcome. An AMS implementation can come to a standstill when you find out how truly complex data conversion can be. And, even more surprising, you may find out that your historical transactional data won’t be migrated in ‘true form’ to your new system.
Don’t panic, don’t get mad, this is normal.
Why converting transactional data is a challenge
Transactional data is information that has been created by recording transactions in a system, such as payments for membership dues, subscriptions, registrations, and purchases. Your database has two types of transactional data:
- Open transactional data relates to unfinished business, such as an outstanding dues invoice.
- Closed transactional data relates to finished business, such as a paid meeting registration invoice.
Transactional data is complicated. One transaction has many parts with data residing in many different tables. Migrating it is challenging because your new AMS doesn’t have the same data structure and posting methodologies as your old system.
To replicate a historical transaction in the new system, a specific set of data values must be present to populate various tables and to trigger certain functions and ‘switches.’ Because the new AMS doesn’t have the same data elements as your old system, it can’t create a true transaction using your old data.
For example, your new AMS will have a member’s renewal date, but the underlying transactional data related to their most recent dues payment won’t come over to the new system—at least not as a real transaction (read on!)
Three compromise solutions for transactional data
AMS vendors typically import historical transactional data to a flat file in the new AMS so you can view and access the information. Because this data is stored in a separate table with a different format, it won’t be included in standard reports that compare transactional data from before and after your new AMS go-live date.
This reporting challenge often comes as a surprise because associations believe they’re getting all their old data in their new system in the correct format, when, in fact, a look-up table containing those flat files is probably what they’re getting.
Your options for accessing historical, transactional data in its true, transactional form are relatively limited.
- You can use your old system, if you still have access to it, to generate reports and compare them to reports that are run in the new system, for example, periodic meeting registration data from one year to the next.
- Another option is to create a data warehouse that allows an analytics tool to pull data based on a common ID. This is getting into a whole other topic—data analytics, deserving its own blog post—but, for the time being, just know this is a possibility.
- Or, you can forget about comparing transactions in your new system to historical data and simply start fresh. Easier said than done? Actually, we’ve found that most people say they’d like to be able to access historical data, but they don’t actually follow through. Start anew and begin comparing backwards once you have enough data in your new system to make it worthwhile.
Questions to ask—before conversion—about the value of your data
Instead of worrying about how your association will move all your ‘indispensable’ data to the new system, think about the value of your historical data. This is a great opportunity to ask the hard questions about your data hoard.
- How does historical data support the goals of your organization?
- What is the business need for historical data?
- What is the quality of historical data?
- Does the association have historical reports saved that can be referenced in the future, for example, sales or registration counts?
- Will your old system be available for 6-12 months for reference?
- Can historical data be considered as part of Phase 2?
If you’re not sure where to start the discussion, consider leaving this historical data behind:
- Complete invoice history. Rebuilding a full invoice in a new system is the most expensive type of transaction because it requires vendor time to configure and staff time to review and edit.
- Orders (sales or registration) from past years. If you need a history of the exhibit sales that contributed to an exhibitor’s priority points (for preferential booth selection), convert only the value of the points, not the underlying data.
- Customer/member service activities. Decide whether you really need to convert the activities that track contact with customers/members.
- Notes.
Data conversion is time-consuming, therefore, it’s expensive. If you wish to convert transactional data, you need to have that conversation with your vendor early in the selection process, include those requirements in your RFP, and find out what the vendor will need from you to convert that data.
Preparing for data conversion
Considering the expense of data conversion and your vendor’s ability to convert data, you’ll want to make the data migration process as simple as possible. Develop a data migration map to ensure data is moved to the correct location in the new system.
Discuss the best option to clean up data with your vendor. In some cases it’s better to clean up your data before conversion. But in other cases, the new system may have better tools to evaluate and purge information. If you’re pulling data from more than one system, make sure there’s a common unique identifier used for the same entity across systems.
If possible, time your AMS implementation to coincide with the end of a fiscal year or quarter and only bring the balances of any open transactions over to the new system. What’s behind the open transactions may not convert, but the balances will, so your new system will be at the same point as your old system.
Moving to a new AMS provides an opportunity to reevaluate your data management practices. If you had to clean up dirty or duplicate data, you should consider implementing more stringent data entry procedures and business rules. You may even decide to stop collecting and maintaining data you don’t (or no longer) use.
Data conversion is a huge implementation issue requiring a good deal of forethought, planning, and expectation-setting in order to minimize frustration. It may be a necessary evil, but it need not be as evil or even as necessary (at least, not all of it) as you might think.
