Fundraising tech/Acoustic interaction

We export data to acoustic once a day from our CiviCRM database. We also import data back in on a more regular schedule.

(You may hear Acoustic referred to one of it's many previous brandings - such as Silverpop, WCA, Watson Campaign Automation, IBM ). Our supplier in our relationship is Trilogy and our contact there is Brian Sisolak. Brian is a Phab user and can be added to tasks).

= Exporting data to Acoustic =

The export scripts upload 4 separate files to Acoustic which are imported into Acoustic using mappings that Katie H manages within Acoustic. The files are

1) DatabaseUpdate-*.csv - this file holds detailed information about contacts who have recently been updated. The update is cumulative - ie. we add/update contacts but don't delete them. We use the field civicrm_contact.modified_date to determine which contacts to include in the update.

2) Unsubscribes-*.csv - used to update the master suppression list.

3) Optout-*.csv - this file is identical to the unsubscribes file. It is used separately from the Acoustic end - updating the main database with opt out information whereas the Unsubscribes csv is used to update the Master Suppression list

4) MatchingGifts-*.csv

Database Update export
This is our main export job. It runs once a day and sends a complete dataset for any contacts within it's upload set. The job only every updates or creates - it NEVER DELETES. Any deleting is done through a separate manual process by Katie.

Who is included in the update?
The upload set is contacts modifed within the configured date range (currently 7 days). Some notes on the modified_date & it's limits are in this phab.

What is included in the update?
The date for the export is compiled into the view `silverpop_export_view` before being exported into a csv. This lives in the silverpop database on the staging server.

. The nightly uploads include a calculation of which mails are opted_out based on if any of the following fields indicate opt_out:

civicrm_contact.is_opt_out

do_not_email

do_not_solicit

email.on_hold

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
 * An action we import from Silverpop causing them to be unsubscribed - see Fundraising tech/CiviCRM
 * 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.

Matching Gifts
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: https://javamatch.matchinggifts.com/search/companyprofile/wikimedia_iframe/ from json: online_resources.id)
 * 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.

= Importing back from Acoustic = We retrieve data back into CiviCRM from Acoustic in the following forms

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

Mailing data
Acoustic 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