Page MenuHomePhabricator

Add two additional fields to silverpop export
Closed, ResolvedPublic3 Estimated Story Points

Description

Related to the work in T252245, we need to request two additional fields: all_funds_latest_currency and all_funds_latest_currency_symbol

After starting to work with the new fields from T252245 in Acoustic, we realized that Donor Services would prefer that we use the all_funds fields in our copy so we can reference a donor's most recent gift, regardless of which fund it came from. This is in response to some donor complaints we've received in the past when we reference an Annual Fund gift that happened less recently than the donor's Endowment gift. Legal has approved the use of the all_funds fields in this context.

Can you please add these fields to the export? This won't be needed until close to when the en6C campaign starts in mid-September. We will need a week between receiving this field and launching the campaign to change our dynamic rulesets, so the deadline on completing this request is September 7th. I marked this task as high for that reason, but please feel free to adjust the priority as makes sense for fr-tech workflows.

subtask list:

  • Add fields to export -1sp
  • running a mysql command, populating the field goin backwards -2sp

Event Timeline

CCogdill_WMF triaged this task as High priority.Jul 9 2020, 5:04 PM
CCogdill_WMF created this task.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 9 2020, 5:04 PM
CCogdill_WMF renamed this task from Add one additional field to silverpop export to Add two additional fields to silverpop export.Jul 9 2020, 5:08 PM
CCogdill_WMF updated the task description. (Show Details)

On the Acoustic side, I've created the 2 new fields today so they're ready to be mapped to when the deployment rolls out.
both_funds_latest_currency
both_funds_latest_currency_symbol

DStrine updated the task description. (Show Details)Aug 3 2020, 8:25 PM
DStrine set the point value for this task to 3.
DStrine changed the point value for this task from 3 to 4.
DStrine updated the task description. (Show Details)
DStrine changed the point value for this task from 4 to 3.
mepps claimed this task.Aug 10 2020, 3:29 PM
mepps added a comment.Aug 11 2020, 6:13 PM

Okay trying to re-wrap my head around silverpop export. My understanding is that I'll need to add these in update_table.sql and also in rebuild_schema.sql and tests. It looks like these fields are in the wmf custom field group, though I'm unclear about all_funds versus latest non-endowment. I need to look at the db.

mepps added a comment.Aug 11 2020, 6:25 PM

I see this field in wmf donor custom: last_donation_currency. Is this what we want or do we need a new field?

Thanks to @Eileenmcnaughton for pointing out that this is calculated at the bottom of update_table.sql.

mepps added a comment.Aug 11 2020, 8:14 PM

Currency will be easy for symbol I might have to do more--either join currency table or get it from endowment earlier.
Add new fields to end of the view so we can deploy it and then ask for mapping.

mepps added a comment.Aug 12 2020, 7:10 PM

I'm trying to figure out how to get the last currency all_funds since I'll need to do a calculation like the one done for all_funds_latest_donation except that one already has the latest donation data. It feels like I'm just missing something obvious.

mepps added a comment.Aug 12 2020, 7:13 PM

Once I typed that above I figured it out. Now I just have to get currency_symbol.

Change 619840 had a related patch set uploaded (by Mepps; owner: Mepps):
[wikimedia/fundraising/tools@master] Add all_funds_latest_currency and all_funds_latest_currency_symbol

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

Change 619840 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add all_funds_latest_currency and all_funds_latest_currency_symbol

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

I just +2d & deployed @mepps changes to add the new fields. I also did this on staging to facilitate it.

ALTER TABLE silverpop_endowment_latest ADD COLUMN `endowment_latest_currency_symbol` VARCHAR(8);

The job is due to kick off in 15 minutes so I'll check it runs. Once it has run the new fields should be available to map, but only updated for the contacts updated in the last week.

@mepps I feel the backfill gets it's own phab maybe?

I see there were issues with the job last night - I had meant to watch the job & ping @KHaggard about the extra fields once it finished but wound up going out on an unexpected errand & forgetting.

I see there were problems overnight but it looked like it uploaded. I'm just doing again now so I can check the job DOES complete

Just confirming that the whole process completed successfully just now - the 2 extra fields should be there but not show up until mapping is redone & not be fully populated until we do extra work

This makes sense to me. Thanks @Eileenmcnaughton. I was out of office last Friday but will work on remapping the file today. Will update here when it runs successfully. Let me know if there's a way I can help on the backfilling project.

Change 620801 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/tools@master] Add query to rebuild the full silverpop_endowment_latest with the new field

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

In order to close this we need to do the backfill - I think we could just run a script on staging direct - but I put the proposed sql into gerrit for review

The data jobs seems to be running successfully on my end. Just making sure I understand correctly, will the backfill export file be similar to how it used to look for just one run?

@KHaggard yeah I guess we'll have to update all 20m rows for a one-off

DStrine closed this task as Resolved.Aug 18 2020, 7:59 PM

Is there a new task for backfilling the data? If not, can we keep this one
open?

Change 620801 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add query to rebuild the full silverpop_endowment_latest with the new field

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

@DStrine and @Eileenmcnaughton : I am pinging this task to underline Caitlin's comment from yesterday. Caitlin confirmed that the fields have been added, but they do not yet have complete data in them, hence the task isn't technically done yet. Do you have an ETA for the final part of this task? Anything you need from the team in order to move forward? TY!

@KHaggard is just checking the backfilled data now

There was supposed to be a follow on task for the backfill but I'm not sure it got created. However, it was included in the work I did to backfill for T260708

Updating this thread, the two new fields are in the database and functioning. However, I cannot confirm with certainty that all contacts in the database have all been backfilled, as there's not a clear way for me to run queries on these changes. When I run a query that I expect to return 0 contacts, I get a large return that warrants a closer look at the contacts case by case - similar to the issue we surfaced in ticket T254304. For example, there are contacts sitting in our database that have blanks for both_funds_latest_currency when Annual_Fund_latest_currency is populated. There's suppression list contacts in our database and old contacts that have been deduped, all of which get pulled into query counts. In general, there's some data hygiene that needs to get done at some point, so that my queries pull as accurately as possible. So, I made a long term task for this here: T261705