TBD in the Q4 maintenance window.

@AKanji-WMF we had better do this in the next sprint so we can add along with the segment fields in the planned outage

Note we need to find out from stake holders what fields they need / whether we can remove some

We can only have 64 searchable fields in this list https://civicrm.wikimedia.org/civicrm/admin/custom/group/field?reset=1&action=browse&gid=18

We have 64 right now - but we want to add 2 new segmentation fields + fields for the next 2-3 years of totals

@ERoden-WMF was going to take a look after our last call

Thanks @Eileenmcnaughton - I'll add it to the Sprint - wondering if Operations can make the calls on the fields we can lose - @ERoden-WMF ?

I have created a spreadsheet of what fields we are looking at

https://docs.google.com/spreadsheets/d/1cNi_NFQVaZfm3Hy-JzSesAZXmhxbTgrZOTkmo_Ijg_k/edit?usp=sharing

It shows we want to add 14 more indexes and there is a column proposing which ones we might remove. I did that as a starting point only.

From a performance point of view we should aim to cull more than the absolute minimum we can get away with (as long as it does not impact on those using the data)

We could consider

• defining a cut off (eg. 2018) and for the period before that just having 'annual_fund_total_pre_2018' with a sum of all donations prior to that (I think there were no endowment at that point.
• what level of detail we need re funds, totals, and changes and for how long
• do we need the 'Is FY 2021-2022 donor' fields - I'm trying to recall why we added them as they duplicate the total_2021-2022 fields... perhaps we had them before we switched to totals & never removed them

Thanks @AKanji-WMF & @Eileenmcnaughton. I've just requested access to the spreadsheet and will add some notes on which ones we can remove.

I updated sharing settings to the foundation

@ERoden-WMF did you take a look at the fields - I need to write the script to update these this week so we can run it next week

Hi @Eileenmcnaughton thanks for the nudge. I think the ones you proposed look fine, but just confirming you're removing the indexing on those columns? Not removing the columns? So the columns will remain, be named the same and we'll still be able to access for analytics purposes. If that's the case then no changes on my end.

Erica - yes that is correct - however if the *only* use for the older year
columns is for analytics then we should probably remove them from CiviCRM.
Superset already has that data and it is not changing so having those
columns in CiviCRM doesn't help analytics.

The costs of having the data are not such that we would remove them if they
are useful but if they actually aren't then we need to be mindful that they

• clog up the UI, including in drop downs in search kit
• increase the risk of users selecting the wrong field
• have some performance impact
• take up disk space which causes longer backup & restores

Got it, thanks @Eileenmcnaughton. Let me double check with major gifts if they use any of them when using Searchkit. I imagine in most cases you could get to the data through other means (like selecting a date range), but I'll confirm and get back to you shortly.

I'm documenting fr-tech steps in case some one else picks this up as I'm conscious I have less than 2 working days left before I fly out. N

Based on the current spec what we need to close this. Note that there is a hard-mysql-limit on the number of indexes per table - which is why we need to drop some. (I'm hoping we can drop some fields too but this is based on discussion so far)

1. Add a function to the upgrader class in the wmf-civicrm extension (CRM_Wmf_Upgrader) to drop indexes on the fields marked as changing to 0 in the sheet - currently

all_funds_change_2018_2019
all_funds_change_2019_2020
all_funds_total_2018_2019
all_funds_total_2019_2020
total_2015_2016
total_2016
total_2016_2017
total_2017
total_2017_2018
total_2018
total_2018_2019
total_2019
total_2019_2020
endowment_total_2018
endowment_total_2018_2019
endowment_total_2019
endowment_total_2019_2020

2. Update the metadata for these fields in calculatedData->getWMFDonorFields() to is_searchable =0 (it might be that the fields don't quite make sense when doing this - in which case minor tweaks to the field set are OK )

3. adjust protected const WMF_MAX_ROLLUP_YEAR from 2023 to 2025

4. assuming the above passes CI it should be enough to add another function to call addCustomFields

5. test on staging to get a timing - probably it will do 2 queries (1 to drop the indexes & one to add new fields & indexes) & each will take a hour each. If you have trouble with any trigger issues on staging Dallas can give permission to triggers there.

Per:

We need an outage to update these & need to do before Big English.
We need to discuss if we can drop or de-index older fields at the same time. We should do this in the first Maintenance window after 1 July

There is one in a week: T333485: Fr-Tech 2022q4 maintenance window
And then one in late October: T337579: FR-Tech FY2324Q2 maintenance window

Hi @Eileenmcnaughton I checked with Major Gifts and they'd like to keep the FY fields as they use them for searching for annual appeal donors in searchkit, all other proposed changes we're good to move forward.

@ERoden-WMF - so they need calendar year & financial going right back to 2006?

@Eileenmcnaughton just FY, but I'm confirming how far back they need. Ideally, we'd just keep the last 5 or so. I'll confirm tomorrow.

Thanks @ERoden-WMF - we currently have calendar and financial year fields going back to 2006.

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

[wikimedia/fundraising/crm@master] Drop indexes on older WMFDonor fields

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

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

[wikimedia/fundraising/crm@master] Add upgrade to add new segment & year fields to wmf_donor

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

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

[wikimedia/fundraising/crm@master] Enhanced error reporting

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

Change 930288 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Drop indexes on older WMFDonor fields

Reason:

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

Change 930290 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Enhanced error reporting

Reason:

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

Hi @Eileenmcnaughton major gifts just needs FY (not calendar) and they only need 5 years back so anything prior to 2017/18 can be removed.

@ERoden-WMF I created a new phab to discuss field removal - see https://phabricator.wikimedia.org/T339314

Note that one thing we need to be mindful of is that we are potentially using the financial year totals as part of our condition to find who needs updating. So we need to be careful not to remove any of them if we will want to consider those years in our criteria. (I think we are OK on that front but will check that again later)

Change 930289 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add upgrade to add new segment & year fields to wmf_donor

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

The script was run and triggers were updated and deployed today.