Migrating Data to AIM Fund Management Software or to Salesforce.com
By Rick Wills
Moving your organization from an Excel-based deal and fundraising log and Outlook-based CRM to a software for private equity or venture capital system is a big project. Certainly we’ve made a living out of guiding clients through this process that can include everything from setup, to change management, to training. Aside from this setup necessary to make the system match your business processes, you need to load it with the data you are already using today. When this data is already stored in Excel or Outlook, the process is not always easy, but it is relatively straightforward.
When things get much more complicated is when you’re moving from one software system to another rather than simply moving data from Excel. In this case, there is the added complexity of getting data from one system into, in our case, AIM or any other Salesforce.com-based system.
Luckily, we’ve had quite a bit of experience performing these types of data migrations and we’ve had good success using this (highly-simplified and admittedly geeky) process:
First off, if you are using some other CRM solution, then you have a bit of an advantage over Excel and Outlook because the data is already stored in structured data in a database. If this underlying database is relatively robust one such as Microsoft SQL Server, MySQL, Oracle, or similar, then its not difficult do a little digging and query the database to tell you exactly where and how your data is being stored.
We’ve written routines for MS SQL Server (links to SQL and VBA source code is below) that run through a database table and tell us thing such as:
- The name and type (number, text, etc.) of each field
- The longest data stored in each field
- How many times the field is blank
- How many records exist
- Where any relationships go
The next step is to poke around to see what is stored in each table so we understand generally where the things we want are stored. Once we have a good understanding of how things are mapped out, we typically use MS Access to build export queries that unload the data and do any rudimentary cleaning. During this process, we love to dump excess white space (blank lines and such) in big text fields using a custom VBA function (again, source code is below). We also build smart multi-line addresses and merge together fields using other custom VBA.
From Access, we take the time to name the output fields matching the exact API names of the fields in Salesforce.com. This allows the Salesforce.com Data Loader to automatically map and load the export files we create. Perhaps the best tip we can offer during this entire process is to look at the RSSBus connector for Salesforce.com. This little ODBC beauty allows you to retrieve Salesforce.com IDs and create tables in Access. But use caution doing much more with the connector–the Salesforce.com API and RSSBus has a hard time with bulk updates and any complicated queries.
After each export from Access -> Import to Salesforce -> Create ID Map in Access cycle, you will have the keys you need to build the next Export and have it link together records properly.
Perfectly simple and easy, right? If you are a private equity or venture capital fund manager and looking for a fund management CRM system, we think AIM is the best there is. And we’d be happy to help you move over all your data.