We'll need to import donation spreadsheets that include arbitrary Soft Credit To values. These should be fuzzy-matched against existing Civi contacts, and unfortunately, we probably want a dialog that appears during the import process to confirm that we've properly linked these to existing contacts.
|Duplicate||CaitVirtue||T90242 Bulk import for existing and future payment methods into CiviCRM|
|Open||None||T92325 Amend fields for International Check Importer|
|Declined||None||T114797 Remove Engage Civi users and revoke SSL keys|
|Open||None||T114683 [EPIC] Engage exclusively uses spreadsheet imports|
|Resolved||awight||T88836 Create an Civi import function for hand keyed (including DAF) donations|
|Resolved||Eileenmcnaughton||T115044 Benevity: Create an import method for matching gifts and payroll deductions|
Checking in on this, as it's a current pain point for us. (The January Benevity file (matching gifts) had thousands of records and Engage has told us it will take a month or more with double staff time.)
from Leanne: "We just want to make sure that an import would correctly assign the
soft credits. Looking at Eileen's comment, many of the Benevity
donations include emails, but many do not."
@Ejegg thanks - I just put last December's file here: Fundraising/Tech/Major Gifts/Benevity Example. As you'll see in the file, there were over a thousand rows and many of them required entering both the donor's gift amount and the matched amount by the company, which would then be soft credited to the donor. Many gift amounts and company matches are the same amounts so I would think it'd be difficult to use unless you also match to first and last name (or email if we have it). The date would likely only be useful to donors whose donation and matching gift came through Benevity, unless you could use a range. From my understanding, the gift date could vary from what is listed in the Benevity file for donors that donated through other methods but the company processed its matching gifts through Benevity.
Cool, looks like the Benevity file has separate columns for the original "Donation Amount" and the "Matched Amount".
- When does the original donation already exist in Civi? Just when the "Donation Amount" column is zero? In other cases, do we create the original donation record along with the matching donation?
- What do we do when no donor details are supplied but there are amounts in both columns?
There's a lot of fuzziness here - email addresses and company names in the Benevity file don't exactly match the records I'm seeing. So it does appear that we'll need a match picking UI.
I had a go at an approach for this that I thought might not take too long. It's only a partial approach with the idea being to allow the 'easy' matches to be handled with a csv of ones not handled downloadable. (Leanne has not been managing to download the csv files from skipped but I tried 'as her' & succeeded so was hoping @Ejegg could take advantage of 'being there' to see what is happening.
Basically the code (now on staging) will do the import if the organization matches exactly one contact & the individual matches exactly one contact. On a small import I got "Successful import! 34 out of 46 rows were imported. 12 Ignored rows logged to file." so this might be enough to make good inroads into the amount of data involved - without it being the full fix.
I did find it VERY slow & I had to split the file into quite small parts before it would finish. Not so bad locally. I think the reason is the api call is slow due to https://issues.civicrm.org/jira/browse/CRM-19811 - I have had a partial fix for that merged into the next version of core but probably not for that field.
Now the challenge will be how to deal with the fact that I am unavailable from (your) 12 Jan to 23 Jan but will probably struggle to talk with people tomorrow during all hands (@DStrine - I'll look for you on IRC tomorrow re that )
I should also note that these are the columns I made & assumptions
Donor First Name (the individual)
Donor Last Name
Donor Email (a match on these 3 fields is a match, Not shared by donor is ignored & if email & a name field are 'Not shared by donor' then match is to contact 72
Donor Street Address
Donor State (update contact with address fields)
Donor Postal Code
Original Amount - donation created against individual with soft credit to Org
Matched Amount - donation created against org with soft credit to individual
- check organization_name for org & failing that the nick_name field
- if the individual does not have exactly one match just create a new one
- gift source - ie custom field 18 - for Organization gift it should be 'Matching Gift', Restrictions should be 'Restricted Foundation'. For the individual it is based on the amount (less than 1000 = Comunity Gift & over 1000 = Benefactor Gift'
- Change import name to Benevity & keep Benevity columns
Note - need to address slow lookup.
After discussing today there was a transactional issue (now fixed by wrapping all update actions in a transaction) and also @LeanneS felt we could be a bit more stringent on individual matching - especially where there was no email.
Here is the logic I am implementing (& adding unit tests for). Generally in the following only contacts with no email will be rejected if no match is chosen.
Note I am not filtering out matches with MORE information - e.g an email only contact will match to an email+first_name contact if there is one email match
UPDATE - we have broadened the definition of employee match to include having previous soft credits from the employer, as well as the employer relationship. Where that leads to multiple choices an employer relationship will be preferred, if still not resolvable a new contact will be created
|First Name||Last Name||Matches found in DB||Action||rejection possible|
|yes||Yes||Yes||1||Match to contact, add relationship if missing||No|
|yes||Yes||Yes||0||Attempt to check for an employer match on first_name & last_name only (in case of using home email), If found add the email but do not overwrite, create relationship.Else create contact, add relationship||No|
|yes||Yes||Yes||2+||Check for an employer match, otherwise create contact||No|
|No||Yes||Yes||1||Match to contact with email+last name, add relationship if missing||No|
|No||Yes||Yes||0||Create contact, add relationship||No|
|No||Yes||Yes||2+||Check for an employer match, otherwise create contact||No|
|Yes||No||Yes||1||Match to contact with same email, add relationship if missing||No|
|Yes||No||Yes||0||Create contact, add relationship||No|
|Yes||No||Yes||2+||Check for an employer match, otherwise create contact||No|
|No||No||Yes||1||Match to contact with same email, add relationship if missing||No|
|No||No||Yes||0||Create contact, add relationship||No|
|No||No||Yes||2+||Check for an employer match, otherwise create contact||No|
|Yes||Yes||No||1||Create contact if not employer match, else match||No|
|Yes||Yes||No||0||Create contact, add relationship||No|
|Yes||Yes||No||2+||Check for an employer match, otherwise create||No|
|No||Yes||No||1||Check for an employer match, otherwise create||No|
|No||Yes||No||0||Do not import||Yes|
|No||Yes||No||2+||Check for an employer match, otherwise create||No|
|Yes||No||No||1||Check for an employer match, otherwise create||No|
|Yes||No||No||0||Check for an employer match, otherwise create||No|
|Yes||No||No||2+||Check for an employer match, otherwise create||No|
|No||No||No||-||Match to anonymous contact||No|
We have had some offline discussion & agreed some further specs
- none of the imported donations should result in emails - Benevity takes care of that. I am handling this by setting no_thank_you to TRUE. We could set thank_you_date but we would be guessing at the date & assuming it went out so I prefer this approach.
- custom data fields per request
We would definitely want to include the Gift Data info for these donations, both the Restrictions and Gift Source fields. Could we have the following?
- For organization gift (i.e. matching gift portion): Restriction = Restricted - Foundation/ Gift Source = Matching Gift
- For individual gift under $1,000: Restriction = Unrestricted - General / Gift Source = Community Gift
- For individual gift $1,000 and over: Restriction = Unrestricted - General / Gift Source = Benefactor Gift
Note that Restriction has the custom field name 'Fund' but is handled as 'restriction' in the import code. Gift source is AKA 'Campaign' and gift_source
After the latest fixes we determined there was still a problem where soft-crediting was not happening where there was only a matching gift (and no individual gift). There is a fix in review for this.
In addition I just noticed the financial type of Cash is inconsistent with the others entered by engage
Added 2 more commits
- create & Benevity-specific Financial Type
- create employer relationship when there is no Individual gift and we are not creating a contact. In practice this means when the relationship has been determined by prior soft credits.
It seems there are just too many contacts with no emails in the files, after talking with @LeanneS we will create contacts for these rather than throw exceptions. The created relationship & soft credit will help us find these in future. Future contributions can hopefully match up rather than create duplicates using the employer relationship
Hopefully the last round of fixes, we still had some ambiguous contacts:
My response was
It looks like we are down to about 30 contacts where there is more than one matching person. This is happening in this scenario
- we don’t have an email for the Benevity contact
- there is more than one contact in the database with the same first and last name who have been soft credited by the matching gift organisation.
For example the first contact on this list is a name match to 2 different contacts who have both had soft credits from google (and in this case should almost certainly be merged)
The code could make a ‘decision’, to match to one, in which case it risks being wrong, or to create a new one, in which case it will happen every month! The lack of email data means it will not be possible to resolve the duplicates later.
I’m inclined to think we should try to resolve the individuals that are not resolvable, ie. review them & try to do any merges & if still more than one identify the correct one & create an employer relationship. There is not a large number in that export you did.
I’m able to generate a bunch of links for the unresolved contacts - I put them into a second sheet in your spreadsheet on the file drive. The sheet contains all possible ones for google, not just ones hit in that import but you can search within it. Here are the next few contact merge links.
All the duplicate ones I looked appeared to be legitimate duplicates
Major Gifts confirmed they just wanted new contacts created. I had a subsequent idea that this need not result in new contacts being created every time for these contacts every time as after the first time we could use the employer relationship to decide the 'best choice'. I can't think of a situation where the name match + employer relationship would not be unique, except in the case of a very common name. If that happened new ones would be created every time