Tales from a Blackbaud Kintera Conversion to a Drupal CiviCRM Solution - Part 2 - Converting Transactions

Hey gang sorry it's taken me so long to get back to you but we've been busy slogging through a few outstanding issues.  For those of you who are currently in the throes of your data conversion here are a few quick words of advice.

1. Set up a local site for your data conversion so you don't run into any restrictions on how many records you can import at one time on your server, otherwise, you will spend a lot of time creating many, many small text files.

2. Learn as much as you can about the database tables and how many tables make up a single transaction. You will not be able to import all of your transaction data through the admin interface alone.  I will explain about this in detail further.

3. In order to convert past events you must create separate event records for every event. There is no way to get around this that I can see. We tried to avoid this when we converted to Kintera only to be faced with it again upon converting to CiviCRM.  We did the homework this time and now they can see what events folks attended all the way back to 2005.

4. Membership data is tricky to convert and we had a combination of several years of fixed membership data versus one year of rolling membership data. Add to this, trying to launch a seven effort renewal reminder program and I have a few more gray hairs than before.

5. Be careful about using Kintera's transaction id as your transaction key.  It will work except when you have multiple registration amounts within a single transaction, e.g., a member brings 2 non-member guests.  The transaction id will be the same for all pieces of this transaction.  You need to sum the amount by transaction for the contribution table and create a translation table for the parts of the transaction to create the right label for the event record.

6. Do use transaction id as a key for all straightforward single transactions.  I put this id in the source field for both the contribution record and the matching event or membership record.  I imported the records using the interface and then dumped out the civicrm_contribution and civicrm_membership or civicrm_participant tables.  In MS-Access, I matched the records up by transaction id and created a table with civicrm_contribution_id and civicrm_participant_id or civicrm_membership_id and then appended these records to the corresponding civicrm_membership_payment or civicrm_participant_payment table.  This is how you link the contribution records to their membership or event record.  It would be nice to be able to do this in the interface.  Idea off top of my head.  Add a key field to all tables so you would not have to use source which is used in other ways.  Import both halves of the transaction in via the interface, then run a process to find the matching records and create the appropriate payment records.

7.  For the event records, this was a bit trickier when someone paid for multiple tickets or levels.  In this instance, the contribution record stores the total amount paid but the detail of exactly what was purchased is saved in a text label in the civicrm_participant record.  Also, there is a specific separator that looks like a bunch of 000s and a 1 that you must use in this field and you must also update a table via PHPMYAdmin called civicrm_line_item.  This contains all of the detail of the # of items purchased which links back to the civicrm_participant table.

More to come as I find the time....as always feel free to contact me directly if you are faced with a specific timely issue.  Will provide whatever guidance my memory can recall..:-)