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;

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

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

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

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;

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