Page MenuHomePhabricator

Recurring Payments Reporting
Open, NormalPublic

Description

Megan has some requests about recurring payments:

https://trello.com/c/FeEYcx2f/223-fundraising-monthly-recurring-questions
https://trello.com/c/AEigLFqN/222-low-fundraising-recurring-spot-check

It sounds like Matt Walker used to generate these kinds of reports. We don't want to get in the habit of doing these things manually / repeating effort across Matt, me, FR tech. Adam suggested we answer the types of questions Megan raises though "Civi reports".

The report should include:

Health of recurring payments system e.g.:

Number of recurring payments that should've processed on a given day/month
Number of recurring payments that did get process on a given day/month
Number of recurring payments that failed, broken down by reason
Number of canceled subscriptions per month

Stats on recurring payments e.g.

What percentage of the yearly revenue comes from recurring donations?
How much do we raise each month from recurring donations?
How long do our recurring donations last on average? 
What's the average amount we raise in the lifetime of a recurring donor? ,
What's our average recurring amount selected?  
The above three broken down by payment method, year

Related Objects

StatusAssignedTask
OpenNone
ResolvedNone
Resolvedawight
Resolvedawight
Declinedawight
Invalidawight
Resolvedawight
Resolvedawight
Resolvedawight
Resolvedawight
OpenNone
Resolvedawight
Resolvedawight
Resolvedawight
Resolvedawight
ResolvedEileenmcnaughton
Resolvedawight
Resolvedawight
ResolvedEileenmcnaughton
ResolvedNone
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedNone
Resolvedawight
ResolvedDStrine
ResolvedEileenmcnaughton
DeclinedEileenmcnaughton
Resolvedawight
Resolvedawight
Resolvedawight
Resolvedawight
OpenEileenmcnaughton
ResolvedNone
Resolvedawight
Resolvedawight
ResolvedNone
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEjegg
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
InvalidEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
DuplicateEileenmcnaughton
InvalidEileenmcnaughton
ResolvedJgreen
OpenNone
OpenNone
OpenNone
Resolvedawight
ResolvedEileenmcnaughton
OpenNone
ResolvedEileenmcnaughton
Resolvedawight
DeclinedNone
Declinedawight
ResolvedEileenmcnaughton
OpenEileenmcnaughton
Resolvedawight
Resolvedhashar
ResolvedXenoRyet
Resolvedawight
ResolvedEileenmcnaughton
OpenNone
OpenNone
OpenNone
ResolvedEileenmcnaughton
ResolvedEileenmcnaughton
OpenNone
DuplicateNone
ResolvedEileenmcnaughton
OpenNone
OpenNone
Resolvedawight
ResolvedEileenmcnaughton
Resolvedawight
ResolvedEileenmcnaughton
Resolvedawight
Resolvedawight
Resolvedawight
Resolvedawight

Event Timeline

ellery created this task.Feb 24 2015, 8:06 PM
ellery assigned this task to awight.
ellery raised the priority of this task from to Needs Triage.
ellery updated the task description. (Show Details)
ellery added a project: Fundraising-Backlog.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 24 2015, 8:06 PM
ellery removed awight as the assignee of this task.Feb 24 2015, 8:06 PM
ellery set Security to None.

@ellery we can try to get this in tech's workflow, but that won't be immediate.

Are you going to pull for the immediate request https://trello.com/c/AEigLFqN/222-low-fundraising-recurring-spot-check?

@ellery ping on the above question

I don't know how you guys process recurring payments. Is there documentation on this? If not, I would prefer if whoever knows the most about recurring payments were to tackle some of these questions if this needs to happen before you can make it a regular report.

Caitlin is amazing and has been checking to make sure recurring donations are recurring every 2 weeks. I love that! Adding her to the card to share the query info.

We do still need the recurring analysis on how long recurring donations last.

Here is the query I usually use to check all recurring failures from all gateways:

mysql civicrm -B -e "SELECT * from civicrm_contribution_recur JOIN civicrm_contribution ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id WHERE civicrm_contribution_recur.contribution_status_id = 1 AND civicrm_contribution_recur.end_date IS NULL GROUP BY civicrm_contribution_recur.id DESC HAVING max(civicrm_contribution.receive_date) <= DATE_SUB(NOW(), INTERVAL 1 MONTH) LIMIT 50" > recur.tsv

I spot check transaction IDs on this report to make sure there is an obvious failure reason. They are almost always failures and cancellations of PayPal donations made on the PP side.

Anne asked for a GC-specific search so I came up with the query below. It returns 1932 rows for recurring contribs that failed in 2012 or earlier. I'm not confident this query is correct as I have to guess there have been other mysterious recurring failures since then. Can someone double check this GC query?

mysql civicrm -B -e "SELECT * from civicrm_contribution_recur JOIN civicrm_contribution ON civicrm_contribution.contribution_recur_id = civicrm_contribution_recur.id WHERE civicrm_contribution_recur.contribution_status_id = 1 AND civicrm_contribution_recur.end_date IS NULL AND civicrm_contribution_recur.trxn_id LIKE 'RECURRING GLOBALCOLLECT %' GROUP BY civicrm_contribution_recur.id DESC HAVING max(civicrm_contribution.receive_date) <= DATE_SUB(NOW(), INTERVAL 1 MONTH)" > recurGC.tsv

@atgo if it's sufficient, @Juro2351 now does the biweekly scan I used to do
for both GC and PP. I believe our queries are accurate, so there is a human
looking at this regularly :)

Le vendredi 29 mai 2015, atgo <no-reply@phabricator.wikimedia.org> a écrit :

atgo moved this task to Analysis on the Fundraising-Backlog workboard.
TASK DETAIL

https://phabricator.wikimedia.org/T90630

WORKBOARD

https://phabricator.wikimedia.org/project/board/41/

EMAIL PREFERENCES

https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: atgo
Cc: CCogdill_WMF, MeganHernandez_WMF, atgo, Aklapper, ellery, cwdent

MBeat33 edited subscribers, added: MBeat33; removed: Juro2351.Apr 26 2016, 4:37 PM