Page MenuHomePhabricator

Creating New Fidelity File Civi Import
Closed, ResolvedPublic

Description

Hello,

We are hoping to create an import for the Fidelity EFT gift reports we receive from Fidelity Charitable. They are currently being hand entered by Engage so creating an import will increase efficiency with gift data entry into civi.

This is a weekly donation file that we compile and share with Engage for hand entry. All of the gifts on this file will have the same fields to designate the contribution record:
Payment method: EFT
Soft Credit: CID 67
Restrictions: Restricted - Foundation
Gift Source: Donor Advised Fund

An example of a recent file is at: smb://filesrv1/Fundraising/Tech/Fidelity File

The Individual contact record would correlate with the Addressee Name column since the Individual record is usually where most of the giving history in civi is found for the donor. However, this information is not always provided in the file. In those cases we could default to using the Giving Account Name field, which would need to import as an Organization record.

That may be too complicated for one import so perhaps splitting the import into two files - one with the gifts that have the Addressee Name (Individual record) column filled in and the other that does not, but instead has the Giving Account Name Field information(Organization record).

We would also like to get your thoughts on how to import with the Addressee Name when first and last names are all in one column in the file. Ideally we’d like to avoid manually splitting these fields into First Name and Last Name with the goal being to streamline the process.

Thank you!
Melanie

Event Timeline

@MDemosWMF when I look at that file there is quite a bit of variation in what is in the Addressee Name

I can attempt with code to split out things like 'Mr. Bob Marshall Smith II & Mrs. Mary Jones Phd' - but at some level a human would do a better job of that.I'm just wondering what the process is by which it gets into the spreadsheet & whether we could have someone enter it in more columns to start with

@Eileenmcnaughton I see what you mean. Unfortunately we get the file directly from Fidelity, so I'm not sure they are able to edit that for us. I did just do a test using 'text to column' in excel. I could go through and insert a semicolon before all last names and this would split the the columns in two.

For any couple with different last names I would just copy and paste the second name into a Partner column - this is more rare so hopefully not too many of those. It is somewhat manual but I think it could be an option. All of my test files are saved to the same Fidelity File Folder if you want to take a look and see if that would work.

Here is the process I followed: https://support.microsoft.com/en-us/office/split-a-cell-f1804d0c-e180-4ed0-a2ae-973a0b7c6a23

Change 675577 had a related patch set uploaded (by Eileen; author: Eileen):
[wikimedia/fundraising/crm@master] Consolidate name splitting on new api, called from parent class

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

@MDemosWMF I've put code to do this import on staging - do you know how to test there?

The main question I have is whether the grant id column is unique (in which case I will use it for transaction id)

@Eileenmcnaughton Just tried logging in to staging site again and I'm still having issues with getting access. That Grant ID should be unique, but I'm reaching out to Fidelity to confirm and will let you know.

@Eileenmcnaughton confirmed Grant ID is a unique number to each donation.

Change 675577 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Consolidate name splitting on new api, called from parent class

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

@MDemosWMF I have pushed this import onto production since I understand that it is easier for you to test it there and as you will be the only one using it I don't think there are any risks around that.

Please test with just a few rows in the first instance until you have confirmed the import is working (or let me know of any issues with it)

@Eileenmcnaughton I just tested importing a few lines of a file and it looks like it's working well! I see that it will default to the individual record if that column is filled and if not uses the org record information, which is great.

It seems to split a couple even with the same last name to the partner field - @RLewis is this alright for your TY letter purposes?

@MDemosWMF - that's fine, thanks for letting me know.

@RLewis It sounds like if we keep the import as it is now we should look into having it put both partner names into addressee field for TY letter merge purposes? Want to confirm that is what would work best on your end. @Eileenmcnaughton you mentioned this might be doable, but just wanted to get it logged in the task. thanks!

Hi @Eileenmcnaughton just wanted to hop back in here and see if the import can include the full names of the couple in the addressee field on the record? This is helpful for @RLewis when she is doing mailings and can make sure both names are included in the letters for MG donors.

Change 682356 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Save full name to addressee where supplied

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

@RLewis @MDemosWMF I've put up a patch that will ALWAYS put the value provided as the full name into the addressee field for all imports.

I can make it narrower by
a) only doing it when we determine partner has been populated and / or
b) only doing so for fidelity import

I'm of 2 minds - it feels a little risky doing it to all imports but we also know we have a track record of not quite nailing name parsing on imports and losing the original 'full_name' data. This field is probably only used by major gifts & with some degree of manual review so all of that might outweigh the perception of riskiness
@Ejegg @Cstone

Thanks @Eileenmcnaughton - does this patch only apply to imports that have a column with the full addressee information? For example, the engage individual imports are only first name and last name columns so the full name is divided.

@MDemosWMF yes - you are spot on - this would only apply where we are spilling / wrangling the full name and not when they first and last names are separate

Ok great - in that case @RLewis it might be fine to do for all imports since I don't believe very many have a single column with full names combined. As far as the imports I do it is only Fidelity, but if it pops up for other imports it would catch those if it's set for "all." @RLewis what do you think?

@MDemosWMF I think this is fine too, the Benevity one is separate fields. I'm not sure how the Bitcoin import works though, since the data in that import can be a little bit messy @LeanneS looping you in here too.

@RLewis you're right I see bitcoin import has a single column for the full name. @Eileenmcnaughton I'm guessing it would work the same for the Bitpay import then? These are mainly individual donors, not listed as couples (usually) so it would just put their full names into the addressee field I believe.

@RLewis so now full name parsing is enable on any import if full name is provided and no more than one of first_name & last_name is - that second check was just in case both full name & name fields were passed in.

When full name parsing is triggered it puts the full name value in the addressee custom field (as provided). It also attempts to split up the name appropriately & split out any partner.

Change 682356 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Save full name to addressee where supplied

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

@RLewis @MDemosWMF I just deployed a fix which should bring behaviour into line with discussions - ie the full name goes in the addressee_custom field as a replacement except for 25k + where it becomes an append

Hi @Eileenmcnaughton - @RLewis and I noticed that a few of the Fidelity file contacts imported into Civi are putting the name information into the wrong fields. I will leave a few examples below and see if you think there are ways to fix these situations.

CID 24958873 - Mr. and Mrs. are in first name column instead of prefix
CID 51154868 - Record's first name is in last name field

I saved the file to the tech folder as well to see how it was originally written in the file: smb://filesrv1/Fundraising/Tech/Fidelity File/051021 to 051421.csv

It looks like most of the others in the files are doing pretty well with parsing out the name fields.

Thanks!

@Eileenmcnaughton apologies! I consolidated on the server to an FR-ops folder: smb://filesrv1/Fundraising/Tech/Fr-Ops/Fidelity File/051021 to 051421.csv

@Eileenmcnaughton Just a heads up in case you had changed any of the rules for this import, it looks like when I did this week's it wasn't able to keep the last name for both partners when two people are listed with the same last name.

For example, Bert and Colleen Smith shows up as Bert [no last name] as the individual record name and then Colleen Smith is correct in the partner field. It's still catching the full listing in the addressee field which is good, but seems like something has been thrown off since I didn't see that issue before.

I edited the records by hand so they should be correct in civi.

@MDemosWMF - that does sound like the issue we had before but I had hoped it would be fixed now - I'd need to see the full file. What say we close this issue out & when you next hit this or other issues we create a new issue?

@Eileenmcnaughton that's fine with me, feel free to close this one out and I can create another one if it happens again on the next file.

Thanks @MDemosWMF - I'm definitely a fan of phabs that are 'single-purpose'