@Ejegg @XenoRyet @AKanji-WMF Note to this task that the Ingenico tokens are now being transferred as of 29 September 2023 25% of the tokens are now at Adyen and the remainder are in progress. We need to find a way to confirm that all of the currently active tokens are migrated successfully before Ingenico closes out their work on this. As I cannot see the tokens in the Adyen console until we bill, is there a backend way to confirm what is scheduled to be billed for the month of October is indeed included in the transferred tokens in advance?

Ingenico reports the following token counts are being transferred: These are the number of profiles per year.
COUNT(1) YEAR

   9,592 - 2018
 758,953 - 2019
 902,474 - 2020
1,686,871 - 2021
 420,369 - 2022
 327,758 - 2023

All tokens have been sent to Adyen for import. I have spoken to Adyen and they are now working on the import and providing a list of all the migrated tokens for us to validate against that all active tokens made it across.

Just closing a loop, once Adyen gets us the list of tokens, we will confirm that everything came over, as discussed on Slack.

@XenoRyet @AKanji-WMF Adyen has now imported all of the Ingenico tokens to their platform. As above, I would like to confirm that everything we need to bill in the next 30 days has come across. Please find the file available in the following Google Drive link to compare the active donor list to:
https://drive.google.com/drive/folders/1n1WD9sp925n_fZFNpE2UBjtCoyWFA1-S?usp=drive_link

I've requested access to that drive link and will start importing tokens in small batches once I get it.

OK, I have the files. I am uploading them to Civi and I'll try importing a small batch.

Already raising some data quality questions - 32k rows in the first 800k line file have a blank field for the 'echodata' where the Ingenico token is supposed to be. That'll break the script I've written.

I'll investigate a few based on the order ID, which seems to be present for all of the rows in that first file. It's possible those donors don't actually have a recurring, or that we can migrate another way.

I emailed Pim at Ingenico today to understand why this field would be blank.

Thanks, Evelyn. I have done some database queries, and it looks like of those 32,118 blank rows in the first file, 3934 donors do have an Ingenico token stored in our database. Of those, 1,194 DO have an active Ingenico recurring.

It's possible to find the donors and import the tokens based on just the order_id, I'll just have to adjust my script.

@EMartin There are also some rows where the echodata (the Ingenico token) is partially replaced with * * * characters. I've checked a couple, and we have the version without the * * * characters in our database. Again, not a complete blocker but we will have to fall back to looking up by order ID.

@Ejegg Thanks Elliott! Then from Pim we want:

• the 32,118 blank rows in the first file (1a.csv) Echodata field populated fully with the Ingenico tokens. Some are blank and we see are valid recurring donors and some have **** where the token should be. Let me get that out to him. We won't hear until Monday as he is in Central EU. Are the other 4 files looking good?

No, the other files have the same problems - I was just using the first file to get an idea of what percentage of rows were affected.

Ok, I have submitted the problem to Pim in email. I assume that means he will have to re supply all of the files to Adyen with all echodata fields and they have to reimport with that populated or we cannot bill, correct? I know we had the ORDERID populated into the as the shopperReference field, but we need both values to bill correctly. Do I have that straight @Ejegg?

Oh sorry, I didn't realize Pim was with Ingenico. I was thinking the problem was on Adyen's side, that they had mistakenly omitted that row when they were creating CSVs after doing the migration.

Let's ask our Adyen contact to take a look at some of those rows (including the very first data row in the first file - it has blank echodata) and let us know if they are a result of the CSV generation or a bad input file.

I'm just using the echodata as a quick way to find the existing Ingenico token in our own database. If necessary, I can use the order ID to look things up - it just might be slower.

In T344645#9250946, @EMartin wrote:

Ok, I have submitted the problem to Pim in email. I assume that means he will have to re supply all of the files to Adyen with all echodata fields and they have to reimport with that populated or we cannot bill, correct? I know we had the ORDERID populated into the as the shopperReference field, but we need both values to bill correctly. Do I have that straight @Ejegg?

All of the rows have the minimum needed to do the import and then bill new charges - order id, shopperReference, and recurringDetailReference. This is just a question of convenience and speed for doing the lookup.

Thanks. Understood.

(technical musings...) Starting to think that the batch job is not the way to catch all the edge cases in the csvs and in our data. If I can dump all the CSVs into a big temp table, then do some joins to find

• matching contribution ID
• matching contribution_recur ID (if any - most of the CSV rows seem to be for monthly convert donors who never clicked 'yes')
• matching payment_token ID
• count of contribution_recur rows who share the payment_token

We could then check to see how many Ingenico contribution_recur rows and payment tokens are not picked up, and try to associate them via other means before running the update.

Then I could just run a couple of big data update statements during the maintenance window.

Even with the in-database approach, I could start with a selection of the first thousand rows or so if we want to try charging them via Adyen before migrating the whole lot

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

[wikimedia/fundraising/crm@master] Scripts for doing token migration in the db

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

From Ingenico team:

"Can you please clarify what you mean with echodata?

These are the fields I’m sending:

CREDITCARDNUMBER

EXPIRYDATE

MERCHANTID

ORDERID

EFFORTID

ATTEMPTID

CURRENCYCODE

AMOUNT

REFERENCE

RECEIVEDDATE

CREDITCARDCOMPANY

NAME

CUSTOMERID

FIRSTNAME

SURNAME

STREET

CITY

STATE

ZIP

COUNTRYCODE

EMAILADDRESS

PHONENUMBER

PROFILETOKEN

SCHEMETRANSACTIONID

If it is schemetransactionid, then I can’t find this for most of the records (3.9M)"

In T344645#9252620, @RKumar_WMF wrote:

From Ingenico team:

"Can you please clarify what you mean with echodata?

Sorry @RKumar_WMF I should have specified initially that we needed to reach out to Adyen first. Adyen should be taking the field that Ingenico sends over as PROFILETOKEN and putting it in a CSV column that Adyen calls 'echodata'.

As long as we're talking to Ingenico too, we can ask them if all the rows in their files had a 'PROFILETOKEN' field, or if some 4% of the rows had blank PROFILETOKEN.

The other problem I found with that field was that some number of the Ingenico tokens had a series of their characters replaced with *****. We can ask Ingenico if they did that.

Adyen has responded to the query. Since FR-tech is already copied on the emails, I am not posting the updates here. Please let me know if you would like me to.

From Adyen: I have added an extra column to the results file containing the unmasked profile token at the end ( Column Name: PROFILETOKEN )

The complete list of the 5 results files can be found here: https://drive.google.com/drive/folders/1jrlJpvHRrr0uv0XGMoExHCszpPjUqXh-?usp=drive_link . It’s a sub-folder of the original shared link that Laura provided so hopefully you won’t have any issues opening it.

I left the original echodata column along with the new, unmasked column PROFILETOKEN just as an extra check to ensure the merging went OK and can be a reference point as well.

Hopefully this is now sufficient for the results file on your side but if anything else is needed , please let us know.

For the 4 cycle days 16th-19th, we actually saw fewer failures among the migrated tokens than among the always-Adyen ones: 3.02% migrated token failure vs 3.23% non-migrated. That plus the similar failure numbers for the earlier batches has me convinced to migrate all the rest of the tokens. Will start on that today.

@Ejegg Great! THANK you!

OK, I have migrated all of the tokens in all of the CSV files from Adyen. That leaves us with 60 monthly recurrings still on Ingenico. I'll mail a file of those missing tokens to our Adyen and Ingenico contacts to see who can find those.

This is my last working day before next year, so to avoid trying to charge Ingenico donors in January, I can postpone the next charge date for the 29 unmapped donors who are due to be charged in the first week of the month (to February rather than January).

Change 965844 merged by Ejegg:

[wikimedia/fundraising/crm@master] Scripts for doing token migration in the db

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