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

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.

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.

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.

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.

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

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