Fundraising Auditing

Periodically, it's important to audit the contribution records in CiviCRM against the various payment processors that we use. This is necessary for data sanity as well as the obvious general good practice for financial records and for accurate data reporting for various groups in and externally to the Foundation (eg Finance, Global Development, chapters, etc.)

Because we rely on three on different payment processors, there are multiple methods of auditing. All, however, are handled as Drupal modules.

The modules

contribution_audit

The contribution_audit module is a framework for auditing CiviCRM contribution records against the various payment processors. It is fairly immature. It currently only supports plugin reporting aggregate dollar amount totals broken out by country for missing contribution information. The long term vision, however, is for this module to provide a mechanism for inserting missing contribution information into CiviCRM.

Drush script

The module provides a Drush script for 'allocating unallocated transactions' - more clearly, for determining the aggregate dollar amount totals broken out by country for missing contribution information. It provides a hook for other payment processor-specific modules to locate missing transactions, which contribution_audit then aggregates and breaks out by country. It returns a CSV in the format:

"two letter country code","total missing","number of transactions"

A quirk worthy of mention in this script is that when given a date range greater than a week long, it will chunk the auditing attempts into week-long chunks. The chunking is not currently configurable, but exists as a precaution to prevent out of memory errors.

To run the script, either be in the Drupal instance's docroot or be sure to use the "-r" parameter to specify its location:

$ drush allocate-unallocated-contribs --start="a date parsable by strtotime()" --end="a date parsable by strtotime()"

Or, you can use the abbreviation:

$ drush auc --start="a date parsable by strtotime()" --end="a date parsable by strtotime()"

log_audit

The log_audit module searches through IPN logs to find missing transactions. You can tell log_audit where to find the IPN logs in its administrative settings page in CiviCRM. At the moment, it is configured to look in /tmp/ipn_logs on the host where this is running (currently Aluminium). There is currently no mechanism to get the IPN logs to Aluminium. I typically empty /tmp/ipn_logs then copy over the pertinent IPN log files from storage3 before running the auditing scripts.

(Payflow pro) paypal_audit

The paypal_audit module makes use of PayPal's API for checking transactions. This is primarily used to determine which PayflowPro and PayPal transactions are missing. From PayPal's API, we're able to get some transactional information about PayflowPro transactions, but NOT PayPal transactions. So transaction IDs are collected and fed to log_audit for PayPal transactions. This module relies on a suite of scripts for communicating with the PayPal API which can be found in svn. This modules needs to be configured to know where to find those scripts, which can be done through its administrative interface in CiviCRM.

globalcollect_audit

GlobalCollect auditing is handled rather differently and does not tie into log_audit. It relies on wr1 files (provided by GlobalCollect), which contain transactional information for /every/ Global Collect transaction made over a certain period of time. The module relies on the WR1 parsing scripts. It actually recreates missing transactions and plays them back into CiviCRM.

Performing the audit

PayflowPro and PayPal

Simply run the drush command:

$ drush allocate-unallocated-contribs --start="a date parsable by strtotime()" --end="a date parsable by strtotime()"

Or, you can use the abbreviation:

$ drush auc --start="a date parsable by strtotime()" --end="a date parsable by strtotime()"

Global Development reporting

For the Global Development department, the fundraising team must report financial information about how much money was raised broken out by countries on a monthly basis. Historically, the engineering team has only been responsible for ensuring data accuracy and the reports themselves were generated and handed off to Global Development by the fundraising analytics team.