Accounting
Learn how to run common reconciliation reports between OnTrack transactions and Stripe bank payouts
With C360 BI we can leverage the question builder to easily fetch records to compare against payouts to your bank account via Stripe.
When someone makes a transaction via OnTrack, an Income record gets created. Each Income record represents a single transaction.
Income records have several fields to keep track of various transaction details. For reconciliation, we'll focus on the Batch Number field.
Take a look at the Income table in BI to understand more about the data structure
Batch Numbers
When OnTrack processes an online transaction, it sends that information over to Stripe, processes the transaction, and the user continues on their journey. As time passes, and more transactions are processed, Stripe will collect these individual transactions and make a single deposit into your bank.
When this happens, OnTrack is automatically notified. We create a unique batch number and assign that to the income records effected.
Not all Stripe Payout schedules are the same. Please refer to your Stripe Payout settings for more information
Visualizing Batch Numbers
Using some basics in C360 BI, we can craft a question to help us visualize recent batch numbers including transaction counts and total amount.
In OnTrack, head over to reporting and create a new question. You can choose a simple or custom question. Here we'll use custom, but the end result is the same
Lets start with the Income table and add two summaries
Count Of Rows
Sum Of
Total Amount
We then add a group by on the
Batch Numberfield
In the question editor, that would look like this
To make our life easier, let's sort descending by the batch number field
With that in place, we can now easily view the total gross amount and transaction count for every corresponding payout reported by Stripe!
Later we'll dive into these batches and view the individual transactions for a line item report.
Pro tip: save this question in a new accounting folder for later
Reviewing Payouts
In Stripe, we can view payout information in a couple of different ways. For our example, we're going to use the Payouts table. It's a simple view where we can easily correspond batch numbers from our previous report.
Being this such a common task, Stripe also has several reports based on reconciliation that may work better for your organization if you require more detail. If your organization utilizes Stripe for payments outside of OnTrack, you may require a line item report instead.
The links above assume you are logged into your Stripe account and have the appropriate permissions to view Payouts and Reports
When viewing Payouts, click on any row to view the details. Here we can see the total gross amount, fees, and deposit amount for the payout. We can also confirm the batch number, which correlates to the batch number in our previous report.
Note the Metadata section, CompassBatchNumber field.
Transaction Detail Reports
In some scenarios you'll need to dive into the individual transactions for a specific payout. In that case, when viewing a Payout in Stripe, the individual transactions are available to export.
Now with that payout batch number, head back over to OnTrack and create a simple question that filters Income by that batch number!
Viewing All Line Items By Batch Number
In some cases, we need to categorize individual line items to assign an accounting code, for example restricted funds. In order to achieve this, we need to build a question that can:
Filter Income by a batch number (see above)
Join in the individual Invoices for those Income
Join in the individual Line Items for those Invoices
Start a new custom question, select the Income table, and add a join to the Invoice table. The right hand side of the join should automatically populate for you!
Add a second join to Line Items, where the Invoice -> Invoice ID = Line Item -> Invoice ID. You'll have to do this one manually.
Now from our previous question, add a simple filter to find Income by batch number.
With this report, we've easily found Line Items tied to a specific Payout from Stripe. We can now view these in detail, export or even create an accounting dashboard!
Pro Tip: this question returns a lot of fields. In the join data sections, use the dropdown on the table to select only the fields you need!
This question will return a single row for each Line Item, these do not represent a count of transactions
More Information
If you need help with some custom reports, or have a workflow that requires more detailed information on the transaction line items, reach out to support and we can point you in the right direction!
Last updated
