Fundraising tech/CiviCRM

Jump to navigation Jump to search

We use Open Source CRM CiviCRM to manage our donor data.


We use both CiviCRM extensions and custom drupal modules to manage our workflows and donor data

CiviCRM extensions (checked in to drupal/sites/default/civicrm/extensions/ in the CRM repo):

  • civi-data-translate Allows more extensive localization of CiviCRM messages than is possible in Civi core
  • Extended Mailing Stats
  • Email Amender Corrects common errors in domain names in email addresses
  • Deduper Combines multiple contact records for the same person
  • Matching Gifts Retrieves lists of employer matching gift policies with a third party provider. Also exports the list of employers and IDs so that we can use it in front-end forms
  • Monolog Use monolog streams to output logging to e.g. syslog rather than the files written by Civi core.
  • Export Permission Allows us to disable exporting search results for certain user roles
  • CiviToken Provides extra tokens for anything Civi renders from templates
  • Extended Report More report templates
  • Angular Profiles
  • Contact Layout Editor GUI for editing contact summary screen
  • Shoreditch theme based on Bootstrap
  • Tutorial
  • Contact Editor Allows changing contact types
  • Data Checks Detects and batch-fixes contact data inconsistencies such as no primary location.
  • Forgetme UI for complying with GDPR data deletion requests
  • Wikimedia Geocoder Privacy respecting but imprecise address geocoder that uses a local zip code database
  • Omnimail Integrates with bulk email providers (Acoustic is most fully implemented). Attaches CiviCRM activities to contacts based on email sends from bulk mail providers
  • Relationship Block Shows relationships in contact summary
  • RIP Sets the "No Bulk E-mail (User opt out)" flag on deceased people
  • SmashPig Uses the SmashPig library to make nightly recurring charges for monthly donors
  • Wikimedia System Tools debugging helpers
  • The The Normalizes use of articles in organization names
  • TargetSmart Helper for adding contact data from TargetSmart
  • Unsubscribe Email Dedicated screen to facilitate opting contacts out by email address
  • WMF Fraud reports Helps the payments team review suspicious transactions when deciding whether to capture, reject, or refund.
  • Replay-On-Write Allows using CiviCRM with a Primary+Replica database setup
  • Activity Assignee Settings Limit who can be assigned activities
  • WMF CiviCRM Catchall for our site-specific code which has migrated out of Drupal modules
  • WMF Thank You Uses our twig templates to automatically thank donors for donations. Fast and multilingual, but uses a different template language from the rest of Civi and has no editing UI. Should eventually evolve to use templates from civi-data-translate

Drupal modules (Checked in at drupal/sites/all/modules/ )

See outdated list at

We use the civix utility to generate and update Civi extension boilerplate.

Email sending logic[edit]

We send bulk email via Acoustic based on nightly uploads. The nightly uploads include a calculation of which mails are opted_out based on if any of the following fields indicate opt_out:





Contacts can become opted out by the following ways

  • Filling in a donation form with an opt-in option and not selecting it - the contact record is set to opt_in = 0 on import & on merge the latest is kept
  • An action we import from Silverpop causing them to put on hold - see Fundraising tech/CiviCRM#Recipient data
  • An action we import from Silverpop causing them to be unsubscribed - see Fundraising tech/CiviCRM#Recipient data
  • Donor services manually unsubscribing them based on donor feedback via the Unsubscribe form (our url is civicrm/a/#/email/unsubscribe)
  • Major gifts marking them do_not_solicit
  • The donor accessing our unsubscribe form & their desire not to be subscribed being processed through our queue.

Communication Fields[edit]

On Contact Record[edit]

Field Explanation Example
preferred_communication_method Whether contact prefers email/phone/mail/SMS/Fax(!) email, phone
do_not_email Do not email this contact at any address - there are also separate fields for do_not_phone and do_not_sms
preferred_language What language to use with this contact spanish
email_greeting_id/email_greeting_custom These fields allow the contact to specify how they want to be greeted in an email, the ids reference a standard set of options or it can be customized Dear Space Martian
is_opt_out This is for when the user has opted out of bulk mails - in our DB this would be via SIlverpop or the unsubscribe page ds use. CiviCRM will not permit CiviMails to be sent to users with this flag set. We pass this field to silverpop as 'opted_out' - which is true is is_opt_out OR civicrm_email.on_hold OR do_not_solicit is TRUE yes/no

On Email Record[edit]

Field Explanation Example
on_hold Do not email this address as it has bounced or been undeliverable in the past yes/no
is_bulk_mailing Can send buik mail here yes/no
is_primary Is this the email address we should use for the contact? yes/no

Communication Custom Group - Related to Contact[edit]

Field Explanation Example
opt_in Whether a contact has explicitly opted in to our mailing lists yes/no
do_not_solicit Generally entered by Major Gifts yes/no
Known_fraudster yes/no
Employer_Name Wikimedia Foundation

Data transfer between CiviCRM and Acoustic (aka Watson Campaign Automation Silverpop, IBM )[edit]

Mailings are sent out from the external tool Watson Campaign Automation - formerly Silverpop.

We retrieve data back into CiviCRM from WCA in the following forms

TODO: document technical details of how this is synced (Omnimail library and CiviCRM extension, which jobs do what)

Mailing data[edit]

WCA only stores data for 450 days. We retrieve this data for longer storage.

We store the following data in the civicrm_mailing table:

Field Explanation Example
name The unique name in WCA or WCA reference (the WCA reference is the same value as stored in hash) 20190125_UnitedStates(US)_English(en)_TYCampaign_R2-5-FromGratefulInternet (1)
from_name From name in email Wikimedia Foundation
from_email Email in from address
replyto_email Reply to email
subject Subject line Our gift to you
body_text Text version of content Unwrap 15 gorgeous image......
body_html Html version of content <!DOCTYPE html>....
hash WCA unique mailing reference sp58317985
created_date Date the mailing was created on 2019-01-25 13:35:36
scheduled_date In practice this is the same as the created date 2019-01-25 13:35:36
campaign_id Reference to civicrm_campaign table - The only additional data this table holds is start_date from WCA. I feel like the reasons for using the civicrm_campaign table may no longer be valid - I can't recall or determine what they were but I think it was to do with additional requirements that didn't eventuate - ie. distinguishing between Major gifts mailings & normal ones 97713

And we store additional fields in the table civicrm_mailing_stats - these fields are generally aggregate information as calculated by WCA

This table is provided by the extension Extended Mailing Stats . - it also adds the table civicrm_mailing_stats_performance which we don't use. There is double up between this & the civicrm_mailing table on some fields but that is because the schema was

determined by an external extension writer with slightly different needs.

Field Explanation Example
mailing_id Link to civicrm_mailing table 4
mailing_name Mailing name or SP ref 20190125_UnitedStates(US)_English(en)_TYCampaign_R2-5-FromGratefulInternet (1)
created_date Scheduled Date 2019-01-25 13:35:36
start_date Start date 2019-01-25 14:35:36
recipients Number of mails sent for the mailing 4000
delivered Number sent less number bounced 3950
bounced Number bounced 50
opened_total Total number of opens (to the extent email clients make that info available) 2500
opened_unique Number of opens by unique recipients 2000
unsubscribed Number of unsubscribes in response to the mailing 60
suppressed Number of emails that WCA suppressed mailing to due to it's own internal listing of recipients who can receive our emails & have not opted out via them 200
blocked Number of emails blocked by the recipient's provider. Providers such as AOL, gmail may block some or all of the emails based on whitelisting and blacklisting. 90
abuse_complaints Number of users who identified our email as 'spam' or complained to WCA 3


Remind me later contacts[edit]

Contacts who are in Silverpop but who do not have a contact_id from CiviCRM are considered to be 'remind me later contacts' - who signed up via silverpop. We retrieve these contacts and add them to the group in CiviCRM called 'Silverpop imports' (group id is 310 - url is ). We retrieve the following information about them

- language

- source

- created date

- country

- email

Recipient data[edit]

We retrieve information about mailing actions (sending, opening etc) for each contact. This information is stored in the civicrm_mailing_provider_data table with the following fields

Column Used for Notes
contact_identifier WCA contact reference
contact_id CiviCRM contact reference
mailing_identifier WCA Mailing reference joins to civicrm_mailing.hash to get more mailing data- api spec function defines this join
email Contact email Email that was used for the sending - contact's email may change but this should not.
recipient_action_datetime Timestamp of action Makes up unique key in combination with contact_identifier & mailing_identifier
event_type Action We have 2 actions that we take

- Put on hold - this puts the email address on hold - if we get a new email for the contact they will start getting emails again. We set civicrm_email.on_hold to 1 - the api action for this is omnirecipient.process_onhold

- Unsubscribe - this unsubscribes the contact. We add an Unsubscribe activity and set civicrm_contact.is_opt_out to 1. We also search for other instances of that email address & set civicrm_email.is_bulk_mail to 0 for them. Setting this is_bulk_email doesn't really affect anything at the moment but we also do is for DS unsubscribes. The api action for this is omnirecipient.unsubscribe

Type Meaning Our actions
Sent We sent them the mailing
Open They opened the mailing AND we know they did

(not all opens result in an open action)

Click Through They clicked on a link in the mailing
Opt Out Action Unsubscribe
Hard Bounce Put mail on hold
Reply Abuse Put mail on hold
Reply Change Address
Reply Mail Block
Reply Mail Restriction
Reply Other
Soft Bounce Action Unsubscribe
Suppressed Suppressed by WCA based on their own lists - e.g AOL won't accept our emails so all AOL are suppressed Action Unsubscribe
is_civicrm_updated Track whether we have performed an action (e.g unsubscribe) yet

Matching Gifts[edit]

We are building a CiviCRM extension to pull data about corporate matching gift policies from SSBInfo and store it as Organization records with custom fields.

  • Matching gifts provider ID
  • Matching gifts provider info URL (for SSB:<id> from json:
  • Minimum (USD) gift matched
  • Name as specified by matching gifts provider
  • Guide URL
  • Online form URL
  • Matching gifts info last updated date?
  • List of subsidiaries in a text blob (JSON? delimited list? what delimiter?)
  • Flag to suppress companies from the frontend dropdown if they come in on the search but turn out to NOT actually match our donations.