Page MenuHomePhabricator

Export Matching Gifts data to acoustic
Closed, ResolvedPublic

Description

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.

Event Timeline

Ejegg created this task.Jun 8 2020, 7:46 PM
DStrine removed a project: Epic.

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.

jgleeson moved this task from Backlog to Doing on the Fundraising Sprint Lazy Loading Life board.
mepps added a subscriber: mepps.EditedJun 11 2020, 8:36 PM

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.

mepps added a subscriber: Cstone.EditedJun 11 2020, 8:37 PM

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

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.

mepps added a comment.Jun 11 2020, 8:43 PM

@CCogdill_WMF @jgleeson is going to send the column headers on this task.

@CCogdill_WMF The current list of fields for the new matching gifts table looks like this

  1. id
  2. entity_id
  3. matching_gifts_provider_id
  4. matching_gifts_provider_info_url
  5. name_from_matching_gift_db
  6. guide_url
  7. online_form_url
  8. minimum_gift_matched_usd
  9. match_policy_last_updated
  10. suppress_from_employer_field
  11. subsidiaries

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.

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.

jgleeson added a comment.EditedJun 17 2020, 4:10 PM

Sample Company: Boeing

FieldSample
matching_gifts_provider_info_urlhttp://www.boeing.com/
name_from_matching_gift_dbBoeing Company
guide_urlhttps://s3.amazonaws.com/branding.yourcause.com/boeing/2098153.116204.0.pdf
online_form_urlhttps://boeing.yourcause.com/auth
minimum_gift_matched_usd$25
match_policy_last_updated01/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.

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.

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

Change 607122 had a related patch set uploaded (by Jgleeson; owner: Jgleeson):
[wikimedia/fundraising/tools@master] Add employer_name field to full export.

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

jgleeson updated the task description. (Show Details)Jun 22 2020, 9:34 PM

Change 607122 abandoned by Jgleeson:
Add employer_name field to full export.

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

Change 607366 had a related patch set uploaded (by Jgleeson; owner: Jgleeson):
[wikimedia/fundraising/tools@master] Add employer_name field to full export.

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

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.

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

Change 607366 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add employer_name field to full export.

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

@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

DStrine closed this task as Resolved.Jul 7 2020, 8:27 PM
MNoorWMF reopened this task as Open.Jul 8 2020, 3:55 PM

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!

@jgleeson
@Eileenmcnaughton
@KHaggard

Bump! See Moska's Q above.

DStrine closed this task as Resolved.Jul 16 2020, 5:05 PM

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

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-*