Author Topic: Recurring member payments imported from CSV (Read 1466 times)

leemaguire · September 26, 2012, 09:41:01 am

I work for a small non-profit UK campaign group that is primarily supported by regular (monthly/annual) contributions via standing order, direct bank transfer and direct debit. Payment details are currently received as CSV files and imported into an in-house membership management applications.

We're looking into transitioning from the in-house app to CiviCRM. One reason is that it's usable to cope with a member making a contribution from, say, two different accounts. Work has been done to export the data from the existing app into a form that can easily imported into CiviCRM. However since the CSV files we receive are not indexed by name or email address they cannot be imported directly.

We currently need a mechanism to import these CSV files that is able to
a) store and use identifiers supplied in CSVs with the relevant member records (these mappings can be exported from our existing app);
b) provide an interactive mechanism for resolving records that cannot be linked to a single member

I've spoken briefly to Michael McAndrew who has suggested that additions to the current import process could be made, or that a pseudo Payment Processor that accepted uploaded CSV files might be the cleanest way of implementing this.

Does this seem like the best approach to the problem? Are there any existing CiviCRM developments that provide similar or equivalent functionality?

Thanks.

(While we can eventually move to a supported Direct Debit Payment Processor, I don't anticipate any usable interface mechanisms being available from our bank in the short term.)

JonGold · September 26, 2012, 02:23:08 pm

Hi Lee,

I think what you need is already available in CiviCRM. Do I understand you correctly that when you say, "Payment details are currently received as CSV files", the payments have already been made? That is, you don't need CiviCRM to actually process the payments - you need to record transactions that have already taken place.

If so, you can use the (existing) "external identifier" field. You can populate it with the identifier from your existing app. Then, simple import payment records using the external identifier as your key.

I'm not sure what qualifies as an "interactive" method of resolving the errors, but the CiviCRM approach is actually quite good.
If you upload 12 payments, and 2 of them don't have a corresponding external ID, at the end of the export it will say, "10 records successfully imported, 2 not successfully imported. Click here to download records with errors". When you click, you'll get a CSV with just the two problem records, and an additional column that explains why the records weren't imported. A user can then look up those contacts and fix them.

All in all, I think that you shouldn't need any additions to the current process!

Michael McAndrew · September 27, 2012, 02:48:41 am

Hey there,

as far as i know (and please correct me if I am wrong) I think a couple of issues / areas that could be improved in the current system that org wants are:

* one contact should be able to have multiple payment references (which is why I thought recurring contributions might be useful here)
* contribution import does update memberships (not sure if the membership structure is defined enough for this to be an issue - lee - can you elaborate?)
* ability to track missing payments, i.e. we were expecting a payment from this contact but we did not get one.

Quote

provide an interactive mechanism for resolving records that cannot be linked to a single member

the contact import process does an OK job of this, but it can get fiddly / could certainly be improved. One approach is to work with / test the current import process for a while, you could then provide some specific feedback on how it can be improved.

Parvez · September 27, 2012, 05:08:11 am

Hi Lee

We have implemented a payment importing process for Leukaemia Lymphoma Research which does this sort of thing i.e. allowing import of financial information with the ability to manually match contact records etc. They have various mechanisms of importing so a contact may have differing references in each of the systems so the external identifier approach mentioned previously wont work.

If your interested let me know and we can have a chat about it.

Thanks

JoeMurray · September 28, 2012, 01:10:40 am

I think Parvez's method when released to the community would be the best choice - if I recall correctly it is working well for Parvez's initial client but has not yet been packaged and released.

Eileen has also extended Drupal's Migrate module to make it easier to import into CiviCRM in various releases of her CiviMigrate module (http://fuzion.co.nz/content/migrate-2-civicrm-api-3-civimigrate and check Google).

leemaguire · September 28, 2012, 06:22:47 am

Yes, using a value for an external identifier is problematic for various reasons. There needs to be a level of intelligence in matching records to members.

An example of a CSV line we might have to process is

date	method	bank ref	customer ref	credit	debit	additional info
2012-01-01	Faster Payment	SOMETHING 123	RANDOM STRING	100		ORIGINATING ACCOUNT - 88888888888

The bank ref string is only unique per bank account, not globally.
Both the bank ref and customer ref are usually some form of the member name, but not always.
The customer string is chosen by the customer, clearly not usable as an identifier.
The identifiers are, surprisingly, internally variable. Occasional changes in spacing, for example.
The additional info string is not unique (we have different members paying from the same account, sting content cannot be relied on)
People change bank accounts
Members can have more than one bank account

However, we can check if the "bank ref" and the "customer ref" match existing "bank ref" and "customer ref" combinations. If not we can check if the originating account is listed. And if we can't see anything that matches it, we need to flag it for manual checks. (We've been pushing people towards using Direct Debit for payment since that makes it far easier to uniquely identify members.)

And on top of that, as well as membership payments we also want to be able to solicit donations for specific projects from our members - which often won't come from the same source.

(I'd love a solution that involves changing banks, but sadly that isn't on the cards.)

leemaguire · September 28, 2012, 06:57:33 am

Our membership payment logic is

People sign up to be members and tell us how much they'll pay a year/monthly (usually £5-10 p/m)
Payments that match this expectation are recorded as membership payments.
Payments that don't match this expectation are manually checked. And we either adjust the expectation, or (if membership is already paid) it is recorded as a donation.
Resolving these occasionally involves contacting members via email/phone - for which having contact details at hand would be critical.

The actual amount people pay is not as important as being able to identify that payments have stopped. People under changing financial circumstances can adjust their payments - we understand this when people adjust to pay use less, we tend not to complain when people pay us more.

We've had a situation where a member (deliberately) set up recurring payments to us from two different bank accounts (something our existing system couldn't cope with).

CiviCRM Community Forums (archive)

News:

Author Topic: Recurring member payments imported from CSV (Read 1466 times)

leemaguire

Recurring member payments imported from CSV

JonGold

Re: Recurring member payments imported from CSV

Michael McAndrew

Re: Recurring member payments imported from CSV

Parvez

Re: Recurring member payments imported from CSV

JoeMurray

Re: Recurring member payments imported from CSV

leemaguire

Re: Recurring member payments imported from CSV

leemaguire

Re: Recurring member payments imported from CSV