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.
Description
Description
Details
Details
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Duplicate | CaitVirtue | T90242 Bulk import for existing and future payment methods into CiviCRM | |||
| Declined | None | T92325 Amend fields for International Check Importer | |||
| Declined | None | T114797 Remove Engage Civi users and revoke SSL keys | |||
| Declined | 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 |
Event Timeline
There are a very large number of changes, so older changes are hidden. Show Older Changes
Comment Actions
Removing myself as a reminder that we're starting fresh when we get back to this problem.
Comment Actions
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.)
@awight @Eileenmcnaughton have there been any changes that would make soft credits via import possible? Getting Engage out of Civi is no longer a priority.
Comment Actions
It looks like there have been some fixes around matching by email - but not about matching across a range of fields at the moment
Comment Actions
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."
Comment Actions
@LeanneS last comment was for you. Also, is it possible to use the gift amounts and dates to help find the matching donor? Or do different companies match at different percentages?
Comment Actions
@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.
Comment Actions
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.
Comment Actions
Change 331575 had a related patch set uploaded (by Eileen):
wip Matching gifts import.
Comment Actions
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 )
Comment Actions
I should also note that these are the columns I made & assumptions
Organization Name
Received Date
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 City
Donor State (update contact with address fields)
Donor Postal Code
Transaction ID
Original Amount - donation created against individual with soft credit to Org
Matched Amount - donation created against org with soft credit to individual
ignored
Project
Comment
Donation Frequency
Total
Comment Actions
Changes :
- 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.
Comment Actions
Change 334235 had a related patch set uploaded (by Eileen):
wip Matching gifts import.
Comment Actions
Change 334236 had a related patch set uploaded (by Eileen):
Add index to nick_name field.
Comment Actions
@Eileenmcnaughton I think it would be fine to have it import to the first matching organization. Ideally we'll have most of these records merged so it shouldn't be a problem. I'll defer to what you think is best though.
Comment Actions
Change 336456 had a related patch set uploaded (by Eileen):
CRM-19980, CRM-19881 - Fix slow queries due to LOWER on contact name fields
Comment Actions
Change 336456 merged by jenkins-bot:
CRM-19980, CRM-19881 - Fix slow queries due to LOWER on contact name fields
Comment Actions
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 |
Comment Actions
Change 337523 had a related patch set uploaded (by Eileen):
Add tests for relationship creation.
Comment Actions
Change 338015 had a related patch set uploaded (by Eileen):
Amount related fixes for Benevity import.
Comment Actions
Change 338031 had a related patch set uploaded (by Eileen):
Further Benevity report fix on handling on formatted '0'.
Comment Actions
Change 338031 merged by Eileen:
Further Benevity report fix on handling on formatted '0'.
Comment Actions
Change 338634 had a related patch set uploaded (by Eileen):
Further Benevity fix, resolve error in 'None Provided By Donor fix'
Comment Actions
Change 338634 merged by jenkins-bot:
Further Benevity fix, resolve error in 'None Provided By Donor fix'
Comment Actions
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
Comment Actions
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
Comment Actions
Change 340358 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Set financial_type for Benevity imports to Engage.
Comment Actions
Change 340358 merged by jenkins-bot:
Set financial_type for Benevity imports to Engage.
Comment Actions
Change 340408 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Broaden the definition of 'employed by' an org to include soft credits.
Comment Actions
Change 340408 merged by jenkins-bot:
Broaden the definition of 'employed by' an org to include soft credits.
Comment Actions
Change 341704 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Add & use Benevity financial type.
Comment Actions
Change 341749 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Benevity import, still create relationship when no individual gift
Comment Actions
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.
Comment Actions
Change 341704 merged by jenkins-bot:
[wikimedia/fundraising/crm] Add & use Benevity financial type.
Comment Actions
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
Comment Actions
Change 341945 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Loosen Benevity matching logic for individuals.
Comment Actions
Change 341958 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] More loosening of benevity matching logic.
Comment Actions
Change 341945 merged by Eileen:
[wikimedia/fundraising/crm] Loosen Benevity matching logic for individuals.
Comment Actions
Change 341958 merged by Eileen:
[wikimedia/fundraising/crm] More loosening of benevity matching logic.
Comment Actions
Change 341749 merged by jenkins-bot:
[wikimedia/fundraising/crm] Benevity import, still create relationship when no individual gift
Comment Actions
Change 342776 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Benevity loosening, handle multiple contact use in the past.
Comment Actions
Hopefully the last round of fixes, we still had some ambiguous contacts:
My response was
Hi Leanne,
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)
https://civicrm.wikimedia.org/civicrm/contact/merge?reset=1&cid=6509163&oid=10984899
https://civicrm.wikimedia.org/civicrm/contact/view?reset=1&cid=10984899
https://civicrm.wikimedia.org/civicrm/contact/view?reset=1&cid=6509163
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.
https://civicrm.wikimedia.org/civicrm/contact/merge?reset=1&cid=153092&oid=15013304
https://civicrm.wikimedia.org/civicrm/contact/merge?reset=1&cid=1012093&oid=15010105
https://civicrm.wikimedia.org/civicrm/contact/merge?reset=1&cid=7424522&oid=14916081
https://civicrm.wikimedia.org/civicrm/contact/merge?reset=1&cid=2468577&oid=14916081
https://civicrm.wikimedia.org/civicrm/contact/merge?reset=1&cid=2468577&oid=7424522
https://civicrm.wikimedia.org/civicrm/contact/merge?reset=1&cid=2462784&oid=19958382
All the duplicate ones I looked appeared to be legitimate duplicates
Eileen
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
Comment Actions
Change 342776 merged by jenkins-bot:
[wikimedia/fundraising/crm] Benevity loosening, handle multiple contact use in the past.