Cleaning up legacy contributions so that renewals happen as expected

Question

We have a site that has some legacy membership records with associated contributions that were imported manually (before our time, with a previous provider). We are experiencing problems where these legacy memberships do not renew as expected (new memberships that were set up since that time are working fine).

We went through a round of clean up fixes about two years ago and memberships started to renew as expected but they recently stopped working again after an upgrade.

Our assumption is that CiviCRM has changed the way that it does membership processing again and that it is now relying on a characteristic of the membership and its associated records that we did not address in our initial round of fixes.

We have come across different flavours of this problem on different sites in the past and I am sure lots of you have too. I am aware that there have been different ways of doing the processing and that it might change again in the future. We are keen to do a comprehensive clean up this time and I thought it would be worth getting some thoughts on how to proceed.

Our strategy is to compare DB records for succesful renewals with unsuccessful ones and to all identify differences, and then write scripts to bring them into line as appropriate.

So my first question is What tables should we be looking in?

• civicrm_membership
• civicrm_membership_payment
• civicrm_contribution
• civicrm_contribution_recur

Seem like obvious candidates. So does civicrm_line_item though I am less clear about what we should be looking at in that table and how it connects to memberships and contributions. Are there relevant columns in civicrm_line_item?

Are there other tables I should be looking in?

The following tables seem unlikely to be relevant but I add them here just in case and am happy to be corrected.

• civicrm_financial_item
• civicrm_financial_trxn
• civicrm_entity_financial_trxn

Once we know all of the tables,

Are there specific ways I should be joining tables when doing the comparison? e.g. the first or last in a set of contributions related to a contribution recur?

What about template contributions? I am not sure if they are relevant in this case as there are no contributions with is_template = 1 but feel free to address this point in your answer :)

Any thoughts on the approach we are taking, and your experiences with / suggestions for alternative approaches are very welcome.

Finally, and in case it is relevant, the payment processor we are dealing with here is GoCardless.

Thanks all!

Answer 1

This is not a fun task, but at least I made a tool to make it pretty.

If you install Uh-Oh troubleshooting extension it includes a diagnosis page at Support » Map Contributions which lets you specify contacts, contributions, recurs, and memberships, and it will graph the links as shown here. It's not GoCardless specific. It's helped me identify a problem with non-renewing memberships.

It shows how all the tables (yes, line items are vital to membership) that are related to a recurring membership, and you can check that they're wired up correctly.

Example

Here is a wrong un':

We can see here that most of the contributions' line items relate to the contribution table, not to the membership table. This means they're not right.

After some fixing of the SQL data, it looks like this:

Note how now the line items all know they're for memberships, and have a direct yellow arrow to the membership. This is now correct.

The tool is very useful for (a) documenting before and after fixes with the table IDs, and (b) learning how everything joins up.

On a job I had, I used SQL like the following to fix one category of tangles, but I only include this as an example: Don't run this SQL on your data! It contains hard coded IDs that won't be right for your use case. Here I correct the line items that belong to membership contributions, but are themselves linked directly to civicrm_contribution instead of civicrm_membership. I have to change the table, the entity_id and the price_field_value_id and price_field_id - the price field ones must be for the membership type in question.

Without those all correct, membership renewal won't work.

UPDATE civicrm_contribution cn                                                                                              
   INNER JOIN civicrm_contribution_recur cr ON cn.contribution_recur_id = cr.id                                            
   INNER JOIN civicrm_membership m ON m.contribution_recur_id = cr.id                                                      
   INNER JOIN civicrm_line_item li ON li.contribution_id = cn.id                                                           
SET li.entity_table = 'civicrm_membership',                                                                                 
li.entity_id = m.id,                                                                                                        
li.price_field_value_id = CASE m.membership_type_id WHEN 1 THEN 2 WHEN 2 THEN 17 WHEN 3 ThEN 8 WHEN 4 THEN 9 else null END, 
li.price_field_id = CASE m.membership_type_id WHEN 1 THEN 2 WHEN 2 THEN 10 WHEN 3 ThEN 5 WHEN 4 THEN 5 else null END,       
li.membership_num_terms = 1                                                                                                    WHERE li.entity_table = 'civicrm_contribution'                                                                         
;

A final tip, one that's caught me out before is: A membership has a field that points to the (singular) current ContributionRecur record. Over time, it's possible that multiple recurs have paid for a membership, and this can lead to trouble when the membership points to the completed recur, not the current recur.

I couldn't fix everything by SQL, as I recall, I think I ended up writing some PHP scripts to do the more tricky things.

Backing up all the tables you're going to touch before and after each programmatic change is highly recommended.

Edit: what's the correct wiring up for a membership?

I think this:

• Contributions (CN) that are part of a recurring contribution (CR) need to point to them: cn.contribution_recur_id = cr.id
• A Membership (M) should be linked to a recurring contribution (CR) : m.contribution_recur_id = cr.id
  • Note: if a membership has been paid my more than one CR, e.g. because one CR completed/failed/finished and was replaced by a new one, then the old CR will not be referred to by the membership as it will be pointing at the new CR (via the M.contribution_recur_id field)
• A Contribution that pays for a membership should have
  • a Line Item (LI): li.entity_id = m.id with li.entity_table = 'civicrm_membership'
    • The Line Item must point to a Price field price_field_id and value price_field_value_id for the correct Membership type
  • an entry in the memberships-payments link table (this is slated for deprecation but is still used as of 5.49 at least). This is shown by the dashed line in the 2nd diagram.
• M, CR, CN records all point to a contact via contact_id, obvs.