Page MenuHomePhabricator
Create Task
Maniphest T430865

Add special log table for previous segments & statuses for Analytics
Closed, ResolvedPublic
Actions

Description

This will avoid them having to calculate what past segments and statuses would have been at the end of each quarter. Will need to investigate the feasibility of backfilling from the log tables as well, as Analytics currently calculates these back 6 years.
We'll store a unique id, the log_date, the contact id and the updated values for all segments and statuses and, for convenience, a bitfield indicating which fields have changed for each row (0001 indicates the first field changed, 0010 the second field and so on).

Details

Related Changes in Gerrit:
SubjectAuthorRepoBranchLines +/-
Switch Donor History table to order by log id as multiple changes at the same timestamp can be confusing otherwiseLars SGwikimedia/fundraising/crmmaster+1 -1
Ensure years_consecutive is 0 if no valueLars SGwikimedia/fundraising/toolsmaster+3 -3
Add previous segment & change date to Acoustic exportLars SGwikimedia/fundraising/toolsmaster+173 -5
Test fix for donor segment / status historyLars SGwikimedia/fundraising/crmmaster+2 -2
Add special log table for previous segments & statuses for AnalyticsLars SGwikimedia/fundraising/crmmaster+351 -0
Customize query in gerrit

Related Objects

Event Timeline

Lars created this task.Jul 1 2026, 5:12 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 1 2026, 5:12 PM
Lars moved this task from Triage to Current Sprint on the Fundraising-Backlog board.Jul 1 2026, 5:12 PM
Lars claimed this task.Jul 6 2026, 7:47 PM
Lars moved this task from Backlog to Doing on the Fundraising Sprint: Loud carpet board.
Lars added a comment.Jul 6 2026, 11:17 PM

Still to do: Display the latest segment change and change date from this table on the contact summary.

gerritbot added a comment.Jul 6 2026, 11:20 PM

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

[wikimedia/fundraising/crm@master] Add special log table for previous segments & statuses for Analytics

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

gerritbot added a project: Patch-For-Review.Jul 6 2026, 11:20 PM
gerritbot added a comment.Jul 7 2026, 11:20 PM

Change #1307869 merged by Eileen:

[wikimedia/fundraising/crm@master] Add special log table for previous segments & statuses for Analytics

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

gerritbot added a comment.Jul 7 2026, 11:22 PM

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

[wikimedia/fundraising/crm@master] Add tab for DonorHistory

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

gerritbot added a comment.Jul 8 2026, 12:32 AM

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

[wikimedia/fundraising/crm@master] Test fix for donor segment / status history

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

gerritbot added a comment.Jul 8 2026, 1:24 AM

Change #1308241 merged by Eileen:

[wikimedia/fundraising/crm@master] Test fix for donor segment / status history

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

Lars mentioned this in T431586: Backfill donor status & segment history.Jul 8 2026, 2:45 PM
Lars mentioned this in T431741: Merging contacts can create multiple segment/status change rows in wmf_donor_history.Jul 9 2026, 4:29 PM
gerritbot added a comment.Jul 9 2026, 7:52 PM

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

[wikimedia/fundraising/tools@master] Add previous segment & change date to Acoustic export

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

gerritbot added a comment.Jul 10 2026, 12:04 AM

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

[wikimedia/fundraising/tools@master] Ensure years_consecutive is 0 if no value

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

gerritbot added a comment.Jul 14 2026, 1:42 AM

Change #1309289 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Add previous segment & change date to Acoustic export

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

gerritbot added a comment.Jul 14 2026, 1:42 AM

Change #1309312 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Ensure years_consecutive is 0 if no value

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

AKanji-WMF edited projects, added: Fundraising Sprint: Mediocre Ikea Kit; removed: Fundraising Sprint: Loud carpet.Jul 14 2026, 5:20 PM
AKanji-WMF moved this task from Backlog to Doing on the Fundraising Sprint: Mediocre Ikea Kit board.
gerritbot added a comment.Jul 23 2026, 9:21 PM

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

[wikimedia/fundraising/crm@master] Switch Donor History table to order by log id as multiple changes at the same timestamp can be confusing otherwise

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

gerritbot added a comment.Jul 23 2026, 11:37 PM

Change #1315065 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Switch Donor History table to order by log id as multiple changes at the same timestamp can be confusing otherwise

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

Lars moved this task from Doing to Done on the Fundraising Sprint: Mediocre Ikea Kit board.Jul 23 2026, 11:55 PM
SBurnett-WMF edited projects, added: Fundraising Sprint: Notorious B.E.D.; removed: Fundraising Sprint: Mediocre Ikea Kit.Aug 4 2026, 6:34 PM
Lars moved this task from Backlog to Done on the Fundraising Sprint: Notorious B.E.D. board.Aug 4 2026, 9:58 PM
AKanji-WMF added a project: fr-current-sprint.Aug 10 2026, 6:16 PM
SBurnett-WMF added a project: fr-tech-maintenance.Aug 11 2026, 4:54 PM
XenoRyet closed this task as Resolved.Aug 11 2026, 6:57 PM
XenoRyet set Final Story Points to 4.
Damilare mentioned this in T434610: Sprint N Priorities.Aug 11 2026, 8:18 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits