Fundraising/Internal-facing/CiviCRM/Imports

We get data to import to CiviCRM from various external sources in csv format.

Our primary import method is the CiviCRM Contribution import UI. This UI allows Melanie to map the fields in the csv appropriately and add defaults for various values.

As we do have some custom logic we need to implement 2 hooks and (as of April 2025) we also have a core hack in play.

The following are handled using the alterMappedRow import specific hook

Selecting the organization

There are 2 fields that should be used for matching to an organization

• Contact ID. If this is used then it is the only field used
• Organization Name. If Organization name is provided then the following logic applies to the Benevity Import and to all core Civi-Import imports
  • First, look and see if there is exactly one organization with that name in the `nick_name` field, if so, match
  • Second, look and see if there is exactly one organization with that name in the `organization_name` field, if so, match
  • If there is less than or more than one match on either of the above, reject the record. The importer will need to fix before re-trying Note that the organization_name is discarded after the match is made. This is to avoid inadvertantly changing situation where the nick_name and organization_name are deliberately different (eg. legal name vs trading name).

The correct dedupe rule to use is the organization_name one - but it will not 'kick in' as the match will already be determined by custom code before it reaches that point. The Benevity import uses the same logic to match the contact

Selecting the individual

Once the organization is selected the individual selection is done as follows (also like the Benevity import)

1. If first name, last name AND email are empy, select the anonymous individual record else...
2. Look up contacts with the same first_name, last_name, email. If not all parts are provided use the provided parts
   1. If exactly 1 contact is found,
      • IF email has been provided OR the contact is an employee of the organization OR the contact has a previous soft credit (in either direction) with the organization, use that as the match, otherwise, fail to find a match
   2. If more than one contact is found exclude any that do not have a soft credit with the organization or a relationship with the organization. If this whittles it down to 1 then use that, otherwise no match
3. If the above fails then fall back to the selected dedupe rule. This rule can either be strict enough to only find name & email matches (ie it will not find any if the above doesn't) or it could also find based on name + address fields

If the above cannot narrow it down to one individual the row will fail to import

Gateway

As of writing we are only doing one import via the Civi-Import method and hence have hard-coded to set the gateway to 'Matching Gifts'. This is stored to the field `contribution_extra.gateway`

Gateway Trxn ID

If no gateway transaction ID is provided through a mapped field in the csv then one is calculated based on hashing the name fields, check number (if any) and date. This helps ensure that the same row cannot be imported twice

No thank you

No thank you is set to 'Sent by portal' to prevent thank yous from going out

Source

We populate the contribution source with the currency & amount (e.g GBP 10.00).

Fidelity features

The Fidelity file reaches us with either just the name of the Donor Advised Fund OR the Donor Advised Fund AND the individual who gives via that fund. There is only one address column (which might be blank) and this applies to both the Fund and the Individual. For this import the contribution contact is the Organization and the soft credit contact is the Individual, if present. We agreed to use a dedupe rule that uses organization_name + street_address for the main contact and first_name + last_name + street_address for the Individual. Where there is no street_address we will map 'fidelity' as the default. This means that where two funds have the same name and have no address they WILL be treated as the same, as long as they both come from the Fidelity import. (Fidelity are currently not willing to give us adequate information to be able to accurately identify whether they are duplicates so we have a choice between over merging & under merging in this scenario)

In our hook we

a) Copy the address details from the contribution contact (the fund) to the soft credit contact (the individual), before dedupe lookups are done

b) Create a second 'Banking Institution' soft credit that maps back to the 'Fidelity Charitable Gift Fund' contact

c) Switch 'Anonymous' to "'Anonymous Fidelity Donor Advised Fund'

d) see the Pre hook section for relationship creation.

The following is handled through the pre hook

Create employer relationship for all workplace giving and matching gifts soft credits

Logic now ensures that

1) an employer relations is created whenever a Soft credit records is created between an individual and organization with one of these soft credit types

• Matching Gift
• Workplace Giving2) a 'Holds a Donor Advised Fund of' relations is created whenever a Soft credit records is created between an individual and organization with a Donor-advised Fund soft credit types

The logic is not restricted to imports but it is restricted to contributions less than 3 months old (just in case someone brings in some really old data). This would also apply to manual entry. It doesn't apply to our drupal imports because they don't set the soft credit type id (!!)

For consideration - things in our drupal imports

As of writing we still do the Fidelity and Benevity imports using the old offline2civicrm drupal module. The Fidelity is being actively migrated per https://phabricator.wikimedia.org/T386017 and we have yet to tackle Benevity

This is an audit of handling in our drupal imports not currently in our Civi-Import customisation

• Benevity import - set gift_source to Community Gift if < 1000 & Benefactor GIft if >
• Benevity import - remove any fields set to 'Not shared by donor'
• Benevity import - handle currency conversion
• Benevity import if the contact is the anonymous contact, unset address fields - 'city', 'street_address', 'postal_code', 'state_province', 'country'
• Benevity import - set email location type to work, do not overwrite primary email (possible home email)
• All data coming in CiviCRM - we truncate various fields for length - could be better in a pre hook
• We consider the gateway + gateway trxn if for duplicate (CiviCRM considers trxn_id & invoice_id but not our custom fields)
• All imports - we set a bunch of source fields - see below

Source fields

$message['source_name'] = $context->getSourceName();
$message['source_type'] = $context->getSourceType();
$message['source_host'] = gethostname();
$message['source_run_id'] = getmypid();
$message['source_version'] = $context->getSourceRevision(); 
$message['source_enqueued_time'] = UtcDate::getUtcTimestamp();

External Identifier

The civicrm_contact.external_identifier column is useful for imports where the sources have a unique ID for the imported contact. This is useful for mapping Civi contacts with external contacts without having to use the contact email which could also be modified.