- 35: Number of custom fields
- 42: Number of CSV files
- ~19,000: Number of contribution records
- ~42,000: Combined columns in all CSV files
- ~123,000,000: Combined size (in bytes) of all CSV files
At the beginning, we picked a group of contacts that had unique characteristics. Some had multiple addresses, some had recurring contributions, some had a detailed activity history. By picking a subset of contacts, we could follow them from start to finish and help validate accuracy of the import. To be able to compare one-to-one RE and CiviCRM.
- Import basic contact information, locations (addresses, phones), activities, notes, tags, groups and custom fields for individuals and organizations
- Import relationships individuals and organizations
- Import pledges (recurring, one-time gifts, soft credits)
- Import contributions
- Relationships to non-constituents
- Multiple soft credits on pledges and gifts (automatic or manual)
- Multiple payments and installments (regular and irregular frequencies)
- Third party pledge payments and anonymous contributions
- Storing change of address information
- Constituent relationships
1, John Smith, Son, Joe Smith, Daughter, Jane Smith
1, John Smith, Parent, Joe Smith, Parent, Jane Smith
Thanks for sharing!
Interesting, I recently did a similar project with recurring payments (considered a backbone of any UK fundraising database and surprisingly missing in CiviCRM).
I looked at pledge, but I didn't like the way it worked, importantly, it didn't allow open-ended pledges ("here's £5/month from now on"), and the UI for seeing payments inside a pledge is troublesome when you're talking a hundred or so payments for a long standing supporter.
I ended up using the civicrm_contribute_recur table. Basically I used the processor_id and trxn_id field (both long varchars) to store information that would be needed to identify incoming payments. So for Direct Debits, this is a unique reference number; for Standing (Banker's) Orders, this is a sort code, account number and possibly a reference. In those fields also I could store other information that would be used as the template for civicrm_contribute records.
I wrote code for a new Recurring Payments tab, and reconciliation routine that takes monthly .csv files from the bank and Direct Debit service provider and matches them up, creating contribution records as it does it.
This gives my client an easy way to import the hundreds of transactions each month, and a rational, paged, list of contributions on each contact's record, that can be searchable in the normal way.
Anyway, good to hear other people doing similar hacks! And good to hear Civi' taking on Raiser's Edge as it's considered "industry standard" here in UK but I've not been that impressed with it (compared to its price).
Do you know of a direct debit provider in the UK that has a decent API that you could use in recurring payements?
Would be great if you could share the code you used for the direct debit integration.
Am working on a similar integration for DD here using a custom import: https://github.com/michaelmcandrew/gp/tree/master/modules/custom/gpew_custom_import. It requires manual feeding of DD files like yours. It uses custom data table rather than civicrm_contribute_recur (nice idea). Definitley a stop gap at the moment. I'm interested in developing a proper DD integration. Happy to pool resources on that if you have ideas / time / money. You want to get in touch / post your contact details?
We tend to use the api as soon as we have a "not completly trivial" import. Just for the sake of being able to run it several time without the pain induced by the import wizard, if nothing else ;)
Think eileen has been working on a pledge API. In general, when you see a missing api, please discuss it on the forum, either someone is working on it already, or your contribution would be more than welcome.
When you use the word "you", we assume you mean "community at large." :)
During this project we found bugs in the code base, reported them and supplied patches for most. We always report bugs when we see them and *almost* always post a patch. Being active in the forums or writing an API isn't the only way to get involved and isn't always practical. The resources of the person/team/organization for client work must be balanced with their commitment to open source; do what they can knowing the "tangible" contribution won't be the same for everyone.
Yep - I think that's the right interpretation of the word 'you'. We just don't like to miss a chance to try to tell people about how to use the API :-)
Great Blog BTW - I think I forgot to mention that
Indeed, thanks for the clarification. my comment was
1) to point out to other readers that patches welcome, and that adding a new api is not difficult.
2) to promote Eileen pledge api
3) And to thanking you (as in the dude typing on the keyboard, not the community at large :) of your post.
Err. the 3) might have been missing it, so:
Great Blog BTW, thanks for sharing ;)
Yep - the pledge API is OK to use - I think we decided to post it into v2 of the API once I've written some doco (must do that). I actually used a pledgePayment api to link the payments too. The contribute wrapper in civimigrate allows you to pass in a pledge ID (or membership ID) when you map up a contribution & the two get linked and the payment status gets calculated.
I used drush w views & migrate module with an extra civimigrate module which I wrote for the purpose but quite a bit in common between the 2 imports.
Also note you can add soft credits using contribute api
Google Refine is certainly worth looking into... it crossed our radar a couple of months ago after reading about it on HN. Being able to load an instance of CiviCRM somewhere in Google space using GAE would be pretty sweet too. :)
Happy to share our code. Good luck with the Kintera migration.
We are about to begin a migration for a small Educational group with around 10,000 clients currently in BB RE.
This is so helpful and a tribute to OS.
Thanks very much