Fundraising tech/CiviCRM

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

Communication Custom Group - Related to Contact
For the Silverpop/Watson export (detailed below), is_opted_out is set to 1 if any of the following fields indicate opt_out:

civicrm_contact.is_opt_out

do_not_email

do_not_solicit

email.on_hold

Data transfer between CiviCRM and Watson Campaign Automation (aka Silverpop, IBM )
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

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

We store the following data in the civicrm_mailing table: 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.

Remind me later contacts
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 https://civicrm.wikimedia.org/civicrm/group/search?reset=1&force=1&context=smog&gid=310&component_mode=1 ). We retrieve the following information about them

- language

- source

- created date

- country

- email

Recipient data
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