Page MenuHomePhabricator

New total fields needed for MG prospecting
Closed, ResolvedPublic8 Story Points

Description

Thinking ahead here to what information would be really useful to the MG team when prospecting for major gift/endowment donors as well as planned giving donors.

In the same vein as Last Donated (date) and Last Org Amount - is it possible to also add fields such as First Donated (date) and First Org Amount?

ALSO- Is it also possible to add a field that totals the count of donations?

I am finding that these data points will be extremely useful in using our prospecting database iWave to its fullest potential.

Details

Related Gerrit Patches:
wikimedia/fundraising/crm : masterAdd fields for calculating the date of the largest donation
wikimedia/fundraising/crm : masterBack out having any combined fields other than lifetime total
wikimedia/fundraising/crm : masterAdd Annual fields for endowment, add change year on year fields
wikimedia/fundraising/crm : masterFor 2017 onwards add Endowment totals AND change amounts
wikimedia/fundraising/crm : masterRemove first_donation_amount
wikimedia/fundraising/crm : masterAdd additional requested major gift fields
wikimedia/fundraising/crm : masterUpdate triggers for new calculated fields

Event Timeline

NNichols created this task.May 10 2019, 3:13 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 10 2019, 3:13 PM
Eileenmcnaughton added a subscriber: DStrine.

@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

Eileenmcnaughton added a comment.EditedJul 9 2019, 5:30 AM

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

2019-07-08 19:40:09staging, triggers as per production61 second(s) for 500 donationsAverage performance is 492 per minute
2019-07-08 19:41:18staging, triggers as per production56 second(s) for 500 donationsAverage performance is 536 per minute
2019-07-08 19:44:18staging, triggers as per production56 second(s) for 500 donationsAverage performance is 536 per minute

Afer

2019-07-08 22:04:52triggers after changing (but not first amount as yet)58 second(s) for 500 donationsAverage performance is 517 per minute
2019-07-08 22:05:59triggers after changing (but not first amount as yet)57 second(s) for 500 donationsAverage performance is 526 per minute
2019-07-08 22:09:22triggers after changing (but not first amount as yet)57 second(s) for 500 donationsAverage performance is 526 per minute

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)

2019-07-09 23:20:42triggers after changing including first amount59 second(s) for 500 donationsAverage performance is 508 per minute.
2019-07-09 23:21:47triggers after changing including first amount58 second(s) for 500 donationsAverage performance is 517 per minute.
2019-07-09 23:22:49triggers after changing including first amount59 second(s) for 500 donationsAverage performance is 508 per minute

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 ^^

2019-07-10 17:57:04triggers after the grand whammy60 second(s) for 500 donationsAverage performance is 500 per minute.
2019-07-10 17:59:18triggers after the grand whammy60 second(s) for 500 donationstriggers after the grand whammy
2019-07-10 18:00:55triggers after the grand whammy61 second(s) for 500 donationstriggers after the grand whammy
2019-07-10 18:02:22triggers after the grand whammy59 second(s) for 500 donations.triggers after the grand whammy
2019-07-10 18:03:29triggers after the grand whammy59 second(s) for 500 donations.triggers after the grand whammy
2019-07-10 18:05:48triggers after the grand whammy59 second(s) for 500 donationstriggers after the grand whammy

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.

Eileenmcnaughton added a subscriber: Ejegg.EditedJul 11 2019, 2:02 AM

@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)

FieldNameStatusEndowment giftsMeaningComments
FY 2018-2019 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2017-2018 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2016-2017 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2015-2016 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2014-2015 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2013-2014 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2012-2013 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2011-2012 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2010-2011 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2009-2010 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2008-2009 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2007-2008 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2006-2007 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2019-2020 totalExistingExcludedFinancial year totalsCalculated (fast)
FY 2020-2021 totalExistingExcludedFinancial year totalsCalculated (fast)
Last DonatedExistingExcludedLast donation dateCalculated (fast)
Last CurrencyExistingExcludedLast currency donated injoined field - these are the slower ones
Last Orig AmountExistingExcludedLast currency donated injoined field - these are the slower ones
Last USD AmountExistingExcludedLast currency donated injoined field - these are the slower ones
Lifetime USD TotalExistingExcludedLife time totalCalculated (fast)
First Donation Amount (USD))new T222958Excludedamount of first donation in USDjoined field - these are the slower ones - although USD may not add time over & above orig amount
First donation datenew T222958ExcludedDate of first donationCalculated (fast)
Number of Donationsnew T222958ExcludedNumber of donationsCalculated (fast)
2006 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2007 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2008 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2009 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2010 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2011 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2012 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2013 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2014 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2015 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2016 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2017 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2018 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2019 totalnew T170972ExcludedCalendar year totalCalculated (fast)
2020 totalnew T170972ExcludedCalendar year totalCalculated (fast)
Largest Donationnew - experimentalExcludedLargest single donationCalculated (fast)
Largest Donation Datenew - experimentalExcludedDate of Largest single donationjoined, Slow
Lifetime Donations Including Endowment (USD)new - experimentalIncludedTotal USD donations including endowmentCalculated (fast)
Change 2017-2018 totalnew - experimentalExcluded2020 total less 2019 totalCalculated (fast)
Change 2018-2019 totalnew - experimentalExcluded2019 total less 2018 totalCalculated (fast)
Change 2019-2020 totalnew - experimentalExcluded2020 total less 2019 totalCalculated (fast)
Change 2020-2021 totalnew - experimentalExcluded2021 total less 2020 totalCalculated (fast)
Endowment Last donation datenew - experimentalOnly EndowmentLast endowment donation dateCalculated (fast)
Endowment First donation datenew - experimentalOnly EndowmentFirst endowment donation dateCalculated (fast)
Endowment Lifetime Donations (USD)new - experimentalOnly EndowmentEndowment lifetime totalCalculated (fast)
Endowment Number of Donationsnew - experimentalOnly EndowmentNumber of Endowment donationsCalculated (fast)
Endowment Largest Donationnew - experimentalOnly EndowmentLargest donation to endowmentCalculated (fast)
Endowment FY 2018-2019 totalnew - experimentalOnly EndowmentFinancial year totalCalculated (fast)
Endowment FY 2019-2020 totalnew - experimentalOnly EndowmentFinancial year totalCalculated (fast)
Endowment FY 2020-2021 totalnew - experimentalOnly EndowmentFinancial year totalCalculated (fast)
Endowment CY 2018 totalnew - experimentalOnly EndowmentCalendar year totalCalculated (fast)
Endowment CY 2019 totalnew - experimentalOnly EndowmentCalendar year totalCalculated (fast)
Endowment CY 2020 totalnew - experimentalOnly EndowmentCalendar year totalCalculated (fast)

(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

Eileenmcnaughton added a comment.EditedJul 12 2019, 8:51 PM

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

Eileenmcnaughton set the point value for this task to 8.Jul 23 2019, 1:03 AM
Eileenmcnaughton closed this task as Resolved.Jul 24 2019, 8:37 AM