Key Data

For the purpose of the data reconciliation, the new data you should review is in the final two columns at the far right end of the table in the CSV file. The columns are labeled:

• change_type — revealing whether the donation in question is being updated with new data or is a new transaction not previously included in our database

• updates — carrying a summary of changes to the data associated with each transaction; this is only relevant for updated data associated with existing charges, not new charges.

‘change_type’ column

There are only two options for what will appear in this column:

• Charge created — this identifies a new transaction (not previously in our database)
• Charge updated — this identifies an existing transaction that saw a change in data as a result of the reconciliation

‘updates’ column

In this column, set within { } (curly brackets), you will find coded language explaining the data updates discovered through the reconciliation. Numerous updates may be involved, including any of the following “fields":

• amount - the amount of the donation
• amount_refunded - the portion of the donation refunded to the donor
• campaign_id - a number identifying the fundraising campaign to which the donation was made
• customer_id - a number identifying the donor who made the donation
• event_id - a number identifying the event to which the donation was made
• payment_method - the method used to make the donation
• paid - a notation about whether the donation payment was successful
• refunded_at - the date and time of the refund
• status - a notation about where in the payment-disbursement process the donation is
• transaction_fee - the amount of the transaction fee (charged by Stripe) that the donor has agreed to cover, as an extra amount added to the donation  
• subscription_id - a number identifying a recurring donation

Each updated field, listed one after the other within the curly brackets, follows the same format:

"(field name)"=>[old value, new value]

For example, in the case of updated information about the payment_method the update might be:

“payment_method"=>[nil, “card"]

This signifies that the reconciled data about the method used to pay for a donation was updated from “nil” (no recorded data) to “card” (a payment by credit card).

 Generally speaking, nonprofits will be most interested in understanding updates to the ‘amount,’ ‘amount_refunded,’ ‘paid,' ‘refunded_at,' ‘status' and perhaps ‘transaction_fee’ fields.  

Here is a full explanation of all the different values associated with each possible field: 

• amount - The values will always be numeric, but shown in cents, not dollars. For example, "amount"=>[3500, 3600] means that the total donation amount has been changed from 3500 cents (or $35.00) to 3600 cents (or $36.00).
• amount_refunded - The values will always be numeric, but shown in cents, not dollars. (See the example shown in “amount” above.)
• campaign_id - The values will always be a string of characters (digits, letters and hyphens) in UUID format useful only in identifying a campaign in the Give Lively database
• customer_id - The values will always be a string of characters (digits, letters and hyphens) in UUID format useful only in identifying a specific donor in the Give Lively database. 
• event_id - The values will always be a string of characters (digits, letters and hyphens) in UUID format useful only in identifying a specific event in the Give Lively database. 
• payment_method - The values identifying the type of identified payment method include:
  + nil (none identified)
  + “android_pay” (Android Pay)
  + “apple_pay” (Apple Pay)
  + “bank” (ACH Credit Transfer)
  + “card” (credit card)
  + “google_pay” (Google Pay).‍
• paid - There are only two values here:
  + true (the payment was successful)
  + false (the payment was not successful).‍
• refunded_at - The values here are timestamps showing the date and time of the refund.‍
• status - The values identifying the status of a donation are largely self-explanatory and include: 
  + “failed” (the payment could not be completed)
  + “pending” (the payment is still passing through the system)
  + "refunded” (the payment was returned to the donor)
  + “succeeded” (the payment was completed). 
  The following are also possible as the “old value”: 
  + “confirmed” (donor clicked ‘Donate' button but Stripe has not sent notice that charge went through)
  + “initialized” (donor activated payment form but didn't yet click ‘Donate' button)
  + “pending_first_sub_charge” (pending charge that is first installment of a recurring donation). ‍
• transaction_fee - The values will always be numeric, but shown in cents, not dollars. (See the example shown in “amount” above.)‍
• subscription_id - The values will always be a string of characters (digits, letters and hyphens) in UUID format useful only in identifying a specific recurring donation in the Give Lively database.

As another example, what does the following mean?

{“amount”=>[3500, 3600], "payment_method"=>[nil, "card"], "paid"=>[false, true], "status"=>["failed", "succeeded”]}

It means that in our database:
* the amount of the donation has changed from $35 to $36
* the payment method has changed from none having been identified to credit card
* the donation payment was changed from false (unsuccessful) to true (successful)
* the status of the donation was changed from having failed to having succeeded.

Other columns

There is plenty of other useful information in the other columns in the CSV file. This is the information normally available in a donor data download from the Give Lively Nonprofit Admin Portal.

However, please keep in mind the following:

• When a change in data is shown in the ‘updates' column, only the updated (or 'new value') data is shown in any related column. This means the following: when “amount”=>[3500, 3600] appears as an update, $36.00 will be the amount shown in the ‘donation_amount' column. Similarly, for "status"=>["failed", "succeeded”], “succeeded” will appear in the ‘status' column.
  
  Notably, if “amount_refunded”=>[0, 1500], then $15.00 will appear in the ‘amount_refunded' column. Unless otherwise noted in the ‘updates’ column, there will be no change in the ‘donation_amount' column. This is because the original donation amount remains the same, even if part (or all of it) was refunded.

• The “field” names (those used in the ‘updates' column) won’t always correspond exactly to the columns in the CSV, although differences are usually easily sorted. To emphasize an earlier example, changes to the “amount" field in the ‘updates' column map to the ‘donation_amount' column in the CSV.
• Finally, the 'id' column (the very first column, in gray), is where you'll find our charge identifier, a string of characters (digits, letters and hyphens) in UUID format used to identify a specific transaction in the Give Lively database. If you find this field empty, then you're looking at a new transaction. (The 'charge_type' column will show "Charge created".) There's no ID yet because the new charge has not yet been registered; this will happen the moment we apply our reconciliation process improvements to our live database.