This will be an entirely new table to export nightly.
We also wanna drop in the employer_id and employer_name into the main export.
Description
Description
Details
Details
Customize query in gerrit
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Resolved | None | T228765 Epic: matching gifts HEP integration (was employer field V2) | |||
| Resolved | jgleeson | T254809 Export Matching Gifts data to acoustic |
Event Timeline
Comment Actions
Please get us the column headers and demo data whenever you can! That will allow the email team to get to work while y'all do your side.
Comment Actions
From call with @jgleeson and @Cstone:
The question here seems to be whether we create a new table for https://github.com/wikimedia/wikimedia-fundraising-tools/blob/master/silverpop_export/export.py in the sql file https://github.com/wikimedia/wikimedia-fundraising-tools/blob/master/silverpop_export/update_table.sql and do a separate import, or just add this table to the silverpop_export table.
Comment Actions
@CCogdill_WMF Will you be using this data to know which individuals have been able to get gifts matched, and by what company? As we plan the export, we're just trying to figure out what fields to send, and in what format.
Comment Actions
I think I want to defer to Nora for what fields we need, as I haven't seen
the HEP data and don't know what all is at our disposal. The purpose of the
emails we're going to send is to connect donors to their relevant matching
gifts portals.
Comment Actions
@CCogdill_WMF The current list of fields for the new matching gifts table looks like this
- id
- entity_id
- matching_gifts_provider_id
- matching_gifts_provider_info_url
- name_from_matching_gift_db
- guide_url
- online_form_url
- minimum_gift_matched_usd
- match_policy_last_updated
- suppress_from_employer_field
- subsidiaries
Comment Actions
I think I still want to defer to Nora here :) I don't know what is in each
of these fields.
In terms of IDs, I think all we are going to need is employer name (is that
name_from_matching_gift_db?). We need our equivalent of an "ID" field to
match the employer field you'll be sending us in the main export--we need
something to join to this table.
Comment Actions
From our meeting today:
@jgleeson can you share an example of the data that would show up under these headings?
In general we are ok sending more info than we need for now just to give the email team a bunch of options.
The #1 ID column is the organizational/employer ID and that will be added to the regular acoustic/civi export
We may need to add the employer name to the regular export too but the email team will update us on that soon.
Comment Actions
Sample Company: Boeing
| Field | Sample |
| matching_gifts_provider_info_url | http://www.boeing.com/ |
| name_from_matching_gift_db | Boeing Company |
| guide_url | https://s3.amazonaws.com/branding.yourcause.com/boeing/2098153.116204.0.pdf |
| online_form_url | https://boeing.yourcause.com/auth |
| minimum_gift_matched_usd | $25 |
| match_policy_last_updated | 01/29/2020 |
I've omitted the id fields and the subsidiaries block as I think subsidiaries might need to be broken out in their own rows. Currently, it's a list of other company names grouped together in one field.
Comment Actions
Change 606463 had a related patch set uploaded (by Jgleeson; owner: Jgleeson):
[wikimedia/fundraising/tools@master] WIP: Add in matching gift table to export and include employer_id alongisde contact row fields.
Comment Actions
Change 607122 had a related patch set uploaded (by Jgleeson; owner: Jgleeson):
[wikimedia/fundraising/tools@master] Add employer_name field to full export.
Comment Actions
Change 607366 had a related patch set uploaded (by Jgleeson; owner: Jgleeson):
[wikimedia/fundraising/tools@master] Add employer_name field to full export.
Comment Actions
Change 606463 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add in matching gift table to export and include employer_id alongisde contact row fields.
Comment Actions
Change 607366 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add employer_name field to full export.
Comment Actions
@jgleeson I merged yours but added a follow up patch here https://gerrit.wikimedia.org/r/#/c/wikimedia/fundraising/tools/+/607391/ - per comments - the individual record holds the employer name, where applicable.
Note this has to be deployed in conjunction with @KHaggard as the new fields employer_id and employer_name are now right after postal_code in the csv file
Comment Actions
Hi yall - had to re-open this as I'm not seeing more than a row in the relational table we set up in Acoustic. Are we supposed to be grabbing the file from the FTP?
If yes, what’s the file name so we can grab that and set up the import? Thank you!
Comment Actions
@CCogdill_WMF @MNoorWMF we heard from @KHaggard that the file is showing up at this point so all the plumbing is in and working. We've got some data issues from the vendor that we are fixing here: T257963 when that task is done, it should be ready to import.
Comment Actions
Just clarifying here - I can't actually see the file showing up in Acoustic but I know the file is being sent over because jgleeson mentioned it in IRC a while back. I dug up the file name from IRC chat history: MatchingGifts-[dateandtimestamp].csv so @MNoorWMF when you and I set up this recurring data job we will mark down the file name as: MatchingGifts-*