You are browsing a read-only backup copy of Wikitech. The live site can be found at wikitech.wikimedia.org

Difference between revisions of "Fundraising/Data and Integrated Processes/Acoustic Integration"

From Wikitech-static
Jump to navigation Jump to search
imported>Eileen
(Created page with "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).")
 
imported>Eileen
(→‎Matching Gifts: match to export)
Line 1: Line 1:
We export data to acoustic once a day from our CiviCRM database. We also import data back in on a more regular schedule.
We export data to acoustic once a day from our CiviCRM database. We also import data back in on a more frequent 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).
(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) [[Fundraising/Data_and_Integrated_Processes/Acoustic_Integration#Email sending - Acoustic interaction|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 [[phab:T293945|this phab]].
 
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.
 
 
. 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#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.
 
<br />
 
==Communication Fields==
 
===On Contact Record===
{| class="wikitable"
!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<br />
|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==
{| class="wikitable"
!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==
{| class="wikitable"
!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
|
|
|}
<br />
 
==Matching Gifts==
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
{| class="wikitable"
|+
!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
|
|-
|minimum_gift_matched_usd
|minimum_gift_matched_usd
|Minimum (USD) gift matched
|
|-
|match_policy_last_updated
|match_policy_last_updated
|Matching gifts info last updated date?
|
|}
Other matching gift fields
* Matching gifts provider ID
*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:
{| class="wikitable"
|+
!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&amp;nbsp;15&amp;nbsp;gorgeous&amp;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 WCA
 
This table is provided by the extension [https://civicrm.org/extensions/extended-mailing-stats 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.
{| class="wikitable"
|+
!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 ===
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
 
<br />
 
===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
{| class="wikitable"
|+
!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
{| 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
|
|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
|
|}
 
= 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:
<pre>
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"
</pre>
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:
<pre>
PYTHONPATH=`pwd` python3 silverpop_export/update.py
</pre>
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.

Revision as of 23:45, 25 October 2021

We export data to acoustic once a day from our CiviCRM database. We also import data back in on a more frequent 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.

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.


. 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#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

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


Matching Gifts

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

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
minimum_gift_matched_usd minimum_gift_matched_usd Minimum (USD) gift matched
match_policy_last_updated match_policy_last_updated Matching gifts info last updated date?

Other matching gift fields

  • Matching gifts provider ID
  • 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:

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

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

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
Clickstream
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

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.