@DStrine this task is logically linked to https://phabricator.wikimedia.org/T170972 in terms of the tech work involved (I made it a subtask to draw that connection) since it makes sense to do them both together

Change 518868 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add additional requested major gift fields

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

Change 521393 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Update triggers for new calculated fields

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

Change 521400 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add new custom fields and remove discontinued ones.

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

Testing on staging so far shows marginal loss of speed in removing the is_xxx_donor fields and adding the year_xxx fields along with first_donated_date and number_donations. I don't think this is outside the margin of error - note the first run is generally slower due to warming up caches

I have not yet tried first_donation_amount which IS likely to have a speed hit. Here are my test results

command drush qperf-d 500 "staging, before change"

Before

Afer

I added the first donation amount fields - which was the one I thought would cause a performance issue & my . feeling is . the drop is outside the margin or error on this - but still much less than the May speed increase (which . I think was the php upgrade)

Change 521393 abandoned by Eileen:
Update triggers for new calculated fields

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

I just tested adding a LOT more fields with triggers & the performance was in the same ballpark as ^^

I would conclude from that the the performance degrades pretty slowly when we add additional summary calculations and that we can add a dozen calculated fields for the performance 'price' of adding one joined field - ie the joined fields are details relating to the last donation or first donation (first usd amount, last usd amount) - the calculated fields are everything else.

My feeling is that if people feel there are other . fields they want we should add them at this stage. There is some risk around table size & deadlocks that I can't quantify but I don't think it's strong enough not to add useful fields.

@DStrine @Ejegg I have some concern that once we add these fields there are others people will request and that the work of adding them now is a lot less than later (when we would need another outage, another code dive, another backfill process etc).

I order to get a feel for what the impact would be of adding more I did some tests on staging - adding a bunch of field that I imagined people might possibly want and adding a whole swag more didn't seem to be a huge performance hit (in my tests add ALL the new fields in the table below & automating them brought staging throughput down from 536 to 508 per minute - which might have been mostly the 2 join fields that were requested. Note the May php upgrade increased this metric by 100+ per minute so we are still running much faster than last BE.

The full list of extra fields I tested (note I removed the is_2007_donor etc fields)

(note experimental field added in https://gerrit.wikimedia.org/r/#/c/wikimedia/fundraising/crm/+/521820/ - requested fields are in this chain https://gerrit.wikimedia.org/r/#/c/wikimedia/fundraising/crm/+/521813/

@Eileenmcnaughton These experimental fields are really interesting. Would you like me to explore interest among the team? And if so how much time do we have to make a decision?

@NNichols yes please - at this stage the outage plan is for next Thurs (yours) so we'd need to add any extras in by Wed
@CCogdill_WMF you might have thoughts for email too.... ^^

@Eileenmcnaughton So is it possible to have ALL of them? We're just thinking that even if we don't add them to our summary screen view it would be great to be able to search and export these types of calculations when we are reviewing prospect lists.
For example, Danny is really interested in being able to search for donor sets that might have had an increase of $500 between two calendar years.

Is it possible to also get the change in calendar year between 2017-2018?

@Eileenmcnaughton I've consolidated my last message here and some of the team's new Qs and requests here:

1. Is it possible for the fields First Donation Amount, First donation date, Number of Donations, and Largest Donation to include Endowment gifts?
2. Can we add a field "Largest Donation Date" so that we can have the same corresponding information as First and Last?
3. Does the field Lifetime Donations Including Endowment (USD) refer to a count total or total in USD?
4. Is it possible to also get the change in calendar year between 2017-2018?
5. Can we have ALL the fields?

@NNichols so of the above the trickiest part is the Largest donation date field as that requires an extra DB join so it is code-complex and will also have a speed impact. It's not that we can't do it but it has a greater performance cost than the other fields so I guess it depends if you really want it or just only kinda do. I don't know how to quantify the trade off between how much you want it vs performance so don't ask me that :-)

1. yes
2. see above
3. currently it is the total $ amount - but it could be whichever you want
4. yes
5. yes - I tried to indicate above which fields are the 'expensive' ones because we could pretty much add all the 'cheap ones' for the performance (& dev time) price of one expensive one. The things that have a higher cost are getting a second piece of information about a max of min - ie. get me the amount for the contribution with the highest or lowest date or conversely get me the date of the contribution with the highest or lowest amount.

@Ejegg any thoughts

I just want to float an idea I have about getting data about donation pattern. I think we could create a field like

Donor Patter to 2018 with options like

• Donated five years earlier only
• Donated Five & four years earlier
  
  right through to
• Donated for last four years
• Donated for all five consecutive years.

The values the database would be like
10000 = Donated in year 1 & not since
10101 Donated in years 1,3,5
11111 Donated in years 1,2,3,4,5

I feel like this would create an option to select 'people who donated in 3 or more of the last 5 years prior to 2018'. We probably create donor pattern fields for the same set of years as Endowments ie 2018 onwards I expect.

I think we'd get 32 variants

We could simplify to just one number (how many of the last 5 years they donated in) - but that feels less nuance than what I've been hearing - we could ALSO have a field 'how many of the last 3 years have they made gifts over $500 in' - I'd have to test performance it we want these but I think we could have a lot of these for the 'price' of adding one field that requires a join

@CCogdill_WMF just .pinging you to look at the above ^^

Hi @Eileenmcnaughton Thanks for this.

1. Perfect. Please include Endowment gifts in those fields.
2. I did a little research over the weekend and having this field would greatly benefit our prospecting scoring system. So YES I would very much like to have the Largest Donation Date.
3. Again perfect. We just wanted clarification that it was $ total.
4. Thank you
5. We would love to have all of the "cheap" options including adding change in calendar year between 2017-2018 and the including of Endowment gifts for the First amount, first date, largest amount, largest date, last amount, last date, and overall count of gifts. Of the ones that are labeled as slow we don't need First Donation Amount (unconverted) (as our prospecting scoring is done in USD) and Last Currency.

Change 518868 merged by Eileen:
[wikimedia/fundraising/crm@master] Add additional requested major gift fields

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

Change 521820 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Additional calculated fields as discussed in https://phabricator.wikimedia.org/T222958#5323233

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

Change 523850 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] For 2017 onwards add Endowment totals AND change amounts

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

@NNichols b

Last currency is important for @CCogdill_WMF - I see we are calculating it in the silverpop export rather than using the field but I don't think we should drop it at this stage, but I won't add first amount original currency because I think the reason we want that for the last donation is when messaging them whereas the 'first' fields are all about segmentation

Regarding the fields - I've updated the table above - can you check it is right now for what you want?

Change 523856 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Remove first_donation_amount

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

Change 523856 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Remove first_donation_amount

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

@Eileenmcnaughton These look good to me. Danny did request adding change in calendar year between 2017-2018 is that possible?

Hey all, apologies for not adding feedback sooner! Your suggestion was
interesting, Eileen, but felt like it's achieving something similar to the
calendar year totals we're adding. Maybe I'm not seeing what problem we're
trying to solve for here since I don't typically use the Civi UI? Let me
know if there's something for me to look at here.

Change 524023 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add Annual fields for endowment, add change year on year fields

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

Change 523850 abandoned by Eileen:
For 2017 onwards add Endowment totals AND change amounts

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

Change 524023 abandoned by Eileen:
Add Annual fields for endowment, add change year on year fields

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

Okay, looks like I missed something big here! @NNichols can I get some more context on why it's important to combine endowment gifts with annual fund gifts in these "latest donation" fields? These fields are some of the most important ones we use in our fundraising appeals. I'm worried that combining this data will get us in some legal trouble because we'll end up pulling them into our annual appeal emails and may suggest that an endowment gift actually went to WMF.

Should we find a time to talk about this, or maybe sync up on what fields are really important to the online team so they keep working as we need them to?

Change 524053 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Back out having any combined fields other than lifetime total

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

Change 524053 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Back out having any combined fields other than lifetime total

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

Per chat I've updated the table - in summary except for the one field (lifetime total inc endowment) endowment & foundation gifts are not combined

Change 521820 merged by Ejegg:
[wikimedia/fundraising/crm@master] Add triggers for additional calculated fields as discussed in https://phabricator.wikimedia.org/T222958#5323233

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

Change 524071 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add fields for calculating the date of the largest donation

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

My final testing on the fields to go out tomorrow showed they were still in the same performance ballpark - seeing a high of 508 (silverpop export was running which might have caused the slightly slower earlier ones

In the final cut I removed the 2 endowment 2017 fields as I found we had hit a hard limit on number of indexes & these were added when I added change_2017_2018 because it seemed simpler code wise rather than by request - will update the table above now

+2019-07-18 00:04:14 Donation queue performance profile took 60 second(s) for 500 donations.
+ Context is : staging, triggers after the grand whammy
+Average performance is 500 per minute.
+2019-07-18 00:04:20 Starting donation queue performance profile for 500 donations.
+ Context is : staging, triggers after the grand whammy
+2019-07-18 00:05:20 Donation queue performance profile took 60 second(s) for 500 donations.
+ Context is : staging, triggers after the grand whammy
+Average performance is 500 per minute.
+2019-07-18 00:05:29 Starting donation queue performance profile for 500 donations.
+ Context is : staging, triggers after the grand whammy
+2019-07-18 00:06:28 Donation queue performance profile took 59 second(s) for 500 donations.
+ Context is : staging, triggers after the grand whammy
+Average performance is 508 per minute.
+2019-07-18 00:06:35 Starting donation queue performance profile for 500 donations.
+ Context is : staging, triggers after the grand whammy
+2019-07-18 00:07:34 Donation queue performance profile took 59 second(s) for 500 donations.
+ Context is : staging, triggers after the grand whammy
+Average performance is 508 per minute.
+2019-07-18 00:07:38 Starting donation queue performance profile for 500 donations.
+ Context is : staging, triggers after the grand whammy
+2019-07-18 00:08:37 Donation queue performance profile took 59 second(s) for 500 donations.
+ Context is : staging, triggers after the grand whammy
+Average performance is 508 per minute.

Change 524071 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Add fields for calculating the date of the largest donation

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