jgleeson I was able to parse out a list of gateway_txn_ids from the OFFER_CLOSED IPNs:

/srv/archive/frlog1002/logs$ zgrep OFFER_CLOSED payments-listener-smashpig-2022* | egrep -o [A-Z0-9]{16} | sort | uniq | sed -e "s/^/'/" -e "s/$/',/" | tr -d '\n' > ~/closedOfferTxnIds

That puts 'em in a giant comma delimited list ready for use in a SQL query on frdev (after snipping off the last comma)

SELECT entity_id from wmf_contribution_extra where gateway_txn_id IN (
<huge list>
);
Running that query gives a list of 248 contribution IDs (civicrm_contribution.id), which are saved in frdev1001:/tmp/closedOfferContributionIds

Thanks @Ejegg. That number looks a lot lower than the numbers in the Adyen console, even if we ignore the jump in August. I'll take a look and see why there's such a big difference.

As discussed in IRC, the <huge list> included 8000 or so OFFER_CLOSED IPN messages. The reason the query only outputs 248 contributions is that the vast majority of those had come in with status=PENDING, which we were correctly not sending to Civi all along. The 248 were ones that had status=RECEIVED which we were incorrectly treating as completed.

In T316778#8219122, @Ejegg wrote:

As discussed in IRC, the <huge list> included 8000 or so OFFER_CLOSED IPN messages. The reason the query only outputs 248 contributions is that the vast majority of those had come in with status=PENDING, which we were correctly not sending to Civi all along. The 248 were ones that had status=RECEIVED which we were incorrectly treating as completed.

Thanks, @Ejegg and thanks for the detailed explanation. @krobinson will be happy to hear that the number of affected donors is much lower than I previously indicated.

The next task in this chain is T316780 which is taking the list you've kindly generated and writing a script to clear out the erroneously saved donations and related data due to this issue. I guess it wouldn't hurt to confirm with @XenoRyet, @krobinson and other donor relations folks that this is the desired next step.

Confirmed, go ahead and clear the bad data.