@Eileenmcnaughton I heard back from Fidelity about why there is no address listed when there is a Fund name only in the file: "When donors give funds, they get to choose how to display their information. If the selection is from a giving account only, the information being reflected is the giving account name only. In some cases, the donors choose to reflect their name and address associated with their gift."

So it looks like these are cases where the donor didn't want to share their information.

@Eileenmcnaughton wanted to see if you found out if 2 soft credits on a gift would cause any challenges for analytics?

OK so just trying to get my head into this. Up until now there are 6 contributions for Fidelity that have been soft credited to contacts OTHER THAN the fidelity contact

select contact_id, count(*) FROM wmf_contribution_extra LEFT JOIN civicrm_contribution_soft sc ON sc.contribution_id = entity_id WHERE gateway= 'fidelity' group BY contact_id ORDER by entity_id DESC LIMIT 1000;
+------------+----------+

+------------+----------+

Slightly more detail

select sc.contact_id,soft_credit_type_id, min(receive_date), count(*) FROM wmf_contribution_extra LEFT JOIN civicrm_contribution_soft sc ON sc.contribution_id = entity_id LEFT JOIN civicrm_contribution c ON contribution_id = c.id WHERE gateway= 'fidelity' group BY sc.contact_id, soft_credit_type_id ORDER by entity_id DESC LIMIT 1000;
+------------+---------------------+---------------------+----------+

+------------+---------------------+---------------------+----------+

Adding some notes from our checkin:

New aspects to investigate:

• Fund soft credits individual and banking institution (need to think through soft credit type rules)
  • Is automatic soft credit something that could be set up to happen in Civi after import? If there is an individual record linked to an Org record, gifts on org record will soft credit linked individual (relationship - DAF)
  • Then we wouldn’t need to build it into import
• If Fund and Individual listed - link “Is the donor advised fund of”
• Fund name is not necessarily unique - can we use address (if included) for dedupe of hard credit and same address to dedupe linked individual record?
  • If no address, may need to create new record and dedupe will happen on back end?
  • Pull into searchkit and review/dedupe by DR?
  • Reached out to Fidelity to see if we can get a unique ID for the donor included in the file

FYI RE Fidelity unique donor number: "The giving account number is specifically associated with the donors account and listed in our database for referencing purposes only. This information is confidential and not included in the Grant Details Report sent to organizations."

@MDemosWMF ok so we CAN use it!

@Eileenmcnaughton Unfortunately they said we can't use it based on their response above. It is confidential and not able to be included in the report. :(

oh - so the number they provide is not it - is there any way the can provide a unique number - ie they could easily provide a hash or something that doesn't tell us the actual number (not that I understand why it would be confidential)

Following up, I'll see what they say!

Fidelity: "Currently the information in the excel sheets, for grant details, are the only details to be given, per policy and procedure. As for the unique names, the donors choose their names and so there may be cross over." :(((

Notes from offsite discussion

1. We no longer need the field owns_donor_advised_for & associated logic - this was used for recording the contribution on the Individual & relating the individual to the fund (organization record) - but we are now going to do the reverse - this code can be removed from the import initially but by the end we can fully remove the field & related code

2. (DONE) We agreed to add a new SoftCredit type of 'Banking Institution' and use this to link Soft credits to the banking institution (e.g Fidelity)

3. Addresses - where there is an Organization AND an individual in a Fidelity import the address from the Organization will be copied to the individual. Within the template Melanie will map 'fidelity' as the default address for when there is none - so we can match orgs with the same name within the Fidelity import with each other. We agreed the risk of over-merging here seemed less than under-merging

4. In a pre hook we will ensure that the creation of a Donor Advised soft credit on an org record, linking an individual will also cause a relationship ‘has donor advised fund' to be created. Hook to live in \WMF\Hook\ContributionSoft

5. We should create a new anonymous organization record to go with out anonymous contact - maybe we can use code to cheat & grab a low memorable contact ID. UPDATE I did this

INSERT INTO civicrm_contact (id, contact_type, organization_name) VALUES(71, 'Organization', 'Anonymous Fidelity Donor Advised Fund');

Fidelity import notes

• Fidelity (Banking Institution) - always has a fund - sometimes has an individual
• fund is not unique & Fidelity refuse to provide any unique identifier

• Other DAF
• we get Check gifts through engage - entered in data entry - Various banking Institutions in these & we will also use that Soft Credit Type

Deduping notes
Dedupe - organizations - rule is org name + street address? - create new org if no match - put them in a group?
Organization w/ no address or individual info - we are gonna enter the banking institution as the address so they can be deduped within the same banking instituation

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

[wikimedia/fundraising/crm@master] Fidelity import prep step 1

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

Change #1134350 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Fidelity import prep step 1

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

@MDemosWMF I have deployed code updates onto staging & there is a configured Fidelity import at https://civicrm.frdev.wikimedia.org/civicrm/import/contribution?reset=1&template_id=1586 using the dedupe rules we discussed. I'm doing some more testing myself but if you are able to try it out & let me know what you hit!

Note I have been manually pushing through the import as staging doesn't have the background job

@Eileenmcnaughton It's looking really good! I just ran a test file on staging and pushed it through manually like you said (#1589). I added some other contribution fields to the template and they all came through. I tweaked the 'street address' field being set to Fidelity since we will only use that for when there is no address. I think we will have to add that into the file where there is no address before import.

The soft credit = fidelity/SC type =Banking institution came through as well! The only thing I saw was that it still created an anonymous individual soft credit when there was only a Fund name. Is there a way to set it up so that it doesn't create an individual DAF soft credit if there is no individual name information present? I think this would just create a lot of unnecessary anonymous soft credits in Civi.

@MDemosWMF - I've added an additional fix to prevent those extra soft credits - now on stagingEileenMDemosWMF wrote:View TaskMDemosWMF added a comment.
@Eileenmcnaughton It's looking really good! I just ran a test file on staging and pushed it through manually like you said (#1589). I added some other contribution fields to the template and they all came through. I tweaked the 'street address' field being set to Fidelity since we will only use that for when there is no address. I think we will have to add that into the file where there is no address before import. The soft credit = fidelity/SC type =Banking institution came through as well! The only thing I saw was that it still created an anonymous individual soft credit when there was only a Fund name. Is there a way to set it up so that it doesn't create an individual DAF soft credit if there is no individual name information present? I think this would just create a lot of unnecessary anonymous soft credits in Civi.TASK DETAILhttps://phabricator.wikimedia.org/T386017EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: Eileenmcnaughton, MDemosWMFCc: Eileenmcnaughton, Aklapper, EMartin, MDemosWMF, RLewis, DBautista-WMF

@MDemosWMF - I've added an additional fix to prevent those extra soft credits - now on stagingEileenMDemosWMF wrote:View TaskMDemosWMF added a comment.
@Eileenmcnaughton It's looking really good! I just ran a test file on staging and pushed it through manually like you said (#1589). I added some other contribution fields to the template and they all came through. I tweaked the 'street address' field being set to Fidelity since we will only use that for when there is no address. I think we will have to add that into the file where there is no address before import. The soft credit = fidelity/SC type =Banking institution came through as well! The only thing I saw was that it still created an anonymous individual soft credit when there was only a Fund name. Is there a way to set it up so that it doesn't create an individual DAF soft credit if there is no individual name information present? I think this would just create a lot of unnecessary anonymous soft credits in Civi.TASK DETAILhttps://phabricator.wikimedia.org/T386017EMAIL PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To: Eileenmcnaughton, MDemosWMFCc: Eileenmcnaughton, Aklapper, EMartin, MDemosWMF, RLewis, DBautista-WMF

@Eileenmcnaughton I just caught that the previous test I did on staging doesn't show the creation of the relationship on either CIDs 65076114 / 65076113. Once that's fixed I'll do another test including checking for that fix you did for the anonymous soft credits.

It's because the relationships only get created in the Contribution date is
in the last 3 months - if this is too restrictive we can change it - but
the worry is that if you ever imported really old donations we could wind
up with relationships that are obselete being created (this is the rule we
have for workplace giving-> employer relationship too)EileenMDemosWMF
wrote:View TaskMDemosWMF added a comment.
@Eileenmcnaughton I just caught that the previous test I did doesn't show
the creation of the relationship on either CIDs 65076114 / 65076113TASK
DETAILhttps://phabricator.wikimedia.org/T386017EMAIL
PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To:
Eileenmcnaughton, MDemosWMFCc: Eileenmcnaughton, Aklapper, EMartin,
MDemosWMF, RLewis, DBautista-WMF

It's because the relationships only get created in the Contribution date is
in the last 3 months - if this is too restrictive we can change it - but
the worry is that if you ever imported really old donations we could wind
up with relationships that are obselete being created (this is the rule we
have for workplace giving-> employer relationship too)EileenMDemosWMF
wrote:View TaskMDemosWMF added a comment.
@Eileenmcnaughton I just caught that the previous test I did doesn't show
the creation of the relationship on either CIDs 65076114 / 65076113TASK
DETAILhttps://phabricator.wikimedia.org/T386017EMAIL
PREFERENCEShttps://phabricator.wikimedia.org/settings/panel/emailpreferences/To:
Eileenmcnaughton, MDemosWMFCc: Eileenmcnaughton, Aklapper, EMartin,
MDemosWMF, RLewis, DBautista-WMF

@Eileenmcnaughton Ah right! When I run the test I'll do that with a more recent date. Will report back!

Did another test and the relationship link is working as well as no anonymous soft credit! Will wait to test again on production once it's pushed through.

@MDemosWMF The fidelity import template & associated logic are now on production for you to test - also the first cut of Benevity, based on the logic from the legacy import is on prod

@Eileenmcnaughton Just ran some tests and it is looking great! The only thing I would suggest that we had in the previous import was when there are two names listed in the addressee column we parse out the names, but keep the full names how they're originally shown in the file in the "addressee" section in the communication preferences block on the profile.

Could we add that capability in? I think that should be the last piece!

Hmm @MDemosWMF - when I tried the Fidelity import locally it DID populate the addressee name - is it definitely not doing that? The functionality is attached to that 'Full Name' mapping field

@Eileenmcnaughton When I tried it out last week it didn't - you can see it on CID 67725034 (I think it was import 2451). Information in the partner field is there, but not in the addressee.

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

[wikimedia/fundraising/crm@master] Populate addressee_custom during import off full_name

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

Change #1141988 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Populate addressee_custom during import off full_name

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

@MDemosWMF OK - I just pushed out a fix

@Eileenmcnaughton Hey! @Elbar53 and I ran a real fidelity import file. We noticed that for anonymous records the import shows a blank record name instead of being listed as 'anonymous.' This is CID 71 if you can take a look so that in future imports the name of the record isn't blank.

@Eileenmcnaughton We had to re-map the import and ran an import today - however the individual soft credit + relationship link doesn't seem to be working. I deleted the records but the import was #2565. To me it looks like the mapping is correct and I compared it to a recording we had of the mapping before the civi update. Maybe I am missing something? Can you look into what might be happening with the import?

@MDemosWMF I took a look and the 'Full Name' field is mapped to the main contact not the soft credit contact now - I imported the first row, changing that mapping as I did it - and it seems to have worked - see https://civicrm.wikimedia.org/civicrm/contact/view?reset=1&cid=67815765

I'm afraid I didn't save that update to the template - but if the record looks good that is the next step. I'm going to check on the Benevity import now & hopefully that will surface why the mappings went wrong

@Eileenmcnaughton That test run looks good! I will try and see if I can implement that fix to the mapping.

Updated the mapping and ran the rest of the test file and it is all good! Thanks for unblocking this!