Page MenuHomePhabricator

Add calendar year totals fields to CiviCRM & export ? some? of them to silverpop
Closed, ResolvedPublic4 Story Points

Description

There used to be a series of Y/N fields in the Silverpop export for is_YYYY_donor, one for each year we'd been fundraising. I don't necessarily need one for every year, but we're looking to do much more advanced segmentation of our donor list this year based on giving history, and it would be great if I didn't need to write sql in order to pull these lists. Having these fields for is_pre2014_donor, is_2014_donor, and up would be so helpful.

We removed these fields because the export job was liable to time out. How much flexibility do I have to add data back in?

Thanks!

Details

Related Gerrit Patches:
wikimedia/fundraising/tools : masterAdd endowment fields to silverpop
wikimedia/fundraising/tools : masterAdd total_2018 to total_2020 to silverpop export
wikimedia/fundraising/crm : masterUpdate triggers for new calculated fields
wikimedia/fundraising/crm : masterAdd new fields for calendar year totals
wikimedia/fundraising/crm : masterMove remaining wmf_donor fields over to update_custom_fields
wikimedia/fundraising/crm : masterUpdate field add mechanism
wikimedia/fundraising/crm/civicrm : master[REF] extraction of a function to build the sql for changing a custom field.
wikimedia/fundraising/crm : masterRemove label tweak on is_x_donor fields
wikimedia/fundraising/crm : masterRemove update to is_x_donor fields
wikimedia/fundraising/crm : masterMove creation of wmf_donor fields to update_custom_fields
wikimedia/fundraising/crm : masterMove actual creation of wmf_donor fields to update_custom_fields
wikimedia/fundraising/crm : masterRemove old wmf_donor instantiation file

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
CCogdill_WMF raised the priority of this task from Medium to High.Feb 15 2019, 10:35 PM

Hi, I'm changing this task to High priority because the topic of increased email segmentation has been brought up numerous times by Lisa and other managers as a way for us to grow revenue in the future. The kind of segmentation they're talking about is totally dependent on us having these fields available in the database. Can we please add these fields back?

This is a duplicate task. I'm not sure how to merge them: https://phabricator.wikimedia.org/T105254

I think this task may end up being related: https://phabricator.wikimedia.org/T173538

DStrine added a subscriber: DStrine.

I closed T105254 and moved this back to Sprint+1. We'll look at it first thing next week.

Awesome, thanks!

We met today and discussed making the fields numerical rather than Y/N, which is good! We talked about the benefits of including the native amount rather than USD, but after considering that further, I've changed my mind. Supporting the native amount would just require too many fields and it would make the data harder to work with.

Here's all I need:

  • Data for donations in 2014 - 2019
  • USD only is fine
  • Calendar year preferred over FY (email campaigns don't happen at the same time every FY so it's a trickier metric)

I think those are my only specifications. Let me know if I missed anything!

I've noticed the number of rows imported to IBM nightly has decreased over the last two days. It's only about 5k rows smaller each day, but that's an odd phenomenon. Is it because someone is working on this task?

Guessing it's because of the job @Eileenmcnaughton just re-enabled to bring undeliverable data back from Silverpop into Civi. If that job marks an address as on_hold, it won't be exported.

Okay that makes sense. Should we be seeing the unsubscribes file grow
proportionately? Is there any amount of variance we should flag?

I think my checks when I turned it on were that there were about 400k to process - if it's only doing 5000 per day we should probably speed it up

Here is the query of how many are still to process

select count(*) FROM civicrm_mailing_provider_data WHERE event_type IN ('Opt Out', 'Reply Abuse', 'Suppressed')  AND is_civicrm_updated = 0;

Looks like there are LOTS to go - 4986118

5000 per day lines up with what we're seeing. Thanks, that makes sense!

@CCogdill_WMF we just added a 0 to the number to be processed per run - so it should speed up a bit now

This comment was removed by DStrine.

Hey all,

Bumping this task! Segmentation by donation history is a big priority for next FY so I need these fields before October--ideally sooner so we can use it to plan our send schedule.

Thanks!

@MNoorWMF ccing you so you can help me implement this when ready! :)

Change 514416 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/tools@master] Add 2018_2019_total & 2019_2020_total to silverpop export

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

@CCogdill_WMF I just put up a patch for review but it will require you to add 2 new fields to Silverpop

2018_2019_total,
2019_2020_total

However, re-looking at this it seems you wanted something about what (would have been then) pre_2014 _donor.

Major gifts have requested we add a calculated 'first donation' field - would having that in conjunction with the 2 fields above meet that need?

Also note - the 2 years are hard coded - at some point we'd need to add in 2020-21. I thought about not doing that but I feel like we should make it cleverer in conjunction with our eventual re-write to reduce the volume of change we transfer (based on contact.modified_date)

Er, weird! I sent this reply via email yesterday morning but it looks like it didn't post:

Great, Eileen! I wonder if it's possible to show this data as calendar year rather than fiscal year, though? Our fiscal year is pretty meaningless to donors. Saying, "In 2018-2019, you gave $5" sounds pretty funky.

We already have first_donation_date / first_native_amount fields in our database so no need to add that! We could probably use that field rather than the pre_2014_donor field, though--good point. No need to add that either.

@DStrine @Ejegg we only grab fiscal due to MG requirements - we could add calendar year when we add extras for MG?

Eileenmcnaughton changed the point value for this task from 2 to 4.Jun 24 2019, 8:28 PM

Change 518839 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Remove old wmf_donor instantiation file

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

Eileenmcnaughton renamed this task from Re-add fields to Silverpop export to Add calendar year totals fields to CiviCRM & export ? some? of them to silverpop.Jun 24 2019, 11:57 PM

Change 518846 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Move actual creation of wmf_donor fields to update_custom_fields

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

Change 518847 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Move creation of wmf_donor fields to update_custom_fields

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

Change 518850 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Remove update to is_x_donor fields

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

Change 518851 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Remove label tweak on is_x_donor fields

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

Change 518839 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Remove old wmf_donor instantiation file

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

Change 518846 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Move actual creation of wmf_donor fields to update_custom_fields

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

Change 518847 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Move creation of wmf_donor fields to update_custom_fields

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

Change 518850 merged by Eileen:
[wikimedia/fundraising/crm@master] Remove update to is_x_donor fields

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

Change 518851 merged by Eileen:
[wikimedia/fundraising/crm@master] Remove label tweak on is_x_donor fields

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

Change 518864 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Move remaining wmf_donor fields over to update_custom_fields

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

Change 518867 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Add new fields for calendar year totals

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

Change 519321 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm/civicrm@master] [REF] extraction of a function to build the sql for changing a custom field.

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

Change 519321 abandoned by Eileen:
[REF] extraction of a function to build the sql for changing a custom field.

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

Change 520667 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm@master] Update field add mechanism

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

Change 520667 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Update field add mechanism

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

Change 518864 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Move remaining wmf_donor fields over to update_custom_fields

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

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

Change 518867 abandoned by Eileen:
Add new fields for calendar year totals

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

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

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

Hey @Eileenmcnaughton did this get deployed during the maintenance on
Monday?

@CCogdill_WMF not the outage was pushed to tomorrow as it was going to cause Moska problems

Got it, thank you!

@CCogdill_WMF you'll need to create fields in silverpop for them to go to - the calendar fields are all named like 'total_2018' etc - there are other fields we are adding in https://phabricator.wikimedia.org/T222958#5323233 which we can also push to silverpop if you want

Thanks for asking! It would be great to have some of those endowment
fields, too. These would be great to start with:

  • Endowment last donation date
  • endowment first donation date
  • endowment number of donations

The endowment amount fields would be great, too, but unfortunately aren't
much use to us if they're all converted into USD.

@CaitVirtue @NNichols has asked that some of the totals that previously excluded Endowments now include them - notably

last donation date
last donation amount (original & usd)

I think at the moment we are just re-calculating these things in the silverpop export & all the totals you get include Endowments anyway.

However, in order to change those we should also change the 2 last currency fields to include endowments - ie you have been getting the currencies of the most recent non-endowment gift. My suspicion is that you didn't realise that & changing to 'currency of the last donation or either sort' will make more sense to you anyway

Oh huh I didn't realize that our totals were including endowment gifts. Can
we make it so that the non-endowment fields in the silverpop export are
just that--non endowment? We might get ourselves into some legal trouble if
our copy suggests that someone's last gift was to WMF when it was really to
the endowment... Why have these fields cross over when we can have two
unique fields?

oh - OK - so I should probably get you in on https://phabricator.wikimedia.org/T222958#5323233 & the discussion from there down - the including of Endowment in some fields is a request from @NNichols for segmenting - but better to discuss over there - I've updated the table to what I understand as the latest agreed

DStrine closed this task as Resolved.Jul 23 2019, 8:04 PM

Change 526261 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/tools@master] Add endowment fields to silverpop

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

Change 514416 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add total_2018 to total_2020 to silverpop export

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

Change 526261 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add endowment fields to silverpop

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

@CCogdill_WMF we are now ready to deploy the addition of

total_2018
total_2019
total_2020
endowment_last_donation_date
endowment_first_donation_date
endowment_number_donations

And the renaming of of opt-in per Bug: T225544 - we just need to . co-ordinate deployment with you
opted_in -> latest_optin_response

Perfect! This is a great time to deploy T225544. FYI @MNoorWMF.

Yes, that was included