Add last otg amount change fields to Acoustic export
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Lars
	Jul 10 2026, 3:36 PM

Project Tags

Referenced Files

None

Subscribers

Description

See https://docs.google.com/spreadsheets/d/17Pc1tIvqol6XJhuu97RlOfwy_Avbb9MEaRwhy2F529I/edit?gid=292544213#gid=292544213
This is a native amount fields paired with a date field that is set whenever the corresponding field is changed

Minor note: We can't calculate the otg change when the currency has changed, so we ignore any contributions that aren't in the currency of the most recent contribution (effectively, we treat each donor's history of giving on a per currency basis and this field only holds the change amount from the currency of their latest donation).

Details

Related Changes in Gerrit:

Subject	Author	Repo	Branch	Lines +/-
Add last OTG amount change and date	Lars SG	wikimedia/fundraising/tools	master	+223 -77
Change last_donation_ fields to OTG-only	Lars SG	wikimedia/fundraising/crm	master	+16 -6
Change last_donation_ fields to OTG-only	Lars SG	wikimedia/fundraising/tools	master	+59 -43

Customize query in gerrit

Related Objects

Mentioned In: T435960: Sprint O priorities

Event Timeline

Lars created this task.Jul 10 2026, 3:36 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 10 2026, 3:36 PM

Lars moved this task from Triage to Current Sprint on the Fundraising-Backlog board.Jul 10 2026, 3:36 PM

Lars moved this task from Backlog to Doing on the Fundraising Sprint: Loud carpet board.Jul 13 2026, 2:25 PM

Lars renamed this task from Add last otg amount change fields to Civi & Acoustic to Add last otg amount change fields to Acoustic export.Jul 13 2026, 4:47 PM

This would be most efficiently calculated on export to Acoustic if the silverpop_export_latest table and the all_funds_latest_donation_date that is used to calculate it were OTG only instead of the current all donations, including recurring. To be discussed with Audience and Online, as it seems that Online at least may have already understood the following fields which are derived from silverpop_export_latest to be OTG only:

both_funds_latest_currency
both_funds_latest_currency_symbol
both_funds_latest_donation_date
both_funds_latest_donation_source
both_funds_latest_native_amount
both_funds_latest_payment_method

Aiming for August maintenance window for this.

Lars removed a project: Fundraising Sprint: Loud carpet.Jul 13 2026, 4:50 PM

We have agreed to change the both_funds fields as proposed above. This will require a recalculation of the both_funds_latest_donation_date for all contacts whose most recent donation is recurring.

Change #1313302 had a related patch set uploaded (by Lars SG; author: Lars SG):

[wikimedia/fundraising/crm@master] Change last_donation_ fields to OTG-only

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

gerritbot added a project: Patch-For-Review.Jul 21 2026, 6:51 PM

Change #1313343 had a related patch set uploaded (by Lars SG; author: Lars SG):

[wikimedia/fundraising/tools@master] Change last_donation_ fields to OTG-only

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

Change #1313343 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Change last_donation_ fields to OTG-only

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

Change #1314118 had a related patch set uploaded (by Lars SG; author: Lars SG):

[wikimedia/fundraising/tools@master] Add last OTG amount change and date