Page MenuHomePhabricator

Update Civi record with additional data from audit files
Closed, ResolvedPublic4 Estimated Story Points

Description

We need to avoid the Superman III scenario. We should update fee from transaction and the currency conversion.

We currently only send information from audit files to CiviCRM if there is a refund or if a transaction is completely missing in the DB.

Some audit files have additional information, for example, underlying card type for a transaction only coded as 'Apple Pay' from the front end.

It's possible we could skip the log file grepping step for updates since that info should already exist. We will need to be careful that the update with a reduced field set doesn't overwrite any good data with defaults.

Note that this will increase queue load, so we would want to be able to turn this off at the audit parser during peak donation times.

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

@Ejegg how is that coffee crop looking? Did we resolve?

We still need to save the coffee crop! And to update existing records with fee information!

So to update the existing records with fee information, I propose we add a new kind of message to the donations queue consumer. We'd flag it with message_type=update or the like. It would contain a contribution_id and a small amount of extra data, like the fee

When the audit processors find that a donation already exists in Civi they would send an update message rather than dropping the message completely.

Handling this in the donations queue consumer would make sure we're not trying to write to the contribution table at the same time we are inserting another contribution. We'd have to check that the update is really fast so it doesn't cause more queue backups.

@EMartin @ERoden-WMF flagging this task for discussion - we're wondering how valuable having raw fee data coming into Civi would be - knowing that it isn't always reliable (i.e. it seems like it's adjusted upon reconciliation at the end of the month). Do we anticipate this being useful or does fundraising just refer to reporting from processors/finance to analyze fees?

@AKanji-WMF @ERoden-WMF My instinct is that this sort of data should be picked up in a finance application and analyzed there. Civi may be the wrong place for it but it's what we have. What we do today is manual, time consuming and cumbersome to grab and analyze frankly and not sophisticated or nimble. It needs some sort of systemic automation given how frequently we should track it. I know Finance is pulling it from reports and keying it, as are we. I'd like to improve this but aside from the data fields, we need the analytics on top of it to be worthwhile.

Thanks @EMartin I think it's okay to put it in Civi in a field we're not using to track revenue. We could then build out reporting in Superset to alleviate some of your manual work. Do the adjustments Anil mentioned mean that this isn't going to be helpful/giving you an incomplete picture.

@ERoden-WMF @AKanji-WMF I think it would have value. The adjustments are to do with intra-company transfers. If we want to store in Civi and build something in Superset then ok.

Thanks @ERoden-WMF and @EMartin - with the context of FR-Tech heading into a maintenance window/code freeze - checking to see if the hope is that we are able to start recording this fee data during our big english campaigns? Depending on how risky the actual work is (to our CRM resilience) we may have to postpone, but wanted to check on the ideal state from your end.

@AKanji-WMF I don't see this as urgent, Anil and can wait until after freeze.

Thanks! Moving to Near Future.

AKanji-WMF changed Risk Rating from N/A to Informational.Apr 2 2025, 6:24 PM
AKanji-WMF changed Risk Rating from N/A to Informational.Apr 2 2025, 6:26 PM
AKanji-WMF changed Risk Rating from N/A to N/A.
AKanji-WMF changed Risk Rating from N/A to Informational.Apr 16 2025, 6:18 PM

bringing transaction fee associated tasks into +1 for discussion

Change #1193963 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/dev@master] Add settle queue

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

Change #1194293 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Add index to SettlementTransaction table

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

Change #1193963 merged by Ejegg:

[wikimedia/fundraising/dev@master] Add settle queue

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

Change #1194293 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add index to SettlementTransaction table

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

Change #1195343 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/SmashPig@master] Align amount fields between payment & settlement reports

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

Change #1195772 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Initial handling for additional audit information

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

Change #1195824 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Record fees when they come in

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

Change #1195343 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Align amounts passed up with Settlement report

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

Change #1195772 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Initial handling for additional audit information

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

Change #1196225 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Do not update contribution fields on settle for negative transactions

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

Change #1196538 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Fixes to do a batch test with payout validation

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

Change #1196225 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Do not update contribution fields on settle for negative transactions

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

Change #1196538 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Fixes to do a batch test with payout validation

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

Change #1195824 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Record fees when they come in

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

Change #1196885 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Add debug & non do not type-hint fatal

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

Change #1197633 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Fix incorrect field name on gateway_txn_id

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

Change #1197633 merged by Eileen:

[wikimedia/fundraising/crm@master] Fix incorrect field name on gateway_txn_id

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

Change #1196885 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add debug & non do not type-hint fatal

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

Change #1201845 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Avoid double settlement

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

Change #1201845 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Avoid double settlement

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

Change #1204102 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/SmashPig@master] Handle invoicededuction

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

Change #1204102 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Handle invoicededuction

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

XenoRyet set Final Story Points to 8.