Fundraising tech/tools

Silverpop ExportEdit

Exports a de-duplicated list of donor data for sending bulk emails. The name reflects our current provider, but the logic is generic.

We export two files - one with donor data (email, name, language, geographical region, and contribution statistics), and one with email addresses to unsubscribe.

We group contacts by email address and use the donor information associated with the latest donation for each email. We aggregate the number of donations, the total amount given, the latest donation, and the largest donation in USD and original currency.

We unsubscribe an email address if any of the contacts associated with it are marked 'opt out', 'do not email', or 'do not solicit', or if the email has been marked 'on hold' (as is done by CiviCRM's bounce detection). We also unsubscribe any email addresses which are only associated with deleted contacts, or only exist as non-primary addresses. Work is in progress to use the CiviCRM change log to unsubscribe old emails when addresses are edited.

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:

    disable_existing_loggers: false
    version: 1

            # TODO: You need to replace this with each app's name, until we
            # figure out something better.
            format: "silverpop_export: %(message)s\n"

            class: logging.StreamHandler
            stream: ext://sys.stdout

            # Defaults to sys.stderr
            class: logging.StreamHandler
            level: ERROR

            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:
                - localhost
                - 514
            # Magic for socket.SOCK_STREAM, aka. the TCP protocol.
            socktype: 1

    # Note that overriding the root logger is rude.
        # Pass through maximum logging, and let syslog sort it out.
        level: DEBUG
            - 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
    username: foo
    password: "abc123"

    remote_root: /upload/

    db: civicrm

    db: civicrm

    db: drupal

    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` python2.7 silverpop_export/

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.

Landing page impression counterEdit

Tallies hits to landing pages at and Runs as a Django management task for some reason. [Operational details] are documented on wikitech.

It filters server logs and only considers a request to be a landing page hit if it matches certain patterns. In the table below, patterns are listed as prefixes of any wiki page title. For example, the Support_Wikipedia pattern would include

Wiki Included patterns Ignored patterns
  • L11
  • L12
  • L2011
  • L2012
  • WMF
  • Donate
  • Support_Wikipedia
  • Test_120511
  • Special:Landingcheck
  • Special:ContributionTracking
  • Special:RecentChanges
  • MediaWiki:
  • File:
  • Talk:
  • Terms_of_Use
  • New_Terms_of_use
  • New%20Terms%20of%20use
  • Feedback_privacy_statement
  • Home
  • Main_Page
  • Donate/Benefactor
  • Donate/Stories
  • Donate/Thank_You
  • Donate/Transparency
  • SOPA/Blackoutpage
  • L11
  • L12
  • L2011
  • L2012
  • WMF
  • WP
  • Special:FundraiserLandingPage (see note)

Special:FundraiserLandingPage on donatewiki is treated as a special case. For this special page, the 'landingpage' value is a tilde-separated list of these querystring parameters: "template", "appeal-template", "appeal", "form-template", and "form-countryspecific".

All landing page hits are added to the landingpageimpressions_raw table, including timestamp, utm_source, utm_campaign, utm_key, utm_medium, landingpage, project_id, language_id, and country_id.

Email click countsEdit

Hits to donatewiki landing pages that include a contact_id parameter are also added to the donatewiki_unique table with timestamp, utm_source, utm_campaign, contact_id, and link_id. Each combination of utm_source and contact_id can only occur once in this table, so it should be an accurate count of the number of unique donors clicking some link in each email. These are aggregated in view donatewiki_counts by utm_source, utm_campaign, and link_id.