Decipher the CSV file of Reconciled Data Updates

If you received a data reconciliation email from us, you were pointed to a CSV file containing details about your account's reconciled data. This article will help you decipher the CSV and understand the ways in which we are updating data about donations made to your nonprofit.

Examples

No items found.

Before You Get Started

You are reading this article because you received an email from us about data reconciliation. If that's not the case, you've found your way to information that is probably not relevant to your search. Please try again.

If you have not already done so, please download your CSV file through the email we sent you. Keep in mind that your default browser may not automatically download the CSV file when you click on the "Download My Reconciliation Report" button in the email.

  • Chrome will automatically download the file.
  • In Safari, if you click the button and the CSV opens in a browser window, then click on "File" in your top menu and select "Save As...". Choose where you would like the file to be saved, opt for the "Page Source" as the format and then click "Save" to download. You can work around this by right-clicking (or control-clicking) on the button in the email and then choosing to "Download linked file".
  • Firefox will offer you a choice about what to do with the file (if you have not already set this). To download, select "Save File" and then click "OK".

Video Overview

Text Walkthrough

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.

Downloads

No items found.

Related Articles

No items found.