This could have to do with the way we select which rows to export each night.

We only export rows where the contact has been updated in the last week, and we calculate this field as part of the export. If a donor's recurring donation has silently stopped (we just don't get any more donations) that wouldn't trigger them to show up in the export. I'll take a look at the example CIDs and see if their contact record was marked updated around the time the donations stopped.

The number of recurring donors is low enough that we should be able to do a full-db search for ppl with supposedly-active recurrings and no donations within the last 45 days.

May be a symptom of https://phabricator.wikimedia.org/T293945

936002 - 'active' paypal recurring but no donation in a while
75603 - 'active' paypal recurring but no donation in a while

65571 has their recurring contribution marked as inactive and a recent update on 8/12, no longer marked AF_has_active in Acoustic
170753 - recurring is marked 'Cancelled', no longer marked AF_has_active in Acoustic
1683114 - two recurrings, both in status 'Cancelled', but IS still marked as AF_has_active in Acoustic

1683114 shows the recurrings were both cancelled on Jan 3rd, and their Acoustic record shows it was last modified on Aug 5, 2022, 4:13 PM.

Maybe the Acoustic last-modified date isn't always because they were in an export file?

In the calculated table we use to keep track of who has an active recurring, 1683114 is correctly indicated as NOT having one. Their contact record's modified date is the date of their last donation though, not the date of the recurring donation cancellation. So this is indeed looking like a symptom of T293945 as Eileen indicates.

Change 826410 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/tools@master] Silverpop has_recur: require donation within 60 days

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

Thanks both. I'm happy to search for more cids after T293945 is resolved if the issue persists. Let me know if that would be helpful.

@Eileenmcnaughton if we merge the patch on this ticket, I think we can get the AF_ rows correct in Acoustic by just updating the modified_date on all contacts with a contribution_recur row. Would that be too blunt an implement?

The patch on T293945 would keep them up to date, but we're still missing one more piece for the 'quiet quitters' - maybe a daily cron job like you suggested.

Oh hmm, with that cron job and the patch on the other ticket, I guess we wouldn't need the patch on this ticket. And that recurring job could probably be a chained API call if I can figure out how to write it.

The API call syntax is escaping me so far, but this SQL ought to work: That last AND clause is to stop it cancelling the recurrings with no donations yet because they're just starting.

UPDATE civicrm_contribution_recur r
LEFT JOIN civicrm_contribution c on r.id = c.contribution_recur_id AND c.receive_date > DATE_SUB(NOW(), INTERVAL 60 DAY)
SET r.contribution_status_id=3, r.cancel_reason='60 days or more since last donation', r.cancel_date=NOW()
WHERE c.id IS NULL
AND r.contribution_status_id NOT IN (3,4)
AND r.start_date < DATE_SUB(NOW(), INTERVAL 60 DAY);

I could get that running on a regular basis just by echoing the SQL and piping it into drush @wmff civicrm-sql-cli, if that doesn't sound too ugly.

Change 828105 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/tools@master] Exclude Completed recurrings from has_recur

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

Change 828105 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Exclude Completed recurrings from has_recur

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

Change 828129 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/crm@master] Add ContributionRecur::cancelInactives API4 Action

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

Change 826410 abandoned by Ejegg:

[wikimedia/fundraising/tools@master] Silverpop has_recur: require donation within 60 days

Reason:

Going with Iecea7dd3dd8c1043f76d3d6e3fb1ff instead to fix the status in Civi

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

Change 828129 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add ContributionRecur::cancelInactives API4 Action

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

Change 830266 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/crm@master] Cancel inactives even with a cancel_date

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

Change 830266 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Cancel inactives even with a cancel_date

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

Just noting I tested the recurs we expect to see cancelled two different ways & got 10592 - so the sched date is reliable - except for the ones with a null date as found on the call - there should be another ticket for that

select count(DISTINCT cr.id), max(receive_date) as latest, cr.id, cr.contact_id
FROM civicrm_contribution c
         INNER JOIN civicrm_contribution_recur cr ON cr.id = c.contribution_recur_id
    WHERE cr.next_sched_contribution_date  < '2022-07-07'
      AND cr.contribution_status_id NOT IN(1,3,4)
     HAVING latest < '2022-07-07'
     ;


select cr.id, cr.contact_id, cr.create_date, cr.contribution_status_id, cr.next_sched_contribution_date
FROM civicrm_contribution_recur cr
WHERE cr.next_sched_contribution_date  < '2022-07-07'
  AND cr.contribution_status_id NOT IN(1,3,4)
;

After deploy, needs to confirm the date and add this cmd drush @wmff cvapi ContributionRecur.cancelInactives version=4 days=<discussed or use 60 as default> to cron

OK, this is deployed and scheduled. The first run set 10,685 rows with no donation in the last 60 days to cancelled status, and the associated donors should be exported in the next Acoustic export.

Thanks @Ejegg ! I ran the query I had from before to see what came up - the result is 32,617. However, I noticed though that last modified date is a mix of June 23, 2022 and August 5, 2022 so these records haven't been updated by Civi from this morning. I can check again tomorrow, but do you think we'll need to do a full export?

I vote for a full export just on the basis of doing one occassionally anyway - might be a new sub-phab though

Ok, let me know if I should create a new sub-phab for that. We do launch emails on Monday for our big fundraiser, which is soon, so I wonder if we should run a full export during maintenance week (Sep 26-30). What do you both think?

Also quick follow up after checking today - it seems the 32k false positive recurring records I see in Acoustic haven't been updated by the export file yet. @Ejegg @Eileenmcnaughton

@KHaggard darn! I'm looking at the CiviCRM database now, and sure enough it looks like most of the contacts I expected to be in the export do not have a recent modified_date (this is what gets them into the nightly partial export).

just so we're using the same metric here, can you tell me what condition you're using for 'not current'? I see around 29k rows in our export tables with has_active_recurring true and a latest donation before July 10th, about 2 months ago.

We will look into doing a full update and also make sure that the rolling process to mark stale donations cancelled does get them into the nightly export as we intended.

Thanks @Ejegg ! Oh yeah, I just ran a rough query that I was hoping would be 0 and instead it gave me 32,615:
WHEN AF_has_active_recurring_donation is equal to Yes
AND AF_recurring_latest_donation_date is between Jan 1, 2017 and Dec 31, 2021

I chose 2017-2021 out of habit because that's how we capture our audiences for email fundraising.
I'll run a new query to match how you made yours:

WHEN AF_has_active_recurring_donation is equal to Yes
AND AF_recurring_latest_donation_date is before July 10, 2022

^That gives me 70,144 records 😬

Change 831924 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/crm@master] Fix cancel_date for cancelInactives

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

Change 831924 merged by Ejegg:

[wikimedia/fundraising/crm@master] Fix cancel_date for cancelInactives

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

Thanks all for the full file push. It processed successfully and I reran the query that had 70,144 records before and now it's down to 365 records! Woohoo
Query:
WHEN AF_has_active_recurring_donation is equal to Yes
AND AF_recurring_latest_donation_date is before Jul 10, 2022

Listing out some cids that make up the 365 remaining records in case it's helpful:
11434244
3407526
13841482
15880335
10267874

The pattern seems to be they do have an 'in progress' recurring donation in civi but none of them have processed the donation for August or later. Also, the payment processor seems to be PayPal - at least for those first 5 I looked at so far. Let me know what you think @Ejegg @Eileenmcnaughton

@KHaggard - those seem ok - we are cancelling them 60 days after we last expected a payment - so they should get cancelled in due course. Note that with payment is is more likely that they will skip & restart than with other processors. If their credit card expires then it they will start to fail - but if they update it within paypal it will re-start

11434244 August 9th, 2022 10:22 AM
3407526 August 3rd, 2022 10:44 AM
13841482 August 2nd, 2022 12:27 PM
15880335 August 7th, 2022 10:55 AM
10267874 August 3rd, 2022 12:04 PM

I have spotted something else odd I'm gonna check on before closing this

Ah ok, got it. I wasn't sure of the set up. Thanks for explaining @Eileenmcnaughton ! So those dates you listed are the last expected payment dates, and they won't cancel until 60 days after those dates.

Thanks and let me know if you need anything from me before closing!