Page MenuHomePhabricator

Civi recurring donation cancel metric request
Closed, ResolvedPublic

Description

It would be really helpful in assessing the value of third-party autoupdaters if we could track the portion of recurring donations canceled manually by DS versus the portion canceled by PSP audit.

When a human agent cancels a recurring contrib, the Change Log shows:

As far as I can tell, when a recurring donation cancels because of a card issue, the Change Log does not show an entry.

  • Is the above an accurate summary?
  • Is there a unique tag or action for recurring donations that cancel without staff input?
  • Is there a way to query Civi for the total number of recurring cancellations by month, and differentiating the human-processed versus machine processed?

Details

Related Gerrit Patches:
wikimedia/fundraising/crm : masterAdditional comments in cancel block
wikimedia/fundraising/crm : masterChange update of cancel_reason to use batching
wikimedia/fundraising/crm : masterAdd Recurring Contribution pivot chart.
wikimedia/fundraising/crm : masterUpdate existing recurring records to hold payment processor ids
wikimedia/fundraising/crm : masterUpdate extended report
wikimedia/fundraising/crm : masterStart recording the payment processor id for recurring contributions
wikimedia/fundraising/crm : masterAdd a check to fix blank location types
wikimedia/fundraising/crm : masterUpdate primary location fix to also fix when more than one option
wikimedia/fundraising/crm : masterUpdate query to not treat blank location types as duplicate

Event Timeline

MBeat33 created this task.Mar 18 2019, 10:25 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 18 2019, 10:25 PM
MBeat33 updated the task description. (Show Details)Mar 18 2019, 10:43 PM

I had a go at getting some aggregates from existing data on staging. I added an index first & was able to get this data using a query that turned out to be surprisingly quick (< 20 seconds)

MariaDB [dev_civicrm]> `ALTER TABLE log_civicrm_contribution_recur ADD index(contribution_status_id);`
Query OK, 0 rows affected, 4 warnings (16.34 sec)
Records: 0 Duplicates: 0 Warnings: 4;

MariaDB [dev_civicrm]>

SELECT year, month, count(*) as total_cancels, sum(is_user_action) as user_initiated, (count(*)  - sum(is_user_action)) as system_cancels 

FROM (  
  SELECT cr.id, cr.contact_id, IF(ISNULL(log_user_id), 0, 1 ) as is_user_action, YEAR(log_date) as year, MONTH(log_date) as month  
  FROM log_civicrm_contribution_recur cr 
  INNER JOIN (  
     SELECT id, min(log_date) as cancelled_date
     FROM log_civicrm_contribution_recur 
    WHERE contribution_status_id = 3 AND log_action = 'Update' GROUP BY id
   ) as cancel_action   ON cr.id = cancel_action.id AND cr.log_date = cancelled_date AND cr.log_action = 'Update' 
) as cancelled_recurs 
GROUP BY year, month;
yearmonthtotal_cancelsuser_initiatedsystem_cancels
2016549286294299
2016648385874251
2016753845344850
2016890756258450
201691268856512123
2016101245953511924
20161162335575676
20161254396614778
2017152387504488
2017248596154244
2017362197305489
2017462457045541
2017546315834048
2017639465463400
2017739575543403
2017838805023378
2017936185253093
20171046136074006
20171147057743931
201712562313434280
20181671515815134
20182580311954608
2018314078121912859
20184671612585458
20185650010785422
2018647067753931
2018744087513657
2018840217533268
2018939936263367
2018108477767084107
20181131865962590
20181241559203235
2019149409933947
2019259659537

34 rows in set (19.19 sec)

So I guess the question is what we do from here - this query is repeatable by people doing sql queries & I'm not leaning at the moment towards storing more data in our system to better support queries but perhaps I should expose through the UI - I potentially in 2 places

  • visible on the contact in some way
  • visible as a report.

I guess I'd like someone to check if they think my query is solid first ... @Ejegg would like your thoughts

These #s look really great, thank you @Eileenmcnaughton !

@Ppena would #s by payment method also be helpful?

also, calling the Oct 2018 #s an outlier due to a bug, possibly related to the checkbox for recurring being on by default on mobiles, makes sense

Ejegg added a comment.Mar 27 2019, 3:14 PM

I think the October 2018 numbers are more related to those bogus cancellations that PayPal was sending us in audit files

Change 499687 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Update primary location fix to also fix when more than one option

https://gerrit.wikimedia.org/r/499687

Change 499688 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Update query to not treat blank location types as duplicate

https://gerrit.wikimedia.org/r/499688

Change 499689 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add a check to fix blank location types

https://gerrit.wikimedia.org/r/499689

MBeat33 added a subscriber: Jksamra.Apr 1 2019, 5:38 PM
Ppena added a comment.Apr 1 2019, 5:43 PM

@Eileenmcnaughton Yes pls, #s by payment method will also be helpful :)

I got the cancel_reason field we discussed added upstream

Change 501128 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Start recording the payment processor id for recurring contributions

https://gerrit.wikimedia.org/r/501128

Change 501132 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Update existing recurring records to hold payment processor ids

https://gerrit.wikimedia.org/r/501132

Change 499688 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Update query to not treat blank location types as duplicate

https://gerrit.wikimedia.org/r/499688

Change 499687 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Update primary location fix to also fix when more than one option

https://gerrit.wikimedia.org/r/499687

Change 499689 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Add a check to fix blank location types

https://gerrit.wikimedia.org/r/499689

opps a few patches against the wrong phab there

Change 506049 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Update extended report

https://gerrit.wikimedia.org/r/506049

Change 501128 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Start recording the payment processor id for recurring contributions

https://gerrit.wikimedia.org/r/501128

Change 506049 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Update extended report

https://gerrit.wikimedia.org/r/506049

Change 501132 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Update existing recurring records to hold payment processor ids

https://gerrit.wikimedia.org/r/501132

Change 507529 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add Recurring Contribution pivot chart.

https://gerrit.wikimedia.org/r/507529

Change 507529 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Add Recurring Contribution pivot chart.

https://gerrit.wikimedia.org/r/507529

Change 507737 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Change update of cancel_reason to use batching

https://gerrit.wikimedia.org/r/507737

Change 507737 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Change update of cancel_reason to use batching

https://gerrit.wikimedia.org/r/507737

@MBeat33 @Ppena

We have now populated cancel_reason for those recurrings historically cancelled via payment processors and are recording information as it comes in now. The ones we backfilled from historical data have the work 'backfilled' in the text. You can see the results in this report

The ones with no reason appear to have been cancelled by DS agents

The other 3 are populated as our scripts react to incoming notifications from now on.

In a future Civi release DS will see the field to enter reasons (once this is merged).

@Ejegg I notice there are no instances where they have been 'ended' & have no cancel date. This seems to mean we have already cancelled them when we get the end notification. I'll put up a code comments patch with this

Change 508969 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Additional comments in cancel block

https://gerrit.wikimedia.org/r/508969

@Ejegg @Jksamra just looking into end dates vs cancel dates. I believe that we are not winding up with recurrings with an end date but no cancel date now. Historically we have quite a few

select id, contact_id, payment_processor_id, end_date, max(cancel_date), contribution_status_id, cancel_reason FROM civicrm_contribution_recur WHERE e
nd_date IS NOT NULL AND cancel_date IS NULL GROUP BY payment_processor_id;

But we did a lot of cleanups late last year - if I filter after those (mid Oct) I only get one

MariaDB [civicrm]> select id, contact_id, payment_processor_id,max( end_date), max(cancel_date), contribution_status_id, cancel_reason,count(*) FROM civicrm_contribution
_recur WHERE end_date IS NOT NULL AND cancel_date IS NULL AND end_date > '2018-10-17' GROUP BY payment_processor_id;

idcontact_idpayment_processor_idmax( end_date)max(cancel_date)contribution_status_idcancel_reasoncount(*)
52815615415462019-01-10 11:40:16NULL1(auto) backfilled automated cancel49

1 row in set (0.60 sec)

(I don't know why this one was un-cancelled)

Perhaps I should do more to null out the cancel reason on the historical ones where a data fix up happened. But I think we can use cancel_date as a filter on these from what I can see

Change 508969 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Additional comments in cancel block

https://gerrit.wikimedia.org/r/508969

@MBeat33 @Ppena @Jksamra We have populated cancel_reason & payment_processor_id into the civicrm_contribution_recur table & you can view the results in the report - what else do you need here

DStrine closed this task as Resolved.May 28 2019, 8:12 PM