I'll start generating the list of emails and transactions.

Files with the affected emails and transactions to be refunded are on frdev now. @Ejegg will need to work his magic on the GC transactions before we can run the refund script.

Change 422351 had a related patch set uploaded (by XenoRyet; owner: XenoRyet):
[wikimedia/fundraising/crm@master] Unintended Recur Part 2

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

That patch is the one to run the query that cancels the GC subscriptions.

adding @CCogdill_WMF for visibility on the email timing and content.

We learned last round that it's good to email the PayPal donors before we process the cancellations & refunds. And it would be excellent to add a line about how 'the refunds may appear as new donations depending on how your bank displays them, but we assure you there are no new charges.'

@MBeat sounds good. What are you thinking for timing on these emails? When should the GC ones go out?

@XenoRyet There are a bunch of order IDs in that file which have effort id 3 listed to refund but not effort ID 2. For example, OID 1000103169 and 1038197662. Is that intentional?

Here are a few commands to get the merchant references:

cut -d, -f1 unintendedRecurPart2GCTransactions.csv | sed -e 's/^/>/' -e 's/$/</' > ur2oids
cd /srv/archive/civi1001/process-control
bzgrep -F '{XML}{REQUEST}{ACTION}DO_PAYMENT' 20171*/ingenico_recurring_charge* 2018*/ingenico_recurring_charge* | grep '}OK{' | tr '{}' '<>' > ~/recur_XML
grep -F -f ur2oids recur_XML | parse_gc_logline.py ORDERID EFFORTID MERCHANTREFERENCE | tr \\t , | sort | uniq > ur2oidswithmerchref

Because of the OIDs with some of the efforts not listed, I get 4,466 lines in the file with merchant refs vs only 4,240 in the original file.

@CCogdill_WMF for the timing, I'd say any weekday not a Friday is fine.

@XenoRyet could you estimate the $ total for the PayPal refunds? I can ask Tony Le to increase our cash on hand at PayPal to cover them before the batch is sent.

Okay. I'll wait for your/tech's go ahead. As long as I can get the list
(and I have a yubikey again!), there shouldn't be much lead time in getting
the emails out.

@CCogdill_WMF GC cancellations will probably happen today, but that process is invisible to donors. Looks like @Ejegg found an issue with the refund list so that'll take some work before it's ready. I'll know more once I dig in a little deeper.

@MBeat33 Looks like we're at $23,332 on the paypal side for this group.

Change 422351 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Unintended Recur Part 2

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

Change 422546 had a related patch set uploaded (by XenoRyet; owner: XenoRyet):
[wikimedia/fundraising/crm@deployment] Unintended Recur Part 2

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

Change 422546 merged by jenkins-bot:
[wikimedia/fundraising/crm@deployment] Unintended Recur Part 2

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

Change 422551 had a related patch set uploaded (by XenoRyet; owner: XenoRyet):
[wikimedia/fundraising/crm@master] Fix the query

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

Change 422551 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Fix the query

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

Change 422574 had a related patch set uploaded (by XenoRyet; owner: XenoRyet):
[wikimedia/fundraising/crm@deployment] Fix the query

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

Change 422574 merged by jenkins-bot:
[wikimedia/fundraising/crm@deployment] Fix the query

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

GC subscriptions have been canceled. We won't see any more recurrences. Working on getting the refund list ready to run.

Many thanks, @XenoRyet

Also it looks like the PayPal total is below the reserve, so I will not ask Tony to boost it.

@XenoRyet, the ur2oidswithmerchref file is on frdev1001. It's sorted by order id and effort ID, but it only has those two columns plus merchant reference.

@Ejegg I've just addressed the issue you noticed before with some of the early recurrences being missing. The new file is on frdev if you get a chance to work your magic on it, else I'll try to do that myself today.

That same issue will apply to the paypal transaction list, so I'll be trying to get a new file for that as well.

Although now that I think about it, with the holiday tomorrow, it's functionally Friday today so we might not want to push the refunds today anyway. We're not quite in danger of any of them falling off the six-month edge quite yet.

@XenoRyet LMK when you're available, I'll show you how to suture those two files together.

Just noting I'm still on standby re: emails. When are we thinking of doing
the refunds?

@CCogdill_WMF We should have the GlobalCollect side ready to refund in the next hour or so. The PayPal side is going to take a little bit more adjustment due to the long time range covered this time, but I'll keep you posted on that.

@MBeat33 @CCogdill_WMF we should coordinate on when the paypal refunds will run to ensure we send the emails first.

GC side is complete. Subscriptions have been canceled and transactions refunded.

PayPal transaction list is fixed, new file is on frdev.

Cool! We should coordinate more closely for the PayPal emails to donors and when you start to perform the cancellations. We want our emails to hit donor inboxes before they get the automated emails PayPal sends, but we also want to send our emails as close as possible to when you all run the script. Can we schedule the job so we are doing these things at the same time?

@CCogdill_WMF Yea, we should be able to fire off the script at or about a certain time. I seem to recall it took a while finish last time, but not more than a couple of hours I don't think.

Is any particular time better than another for you and @MBeat33?

Thanks y'all. Earlier in the day EST if possible would be great for DS.

How about 8am PST / 11am EST?

@XenoRyet is the file with email addresses available yet? I'd like to get
that uploaded ahead of time.

That works nicely.

@CCogdill_WMF @MBeat33 I'm not online quite that early tomorrow and it's a slightly manual process. I think @Ejegg and @mepps will be due to favorable time zones, but I don't want to speak for them. If you want to ping them in the morning that might work.

The file with the PayPal emails is in the /tmp directory on frdev named unintendedRecurPart2PaypalEmails.csv

Apologies for not replying on this yesterday. As 8am is pretty early for
me, I would prefer to have this scheduled so I know to be up to hit send.
Can we schedule this to go out on Thursday morning?

Thursday morning is fine with me! I would run the refunds around 8:30/9am EST given that last time we had some issues with people not getting the email first (and it's when I start work).

Sorry, can we do 8am *PST*? I won't be up to send the emails at my 5:30 :)

@CCogdill_WMF See above. I would also mention in the email that we are going to cancel the subscriptions/run the refunds that day. Also, did you and @MBeat33 get to talk about language on how the refunds will appear from the banks? Last time people were freaked out because they looked like new charges and we talked about adding language to address that.

@CCogdill_WMF just saw your message. Everything is ready to go now, just need your go ahead to run the paypal processes for cancellation and refunds!

Actually, I saw your note this morning :) so didn't run anything. But I only just realized it looked like I responded yesterday without reading it.

Is cid=2835935 one of the batch of PayPal subscriptions that were canceled & refunded yesterday? Transaction ID is I-AWTHX39NSEY6. The donor has received notices of the refunds, but the subscription is still active in PayPal and in Civi.

I will manually cancel this one, but can we review the batch to make sure all the subscriptions in the batch canceled, and all the refunds processed?

This was in the batch, and the email was included in the list of subscriptions to cancel. Maybe PayPal didn't get them all?

Looking at email threads, it seems like we've got a ticket in with PayPal and we're waiting on them to confirm they all got canceled.

Thanks, @XenoRyet I-KE204H5HF8WU is another one PayPal did not get, so I canceled it in Civi & at PayPal.

Is there a way we could structure a review to make sure everything was canceled/refunded, short of waiting to hear from PayPal or donors?

@MBeat33 I'll poke around and see what I can find, but I think @mepps was the one who actually ran the PayPal side of things. She probably knows more than I do. We can check with her Monday morning if I don't find anything.

Thanks, @XenoRyet

@XenoRyet @mepps cid=22340511 Transaction ID I-EJLMB2VCY7G2 is another one where the donor received notices of the refunds, but the subscription was not canceled at PayPal or in Civi. Can you please take a pass through this batch to make sure that everything was canceled?

@MBeat33 @XenoRyet I had opened an issue with PayPal to confirm all the subscriptions were canceled since their interface doesn't let me check but I'll have to follow back up. Thanks!

@MBeat33 They had a list of more that needed to go through. I re-uploaded it and hopefully will get confirmation tomorrow.

Thank you @mepps

Any word from PayPal about the stragglers, @mepps? cid=14107017 is another we heard from in Zendesk where the refunds processed but the cancellation did not yet. Thanks!

@MBeat33 @XenoRyet Just wanted to post here that the plan is to manually cancel the remaining subscriptions if by May 1st, Paypal has not been able to do it.

We were still hearing from donors with unintended recurrings, and we found another small batch of banners that used the confusing frequency toggle. At least one of them had the frequency option removed after going live.

Can we do a final round of proactive cancellations & refunds for all of the recurring donations sourced from:

@MBeat33 Can you write up a new ticket for that batch? We've got a lot of balls in the air in the current sprint, and it'll get more attention as its own ticket.

Sure thing, @XenoRyet, it's T192958: Cancel & refund the remaining unintended recurring donations from Big EN.

@XenoRyet, there is an issue with the PayPal refunds, where the most recent donations are not showing as refunded in Civi or PayPal. Samples:

I can't tell from the change log what's going on, but it looks like the proactive cancellations worked, but did not refund *all* the donations after the first one. Can you do a batch refund of any leftover final donations from the PayPal list?

@mepps, any word from PayPal whether they've been able to cancel the remainder?

Now that the change log is working, it looks like these two questions might be the same thing: if the PayPal subscriptions that were not canceled in the first pass each had an extra charge applied, could we refund those?

@XenoRyet can you pull the subscription ids for all the paypal unintended recurring txns? We never got an answer for paypal so DS will have to manually cancel them.

@MBeat33 It looks like some recurring charges went through after we ran the refunds. This is because of the failed subscription cancellations. @XenoRyet can you also try to pull these most recent txns in the format to run the refund script?