Flush money via PayPal MassPay #1219

chadwhitacre · 2018-01-24T21:38:27Z

MassPays

Where we have an email address, we can try sending via PayPal even if not explicitly linked to PayPal.

gratipay-bak=# select count(*), sum(balance) volume, email_address is not null as > have_email_address from participants where balance > 0 group by email_address is not null;
┌───────┬──────────┬────────────────────┐
│ count │  volume  │ have_email_address │
├───────┼──────────┼────────────────────┤
│  3162 │ 91861.94 │ f                  │
│   602 │ 12928.67 │ t                  │
└───────┴──────────┴────────────────────┘
(2 rows)

gratipay-bak=#

The text was updated successfully, but these errors were encountered:

Changaco · 2018-02-22T13:14:41Z

SELECT sum(p.balance)
  FROM participants p
 WHERE exists (select 1 from email_addresses e where e.participant_id = p.id)
    OR exists (select 1 from elsewhere e where e.email like '%@%' and e.participant = p.username);
   sum    
----------
 63171.28

chadwhitacre · 2018-06-01T17:28:43Z

Probably worth individually contacting the top 100 or so folks. Could we clear out half the money that way?

gratipay-bak=# select count(*), sum(balance) from (select username, balance from participants where balance > 1000 order by balance desc) foo;
┌───────┬──────────┐
│ count │   sum    │
├───────┼──────────┤
│    13 │ 24934.11 │
└───────┴──────────┘
(1 row)

gratipay-bak=# select count(*), sum(balance) from (select username, balance from participants where balance > 500 order by balance desc) foo;
┌───────┬──────────┐
│ count │   sum    │
├───────┼──────────┤
│    28 │ 34907.09 │
└───────┴──────────┘
(1 row)

gratipay-bak=# select count(*), sum(balance) from (select username, balance from participants where balance > 200 order by balance desc) foo;
┌───────┬──────────┐
│ count │   sum    │
├───────┼──────────┤
│    90 │ 54300.34 │
└───────┴──────────┘
(1 row)

gratipay-bak=# select count(*), sum(balance) from (select username, balance from participants where balance > 100 order by balance desc) foo;
┌───────┬──────────┐
│ count │   sum    │
├───────┼──────────┤
│   216 │ 71718.69 │
└───────┴──────────┘
(1 row)

gratipay-bak=#

chadwhitacre · 2018-06-01T17:28:59Z

gratipay-bak=# select sum(balance) from (select username, balance from participants order by balance desc limit 100) foo;
┌──────────┐
│   sum    │
├──────────┤
│ 56240.30 │
└──────────┘
(1 row)

gratipay-bak=#

chadwhitacre · 2018-06-01T17:29:41Z

On the other hand a lot of that is probably also included in #1219 (comment), so makes sense to first do the general blast via PayPal and then follow up with targeted based on state of play at that point.

chadwhitacre · 2018-11-27T21:26:51Z

chadwhitacre · 2018-11-27T21:31:28Z

Code: https://github.com/gratipay/logs/tree/master/misc/1205

chadwhitacre · 2018-11-27T21:32:05Z

Well, code and data. Will (cross?)post code publicly as it develops.

chadwhitacre · 2018-11-27T21:43:32Z

Per inspection, nobody has more than five addresses on file.

Tooling:

SQL query to give balance, email 1, ..., email 5 > payouts.csv
script to take payouts.csv and blacklist.csv as inputs and output a MassPay CSV input file
script to take MassPay output file and generate/update a blacklist.csv

chadwhitacre · 2018-11-27T21:52:12Z

gratipay-bak=# \i payout.sql
SELECT 3764
gratipay-bak=# \copy tmp to 'payouts.csv' with csv header
COPY 3764
gratipay-bak=#

  SELECT p.balance
       , (select count(*) from elsewhere e where e.email like '%@%' and e.participant = p.username) as n
       , p.username
       , p.email_address as address_1
       , (select e.email from elsewhere e where e.email like '%@%' and e.participant = p.username limit 1) as address_2
       , (select e.email from elsewhere e where e.email like '%@%' and e.participant = p.username offset 1 limit 1) as address_3
       , (select e.email from elsewhere e where e.email like '%@%' and e.participant = p.username offset 2 limit 1) as address_4
       , (select e.email from elsewhere e where e.email like '%@%' and e.participant = p.username offset 3 limit 1) as address_5
    INTO tmp
    FROM participants p
   WHERE balance > 0
ORDER BY n desc, balance desc;

chadwhitacre · 2018-11-27T22:04:54Z

To get started, upload a CSV or TXT file of up to 5,000 payment recipients.

Fits in a batch. 👍

chadwhitacre · 2018-11-27T22:08:17Z

organize your recipient information into the 3 required fields:
• Recipients’ email addresses, mobile phone numbers, or PayPal IDs
• Payment amounts
• Currency type

chadwhitacre · 2018-11-27T22:09:51Z

Per old logs, currency type is usd

chadwhitacre · 2018-11-27T22:13:43Z

$ ./genmasspay.py
62072.15
$ cat genmasspay.py

#!/usr/bin/env python3
import csv
from collections import OrderedDict
from decimal import Decimal as D

payouts = csv.reader(open('payouts.csv'))
masspay = csv.writer(open('masspay.csv', 'w+'))
headers = next(payouts)
blacklist = set(open('blacklist.csv').read().splitlines())
total = D('0.00')

for row in payouts:
    balance = row[0]
    addresses = list(OrderedDict.fromkeys(row[3:]))
    for address in addresses:
        if not address or address in blacklist:
            continue
        masspay.writerow([address, balance, 'usd'])
        total += D(balance)
        break

print(total)

chadwhitacre · 2018-11-27T22:16:24Z

chadwhitacre · 2018-11-27T22:16:56Z

chadwhitacre · 2018-11-27T22:18:15Z

Will give that time to clear (trip fraud?) and write up a blog post!

chadwhitacre · 2018-11-27T22:18:21Z

💃

chadwhitacre · 2018-11-28T03:59:50Z

This evening, I hand-delivered $420 in cash to someone who is friends with someone who had money escrowed with us. 💃

chadwhitacre · 2018-11-28T04:10:40Z

What about fees on PayPal don't we have to downcharge or somesuch?

chadwhitacre · 2018-12-10T16:49:37Z

Transfer complete!

chadwhitacre · 2018-12-10T17:37:54Z

Prepping a chart for the blog post.

gratipay-bak=# \i charts.sql
UPDATE 2827
UPDATE 720
UPDATE 204
UPDATE 13
UPDATE 2112
┌──────┬──────────┐
│  n   │    s     │
├──────┼──────────┤
│   13 │ 24934.11 │
│  204 │ 46884.58 │
│  720 │ 24591.42 │
│ 2827 │  8380.50 │
└──────┴──────────┘
(4 rows)

┌──────┬──────────┐
│  n   │    s     │
├──────┼──────────┤
│    7 │ 14153.18 │
│  127 │ 29339.36 │
│  422 │ 14244.29 │
│ 1556 │  4335.32 │
└──────┴──────────┘
(4 rows)

gratipay-bak=#

alter table tmp add column bucket int default 0;
update tmp set bucket=0 where balance >= 0 and balance < 10;
update tmp set bucket=1 where balance >= 10 and balance < 100;
update tmp set bucket=2 where balance >= 100 and balance < 1000;
update tmp set bucket=3 where balance >= 1000;

alter table tmp add column has_email bool default false;
update tmp
   set has_email=true
 where coalesce(address_1, address_2, address_3, address_4, address_5) is not null
      ;

select count(balance) n, sum(balance) s from tmp group by bucket order by bucket desc;
select count(balance) n, sum(balance) s from tmp where has_email group by bucket order by bucket desc;

chadwhitacre · 2018-12-10T17:38:39Z

gratipay-bak=# select has_email e, sum(balance) s from tmp group by has_email;
┌───┬──────────┐
│ e │    s     │
├───┼──────────┤
│ f │ 42718.46 │
│ t │ 62072.15 │
└───┴──────────┘

chadwhitacre · 2018-12-10T17:39:32Z

gratipay-bak=# select sum(balance) from tmp;
┌───────────┐
│    sum    │
├───────────┤
│ 104790.61 │
└───────────┘

chadwhitacre · 2018-12-10T19:06:30Z

https://medium.com/@chadwhitacre/gratipay-payout-aeb2e985d537

https://docs.google.com/spreadsheets/d/1Ybb6_UeCI-HtQ8WuTN1YcPmRi30w-pERGZ4koRBJzeg/edit?usp=sharing

chadwhitacre · 2018-12-10T19:13:06Z

Submitted to https://gratipay.news/:

https://gratipay.news/paypal-push-aeb2e985d537

cc: @Changaco

chadwhitacre · 2018-12-10T19:19:13Z

I've confirmed that I have access to the {paypal,support}@gratipay.com email addresses.

chadwhitacre · 2018-12-10T19:22:55Z

Hmm ... actually ... maybe we should manually pursue the n=13 that are > $1000 first? And then do a masspay for the long tail?

chadwhitacre · 2018-12-10T22:14:13Z

Yeah let's do that: #1225.

chadwhitacre · 2018-12-15T14:36:00Z

Ftr PayPal makes it pretty easy to connect a second email address to an existing account. I just did that for [email protected]. 😬

chadwhitacre · 2018-12-17T12:13:11Z

https://github.com/gratipay/logs/commit/c02f9d5790db84c9d04d72ca9e8434b3bd921fc3

$ ./genmasspay.py 
               n                $
           ------------- ------------------
  settled  1,523 (40.5%)  61,102.95 (58.3%)
remaining  2,241 (59.5%)  43,687.66 (41.7%)
    total  3,764         104,790.61

  masspay    557 (14.8%)
   target                 13,026.10
remainder               -      0.95
                         ----------
   actual                 13,025.15 (12.4%)
      fee               -    143.73
                         ----------
      net                 12,881.42
$

chadwhitacre · 2018-12-17T20:32:39Z

I've gone through accounts with no email down to $100 and have manually located an email address (mostly by harvesting GitHub and reaching out on Twitter). I have started a transfer of another $20,000 from New Alliance to PayPal (leaving about $23,000 remaining in New Alliance). Once that clears I will process another MassPay. Perhaps I will locate emails for accounts down to $69 to cover the third quartile by volume ...

I am going to manually pay out a few based on representations I made on Twitter that these would go out today/tomorrow.

chadwhitacre · 2018-12-17T22:28:48Z

https://github.com/gratipay/logs/commit/8afd2aaa92792ea8c75e53f0d2f00f3bf392df4f

Done down to $69. 👍

(env) $ ./genmasspay.py 
               n                $
           ------------- ------------------
  settled  1,554 (41.3%)  65,861.96 (62.9%)
remaining  1,654 (43.9%)  38,928.65 (37.1%)
    total  3,764         104,790.61

  masspay     89 ( 2.4%)
   target                 14,858.58
remainder               -      0.00
                         ----------
   actual                 14,858.58 (14.2%)
      fee               -     87.31
                         ----------
      net                 14,771.27
(env) $

chadwhitacre · 2018-12-17T22:34:55Z

Made those few individual payouts as well.

chadwhitacre · 2018-12-20T22:50:39Z

Okay! Got another MassPay ready ...

chadwhitacre · 2018-12-20T23:11:33Z

Submitted with https://github.com/gratipay/logs/commit/a9a25b71e167720e0d4b5fe29ade270cec39bf06!

https://www.paypal.com/activity/masspay/MPA-3SPG7EDFLJ298

chadwhitacre · 2018-12-21T00:29:03Z

MassPay № 2 is still "Processing," making me kinda nervous ...

chadwhitacre · 2018-12-21T13:58:36Z

Yoi! Called PayPal, stuck on (silent) hold a loooong time (long enough to read the captions on all of the Bergeron's Christmas cards). Sounds like they're stumped! The dude was like, "the resolution specialist's manager is going to give you an email"—like, this was super weird for him. 😂

🤞

chadwhitacre · 2018-12-21T14:00:38Z

https://github.com/gratipay/logs/commit/ca14c79d1cf2b5aa8b39366248377c9f510353a9

$ ./tick.py 
               n                $
           ------------- ------------------
    ready     91 ( 2.4%)  14,704.73 (14.0%)
  pending    508 (13.5%)  14,337.03 (13.7%)
 complete  1,542 (41.0%)  61,826.70 (59.0%)
   failed  1,623 (43.1%)  13,922.15 (13.3%)
    total  3,764         104,790.61

  masspay     91 ( 2.4%)
   target                 14,704.73
remainder               -      0.00
                         ----------
   actual                 14,704.73 (14.0%)
      fee               -    142.37
                         ----------
      net                 14,562.36
$

chadwhitacre · 2018-12-23T01:55:15Z

Second masspay processed!

chadwhitacre · 2018-12-23T04:43:24Z

chadwhitacre · 2019-01-02T22:46:24Z

With #1226 we have another batch of masspays to submit! 💃

$ ./tick.py 
                 n                $
             ------------- ------------------
      ready    350 ( 9.3%)   2,959.62 ( 2.8%)
    pending    462 (12.3%)  11,257.56 (10.7%)
   complete  1,683 (44.7%)  81,326.50 (77.6%)
     failed  1,269 (33.7%)   9,246.93 ( 8.8%)
      total  3,764         104,790.61
 incomplete  2,081 (55.3%)  23,464.11 (22.4%)

     volume              1,003,648.55
  disbursed                983,144.06 (98.0)%

    masspay    350 ( 9.3%)
     target                  2,959.62
  remainder               -      0.65
                           ----------
     actual                  2,958.97 ( 2.8%)
        fee               -     56.08
                           ----------
        net                  2,902.89
$

chadwhitacre · 2019-01-02T22:54:16Z

Fee caps updated in https://github.com/gratipay/logs/commit/2767fc6419cf849d09b16fc01bfcb9012bd41271 ...

chadwhitacre · 2019-01-02T22:56:06Z

Lines up ...

$ ./tick.py 
                 n                $
             ------------- ------------------
      ready    350 ( 9.3%)   2,959.62 ( 2.8%)
    pending    462 (12.3%)  11,257.56 (10.7%)
   complete  1,683 (44.7%)  81,326.50 (77.6%)
     failed  1,269 (33.7%)   9,246.93 ( 8.8%)
      total  3,764         104,790.61
 incomplete  2,081 (55.3%)  23,464.11 (22.4%)

     volume              1,003,648.55
  disbursed                983,144.06 (98.0)%

    masspay    350 ( 9.3%)
     target                  2,959.62
  remainder               -      0.66
                           ----------
     actual                  2,958.96 ( 2.8%)
        fee               -     56.57
                           ----------
        net                  2,902.39
$

chadwhitacre · 2019-01-02T22:56:50Z

Sent!

chadwhitacre · 2019-01-02T22:57:08Z

Hopefully this one doesn't take as long to post! 😆

chadwhitacre · 2019-01-02T23:02:50Z

Posted! 💃

Fixed a bug in https://github.com/gratipay/logs/commit/b4ae65b5e6f3edc5fc7342deff5f51ac9a55b640 that overstated disbursed as 98% when it's still 97.7%.

$ ./tick.py 
                 n                $
             ------------- ------------------
      ready    350 ( 9.3%)   2,959.62 ( 2.8%)
    pending    462 (12.3%)  11,257.56 (10.7%)
   complete  1,683 (44.7%)  81,326.50 (77.6%)
     failed  1,269 (33.7%)   9,246.93 ( 8.8%)
      total  3,764         104,790.61
 incomplete  2,081 (55.3%)  23,464.11 (22.4%)

     volume              1,003,648.55
  disbursed                980,184.44 (97.7)%

    masspay    350 ( 9.3%)
     target                  2,959.62
  remainder               -      0.66
                           ----------
     actual                  2,958.96 ( 2.8%)
        fee               -     56.57
                           ----------
        net                  2,902.39
$

Can we get to 98% over the next month or two!?!? 🤞

chadwhitacre · 2019-01-02T23:08:39Z

https://github.com/gratipay/logs/commit/de5d6c33c7a3bba5c2fa0bd56f17e31d010ec311

$ ./tick.py 
                 n                $
             ------------- ------------------
      ready      1 ( 0.0%)       9.23 ( 0.0%)
    pending    590 (15.7%)  12,252.61 (11.7%)
   complete  1,891 (50.2%)  83,093.68 (79.3%)
     failed  1,282 (34.1%)   9,435.09 ( 9.0%)
      total  3,764         104,790.61
 incomplete  1,873 (49.8%)  21,696.93 (20.7%)

     volume              1,003,648.55
  disbursed                981,951.62 (97.8)%

    masspay      1 ( 0.0%)
     target                      9.23
  remainder               -      0.00
                           ----------
     actual                      9.23 ( 0.0%)
        fee               -      0.18
                           ----------
        net                      9.05
$

chadwhitacre · 2019-01-02T23:17:05Z

Single ready payout handled manually in https://github.com/gratipay/logs/commit/f96489d5cbb268660173170c429102d94ae8da77. 👍

$ ./tick.py 
                 n                $
             ------------- ------------------
      ready      0 ( 0.0%)       0.00 ( 0.0%)
    pending    590 (15.7%)  12,252.61 (11.7%)
   complete  1,892 (50.3%)  83,102.91 (79.3%)
     failed  1,282 (34.1%)   9,435.09 ( 9.0%)
      total  3,764         104,790.61
 incomplete  1,872 (49.7%)  21,687.70 (20.7%)

     volume              1,003,648.55
  disbursed                981,960.85 (97.8)%
$

Okay! Now we wait ...

chadwhitacre · 2019-01-02T23:56:06Z

Third MassPay:

https://www.paypal.com/activity/masspay/MPA-SHFNU2CLL6GFE

Consolidated links in issue description.

chadwhitacre · 2019-01-19T19:55:16Z

First masspay is complete! I think we should wait for the other two to complete before issuing the next masspay.

chadwhitacre · 2019-01-21T23:10:33Z

Second complete.

chadwhitacre · 2019-02-02T01:29:33Z

Third complete!

chadwhitacre · 2019-02-02T01:37:44Z

Fourth MassPay sent!

chadwhitacre · 2019-02-02T01:40:12Z

Worth doing! 💃

chadwhitacre · 2019-03-14T01:16:01Z

chadwhitacre · 2019-03-14T01:58:09Z

chadwhitacre · 2019-04-24T12:13:15Z

Fifth MassPay complete!

chadwhitacre mentioned this issue Nov 28, 2018

Dispose of unclaimed balances #1205

Closed

8 tasks

chadwhitacre changed the title ~~Flush money via PayPal~~ Flush money via PayPal MassPay Dec 10, 2018

chadwhitacre closed this as completed Apr 24, 2019

Flush money via PayPal MassPay #1219

Flush money via PayPal MassPay #1219

Comments

chadwhitacre commented Jan 24, 2018 • edited Loading

MassPays

Changaco commented Feb 22, 2018

chadwhitacre commented Jun 1, 2018

chadwhitacre commented Jun 1, 2018

chadwhitacre commented Jun 1, 2018

chadwhitacre commented Nov 27, 2018 • edited Loading

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018 • edited Loading

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 27, 2018

chadwhitacre commented Nov 28, 2018 • edited Loading

chadwhitacre commented Nov 28, 2018

chadwhitacre commented Dec 10, 2018

chadwhitacre commented Dec 10, 2018

chadwhitacre commented Dec 10, 2018 • edited Loading

chadwhitacre commented Dec 10, 2018

chadwhitacre commented Dec 10, 2018

chadwhitacre commented Dec 10, 2018 • edited Loading

chadwhitacre commented Dec 10, 2018

chadwhitacre commented Dec 10, 2018

chadwhitacre commented Dec 10, 2018

chadwhitacre commented Dec 15, 2018

chadwhitacre commented Dec 17, 2018

chadwhitacre commented Dec 17, 2018 • edited Loading

chadwhitacre commented Dec 17, 2018

chadwhitacre commented Dec 17, 2018

chadwhitacre commented Dec 20, 2018

chadwhitacre commented Dec 20, 2018

chadwhitacre commented Dec 21, 2018

chadwhitacre commented Dec 21, 2018

chadwhitacre commented Dec 21, 2018

chadwhitacre commented Dec 23, 2018

chadwhitacre commented Dec 23, 2018

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019

chadwhitacre commented Jan 2, 2019 • edited Loading

chadwhitacre commented Jan 19, 2019

chadwhitacre commented Jan 21, 2019

chadwhitacre commented Feb 2, 2019

chadwhitacre commented Feb 2, 2019

chadwhitacre commented Feb 2, 2019

chadwhitacre commented Mar 14, 2019

chadwhitacre commented Mar 14, 2019

chadwhitacre commented Apr 24, 2019

chadwhitacre commented Jan 24, 2018 •

edited

Loading

chadwhitacre commented Nov 27, 2018 •

edited

Loading

chadwhitacre commented Nov 27, 2018 •

edited

Loading

chadwhitacre commented Nov 28, 2018 •

edited

Loading

chadwhitacre commented Dec 10, 2018 •

edited

Loading

chadwhitacre commented Dec 10, 2018 •

edited

Loading

chadwhitacre commented Dec 17, 2018 •

edited

Loading

chadwhitacre commented Jan 2, 2019 •

edited

Loading