Page MenuHomePhabricator

Matching Gifts: Add fields to all_wikimedia from SSI database
Closed, ResolvedPublic2 Estimated Story Points


If anyone works on this, do not actually deploy this until @KHaggard is in office starting on the 11th.

To make matching gifts emails work properly for the program we'd like to set up, we will need the following additional fields synced from the Matching Gifts database (formerly "HEPdata"/SSI) added to the all_wikimedia database in Acoustic:

  • employer_name
  • guide_url
  • online_form_url
  • matching_gifts_provider_info_url

Event Timeline

Quickly chiming in here that _all_Wikimedia already has been mapped with employer_id and employer_name from the civi export.

Unless anything needs to change there, I think those are covered and we only need to add in those other 3 fields.

Implementation note for the Civi side - it should be possible to just add these in to silverpop_export_view_full by joining silverpop_export.employer_id on civicrm_value_matching_gift.entity_id and selecting matching_gifts_provider_info_url, guide_url, and online_form_url.

DStrine set the point value for this task to 2.Jan 5 2021, 9:53 PM
DStrine moved this task from Sprint +1 to Current Sprint on the Fundraising-Backlog board.

Hey all, I'm back and catching up on everything. Are there any new developments on this phab before I remap the _all_wikimedia database?

DStrine raised the priority of this task from Medium to High.Jan 11 2021, 9:15 PM

Hey @jgleeson ! Thanks for grabbing this task. Just wanted to say that pretty much any Thursday after 4pm UTC will work for me in regards to uploading the CSV file to FTP. I can remap the fields in my Thursday evenings and QA check it on Fridays. Let me know which Thursday sounds good for you and I'll plan for it :) Thanks!

@KHaggard we're going to see if we can get this in in the next 24 hours. We'll update you early tomorrow if that's going to work. If not, then we'll get our side of the work done and wait for the next Thursday to start sending new data over to you.

Thanks @DStrine ! Either option works for me. Thanks for the update :)

Local dev notes

I ran into the following failures when running silverpop export locally.

  • needed to run rebuild_endowment_latest.sql to add a missing field.
  • silverpop_export tbl did not have modified date (deleted all tables to have them rebuilt)
  • "Table 'civicrm.civicrm_value_1_prospect_5' doesn't exist" (I think this has been fixed by @Eileenmcnaughton but requires a fresh install of CiviCRM to take effect)

As discussed, I'll record these gotchas for reference and add them to our fr-tech silverpop export docs.

Confirmed with @KHaggard that this will not be deployed today (21st Jan)

Change 657686 had a related patch set uploaded (by Jgleeson; owner: Jgleeson):
[wikimedia/fundraising/tools@master] WIP: add Matching Gift fields to main export

Change 657686 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add Matching Gift fields to main export

hi @jgleeson - email team's last email deployment for this week is tomorrow, Thursday Jan 28th at 16:00 UTC. So, if things are ready on your side, I'll be ready to receive the new civi export file after 16:00 tomorrow. :)

hey @KHaggard, thanks for the update.

The work needed to add the matching gifts fields to the full update is done.

I guess we just need to talk through testing it at your end and I'd like to understand how we minimise the risk of corrupting our existing data in the event that the new fields break stuff unexpectedly. Would you be able to talk through that here on the ticket or would it be better to go over it on a call tomorrow?


Cool, thanks @jgleeson !

We can try talking it out here, but I'm happy to jump on a call tomorrow too if you prefer.

If the new fields break or don't process through Acoustic successfully, I don't believe it alters the other pre-existing field data we have on our donors. The Acoustic import job will just fail to update all contacts if for example, one of the new fields is formatted incorrectly. Because it fails in that way, we don't prefer to make Acoustic database changes during active email campaigns, when our audience info needs to be freshly updated. Luckily, we don't have emails going out next week so we have time to troubleshoot if there's any issues.

Ideally, the recurring import will run as scheduled tomorrow at 9am UTC, and then our emails go out, and then we'll be ready for the new CSV file for me to map to Acoustic. If you want to see how I map the fields in Acoustic, I'm happy to hop on a call and screenshare!

I hope this addresses your question - please let me know if I didn't!

Thanks, @KHaggard for the explanation.

A call sounds like a great idea, that way I could show you the new location of the fields in a sample CSV generated locally at my end and then we could discuss whether or not they need to be moved and also maybe we could walk through a test run of importing the latest data including the new fields to see how that goes.

Does that sound ok with you?

@jgleeson Yeah sounds great - let's do that. I'm free to hop on a call anytime between 1:30pm - 7pm UTC

@KHaggard I'm also around between those times.

Does it make sense to do the call before or after the 16:00 UTC cut off you mentioned earlier?


Change 659262 had a related patch set uploaded (by Jgleeson; owner: Jgleeson):
[wikimedia/fundraising/tools@master] Add matchings gifts prefix to mg fields

Change 659262 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add matchings gifts prefix to mg fields

@jgleeson and I met and discussed a game plan for the Matching Gifts fields. We decided to play things on the safe side and do a test run of importing the fields to a fake database, before running the real job on _all_Wikimedia.

Creating the fake database took over two hours, but it's finally finished and I've named it: Test_MatchingGifts_all_Wikimedia. I've also deactivated the recurring import for the DatabaseUpdate-* file so nothing tries to process overnight.

Mapping the new fields for the test run will likely happen tomorrow instead of today. If the test is successful, we'll roll out the actual update to _all_Wikimedia immediately on the same day. We do not have email campaigns scheduled next week, so any needed troubleshooting can take place the following Monday.

hey, @KHaggard just a heads up that the updates are live as discussed and a test run of the export just completed with the new fields present and also some rows have values. The numbers look inline with previous runs which is good. The files have also been FTP'd over to their usual spot.

I'll catch up with you tomorrow to see how the mapping goes!

Thanks :)

Hi @jgleeson ! Thanks for this update. I saw it before logging off today, so I went forward with doing a test run with our fake database.

The summary report looks good to me, posting below.

Contact Source Name: Test_MatchingGifts_all_Wikimedia
File Name: DatabaseUpdate-20210128210742.csv

Job ID: 178453698
Job Type: Recurring Contact Source Import @ Thursday, January 28, 2021 at 10:55:21 PM GMT
Job Status: Complete
Total Rows: 63733
Total Valid Rows: 63703
Total Invalid Addresses: 3
Total Duplicate Addresses: 0
Total Disallowed Addresses: 27
Total Bad Data: 0

I also ran a quick query to see how many records got populated, and glanced at a batch of them to confirm they look like URLs:
matching_gifts_guide_url is not blank
OR matching_gifts_online_form_url is not blank
OR matching_gifts_provider_info_url is not blank
Total: 266

Since there were no failures updating this test database, I'd say we're all clear to do it for real tomorrow. I'll sync with you @jgleeson in my morning to talk about deploying the CSV file for _all_Wikimedia.


@jgleeson - I just finished mapping the fields for _all_Wikimedia. The summary report looks good and I'm posting it below:

Contact Source Name: _all_Wikimedia
File Name: DatabaseUpdate-20210129040923.csv

Job ID: 178491207
Job Type: Recurring Contact Source Import @ Friday, January 29, 2021 at 3:34:39 PM GMT
Job Status: Complete
Total Rows: 62294
Total Valid Rows: 62262
Total Invalid Addresses: 3
Total Duplicate Addresses: 0
Total Disallowed Addresses: 29
Total Bad Data: 0

Now that's done, the recurring import process in Acoustic is set up and will continue grabbing the new Civi export files every day at 9am UTC. I will ping here again on Monday to confirm if the recurring import jobs ran successfully over the weekend.

KHaggard added a subscriber: Ppena.

Hello! Just writing to confirm that the export/import process ran smoothly over the weekend with no failures. :) thanks so much for your help on this @jgleeson I'm resolving the ticket now.

Adding @Ppena for visibility! Sorry for not subscribing you sooner

@KHaggard I just reponed this ticket as I have some documentation I want to write as part of the task help with managing this on the fr-tech side.