Page MenuHomePhabricator
Create Task
Maniphest T170972

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

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

Show related patches Customize query in gerrit

Related Objects
Search...

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
• DStrine moved this task from Triage to Sprint +2 on the Fundraising-Backlog board.Jul 18 2017, 7:35 PM
• 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 merged a task: T105254: Add field data back into Silverpop export.Feb 15 2019, 11:02 PM
• DStrine moved this task from Sprint +2 to Sprint +1 on the Fundraising-Backlog board.
• DStrine subscribed.

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

• CCogdill_WMF added a comment.Feb 15 2019, 11:10 PM

Awesome, thanks!

• DStrine moved this task from Sprint +1 to Triage on the Fundraising-Backlog board.Feb 19 2019, 7:22 PM
• DStrine moved this task from Triage to Sprint +1 on the Fundraising-Backlog board.Feb 19 2019, 9:29 PM
• CCogdill_WMF added a comment.Feb 21 2019, 1:20 AM

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!

• CCogdill_WMF added a comment.Feb 27 2019, 5:42 PM

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?

Ejegg added subscribers: Eileenmcnaughton, Ejegg.Feb 27 2019, 5:50 PM

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.

• CCogdill_WMF added a subscriber: • JohnLewis.Feb 27 2019, 8:53 PM

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

• CCogdill_WMF removed a subscriber: • JohnLewis.Feb 27 2019, 8:54 PM
Eileenmcnaughton added a comment.Feb 27 2019, 9:00 PM

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

Eileenmcnaughton added a comment.Feb 27 2019, 9:06 PM

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

• CCogdill_WMF added a subscriber: • JohnLewis.Feb 27 2019, 10:17 PM

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

Eileenmcnaughton added a comment.Feb 27 2019, 10:19 PM

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

• DStrine removed a subscriber: • JohnLewis.Feb 27 2019, 10:35 PM
• CCogdill_WMF added a comment.Feb 27 2019, 10:41 PM

Sounds good!

• DStrine added a comment.Feb 27 2019, 11:51 PM
This comment was removed by • DStrine.
• CCogdill_WMF added a comment.May 28 2019, 4:58 PM

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!

• CCogdill_WMF added a subscriber: • MNoorWMF.May 28 2019, 7:36 PM

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

• DStrine moved this task from Sprint +1 to Current Sprint on the Fundraising-Backlog board.May 28 2019, 8:28 PM
• DStrine added a project: Wikimedia-Fundraising-CiviCRM.
• DStrine added a project: Fundraising Sprint King Kong vs. Mozilla.May 28 2019, 8:36 PM
Eileenmcnaughton claimed this task.May 28 2019, 8:55 PM
gerritbot added a comment.Jun 5 2019, 2:17 AM

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

gerritbot added a project: Patch-For-Review.Jun 5 2019, 2:17 AM
Eileenmcnaughton added a comment.Jun 5 2019, 2:23 AM

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

Eileenmcnaughton moved this task from Backlog to Review on the Fundraising Sprint King Kong vs. Mozilla board.Jun 6 2019, 2:21 AM
• CCogdill_WMF added a comment.Jun 6 2019, 4:14 PM

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.

Eileenmcnaughton added a comment.Jun 6 2019, 9:52 PM

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

• DStrine added a project: Fundraising Sprint Land before Timeouts.Jun 11 2019, 8:00 PM
Eileenmcnaughton changed the point value for this task from 2 to 4.Jun 24 2019, 8:28 PM
Eileenmcnaughton moved this task from Backlog to Doing on the Fundraising Sprint Land before Timeouts board.Jun 24 2019, 9:56 PM
Eileenmcnaughton added a subtask: T222958: New total fields needed for MG prospecting.Jun 24 2019, 11:05 PM
gerritbot added a comment.Jun 24 2019, 11:15 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
gerritbot added a comment.Jun 25 2019, 12:06 AM

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

gerritbot added a comment.Jun 25 2019, 12:06 AM

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

gerritbot added a comment.Jun 25 2019, 12:16 AM

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

gerritbot added a comment.Jun 25 2019, 12:18 AM

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

gerritbot added a comment.Jun 25 2019, 12:59 AM

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

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

gerritbot added a comment.Jun 25 2019, 1:26 AM

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

gerritbot added a comment.Jun 25 2019, 1:26 AM

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

gerritbot added a comment.Jun 25 2019, 1:29 AM

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

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

gerritbot added a comment.Jun 25 2019, 1:29 AM

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

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

gerritbot added a comment.Jun 25 2019, 1:43 AM

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

gerritbot added a comment.Jun 25 2019, 2:13 AM

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

• DStrine added a project: Fundraising Sprint Men In Slack.Jun 25 2019, 8:44 PM
gerritbot added a comment.Jun 27 2019, 1:53 AM

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

Eileenmcnaughton moved this task from Backlog to Doing on the Fundraising Sprint Men In Slack board.Jun 27 2019, 6:06 AM
• CCogdill_WMF mentioned this in T225544: Rename Field in IBM export.Jul 3 2019, 8:27 PM
gerritbot added a comment.Jul 4 2019, 5:19 AM

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

gerritbot added a comment.Jul 4 2019, 5:26 AM

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

gerritbot added a comment.Jul 8 2019, 11:50 PM

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

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

gerritbot added a comment.Jul 8 2019, 11:51 PM

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

gerritbot added a comment.Jul 9 2019, 1:55 AM

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

gerritbot added a comment.Jul 9 2019, 1:55 AM

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

• DStrine added a project: Fundraising Sprint Never Ending Query.Jul 9 2019, 8:36 PM
gerritbot added a comment.Jul 10 2019, 5:25 AM

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

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

gerritbot added a comment.Jul 10 2019, 7:09 AM

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

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

Eileenmcnaughton mentioned this in T222958: New total fields needed for MG prospecting.Jul 11 2019, 2:02 AM
Eileenmcnaughton moved this task from Doing to Review on the Fundraising Sprint Men In Slack board.Jul 11 2019, 6:45 AM
Eileenmcnaughton moved this task from Backlog to Doing on the Fundraising Sprint Never Ending Query board.Jul 17 2019, 5:08 AM
Eileenmcnaughton moved this task from Doing to Pending Deployment on the Fundraising Sprint Never Ending Query board.Jul 17 2019, 5:11 AM
• CCogdill_WMF added a comment.Jul 17 2019, 4:23 PM

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

Eileenmcnaughton added a comment.Jul 17 2019, 7:26 PM

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

• CCogdill_WMF added a comment.Jul 17 2019, 7:41 PM

Got it, thank you!

Eileenmcnaughton added a comment.Jul 17 2019, 7:48 PM

@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

• CCogdill_WMF added a comment.Jul 17 2019, 10:08 PM

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.

Eileenmcnaughton added subscribers: NNichols, CaitVirtue.Jul 17 2019, 10:13 PM

@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

• CCogdill_WMF added a comment.Jul 17 2019, 10:17 PM

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?

Eileenmcnaughton added a comment.Jul 17 2019, 10:18 PM

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

• CCogdill_WMF mentioned this in T228349: Make sure silverpop export does not include endowment donations (except in explicit endowment fields).Jul 17 2019, 10:59 PM
Eileenmcnaughton moved this task from Pending Deployment to Deployed on the Fundraising Sprint Never Ending Query board.Jul 19 2019, 6:27 AM
• DStrine closed this task as Resolved.Jul 23 2019, 8:04 PM
Eileenmcnaughton closed subtask T222958: New total fields needed for MG prospecting as Resolved.Jul 24 2019, 8:37 AM
gerritbot added a comment.Jul 29 2019, 9:43 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

gerritbot added a comment.Jul 30 2019, 3:25 AM

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

gerritbot added a comment.Jul 30 2019, 3:25 AM

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

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

Eileenmcnaughton added a comment.Jul 30 2019, 3:48 AM

@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

• CCogdill_WMF added a comment.Jul 30 2019, 9:37 PM

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

Eileenmcnaughton added a comment.Jul 30 2019, 9:39 PM

Yes, that was included

• DStrine closed subtask T228241: Add new calendar year fields to silverpop export as Resolved.Aug 6 2019, 8:07 PM
• CCogdill_WMF reopened subtask T228241: Add new calendar year fields to silverpop export as Open.Aug 12 2019, 2:25 PM
Eileenmcnaughton closed subtask T228241: Add new calendar year fields to silverpop export as Resolved.Aug 16 2019, 3:23 AM
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