@Eileenmcnaughton - Engage just sent us the DAF import file draft including examples of how the data entry will look. I saved it here for you to reference: smb://filesrv1/Fundraising/Tech/Fr-Ops/DAF Import/Engage_DAF Import Data Example.csv

I also wanted to flag that they are using a different batch number format that is alphanumeric instead of numeric only - wasn't sure if this would cause any issues when importing to civi.

Hmm - just realised I have to sort out my yubikey to get the file - I've switched to my backup for WMF stuff but haven't replaced my broken one yet - which is the one set up with the system. If @XenoRyet confirms I should buy a new one locally (see email) I'll wait for that to arrive, otherwise I'll contact OIT to get them to switch my backup to my main one. Probably means I should start on this task next week althought I've started some preliminary moving code around (https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/699078) as I try to move code towards our extensions plan when I 'touch' it - sometimes it even makes it easier

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

[wikimedia/fundraising/crm@master] Move WMFException to extension

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

Thanks for the heads up @Eileenmcnaughton Hope the yubikey issue gets sorted!

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

[wikimedia/fundraising/crm@master] Move exceptions ointo Civi\WMFException namespace

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

Change 699978 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Move exceptions ointo Civi\WMFException namespace

Reason:

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

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

[wikimedia/fundraising/crm@master] Move exceptions onto Civi\WMFException namespace

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

Change 699980 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Move exceptions onto Civi\WMFException namespace

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

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

[wikimedia/fundraising/crm@master] Extract 2 more exceptions

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

Change 699986 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Extract 2 more exceptions

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

@MDemosWMF I took a look at the files & the code. I started with the sample csv but in fact that perhaps set me off on the wrong path since there are some discrepancies. I'll try to address the various issues / discrepancies one by one.

1. Organization Name. The existing engage import handles the presence of a column 'Organization Name' as follows:
   • if the 'Organization Name' field has data in it then the contact will be treated as an organization, otherwise as an individual. This is the reverse of the logic you suggested but if only either/or is populated it shouldn't matter. In the csv file there were values in both but I've started to think the csv was not really where your focus was. Note the field name in the engage import is 'Organization Name' but in the csv it is 'Organization'

2. Soft credit - if the field 'Soft Credit To' has data then a soft credit will be added if the contact can be found (ie there is exactly one organization with that name) - otherwise the row will fail & you will need to create / fix the contact and try again. Note that in the csv the column is 'Soft Credit Name' but the field mapped for engage is 'Soft Credit To'

3. Fee amount - this is missing from the engage import at the moment

4. The csv had some pretty messy data - ie it has the columns for the person doing the data entry to put in First Name, Last Name and Partner but was erratic as to whether the data was split correctly between them. If we have to deal with that then it will be trickly.

5. emails. I'm pretty sure that the DAF letter is being picked up through our normal thank you letter process - which would mean that when the job runs it will send to the DAF donor if it finds a record with an email - I guess the bigger issue is how do we determine a match in order to match to an existing record. Normally we rely on names+email to decide that a contact is a match. In the Benevity import we have the same donors giving over & over and so we make some assumptions based on them already having a relationship with the matching gift donor. I don't know if this applies. Note the 'Thank You Letter Date' column is required for the engage import but it can be empty.

Potentially the only thing missing from the Engage import to make this work for you is adding support for Fee Amount - but any one of the items about might look a lot like a can opener looking for some worms to liberate.

Change 699078 abandoned by Eileen:

[wikimedia/fundraising/crm@master] Move WMFException to extension

Reason:

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

Hi @Eileenmcnaughton - thanks for going through everything and catching these discrepancies! I had found a few in the .csv file they sent over too, but obviously missed some so thank you!

1. We can definitely update this column to match with the current Engage file and label it ‘Organization Name.’ The logic would be similar to the fidelity import (which is just a specific type of DAF file) where we want to have the individual used if the first name and last name fields are both filled, but if null then use the organization name. This file would have separate columns for first name/last name unlike fidelity, so I think the rule should be if both fields are null, move to using the organization (if that is possible).
2. We can also update this column name to match the mapping as “Soft Credit To” in the file so it aligns with the database. As far as I know, usually we have the donor advised fund organization in our system already. If it fails and is something new I can go back and add the record in civi before reimporting. Like you mentioned, it would just have to have the exact same name to match, is that correct?
3. Yes, we would need to add the Fee Amount column to the file and make sure that column maps to the contribution in civi.
4. Thank you for noting this - I see what you are saying and I will bring this up to Engage and make sure they are aware of the rules of entering the data between these columns. It should be very similar to what they are already doing so I’m not sure why it was reflected in the file this way.
5. Thanks for explaining that - so there doesn’t necessarily need to be an email in the file, it will send the email TY as long as it matches to a profile with an email during import? (let me know if I am misunderstanding). But, like you said, if email is being used to match the record that would have to be tweaked. Would it be possible to use the name+address to match for this import? Then even if the email isn’t in the file and it matches by address to a record with the email on it, it could still send the TY. If that would throw a wrench in the systems already setup, @RLewis maybe there is a way to pull those contacts after import into a group and mass send the TY? I can discuss the TY needs with @RLewis tomorrow.

Really appreciate you working through this with us! I will pass along these notes on column and data formatting to Engage as well.

@MDemosWMF so regarding 1 - the difference between the existing code & the reversed version would only come into play if both are populated - it the sheet would be either Organization name is populated OR first name/ last name are then no code change is needed

@MDemosWMF so on 5 we could add a rule (which would apply to all imports where email is not present) that would match with an existing contact if

1. street address is longer than 5 characters (in case of meaningless data) and
2. exactly one contact is found with the same street address, postal_code and city

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

[wikimedia/fundraising/crm@master] Add support for Fee Amount to engage import

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

Change 701002 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add support for Fee Amount to engage import

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

@MDemosWMF I just deployed #3 - 'support for Fee Amount'

That gets us to the place where the engage import would work with the correct additional column names - as long as Organization Name is empty where you want the contact type to be an individual.

5 is outstanding

@Eileenmcnaughton gotcha for #1 it should be one or the other like you mentioned and the soft credit will always be an organization.

Those rules sound good for #5, so the email will be triggered even if it isn't in the file, but is on the existing record it matches to. @RLewis I know you manually review some that are a higher level gift. Is there a cap you would want to add, maybe don't send auto email if $5K+?

#3 thank you for getting that Fee Amount mapped to the file! I will make sure Engage has all of the required rules and notes for entering this data on their end.

@Eileenmcnaughton I just spoke with @RLewis and she said a good threshold is No auto TY send for any $5K+ gifts for the import.

@Eileenmcnaughton I just spoke with @LeanneS and we had another idea to capture more contribution data in the file if possible. Sometimes there is an Individual listed as well as a fund name (separate from the soft credit organization) on the gift documentation. Would it be possible to have an 'Owns the Donor Advised Fund' field that would link that Individual to their DAF?

This might make the import pretty clunky, since it would hypothetically be de-duping the DAF it links to as well. There wouldn't be any overwriting - ideally either matching to the same DAF or adding another DAF if it is different/field is empty. Wanted to get your thoughts on if this is possible :) We can discuss during fortnightly tomorrow as well.

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

[wikimedia/fundraising/crm@master] [Refactor] extract all that tag 'noise' to it's own function.

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

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

[wikimedia/fundraising/crm@master] [Refactor] extract all the language noise to it's own function

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

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

[wikimedia/fundraising/crm@master] [Refactor] Move existing contact matching code to new WMFContact::save class

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

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

[wikimedia/fundraising/crm@master] Remove match_on_import conditional

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

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

[wikimedia/fundraising/crm@master] Add ability to match on address

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

@MDemosWMF I've spun off that latest request to https://phabricator.wikimedia.org/T286520

• I know we discussed the thank you email on our fortnightly - and I *think* we might have concluded not to worry about not sending the thank you for $5k+ - but I am not 100% sure - @RLewis is that a requirement or not something you are too concerned about

This is in review as the fixes for address matching are in review

Thank you @Eileenmcnaughton! I think you're right, but @RLewis please confirm on that one.

Change 703265 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] [Refactor] extract all that tag 'noise' to it's own function.

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

Change 703266 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] [Refactor] extract all the language noise to it's own function

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

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

[wikimedia/fundraising/crm@master] Move contact update code to save class

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

@Eileenmcnaughton I think it's OK to send the automatic thank you email if an email is supplied. Thanks for double checking on this.

Change 704215 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Move contact update code to save class

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

Change 703267 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] [Refactor] Move existing contact matching code to new WMFContact::save class

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

Change 703268 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove match_on_import conditional

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

Change 703270 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Add ability to match on address

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

@MDemosWMF I've just moved this into 'done' because I just deployed the piece that will do an address based lookup & match on that if there is no email & only one match is found

Great, thanks @Eileenmcnaughton ! Would you like me to test this part or wait until this task is done to do any testing?

@MDemosWMF yes - please test this part. The other task is not in the sprint - if that is crucial to this being usable you might need to connect with @DStrine on prioritisation

Hi @Eileenmcnaughton I'm about to test this out. Does the Fidelity import file format in civi cover this DAF import as well? Wanted to double check, thanks!

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

[wikimedia/fundraising/crm@master] Fix import to limit soft credit to orgs

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

It seems the issue is that it's not finding exactly one contact with that organization_name due to us having employees in the DB. I've put up a patch to restrict the check to organizaitons.

Ahh interesting, thanks for looking into that!

Change 719159 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Fix import to limit soft credit to orgs

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

@MDemosWMF I just deployed another fix

@Eileenmcnaughton I just ran those with the error through import again and it worked!! Seems like it is good to go, thanks for all of your help on this! :)