Author Topic: Provide better way to integrate recurring payments (Read 2650 times)

artfulrobot.com · December 06, 2013, 04:37:22 am

All organisations that I have worked with already have several sources of regular income from supporters. Be this Direct Debit (which may be through any number of suppliers, each with a unique API/exports), standing order (which may be downloadable from their banks, again in different formats), Just Giving, Virgin Money, and dozens of other suppliers.

CiviCRM's current recurring payment functionality seems to be intrinsically bound to CiviCRM having a payment processor set up that it can tell to make payments. This does not help when organisations have 50 records from here, 100 from there and 1000 from somewhere else, each month to import - they can't be expected to change all their suppliers to use one of the few payment providers that CiviCRM supports; it would probably lose them supporters and may not be the most financially effective plan, either.

CiviContribute's import function seems pretty limited - seems it needs to know the person's email (rarely included in bank statements/DD provider's data), or Civi ID (obviously not going to be there) or a single external identifier ID (not useful when you have more than one external provider and therefore multiple possible external Ids).

For my first client I wrote a new CiviCRM "Component" that added a Recurring Payments tab to people's records. This used existing CiviCRM tables to list recurring payments. Editing one you could select a supplier/service/source (DD/SO..) and each of those would then have fields needed to match the data from the source records with the correct CiviCRM contact's recurring payment record - so this might be an external ID specific to that contact and that provider, or for standing orders it was sortcode and account No. It also kept payment type, amount etc. It included an import tool to take whatever the datasource was, match it up, and create contribution records using the RP as a template. Each provider needed some custom code writing, but this was done in a pluggable way and allowed flexibility needed to work with the sometimes crazy dataformats you get from certain financial service providers!

So that worked for them. But it was a lot of work and I had really been hacking lots to get it to play nice. Since then CiviCRM's moved on lots in certain directions and I would worry about keeping this code alive going forward. As I'm now faced with a similar task I'm looking for advice on how to implement this. I can repeat that or do something new.

Another idea that is less elegant but uses more ready-made CiviCRM stuff would be to add custom fields somewhere that provide provider and provider-external-id pairs, then build a pre-import processor for the datafiles which took the data, matched it against these new fields, appended a CiviCRM Contact Id column and spat it back, ready for the user to feed in a 2nd time, to the CiviCRM contributions import thing. The fields could be added as a contribution fieldset which might have certain advantages (e.g. could copy payment purpose)

Any advice?

Donald Lobo · December 06, 2013, 09:29:49 am

hey artfulrobot:

since you are trying to integrate different data streams with different fields etc, wondering whether something like this might make your job a lot easier:

https://civicrm.org/blogs/capo/new-tool-civicrm-data-integration

i've also asked a couple of UK providers (where this problem might be more prevalent) to add their two cents

lobo

xavier · December 06, 2013, 12:58:40 pm

CiviBanking seems a good option (at least for matching existing contribs, not sure about creating new one).

Pinging the Project 60 crew

Parvez · December 06, 2013, 01:03:57 pm

Hey artfulrobot,

We should definitely touch base ASAP. We do the same thing you've talked about and we have a couple of existing extensions we use to make this happen. Our goal is to move over to CiviBanking once we've had a chance so as to leverage the good work thats been done there. As Xavier has highlighted, this would be the preferred option and in my belief is the exact scenario that CiviBanking was created for!

Let me know if you'd like me to show you some of what we do and maybe we could jointly look at leveraging CiviBanking?

Thanks

Detlev · December 06, 2013, 11:59:20 pm

Yes - this is exactly the use case CiviBanking is created for!

Here https://github.com/Project60/CiviBanking you can find more about, download the code etc. ...

endres.systopia · December 09, 2013, 07:24:59 am

Just to repeat the same thing once more: Yes - this is exactly the use case CiviBanking is created for!

CiviBanking aims at giving you a framework and a UI for the workflow you describe: importing bank statements (or similar data) and reconcile that information with CiviContribute. We are currently implementing a pilot with two customers, and will definitely post a detailed report once that's done.

In general, the framework allows you to use one (or a combination) of the built-in modules that come with CiviBanking, or to easily implement your own reconciliation code by subclassing the abstract class called "matcher".

artfulrobot.com · December 12, 2013, 06:56:54 am

Hello all,

thanks for all the responses. I'll take a look at CiviBanking ASAP.

Rich

artfulrobot.com · December 13, 2013, 02:22:26 am

CiviBanking looks like a well thought-out system for specific banking cases. However I'm not sure that CiviBanking is going in the right direction for the cases I know about, and I feel doubtful that I could contribute enough to it to deliver the required functionality to my client charity on time.

CiviBanking from what I've understood is based on:

Contact data:
- Core: Contacts have memberships; Contacts have specific pledged contributions.
- CiviBanking: Contacts own Bank Accounts
External 'Bank' (in quotes because often not actually a bank) issues a Statement containing Transactions.
Transactions are matched (automatically or interactively) to contacts based on the Contact data above. This creates or updates contributions.

My use cases, none of which seem possible with CiviBanking:

Case 1: reconciling standing order payments from our bank

The data we receive from the bank could be matched to contacts through their sort code and account number. However we would have no way of knowing what those payments were for because there is no way in CiviCRM AFAIK to describe the expectation of an indefinite regular payment. We could perhaps re-purpose certain CiviMember concepts to fudge this but this leads to a very complex UX where everything needs re-interpreting.

Case 2: reconciling statements from our Direct Debit processing company

The data we receive includes references for particular mandates, not accounts. i.e. one contact may feasibly have more than one mandate. Multiple bank accounts are fine in CiviBanking, but it is not an "account" so other concepts don't apply. Likewise the transaction batch concepts don't really fit e.g. there is no opening or closing balance for these "statements". All this also falls at the hurdle of Case 1, too - there being no way to say "expect a £5/mo contribution from Rich" so no automated way to enter different types of contribution. The data also includes failed payment attempts which would need special filtering.

Case 3: importing data from a fundraising site like Just Giving, Virgin Money, ...

Here we have a fundraiser and donors, either of which might not be a contact known to CiviCRM at the time of import. These would have to be run through CiviCRM Contact import twice (once for fundraisers, once for donors) and also adjusted to allow for the anonymous donations(!), then some sort of 'bank account' import which stores the fundraiser User Id that the external service uses, then the matching.. well this is very complex!

Please let me know if I've misunderstood (I'd love to be wrong on this because it's a bit of a recurring nightmare situation!). I think the key thing is the generic case for recurring payments in CiviCRM is missing - if that existed CiviBanking, with a few allowances for non-bank data, could do a lot more.

artfulrobot.com · December 13, 2013, 02:47:49 am

Quote from: Donald Lobo on December 06, 2013, 09:29:49 am

https://civicrm.org/blogs/capo/new-tool-civicrm-data-integration

lobo

Hey Lobo. I had seen that, yes. It's half a gig compressed, and I want to build something that makes things easier - I think this 2nd app will frighten my clients (and me in terms of server space/maintenance)! Thanks, though.

xavier · December 14, 2013, 08:21:50 am

Quote from: artfulrobot.com on December 13, 2013, 02:22:26 am

Case 1: reconciling standing order payments from our bank

The data we receive from the bank could be matched to contacts through their sort code and account number. However we would have no way of knowing what those payments were for because there is no way in CiviCRM AFAIK to describe the expectation of an indefinite regular payment. We could perhaps re-purpose certain CiviMember concepts to fudge this but this leads to a very complex UX where everything needs re-interpreting.

recurring contribution is made to model that indefinite regular payment (that's what we use for sepa direct debit for instance).

When you receive the amount from the bank, you can check if there is a recurring contrib and create a new contribution linked to it based on the bank info.

Quote from: artfulrobot.com on December 13, 2013, 02:22:26 am

Case 2: reconciling statements from our Direct Debit processing company

The data we receive includes references for particular mandates, not accounts. i.e. one contact may feasibly have more than one mandate. Multiple bank accounts are fine in CiviBanking, but it is not an "account" so other concepts don't apply. Likewise the transaction batch concepts don't really fit e.g. there is no opening or closing balance for these "statements". All this also falls at the hurdle of Case 1, too - there being no way to say "expect a £5/mo contribution from Rich" so no automated way to enter different types of contribution. The data also includes failed payment attempts which would need special filtering.

probably again recurring contribution.
for the direct debit via sepa, we have a mandate object

Quote from: artfulrobot.com on December 13, 2013, 02:22:26 am

Case 3: importing data from a fundraising site like Just Giving, Virgin Money, ...

Here we have a fundraiser and donors, either of which might not be a contact known to CiviCRM at the time of import. These would have to be run through CiviCRM Contact import twice (once for fundraisers, once for donors) and also adjusted to allow for the anonymous donations(!), then some sort of 'bank account' import which stores the fundraiser User Id that the external service uses, then the matching.. well this is very complex!

I don't know enough about these and what files they generate. I'd probably write a custom import. at least for fundraiser/donor and a bunch of custom fields to store the external ids if you have more than one external system

Quote

Please let me know if I've misunderstood (I'd love to be wrong on this because it's a bit of a recurring nightmare situation!). I think the key thing is the generic case for recurring payments in CiviCRM is missing - if that existed CiviBanking, with a few allowances for non-bank data, could do a lot more.

Recurring contribs exist.

What is missing is a UI to create these recurring contrib or to add manually a contribution to a recurring contrib.

what we did was to create a "dummy" payment processor and create the recurring contribution from there.

X+

xavier · December 14, 2013, 08:30:50 am

Quote from: artfulrobot.com on June 10, 1970, 05:35:16 am

Hey Lobo. I had seen that, yes. It's half a gig compressed, and I want to build something that makes things easier - I think this 2nd app will frighten my clients (and me in terms of server space/maintenance)! Thanks, though.

It's on the client that this software runs. on the server, you don't have to install anything else (beside adding an api key to the user for the REST interface)

Once installed and configured, it looks very simple. I personally haven't tried, so not sure how much of a steep curve.

Donald Lobo · December 14, 2013, 08:43:22 am

1. If there are changes / improvements to the recurring contribution section that will make integration easier, we should spec it out and see if someone will write the needed code and tests for it

2. Rich, i think if you set up the systems right, your client wont be seeing any of the data integration stuff. The csv's would get magically imported via nightly cron jobs

lobo

artfulrobot.com · December 14, 2013, 12:25:45 pm

Quote from: xavier on December 14, 2013, 08:21:50 am

What is missing is a UI to create these recurring contrib or to add manually a contribution to a recurring contrib.

what we did was to create a "dummy" payment processor and create the recurring contribution from there.

Good tips, I'll look into doing it that way. Do recurring payments show up anywhere in the UI though? I know the tables exist for recurring payments (I used them 3 years ago in my implementation of a Recurring Payments tab).

Thanks for the pointers and help everyone. With the tables being there and with RPs being implemented with payment processors I feel it's so nearly there.

For the Just Giving (etc) stuff, I think a custom importer that just uses the API is probably the way to go.

CiviCRM Community Forums (archive)

News:

Author Topic: Provide better way to integrate recurring payments (Read 2650 times)

artfulrobot.com

Provide better way to integrate recurring payments

Donald Lobo

Re: Provide better way to integrate recurring payments

xavier

Re: Provide better way to integrate recurring payments

Parvez

Re: Provide better way to integrate recurring payments

Detlev

Re: Provide better way to integrate recurring payments

endres.systopia

Re: Provide better way to integrate recurring payments

artfulrobot.com

Re: Provide better way to integrate recurring payments

artfulrobot.com

Re: Provide better way to integrate recurring payments

artfulrobot.com

Re: Provide better way to integrate recurring payments

xavier

Re: Provide better way to integrate recurring payments

xavier

Re: Provide better way to integrate recurring payments

Donald Lobo

Re: Provide better way to integrate recurring payments

artfulrobot.com

Re: Provide better way to integrate recurring payments