Experiment with a 'estimated payment for next week' value

[rohitpaulk]

Reticketed from https://github.com/gratipay/gratipay.com/pull/3873#issuecomment-165159031

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[rohitpaulk]

I'm using changes in #3876 and #3873 to do this.

[rohitpaulk]

Here's the output:

gratipay_estimates.xlsx

[rohitpaulk]

Screenshot:

[rohitpaulk]

Script used:

from gratipay import wireup

db = wireup.db(wireup.env())

teams = db.all("""
    SELECT t.*::teams
      FROM teams t
""")

print("Team,Average over 5 weeks,Last Week,Estimated")

for t in teams:
    estimated = t.get_upcoming_payment()
    last_week = db.one("""
        SELECT COALESCE(SUM(amount), 0)
          FROM payments
         WHERE team = %s
           AND direction = 'to-team'
           AND payday = (SELECT id FROM paydays ORDER BY id DESC LIMIT 1)
    """, (t.slug, ))
    average_5_wks = db.one("""
        SELECT ROUND(COALESCE(SUM(amount), 0) / 5, 2) AS avg
          FROM payments
         WHERE team = %s
           AND direction = 'to-team'
           AND payday >= 240
    """, (t.slug, ))
    print(",".join([t.name, str(last_week), str(average_5_wks), str(estimated)]))

[rohitpaulk]

Now that https://github.com/gratipay/inside.gratipay.com/issues/440 is done, I'm going to get values from there

[rohitpaulk]

My aim here is to compare the estimated and actual values and try to see if there are reasons for deviation that can be factored (are predictable) into the formula used.

Once we're down to a case where all the reasons are the kind that we can't predict (payments cancelled, new payments setup, cards failing for the first time), this should be ready for review.

[rohitpaulk]

Team	Estimate	Actual	Reason for deviation
Gratipay	399.95	390.94	Too many tips, skipping analysis
Act	0.05	0.05
aegirproject	0	2	User with balance
Aerith Radio	0	0
asciinema	0	0
Benedict Chen	0	0
Bevry	13.27	3.27	User was suspicious
Bootsnipp	0	0
Bootswatch	0.05	0.05
Botwiki.org	0.05	0.05
Brainfock	0	0.05	New tip
Byebug	10	10.1	User with balance
bzg	0	0
Ca Renters Legal Advocacy and Education Fund	0	0
Cappuccino	0.05	0.05
catapultpgh	24	24.25	User with balance
cdnjs	0.3	0.3
Chameleon	0.05	0.05
Chihiro fansubs	0	0
Chocolatey	0	0
CIDER	29.99	30.09
Citizens Network	0	0
CiviCRM-LTS	0.05	0.05
coala	0.05	0.05
cobbzilla	0	0
cobbzilla software	0	0
Code for Seoul	0.05	0.05
CoInvest	0.05	0.05
Commie Subs	0	0
Concrete Clouds	0.05	0.05
ContribKanban.com	0.05	0.05
CPAN-BOOK	0	0.2
CPAN.io	0.05	0.05
Crrio	0	0
Cuberite	0.05	0.05
Cyberpipe	9	1	CC failed
daurnimator	0	0
davorg	0.05	0.05
Debian LTS by Freexian	6	0	CC failed
Detexify	2.9	2.91
DevDocs	28.3	18.4
doit	0	0
dokku	0.05	0.3	User with balance
Dreditor	0	0
Drupal Search API	12.05	12.05
Drypot	0	0
Duo	156	156
Eftakhairul	0	0
Encommuns	0	0
Endless Parentheses	12.25	12.25
esdiscuss	5	0.25	CC failed
exercism	0.03	0.3	User with balance
Fabric.js	0.05	0.05
FlameCore	0.05	0.05
FlexGet	0.05	0.05
Flexible Grid System	0.05	0.05
FLIF	0.05	0.05
fsnotify	0.2	0.2
Funcool	0.05	0.05
Gammu	0.05	0.25	User with balance
Git Galaxy	0	0
gkz-open-source	5	5
Grimoire	10.05	24.05	User with balance
Grip	3	3.1	User with balance
Gryd	0.05	0.05
hamper	0	0
Horizon Launcher	0	0
Hunter	0.05	0.05
Hypatia Software Organization	0.05	0.05
image_optim	0	0
introtopython	1.03	1.03
ITCase	0.05	0.06
Jens Segers	0	0
Jeto	0.05	0.05
JOSD	10.03	0.03	Failing CC
jsbin	155	150.25
JsFile	0.05	0.05
jshttp	40	50	Unfunded dues > Minimum charge (fixed in #3876)
JSON Resume	0.3	0.3
juice-shop	0.05	0.05
JuNest	0.05	0.05
KanColleWidget	0	0
Kivy	0.05	0	Paypal removed
KnopProject	0	0
KoNLPy	0.25	0.25
Kriasoft	0.05	0.05
Les anomalistes	0	0
libOctavo	0.05	0.05
libravatar	0	0
Logstown	0	0
MacDown	0.05	0.05
Magit	8.5	8.5
MATLAB Schemer	0.05	0.05
MD for Bootstrap	0	0
meteor-active-route	0.05	0.05
Minotar	10.05	10.05
Mojolicious	1	1.01
Mottie	0	0
Musical Artifacts	0.05	0.05
nanoc	0.6	0.6
Neocities	0	0
ng-file-upload	0.05	0.05
Nim	12	12
NoriPyt	0	0
Northern Plains Athletics	0.3	0.3
Nuvola Player	10.13	10.53
Online Daboek	0	0
OpenCV	20	20
OpenLP	0.05	0.05
Org-mode	15	15
Orionjs	0	0
OS.js	0	0
Pageres	0.3	0.3
ParisKiwi	0.05	0.05
Paul Fenwick	0	0
Perl Maven	0.05	0.05
PerlWeekly	0	0.16
Phanbook	0.05	0.05
Pillow	6.25	6.25
Piloting Postdevelopment	0.05	0.05
pjf	0.35	1
Poll	0.05	0.05
pomodoro.cc	0.1	0.1
ProjectCloudly	0	0.05
PylonsProject	102.78	93.29
Record cutting needles	0	0
Reddit Enhancement Suite	0	0.65
Retrospring	0.05	0.05
Rhombus Tech	15	0
Riseup Networks	0	0
rtail	0.05	0.05
RuboCop	0.3	0.3
Sailor	0.3	0.3
SEAN.JS	0.05	0.05
searx	0	0.01
sexy-bash-prompt	0	0
Share Term	0.3	0.3
ShareX	0.05	0.05
Shields	17.5	7.5
Simple Icons	0.05	0.05
simplytest.me	20	20.25
Slap	0.05	0.05
sqlalchemy	50.5	51.26
StreamCenter	0	0
SublimeLinter	0.6	0.6
sudo room	115	115
SudoMesh	0	0
survivejs	0	0
svg.js	10	10.25
Tabula	0.05	0.05
Team Jacinth	0	0
teampopong	0.05	0.05
Tech Store Club	0.05	0.05
TechRaptor	0	0
the Charis Project	0	0
The Coinsultants	0	0
The Mountain Spirit	1	1
The Perl Review	0.05	0.09
Tipsy	0	0.05
toolitup	0.05	0.05
Torque3D	10	10
Totalism Hackbase	10.1	10.1
trakt for Plex	0	0
twolfson	0	0
Tyf	0.05	0.05
ucoin	10	10.05
Vanilla Music	0.05	0.05
Vegeta	0.05	0.05
WDRL	47.5	35.65
Weblate	0.05	0.05
weblog.sh	10.05	10.55
webpack	20	0	CC failed
WebTorrent	13	13
yawTtk	0.05	0.05
YouTransfer.io	0.05	0.05
Zed	14.3	11.3

[rohitpaulk]

^ A lot of the expected values are lower than the actuals because of users who had a Gratipay balance that hasn't been withdrawn. There are two reasons that I'm not taking this into account while calculating the estimated payment:

1. We expect the number of such cases to die out (we are no longer adding to the total escrowed amount) over time.
2. It's not as simple as it sounds. What happens if a user has a gratipay balance of $20, and two tips of $15 each? Which one do we mark as funded?

[rohitpaulk]

This 'analysis' is ready for review. The changes required are in #3876 and #3873.

[rohitpaulk]

cc: @whit537, @rorepo

[chadwhitacre]

!m @rohitpaulk

On my radar ...

[chadwhitacre]

Alright, @rohitpaulk, can you help me understand the implications of this analysis? Does this impact #3876 somehow?

[chadwhitacre]

I'm not seeing an obvious commit on #3876 that implements the estimation algorithm, for example.

[rohitpaulk]

Does this impact #3876 somehow?

No, it shouldn't.

3876 does affect https://github.com/gratipay/gratipay.com/pull/3873 though, since we expose the value of unfunded dues on the receiving page.

[rohitpaulk]

@whit537 - Code for the estimation is present at #3873.

https://github.com/gratipay/gratipay.com/blob/add-team-receiving-page/gratipay/models/team.py#L149-L162

def get_upcoming_payment(self):
    return self.db.one("""
        SELECT COALESCE(SUM(amount + due), 0)
          FROM current_payment_instructions cpi
          JOIN participants p ON cpi.participant = p.username
         WHERE team = %(slug)s
           AND is_funded                        -- Check whether the payment is funded
           AND (                                -- Check whether the user will hit the minimum charge
                SELECT SUM(amount + due)
                  FROM current_payment_instructions cpi2
                 WHERE cpi2.participant = p.username
                   AND cpi2.is_funded
               ) >= %(mcharge)s
    """, {'slug': self.slug, 'mcharge': MINIMUM_CHARGE})

[chadwhitacre]

What was the resolution here?

[rohitpaulk]

I created this as a prerequisite for https://github.com/gratipay/gratipay.com/pull/3873, which has now been merged.

[rohitpaulk]

I guess we might circle back here as part of https://github.com/gratipay/gratipay.com/issues/3992