Actionkit donations have a few important properties that can vary independently:

  • Whether the donation was processed “directly” from an Actionkit form, or via an API call or bulk import
  • Whether it’s a one-time donation or a recurring donation
  • What payment processor was used (PayPal, BrainTree, or
  • Whether the payment succeeded or not

The actual amount of money transferred from a user to the organization is found in a different place in the database (and interpreted differently) depending on these properties.  So answering the question “How much money has User X given us?” is nontrivial.

(Note: even answering that question does not provide the full picture, because additional information is captured for product orders and candidate contributions. So “How much money has the organization raised from User X?” is an even harder question to answer.) 

Here’s some important details:

  1. If the order was processed directly from actionkit, there will be a core_transaction record with an order_id foreign key.  The order will have import_id = NULL.  The transaction will have a status, which must be “completed” for the payment to count.  The order will also have a status.  An order with status=”completed” might still refer to failed or cancelled transactions.  And a transaction with status=”completed” might be attached to a non-completed order!  So both order and transaction must have status=”completed” for the record to truly represent money successfully transferred.  The transaction’s amount field will be the money given.
    • If it’s a one-time donation, the core_order and core_transaction will be one-to-one.
    • If it’s a recurring donation, the core_order will have many core_transactions attached, one per payment period.  
      • There will be additional core_transaction records that represent non-payment events, including the setup of the recurring donation.
      • If the user set up a recurring donation and made the initial payment at the same time, then there will initially be at least two core_transactions for that core_order.
      • These additional non-payment events in core_transaction can be safely included in your tally, because they will have `amount=0`.
    • So, as long as core_transactions with transaction.status <> “completed” or order.status <> “completed” are excluded, it’s safe to just tally the `core_transaction`.`amount` field for all core_orders attached to the user in question.
  2. If the order was processed through an import action or API call, there will not be a core_transaction record.  The order will have a non-NULL import_id.  For these orders (if you wish to include them in the tally) should be tallied after exclusion of rows with core_order.status <> “completed”. 

I’m pretty sure that every core_order with an import_id will have no core_transactions attached; and every core_order with core_transactions attached will have no import_id.

So, to collect a user’s total donations, you can make two queries and sum the result:

  • One to `select amount from core_transaction join core_order where t.status = “completed” and o.status=”completed”`
  • One to `select total from core_order where import_id is not null and status = “completed”`   

This should include the total dollar value of all successfully completed transactions, including individual transactions within a recurring donation.  It will not include pledges of future recurring donations.  It will include past transactions that are not donations to your organization, like product orders and candidate contributions.

Filed January 11th, 2014 under actionkit