Fundraising/Data and Integrated Processes/Acoustic Integration

We use Acoustic to send our bulk emails. We export data to acoustic once a day from our CiviCRM database. We also import data back in on a more frequent schedule.
Acoustic has it's own challenges and it is useful to understand that
# The Acoustic database has one record by Email - which may of may not map directly to a contact ID. For example in CiviCRM 2 contacts might share an email or one contact might have multiple emails. To manage this
## When we push up a contact to Acoustic we 'Acoustic-merge' all contacts with that email address, using the details associated with the most recent donor
## When a donor has more than 1 email we suppress the non-primary emails in Acoustic. There are multiple tickets open to review how we suppress / opt out contacts in Acoustic.
# Acoustic has a bunch of system fields which is applies it's own logic to - these are not always visible in Acoustic and include information around snooze, last send date and opt out.
# 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 with Acoustic is Trilogy and our contact there is Brian Sisolak. Brian is a Phab user and can be added to tasks. We also work with Eric Wilfong. In addition to being able to communicate with them via phab (and email, hangouts) there is a slack channel for trilogy which they are responsive in.
== FTP access ==
Permitted IP addresses are documented (outdated) on collab and are configured under both Administration->Security Settings and under the permitted IPs from the upload user
== SMS ==
The sms documentation regarding Acoustic & the flow in general is here
== List of Scheduled jobs & api ==
We run scheduled jobs to export and import from Acoustic along with ensuring our snooze and privacy jobs are working. A list of the jobs is at Fundraising/Data and Integrated Processes/Acoustic Integration/Scheduled Jobs
Accoustic API docs
== 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. Note that when the files are uploaded Acoustic declares how many 'duplicates' there are. Until early 2020 this number was how many rows duplicated existing DB entries. A change early 2020 means it refers to how many duplicates are in the csv file
As of Oct 2024 we have switched the DatabaseUpdate to use the Acoustic importList api (via our Omnicontact.upload api) to trigger the import at the Acoustic end. The other imports could be switched over but we require a little more development for the 2 out out ones as to make the files / mappings work. The Omnicontact.upload maps the fields in the csv to the Acoustic fields based on field name. The headers in the csv file must match the Acoustic fields name. In the case of the opt out one this is not currently the case and in the case of the master suppression list one we need to fix the process to target the master suppression list (which might be possible now using the databaseID set to the suppression list ID but it is not currently tested.
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. This file is imported using the Omnicontact.upload api which internally calls the Acoustic ImportList api. 
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) Checksum-*.csv - uploads a checksum for every contact
4) MatchingGifts-*.csv - pending deletion https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/tools/+/1104750
=== 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.
==== How the script works ====
At a high level the script builds a table of contacts who have been modified within the last 7 days (time limit modifiable via offset_in_days config value or -d command-line parameter to update.py script) and then builds out various tables of information about them. It creates a final view to be exported from these tables. The reason for the tables is to store calculated values to keep each query somewhat manageable.
Some notes on the modified_date & it's limits are in this phab.
===== sql files =====
The followin files are called by the script
* update_silverpop_staging.sql - builds the 'world' of contacts modified in the last 7 days
*update_table.sql - builds the rest of the details about the contacts and generates the final view
===== DB tables =====
* silverpop_update_world - emails connected to contacts update in the last 7 days 
* silverpop_export_staging - key details about each contact 
*silverpop_email_map - mapping of email to the highest email id - used for merging details for contacts with the same email 
*silverpop_export_stat, silverpop_export_latest, silverpop_export_highest, silverpop_endowment_latest, silverpop_endowment_highest , silverpop_has_recur- calculation tables
*silverpop_export - final collated table of details 
*silverpop_export_view - view formatted for export 
*silverpop_export_view_full - as per the view but not restricted by modified date 
* silverpop_missing_countries - best guess at a contact's country from contribution_tracking (fallback for properly stored country) See phab
* silverpop_countrylangs - best guess at the language to use based on a hard-coded list of countries to languages (fallback for properly stored language) See phab
==== Running the script ====
When running these in production, please do so under screen or tmux, as they take a long time.
screen run-job -j silverpop_daily
This runs a job that then runs:
silverpop_emails_build_export_files which runs though all the SQL and generates the files to upload in /srv/silverpop_export
wmf-cv api4 --user=root -vv Omnicontact.upload which uploads the files generated in the silverpop_emails_build_export_files script and initiates the import at the Acoustic end
====Who is included in the update?====
The upload set is contacts modifed within the configured date range (currently 7 days).
The upload set EXCLUDES contacts who are calculated to be opted out - this means that for opted out contacts or not-opted int contacts their aggregate details and optout details are not updated - which can be confusing. I can only speculate that the reason for this is to reduce the number of non-emailable contacts in Acoustic. Or perhaps it's just one of those things that seemed like a good idea once. Only updates to the silverpop_export table have this restriction - all other tables should be updated with the latest details for these contacts.
When we export we treat all contact records with the same email address as the same contact and export fields that hold aggregate values. ie. if there are 2 contacts with the email address 'masteroftheuniverse@earth.com' then the exported most recent donation date would be the most recent for either contact. The total donated would be the total across both contacts. This is partly because there was a time we didn't have deduping in CiviCRM at all and partly to compensate for gaps in our deduping efforts.
====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.
===== Export fields =====
This spreadsheet, maintained by online, may be more up-to-date than the list below
Exported Field 	CiviCRM data source 	Notes 	
ContactID 	civicrm_contact.id 	 	
contact_hash 	civicrm_contact.hash 	 	
email 	civicrm_email.email WHERE is_primary = 1 	 	
firstname 	civicrm_contact.first_name 	 	
lastname 	civicrm_contact.last_name 	 	
gender 	civicrm_contact_gender_id 	Female|Transgender 	
country 	civicrm_address.country_id WHERE is_primary = 1 	If empty an attempt will be made to get it from contribution_tracking 	
state 	civicrm_address.state_province_id WHERE is_primary = 1 	 	
postal_code 	civicrm_address.postal_code WHERE is_primary = 1 	 	
employer_name 	civicrm_contact.organization_name 	This is filtered out if not provided by the contact - see notes on matching gifts fields 	
employer_id 	civicrm_contact.employer_id 	This is filtered out if not provided by the contact - see notes on matching gifts fields 	
IsoLang 	civicrm_contact.preferred_language 	First 2 letters - eg. en, es, de. If empty a country lookup table is used 	
latest_optin_response 	civicrm.civicrm_value_1_communication_4.opt_in 	This custom field is exported as 'Yes', 'No' or '' - the blank value indicates the Opt in form has not been presented to them.	
TS_birth_date 	civicrm_contact.birth_date 	US format - ie '21/05/1980' 	
TS_charitable_contributions_decile	civicrm_value_1_prospect_5.charitable_contributions_decile 	 	
TS_disc_income_decile 	civicrm_value_1_prospect_5.disc_income_decile 	 	
TS_estimated_net_worth 	civicrm_value_1_prospect_5.estimated_net_worth_144 	Fields are transformed to show the labels not db values 	
TS_family_composition 	civicrm_value_1_prospect_5.family_composition_173 	^^ 	
TS_income_range 	civicrm_value_1_prospect_5.income_range 	^^ 	
TS_occupation 	civicrm_value_1_prospect_5.occupation_175 	^^ 	
TS_voter_party 	civicrm_value_1_prospect_5.voter_party 	^^ 	
both_funds_donation_count 	Calculated field based on wmf_donor 	 	
both_funds_first_donation_date 	^^ 	 	
both_funds_highest_donation_date 	^^ 	 	
both_funds_highest_usd_amount 	 	 	
both_funds_latest_donation_date 	 	 	
both_funds_latest_native_amount 	 	 	
endowment_latest_donation_date 	 	 	
endowment_first_donation_date 	 	 	
endowment_donation_count 	 	 	
endowment_highest_donation_date 	 	 	
endowment_highest_native_amount 	 	 	
endowment_highest_native_currency 	 	 	
endowment_highest_usd_amount 	 	 	
endowment_latest_currency 	 	 	
endowment_latest_native_amount 	 	 	
AF_donation_count 	Number of donations made to WMF (Annual Fund) by the contact 	 	
AF_first_donation_date 	 	 	
AF_highest_donation_date 	 	 	
AF_highest_usd_amount 	 	 	
AF_latest_donation_date 	 	 	
AF_latest_native_amount 	 	 	
AF_highest_native_amount 	 	 	
AF_highest_native_currency 	 	 	
AF_lifetime_usd_total 	 	 	
AF_latest_currency 	 	 	
AF_latest_currency_symbol 	 	 	
AF_has_recurred_donation 	 	 	
AF_has_active_recurring_donation 	 	 	
AF_recurring_first_donation_date 	 	 	
AF_recurring_latest_donation_date 	 	 	
AF_usd_total_2014 	 	 	
AF_usd_total_2015 	 	 	
AF_usd_total_2016 	 	 	
AF_usd_total_2017 	 	 	
AF_usd_total_2018 	 	 	
AF_usd_total_2019 	 	 	
AF_usd_total_2020 	 	 	
AF_usd_total_2021 	 	 	
AF_usd_total_2022 	 	 	
AF_usd_total_2023 	 	 	
both_funds_latest_currency 	 	 	
both_funds_latest_currency_symbol 	 	 	
matching_gifts_provider_info_url 	civicrm_value_matching_gift.provider_info_url 	The civicrm_value_matching_gift table holds the details for the employer, as accessed from the HEP database. We export the value based on using civicrm_contact.employer id on the individual record. 
We only export this field if the donor has provided the employer id to us through the front end form. We don't export it if, for example, we know about the relationship from a Benevity import (in which case they are already a matching gift donor and it would be wrong to send them emails asking them to join the matching gifts programme).
This information is stored in 
civicrm_value_relationship_metadata.provided_by_donor - a custom data table that extends the relationship in CiviCRM	
matching_gifts_guide_url 	civicrm_value_matching_gift.guide_url 	^^ 	
matching_gifts_online_form_url 	civicrm_value_matching_gift.online_form_url 	^^ 	
preferences_tags 	civicrm_entity_tags table where the linked tag label starts with Preferences:	 	
=====CiviCRM Communication Fields=====
======On Contact Record======
Field 	Explanation 	Example 	Accoustic
id 	Contact ID 	4498331 	ContactID
preferred_communication_method 	Whether contact prefers email/phone/mail/SMS/Fax(!). We don't really use this field except possibly for high touch (major gifts) contacts 	email, phone 	n/a
do_not_email 	Do not email this contact at any address - there are also separate fields for do_not_phone and do_not_sms 	yes/no 	If true set opted_out at acoustic, if false, ignore
preferred_language 	What language to use with this contact 	spanish 	IsoLang - we export a 2 character code - eg. 'es'
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. Generally this is used for high touch contacts where we want to be more deliberate in how to address them 	Dear Space Martian	n/a
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 if is_opt_out OR civicrm_email.on_hold OR do_not_solicit is TRUE	yes/no 	If true set opted_out at acoustic, if false, ignore
=====On Email Record=====
Field 	Explanation 	Example
on_hold 	Do not email this address as it has bounced or been undeliverable in the past
- this means either Acoustic our our thank you mailing has registerd a bounce	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=====
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
Survey_group 	 	
Known_fraudster	 	yes/no
Employer_Name 	 	Wikimedia Foundation
optin_source 	 	
optin_medium 	 	
optin_campaign 	 	
Tags
A handful of tags (currently 4) have been setup to opt contacts out of specific campaigns. These all have labels that start
with "Preferences:" and the part after that is uploaded to Acoustic. The options for the field are configured in the field in
Acoustic and must use the same casing for a match to be made in Acoustic.
=== Unsubscribes and opt out export ===
Acoustic automatically (and non-optionally) excludes 'opted out' and 'suppressed' contacts from mailings. It stores these in 3 places....
# Global suppression list - this is a list of emails who have opted out of all emails coming from Acoustic. We cannot see this list as it covers more than just our organization
# Master suppression list. This list holds contacts that have opted out directly to Acoustic and contacts who we have uploaded to acoustic as opted out. The list cannot be searched from within the main search criteria screens in Acoustic as it is organisation-wide whereas the other screens only cover 1 db. To get around this we also update....
# Opted out setting - this cannot be seen within Acoustic on the contact record but the contact will be missing from search results unless specified on the search screen (see image)
We upload the same contact list to the opt out list and to the main WMF Acoustic DB in 2 separate jobs. This is picked up by 2 separate jobs in Acoustic - 1 adds to the Master Suppression list and the other updates the opt out list. The reason for maintaining these 2 lists is searchabilitity in Acoustic
=====Who do we opt out?=====
We opt out email addresses where ANY contact attached to them meets opt out critieria. This means that if 2 contact records exist with the same email address and the older one is qualities as opted out we will opt them out in Acoustic. If these records are subequently merged the merged record should accordlngly be opted out in CiviCRM to reflect the fact that it is opted out in Acoustic. Note that removing an opt out flag from a contact in CiviCRM will not remove the opt out from Acoustic (see the section below on how contacts get opted back in)
Opt out is set f any of the following fields indicating opt_out in any of the contact records sharing the contact name.
civicrm.contact.is_opt_out 
civicrm_contact.do_not_email
civicrm_value_1_communication_4.do_not_solicit
civicrm_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 Recipient data
*An action we import from Silverpop causing them to be unsubscribed - see Recipient data
* Donor services manually unsubscribing them based on donor feedback via the Unsubscribe form (our url is civicrm/a/#/email/unsubscribe) - results in is_opt_out being set
*Major gifts marking them do_not_solicit
*The donor accessing our unsubscribe form & their desire not to be subscribed being processed through our queue.
====== How we generate the list ======
We basically compile a table of every email that has ever existed in our database (from log_civicrm_email) and then remove all the contacts we have previously determined are not opted out and have not declined to be opted in. We also delete all emails associate with civicrm user accounts.
This results in the deliberate suppressing of some emails that we might NOT want to opt out long term such as 
* Non-primary addresses
* Previous email addresses
* Addresses dropped during contact merge
* Addresses that are opted out based on Acoustic suppression that is then lifted (for example they used to suppress all Comcast emails)
* Addresses that chose not to opt in - but later may do so.
====== How do opted out contacts get opted in again ======
It's not clear that they do.
We only send Acoustic updates as to who should be opted out. These are imported to BOTH the master suppression list and the opted out list as per above. There is an occassional manual process by Katie to purge the master suppression list but uploading them to opted_out is relatively new and it does not appear that we have any process to update the opted out setting.
Note that our table (silverpop_excluded) only holds updates - to do a full update we need to run the sql directly in mysql without the restrictive modified date and then run the export only job to regenerate the 2 csvs holding ALL contacts who should be opted out. However, the deletion would also need to be figured out
===== Useful Acoustic links =====
https://engage4.silverpop.com/lists.do?action=listSummary&listId=9574332 (master suppression list)
https://help.goacoustic.com/hc/en-us/articles/360043348693-Suppression-lists (help link)
https://help.goacoustic.com/hc/en-us/articles/360042858094-Master-suppression-list (help link)
===Matching Gifts Export===
We have a CiviCRM extension to pull data about corporate matching gift policies from SSBInfo and store it as Organization records with custom fields. These are exported to Acoustic as follows in the main Database export job:
CiviCRM field 	Acoustic export field name 	notes 	
entity_id 	employer_id 	Within CiviCRM this field links the custom data we store for matching gifts for this organisation to the contact record 	
name_from_matching_gift_db 	employer_name 	Name as specified by matching gifts provider 	
matching_gifts_provider_info_url	matching_gifts_provider_info_url	Matching gifts provider info URL (for SSB: https://javamatch.matchinggifts.com/search/companyprofile/wikimedia_iframe/<id> from json: online_resources.id)	
guide_url 	guide_url 	Guide URL 	
online_form_url 	online_form_url 	Online form URL 	
Other matching gift fields 
*Matching gifts provider ID
*minimum_gift_matched_usd
*match_policy_last_updated
*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.
== Pushing group data to Acoustic ==
Within Acoustic there are a bunch of contact lists. These lists are set up primarily for the purpose or targetting with mailings. Within CiviCRM we have the functionality to push a group from CiviCRM to Acoustic. This is done using apis in the Omnimail extension:
The output from the Omnicontact.get api can be viewed at https://civicrm.wikimedia.org/civicrm/a/#/omnimail/remote-contact?cid=x where x is the contact ID
The push action can be accessed at /civicrm/a/#/omnimail/groupsync/?id=y where y is the group id. Note that the push action can be done repetitively - it will just try to create it all again but should not fail if it already exists
// Create a list within Acoustic with the same name as CiviCRM group with the id 2
// Note that the acoustic id for this group will be saved to a custom field in CiviCRM
// 'Group_Metadata.remote_group_identifier' (ie group name is Group_Metadata field name 
// is 'remote_group_identifier'). If the remote_group_identifier has already
// been stored this will be returned (no interaction with Acoustic)
drush @wmff cvapi Omnigroup.create version=4 groupID=2
// Add a contact from CiviCRM to the remote groupID
// The group must already exits.
drush @wmff cvapi Omnicontact.create version=4 email=jenny@example.com groupID=2
// The contact can also be retrieved
drush @wmff cvapi Omnicontact.get version=4 email=jenny@example.com
// or 
drush @wmff cvapi Omnicontact.get version=4 contactID=202
// Push from CiviCRM to Acoustic
// This ensures the group has been added to Acoustic and then adds
// the contacts within that group in CiviCRM to Acoustic.
drush @wmff cvapi Omnigroup.push version=4 groupID=2
== Importing back from Acoustic ==
We retrieve the following types of data back into CiviCRM from Acoustic 
*Mailing data - details & content for the emails sent through Acoustic
* Remind me later contacts - contact details of people recorded as remind me later in Acoustic
*Recipient data - details about all actions taken regarding the emails (including being send an email, opens, blocks etc)
===Mailing data===
Mailing data is the details of the emails sent from Acoustic. They only store this information for 450 days so we need to store it in CiviCRM if we want to see what was sent more than 18 months-ish ago. Bringing this data into CiviCRM also allows people dealing with donors to see what emails a donor has been sent. 
We retrieve 2 types of data - content and statistics. The former is saved in the table civicrm_mailing and the latter is saved in the table civicrm_mailing_stats. 
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 	donate@wikimedia.org
replyto_email 	Reply to email 	donate@wikimedia.org
subject 	Subject line 	Our gift to you
body_text 	Text version of content 	Unwrap&nbsp;15&nbsp;gorgeous&nbsp;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 Acoustic
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===
online team RML notes
Remind me later contacts have been presented with a banner and clicked on the 'remind me later' link on that banner. They provide either an email or a phone number and if this does not match an existing email or phone in Acoustic a new Acoustic recipient is created. In general when we refer to 'RML contacts' we are often referring to those contact whose email / phone we did not have until they filled in the form. We can identify those contacts because they will not have a contact_id on their Acoustic recipient record. 
We bring them into CiviCRM by fetching the contacts on a list at Acoustic of contacts with the criteria of 'has a country and has no contact ID' (the reason for the country criteria there has been forgotten). Once they have been pulled into CiviCRM and a contact created the newly created contact ID is pushed up to Acoustic in the nightly upload, causing them to no longer be on the aforementioned list. (However, if they opted out they will not be uploaded). 
In CiviCRM when we create these contacts we add them to the group in CiviCRM called 'Silverpop imports' (group id is 310 ). 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 is raw data and contains functional duplication - eg.there will be an 'OPEN' event each time a person opens an email, some mail clients do this over and over as part of some sort of polling.
In addition to importing this information to our database for the purposes of viewing / querying we take action to not email people for some types of events. There are 2 types of actions we take
======Put on hold======
The api action for this is omnirecipient.process_onhold this finds all email addresses with that email and sets them to on_hold. The update is specific to the email (not the contact) - if we get a new email for the contact the email record will be updated to the new email and on_hold will be removed. On hold is one of the fields involved in calculating opt_out for exporting to Acoustic 
This involves the following steps
*Set civicrm_email.on_hold to 1
====== Unsubscribe======
The api action for this is omnirecipient.unsubscribe - this unsubscribes the contact which is involves the following steps. 
*add an Unsubscribe activity
*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.
This information is stored in the civicrm_mailing_provider_data table with the following fields
Column 	Used for 	Notes 	 	 	
contact_identifier 	Acoustic contact reference 	This can be appended to this url https://engage4.silverpop.com/searchRecipient.do?action=edit&listId=9644238&recipientId= to find the contact in Acoustic 	 	 	
contact_id 	CiviCRM contact reference 	The CiviCRM contact ID is stored in Acoustic against the contact record. If the Acoustic contact does not have a contactId they will be imported into CiviCRM as a 'remind me later' contact - ie a contact who entered our system from an Acoustic form.	 	 	
mailing_identifier 	Acoustic Mailing reference 	This is the internal Acoustic mailing reference. It consists of the prefix 'sp' and the Acoustic reference number. The 'sp' prefix stands for Silverpop and is a convention from the email team (possibly legacy). The reference number can be appended to this url https://engage4.silverpop.com/ux/#/sentMailing/ to view the mailing in Acoustic
It can be used to civicrm_mailing.hash field to get data about the mailing	 	 	
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 	
{| class="wikitable" 	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 	 	 	 	
Clickstream 	 	 	 	 	
Opt Out 	 	Action Unsubscribe 	 	 	
Hard Bounce 	 	Put mail on hold 	 	 	
Reply Abuse 	 	Action Unsubscribe 	 	 	
Reply Change Address 	 	 	 	 	
Reply Mail Block 	 	Put mail on hold 	 	 	
Reply Mail Restriction 	 	 	 	 	
Reply Other 	 	 	 	 	
Soft Bounce 	 	 	 	 	
Suppressed 	Suppressed by WCA based on their own lists - e.g AOL won't accept our emails so all AOL are suppressed	Action Unsubscribe 	 	 	
=Forgetting =
When a contact asks to be forgotten we upload this request to Acoustic. We need to do that once for each of the 'databases' Acoustic holds for us - the list of these is held in a setting within CiviCRM and hard-coded into civicrm.settings.php on production. 
Under the hood the following happens 
# When the forget me action is take on a contact a row is added to the civicrm_omnimail_job_progress table
#When the scheduled omnimail_recipient_process_forgetme job runs separate rows in the table are created for each Acoustic 'database' (these can be viewed through search kit - as in this search display or the API explorer
#A forget me request is sent for each database
# Next run it checks on the status of the forget me request (by querying Acoustic) - if the request has successfully been completed the row is removed
# A separate job checks the rows do not stall in the job_progress table and sends fail mail if they do.
The forget me request is technically the same as uploading an email in the UI to be subject to a GDPR erasure request as outlined in goacoustic's docs. Despite the terrifying message it will only deleted the email/s in the uploaded csv.
= Sandbox =
Note that as of August 10 2003 we are temporarily unable to access the account described in the text below - Brian Sisolak is helping us to sort this out. The temporary work around is to copy the live credentials (from civicrm.settings.php on prod) into our `AcousticCredentials.php` - but ensuring the database id is set to the sandbox one - 42168915.
Acoustic does not supply us with a sandbox system but we have set up a user account and data structure on the live database to work as a sandbox. 
The user account is connected to our main fr-tech email. Whenever someone logs in from a new IP an email is sent to fr-tech with a confirm code - if you trigger this please reply to say you initiated it & everyone else should keep an eye on them to make sure they are 'owned'. In addition any chance to change the password will wind up emailing fr-tech. 
Within Acoustic the user account can be restricted by folder - it was necessary to create folders like 'sandbox'. 'sandbox-lists' etc all over the place to limit the access. The end result is that only the sandbox folders and some metadata about old emails can be seen when logging in using the sandbox credentials. 
Sandbox database id : 42168915 
The sandbox credentials are automatically added to new docker installs and reside in /config-private/civicrm/AcousticCredentials.php. They are loaded from the file in /config/civicrm/settings.d in acoustic (which is loaded when civicrm.settings.php is loaded) 
To use the api you should access via the VPN - unless you have a static IP in which case it can be added in Acoustic
The same user name & password can be used in the UI at https://login.goacoustic.com/signin
You can confirm the credentials in use with the following drush command:
drush @wmff cvapi Setting.get return=omnimail_credentials
On production you will see the setting declared directly in civicrm.settings.php - looking something like
global $civicrm_setting;
$civicrm_setting['domain']['omnimail_credentials']['Silverpop'] = [
 'username' => 'my-email, 
 'password' => 'my-password',
 'database_id' => [42168915],
];
=== Acoustic security & accounts ===
Access to acoustic for ftp & api access is restricted by IP - ip addresses are inCollab and configured in Acoustic
=Running silverpop code locally=
==On docker builds==
This is what works for me.... https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/tools/+/673176
==Pre docker notes==
The following notes were writting prior to our docker shift. 
To test this locally, you'll need a settings file for silverpop_export. All of the python tools look for settings files in /etc/fundraising, overridden by settings in $HOME/.fundraising/.
So in one of those places, create a silverpop_export.yaml with contents like so:
logging:
 disable_existing_loggers: false
 version: 1
 formatters:
 app_prefixed:
 # TODO: You need to replace this with each app's name, until we
 # figure out something better.
 format: "silverpop_export: %(message)s\n"
 handlers:
 console:
 class: logging.StreamHandler
 stream: ext://sys.stdout
 error:
 # Defaults to sys.stderr
 class: logging.StreamHandler
 level: ERROR
 syslog:
 class: logging.handlers.SysLogHandler
 level: DEBUG
 # The app prefix is required to trigger patterns on the other end
 # of rsyslogd.
 formatter: app_prefixed
 # TODO: Custom rsyslogd configurations will require `address` and
 # `socktype` keys here, for example:
 address:
 - localhost
 - 514
 # Magic for socket.SOCK_STREAM, aka. the TCP protocol.
 socktype: 1
 # Note that overriding the root logger is rude.
 root:
 # Pass through maximum logging, and let syslog sort it out.
 level: DEBUG
 handlers:
 - syslog
 - console
 - error
# Directory which will stage the working files
working_path: /tmp/
# For archival purposes, how many days of old runs should we keep? 0 means forever.
days_to_keep_files: 1
# Login credentials for the silverpop transfer server
sftp:
 host: 123.123.123.123
 username: foo
 password: "abc123"
 host_key:
 remote_root: /upload/
log_civicrm_db:
 db: civicrm
civicrm_db:
 db: civicrm
drupal_db:
 db: drupal
silverpop_db:
 host: localhost
 user: "silverpopuser"
 passwd: "pass1234"
 db: silverpop
 debug: true
 charset: "utf8"
Depending on your rsyslogd configuration, you may need to change the handlers/syslog/address key.
The silverpop user should have ALL rights in the silverpop db, and SELECT rights to the other dbs.
cd into your tools folder and test the export like so:
PYTHONPATH=`pwd` python3 silverpop_export/update.py
For most updates, you will be making changes in the silverpop_export/update_table.sql file. Make sure to coordinate with Caitlin Cogdill before deploying anything that will change the output format.
= Troubleshooting =
When some part of the SQL script fails with a duplicate primary key, it's usually because CiviCRM has a duplicate employer relationship for one or more contacts. This is a constraint enforced in code, not in the db, so it sometimes fails.
To find duplicate employer IDs: 
CREATE TEMPORARY TABLE duperelationships AS
SELECT max(r.id) AS max_relationship_id,
 min(r.id) AS min_relationship_id,
 contact_id_b AS employer_id,
 max(m.entity_id) AS max_relationship_id_with_metadata,
 contact_id_a AS contact_id
FROM civicrm_relationship r
 INNER JOIN civicrm_contact c ON c.id = contact_id_a AND c.employer_id=r.contact_id_b
 LEFT JOIN civicrm_value_relationship_metadata m ON m.entity_id = r.id
WHERE relationship_type_id = 4
 AND is_active = 1
GROUP BY contact_id_a
HAVING count(*) > 1;
Then to fix them:
DELETE r FROM duperelationships d
 INNER JOIN civicrm_relationship r ON r.id = d.min_relationship_id AND d.max_relationship_id_with_metadata <> r.id;
DELETE r FROM duperelationships d
 INNER JOIN civicrm_relationship r ON r.id = d.max_relationship_id AND d.max_relationship_id_with_metadata <> r.id;