Donation Processing And Data Capture

In this section, we describe in detail how data capture works and how to interpret the data for donations.

One-time Donations

A donation entry is prompted. Either a user submits on a donation page or a donation is created through the API or a donation is imported through the uploader or the API. The following entries are recorded:

Core_action

A row is added that includes:

  • user_id,
  • page_id,
  • source: source of this action,
  • mailing_id: if the source is ‘mailing’ the id of the mailing,
  • timestamp, and
  • status:
    • complete means the action was successfully submitted; this does not mean a donation was successfully processed.
    • failed means something went wrong even before the donation was submitted to the merchant vendor. For example, if the credit card entered doesn’t have enough numbers you might see this status. If the user then corrects the problem and the action is submitted successfully, a new row is added with status=’complete’.

Core_order

A row is added that joins to core_action on core_order.action_id=core_action.id and that includes:

  • total,
  • card_num_last_four: the last four digits of the card number, or the bank account for ACH Direct Debit donations, blank if the donation was imported and you didn’t include this field,
  • user_detail_id: joins to core_order_user_detail where you can find the billing information submitted by the user,
  • import_id: unique identifier used to stop accidental re-import of a donation; has a value only if the donation was imported and you included this column. Duplicates are prevented within a particular payment account.
  • shipping_address_id: if a shippable product was ordered (joins to core_order_shipping_address),
  • status:
    • completed
    • failed: If the action failed, the order status is also ‘failed’. This will also be the status for ACH Direct Debit orders that failed settlement.
    • reversed: the donation was refunded
    • pending: the default state that orders and recurring orders are created in, and should only be valid for credit card orders while ActionKit is processing the donation. ACH Direct Debit orders will be in this state until they settle.
  • payment_method: This will be one of cc, paypal, or ach depending on which was used for the order.

Core_order_user_detail

A row is also added to core_order_user_detail that joins to core_order on core_order_user_detail.id=core_order.user_detail_id and includes the user’s billing information. Billing address will also be used to update the address information stored in core_user.

Core_transaction

A row is also added to core_transaction joining to core_order on core_order.id=core_transaction.order_id and including:

  • type: always sale for one-time donations,

  • account: the name of the merchant vendor account through which the transaction was processed, or import if the donation was imported,

  • amount: which is the same as the total under core_order,

  • status: Completed, failed, pending, or reversed. Always the same as the order status for one-time donations,

  • success: 1 indicates success. If the status is completed or reversed, success is 1. If the status is failed, success is 0,

  • failure fields: several fields providing information from the vendor about why a transaction failed; what’s displayed here depends on the vendor,

  • trans_id: the identifier for this transaction in the merchant vendor’s system.

    Note

    Unlike credit card payments, ACH Direct Debit payments can take up to three days to settle. They will be in a pending state until then. Braintree will send a notification through a webhook when the payment settles and the status of the order and transaction will both be updated in response. You can read more about the process in Braintree's documentation here.

Products And Candidates

Additional data is captured if the order includes a product order or candidate contribution.

Core_order_detail

A row is added to core_order_detail that joins to core_order on core_order_detail.order_id=core_order.id and includes:

  • product_id or candidate_id: if a product was ordered (joins to core_product), and/or if there was a candidate contribution (joins to core_candidate),
  • quantity: the quantity of each product ordered,
  • amount: the total for products or candidates only (excludes any additional donation the user made).

IF there is a product order, and the product is shippable a row is added to core_order_shipping_address. As noted above, this table joins to core_order on core_order.shipping_address_id= core_order_shipping_address.id.

Recurring Donations: Processing

The same information is recorded in the core_action and core_order tables as outlined above. However, recurring donations are more complicated because there are multiple steps and different merchant vendors work slightly differently.

Here's how the processing works for each vendor:

  • Auth.net - If you have set up a Silent Post URL to point to ActionKit then a row is added to core_authnettransactionlog when each payment is processed. Every day we process new entries in this table and turn them into core_transaction rows. The silent post does not provide information about past payments so Auth.net recurring profiles created outside of ActionKit and then imported will not include past payment data.
  • PayPal - Every night we query PayPal for the status of all active recurring profiles. Any payments found will be written to core_transaction. We also collect notices of canceled profiles and change the status on the core_recurringorder row to canceled_by_processor for any that are found.
  • Braintree Credit Card - Our processing is the same as for PayPal. You can read more about how Braintree treats recurring donations here: https://developers.braintreepayments.com/guides/recurring-billing/overview . Recurring donations that become past-due can be configured to automatically retry in either Braintree gateway settings or ActionKit Recurring Retry.
  • Braintree ACH Direct Debit - Because Braintree doesn't support recurring billing for ACH Direct Debit, this is handled entirely on the ActionKit side. A daily job checks for recurring donations that should be charged each day and submits the new charges to Braintree using payment infomation saved in the Braintree vault. The transactions will be in a pending state until we receive the settlement notification from Braintree. If an ACH Direct Debit payment fails, the recurring profile is canceled.

Data is captured in several places for recurring donations. See below for details based on whether the donation fails or succeeds during initial set up or when additional payments are made.

User Signs Up For Recurring Donation

Database capture when user signs up for recurring donation:

  • Action is successful:

    • row added to core_order: status=completed (pending for ACH Direct Debit orders).

    • row added to core_orderrecurring: status=active.

    • row added to core_transaction: type="recurring_order_create" status="completed", success=1, amount=$0.

      (Exception: if the recurring profile was imported instead of created through ActionKit, the row above is not recorded).

    • 2nd row added to core_transaction: type="sale", status="completed", success=1,  amount=first payment amount (Exception: By default, the user is charged for their first payment when they take a recurring donation action and the row above is recorded. If you have modified your templateset to delay the initial payment, the row above will not be recorded until the first payment is made. Status will be pending for ACH Direct Debit orders.)

  • Action fails:

    • If it's a basic validation failure (missing required fields, dupe donation, etc.), it happens in ActionKit before anything is submitted to the vendor. No donation data is recorded.

    • If it's a failure at the merchant vendor (bad credit card, wrong expiration date or address, etc.), the data capture varies by merchant vendor:

      Braintree

      • row added to core_order: status='pending'; COMING SOON: we'll change status to failed instead of pending.
      • row added to core_orderrecurring: status='failed'.
      • row added to core_transaction: type='recurring order create', status=completed, success=0, failure_description provides some detail on why it failed (cc is expired, etc.)

      PayPal/Auth.net

      • row added to core_order: status='failed'.
      • row added to core_transaction: type='sale', status='failed', success=0.
      • row added to core_orderrecurring: status='failed'.

Merchant Vendor Attempts Next Payment (a Month From The Initial Payment)

Database capture when merchant vendor attempts next payment (or when ActionKit attempts it for ACH Direct Debit orders):

  • Donation succeeds:

    • row added to core_transaction with the same order_id as the initial order status=completed, type='sale, success=1 (status=pending for ACH Direct Debit orders until they settle)

  • Donation failed (same for all vendors):

    • row added to core_transaction with the same order_id as the initial order status= completed, type='sale', success=0

      The failure code is provided by the vendor: Braintree: http://www.braintreepayments.com/docs/python/reference/processor_responses

      Note

      Vendors will retry credit card charges multiple times. The timing varies by vendor. ACH Direct Debit charges will not be retried by vendors or ActionKit.

      Braintree tries on day 1, day 11, day 21 and then starts again on the bill date the following month. You can also do a manual retry through the Braintree admin or API (not through ActionKit).

      PayPal/Auth.net do either every day for 3 days or once a month on the bill date. Contact your vendor for more information.

Donor Changes Recurring Amount

If the donor changes the amount of their monthly payment after they set up the profile, the current commitment amount is shown in the core_order_recurring.amount field. The total in the core_order table shows the initial commimtent and does not change.

Of course the core_transaction table is the place to look to see what the donor has actually paid each month.

Recurring Donations: Queries

You'll find summary information about each recurring commitment in the core_orderrecurring table, including:

  • created_at: The date the profile was added to ActionKit (or the date provided in an import); also the billing start date unless you have customized your template to delay the initial payment.
  • action_id: Joins to core_action.id; source of the sign up is recorded in core_action.
  • exp_date & card_num: Info on the card used; we pull in updates from Braintree when they are provided (not other vendors)
  • start: This is not the date of the first payment for this profile, it's the date of the first automatically processed payment. Query core_transaction to find the first payment date.
  • period: Indicates the frequency of billing. Shows "months" for monthly donations, but may also contain "weeks", "quarters", or "years".
  • recurring_id: The profile_id assigned by your merchant vendor. For ACH Direct Debit orders, this is an internal ID assigned by ActionKit.

Payments toward a recurring commitment are not found in the core_orderrecurring table, but in core_transaction. The order_id in core_transaction will match the order_id in core_orderrecurring for each associated payment attempt. Successful payment attempts have core_transaction.success=1, core_transaction.status='completed' and core_transaction.type='sale'.

Imported Donations

You can import donations using the bulk uploader or the API. Imported donations are just like other donations except that they aren't processed through your merchant vendor so the transaction information is just a placeholder and cannot be reversed.

To view imported donations, find donations where the action was taken on a page of the type import. Another way of identifying these records is to check if the account is equal to import in the core_transaction table.

One-time donations, product orders, and candidate contributions can all be imported. You can also add recurring profiles to ActionKit through the uploader (although you cannot import individual payments toward a recurring profile). Read more about importing donations.

Queries

The SQL used to generate the donation summary box on the individual user record is a good reference to use when writing your own donation queries. Remember, To limit to successful donations only you must limit to those where the transaction status is completed and the type is sale OR the page type the donation came through was import.

Note

There are a handful of older donations that don’t fit this schema, usually because you helped us identify a corner case and we changed something to account for it for future contributions. If you see a recent donation that doesn’t match this outline, please report it to us through the support tab.