What are best practices for reconciling bonterra payments?

Thank you for following up. There are many fundraising platforms out there who make it easy, but EveryAction/Bonterra doesn’t which is disappointing because it’s already quite costly and we’re having to spend more money in the form of staff hours working on arduous workarounds. 

I did speak with Lily Ickow, on their product team, several months ago and went through the process step-by-step to discuss what is difficult and what could be made easier, but no word since then and it’s not certain where this sits on their list of priorities. 

This continues to be a pain point so I wanted to share back a few (again, minor) improvements to the workaround that we’ve been exploring at CLC. ​@patricko , ​@Rumi Matsuyama  or anyone else in this thread, I’d be curious if you have thoughts on these or are already doing them!

As Rumi pointed out, “ Note that each Daily Payout Report with detailed breakdown can only be downloaded ONE AT A TIME by querying the payout date, clicking a link and downloading it.”

• the payouts are daily by default, but this can be changed to weekly or monthly - provided your organization’s operations can sustain that. In theory, this could help reduce the number of payout reports you’re needing to individually export.

Combining reports

• Use Xlookup to combine exports instead of Vlookup. This allows, with one formula, to pull over all the columns in your payout export spreadsheet into your contribution report export based on Online Reference Number. So in one formula you get payout ID, fees, NET into your contribution report export. Open to any other excel / google sheets hacks if there’s an even better way here!

After reconciling, upload payout ID, Fees, Net back to EA to custom contribution fields.

• One-time: Create custom contribution fields (you’ll need 1 x text, 2 x currency)
  • Suggestion: name the custom contribution clearly as a bulk applied field rather than a source of truth. Team members should still always reference the payout report for the most accurate information, and consider this field with some room for human error.
• Each time: Upload by Contribution ID.
  • Map each of the three custom contribution fields to your spreadsheet columns OR save as a Mapping Template for ease of use each time
• Though this does require an additional upload step, it opens up the possibility of now using your contributions report in EA to
  • filter for any contributions without payout ID,
  • filter for a specific payout id,
  • analyze fees compared to how many contributions opted to covered fees, etc… 
  • In short, once you’ve done the work to match, you’re making it usable in EA reporting rather than lost in spreadsheets

Echoing much of what has been said here. Our accountant was completely opposed to the extra work required in Bonterra Payments. Thankfully when we migrated, Paragon was still an option. While not perfect, that has been a much better arrangement for our organization and it’s worth looking into that option while Bonterra Payments sorts out the product feedback

​@veronica thank you for sharing this process about custom contribution fields. Until Bonterra can create those fields in the contribution record itself and make them available in contribution reports and reconciliation reports, this is a great workaround. 

As a business process note, I am recommending that if any refunds are processed, the staff member must remember to manually edit those custom contribution fields back to zero/blank. Does that square with your recommended process? I’m trying to think of other situations that would also require manually editing those fields. 

​@Sally Heaven  good question/point about the refunds! Yes, what you shared makes sense to me in theory. I haven’t pressure tested this to that extent yet! That said, I think needing to do that retroactive update for refunds would depend in part on how often refunds are processed after reconciliation happens. If it’s a frequent or high volume scenario, I might dig a bit deeper into the process as a whole to assess if the custom contribution fields in this way are still the right fit. If it’s 1-2 every few months, I would consider the user doing the process - if they will reliably remember this step! 

Either way, it would be important to make clear that the BP reports remain the system of record, and the custom fields are for ease of reference in the processing. 

​@veronica yeah, I think the Custom Contribution Fields solution is excellent to make sure the fundraising reporting is more accurate (net amount, since the Donation Amount in the Contribution Report doesn’t expose or account for the fees) and to make researching mismatches in the bank account easier, but since it can’t be relied upon for bank account reconciliation, it won’t be useful for finance teams. However, if fundraising teams would find this more useful, then I think adding the steps to blank out fees/net amount would be worthwhile since processing a refund/adjustment requires manual steps anyway in EA.