Page MenuHomePhabricator

Benevity: Create an import method for matching gifts and payroll deductions
Closed, ResolvedPublic8 Estimated Story Points

Description

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.

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Restricted Application added a subscriber: StudiesWorld. · View Herald TranscriptNov 24 2015, 6:43 PM
awight removed awight as the assignee of this task.Feb 7 2016, 1:22 AM

Removing myself as a reminder that we're starting fresh when we get back to this problem.

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.

CaitVirtue triaged this task as High priority.Mar 2 2016, 5:41 PM

It looks like there have been some fixes around matching by email - but not about matching across a range of fields at the moment

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."

@DStrine Can we bump this back up the priority queue? @RLewis and @LeanneS have the details.

Ejegg added a subscriber: Ejegg.Jan 5 2017, 11:43 PM

Can you put a sample of the Benevity file (e.g. last year's) up on the file share?

Ejegg added a comment.Jan 5 2017, 11:47 PM

@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?

@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.

Ejegg added a comment.EditedJan 9 2017, 10:09 PM

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.

Change 331575 had a related patch set uploaded (by Eileen):
wip Matching gifts import.

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

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

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

Eileenmcnaughton added a comment.EditedJan 11 2017, 10:05 PM

Changes :

  1. check organization_name for org & failing that the nick_name field
  2. if the individual does not have exactly one match just create a new one
  3. 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'
  4. Change import name to Benevity & keep Benevity columns

Note - need to address slow lookup.

Change 334235 had a related patch set uploaded (by Eileen):
wip Matching gifts import.

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

Change 334236 had a related patch set uploaded (by Eileen):
Add index to nick_name field.

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

Change 331575 abandoned by Eileen:
wip Matching gifts import.

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

@RLewis @LeanneS - I can't recall what we want to do if there is more than one matching org - options are

  • skip & let importer fix & re-import
  • import against first match

If we do the former it will force a data clean up but ongoing should not be bad.

Change 334236 merged by jenkins-bot:
Add index to nick_name field.

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

@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.

Change 336456 had a related patch set uploaded (by Eileen):
CRM-19980, CRM-19881 - Fix slow queries due to LOWER on contact name fields

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

Change 336456 merged by jenkins-bot:
CRM-19980, CRM-19881 - Fix slow queries due to LOWER on contact name fields

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

Eileenmcnaughton added a comment.EditedFeb 9 2017, 7:39 AM

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 NameLast NameemailMatches found in DBActionrejection possible
yesYesYes1Match to contact, add relationship if missingNo
yesYesYes0Attempt 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 relationshipNo
yesYesYes2+Check for an employer match, otherwise create contactNo
NoYesYes1Match to contact with email+last name, add relationship if missingNo
NoYesYes0Create contact, add relationshipNo
NoYesYes2+Check for an employer match, otherwise create contactNo
YesNoYes1Match to contact with same email, add relationship if missingNo
YesNoYes0Create contact, add relationshipNo
YesNoYes2+Check for an employer match, otherwise create contactNo
NoNoYes1Match to contact with same email, add relationship if missingNo
NoNoYes0Create contact, add relationshipNo
NoNoYes2+Check for an employer match, otherwise create contactNo
YesYesNo1Create contact if not employer match, else matchNo
YesYesNo0Create contact, add relationshipNo
YesYesNo2+Check for an employer match, otherwise createNo
NoYesNo1Check for an employer match, otherwise createNo
NoYesNo0Do not importYes
NoYesNo2+Check for an employer match, otherwise createNo
YesNoNo1Check for an employer match, otherwise createNo
YesNoNo0Check for an employer match, otherwise createNo
YesNoNo2+Check for an employer match, otherwise createNo
NoNoNo-Match to anonymous contactNo

Change 337523 had a related patch set uploaded (by Eileen):
Add tests for relationship creation.

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

Change 337523 merged by jenkins-bot:
Add tests for relationship creation.

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

@LeanneS @RLewis I just deployed this - time for (ahem) testing on production :-)

Change 338015 had a related patch set uploaded (by Eileen):
Amount related fixes for Benevity import.

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

Change 338015 merged by Eileen:
Amount related fixes for Benevity import.

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

Eileenmcnaughton changed the point value for this task from 4 to 8.Feb 15 2017, 11:37 PM

Change 338031 had a related patch set uploaded (by Eileen):
Further Benevity report fix on handling on formatted '0'.

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

Change 338031 merged by Eileen:
Further Benevity report fix on handling on formatted '0'.

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

Change 338634 had a related patch set uploaded (by Eileen):
Further Benevity fix, resolve error in 'None Provided By Donor fix'

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

Change 338634 merged by jenkins-bot:
Further Benevity fix, resolve error in 'None Provided By Donor fix'

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

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

Latest fixes are deployed!

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

@LeanneS @RLewis I'm going to set the financial type to engage unless you for any reason want something different

Change 340358 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Set financial_type for Benevity imports to Engage.

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

Change 340358 merged by jenkins-bot:
Set financial_type for Benevity imports to Engage.

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

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.

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

Change 340408 merged by jenkins-bot:
Broaden the definition of 'employed by' an org to include soft credits.

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

Change 341704 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Add & use Benevity financial type.

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

Change 341749 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Benevity import, still create relationship when no individual gift

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

Added 2 more commits

  1. create & Benevity-specific Financial Type
  2. 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.

Change 341704 merged by jenkins-bot:
[wikimedia/fundraising/crm] Add & use Benevity financial type.

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

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

Change 341945 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Loosen Benevity matching logic for individuals.

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

Change 341958 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] More loosening of benevity matching logic.

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

Change 341945 merged by Eileen:
[wikimedia/fundraising/crm] Loosen Benevity matching logic for individuals.

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

Change 341958 merged by Eileen:
[wikimedia/fundraising/crm] More loosening of benevity matching logic.

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

Change 341749 merged by jenkins-bot:
[wikimedia/fundraising/crm] Benevity import, still create relationship when no individual gift

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

Change 342776 had a related patch set uploaded (by Eileen):
[wikimedia/fundraising/crm] Benevity loosening, handle multiple contact use in the past.

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

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

  1. we don’t have an email for the Benevity contact
  2. 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

Eileenmcnaughton renamed this task from Create an import method for matching gifts and payroll deductions to Benevity: Create an import method for matching gifts and payroll deductions.Mar 15 2017, 10:54 PM
Eileenmcnaughton moved this task from Backlog to Review on the Fundraising Sprint Far Beer board.

Change 342776 merged by jenkins-bot:
[wikimedia/fundraising/crm] Benevity loosening, handle multiple contact use in the past.

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

@LeanneS can we close this one!!!

Yes, we can! Thanks so much @Eileenmcnaughton.

Eileenmcnaughton closed this task as Resolved.Mar 21 2017, 7:17 PM
mmodell removed a subscriber: awight.Jun 22 2017, 9:44 PM