Finance asks a simple question: how much money actually came in, payment by payment, after refunds? You build a query on sales orders, export it, and the numbers don’t tie to the deposit. One order shows its full amount even though half of it was refunded last week. Another order was paid with two cards, and the query shows one line. The order total is accurate. It just isn’t the number finance wants.
The fix is mostly about where the query starts and which kind of “net” you’re after. Here’s how to think it through and build something that holds up at month end.
Sales orders and payments are different records
In Altru, a sales order describes what was sold: tickets, memberships, add-ons, donations, discounts, the order total. The payment is the money that settled that order, and it lives on the revenue side. Most of the time there is one payment per order and the two match. They drift apart in a few predictable cases:
- Split tender. One order paid with cash plus a card, or two cards, produces more than one payment.
- Refunds. A refund is recorded as its own transaction after the fact. The original order and payment don’t shrink; the refund sits alongside them.
- Exchanges and adjustments. Changes to an order after it closes can add or offset revenue without changing what a simple order query reports.
- Payments that cover more than one thing. A single payment can be applied across a membership, a donation, and tickets, which matters when finance wants net by fund or category.
A query built on sales orders answers “what did we sell?” To answer “what did we collect, net?” you generally need to start from revenue.
Decide which “net” you mean
Before building anything, pin down the definition with whoever asked. “Net” means different things to different people:
- Net of refunds. Payments received minus money returned. This is the most common request and the one Altru data supports most directly.
- Net of discounts. Usually already reflected in the amount paid, since the discount reduced what the guest owed. Check this before subtracting anything a second time.
- Net of processing fees. Card processor fees typically live in your merchant statements, not in Altru. Unless your organization records fees in Altru somehow, a query can’t produce this number on its own, and you’ll be matching against processor reports outside Altru.
Write the agreed definition at the top of the query description. Six months from now, someone will ask why the totals don’t match a different report, and that note will answer it.
Building the query
Start from revenue, not sales orders
Create a new ad-hoc query using a revenue-based source rather than a sales order source. That makes each row a payment (or refund) instead of an order. Field names and available sources vary with your Altru setup and security rights, so browse the source’s field list rather than expecting a specific name. Look for the transaction date, amount, payment method, transaction type, and the related constituent.
Bring in the order as context
If you still need the order number or sales channel on each row, add it from the related sales order fields within the revenue source. The row should still represent the payment. If adding a field suddenly multiplies your row count, you’ve likely joined to something one-to-many (like order line items), and amounts will double count when you total them.
Include refunds deliberately
Make sure your filters capture refund transactions, not just payments. Then confirm how they come through: some outputs show refunds as negative amounts, some show a positive amount with a type that says “refund.” Run a test on a date range where you know a refund happened and look at that row before trusting a total. If you can’t tell how refunds are represented, ask Blackbaud support or test in a sandbox before reporting on it.
Filter on the date that matters
Filter on the payment or transaction date, not the order date or the visit date. Advance sales are the classic trap: a ticket bought in March for a July visit belongs to March’s cash, and a refund issued in July belongs to July.
Do the subtraction where it’s reliable
Ad-hoc queries are good at returning rows and summing them, but they aren’t built for arbitrary math like “payment minus later refund on the same order.” If refunds already come through as negatives, a simple sum gives you net. If they don’t, export and compute net in a spreadsheet, or look at whether an existing smart field or report in your database already calculates what you need. Don’t assume a calculated net field exists; check the field list.
Reconcile before anyone relies on it
Pick one closed month and compare the query’s net total against your batch and deposit totals for the same period. If it ties, save the query and lock down who can edit it. If it doesn’t, the difference usually points at the cause:
- Too high: refunds are missing, or a join is duplicating rows.
- Too low: a payment method or channel (online sales, group sales) is filtered out.
- Off by a timing amount: you’re filtering on order date instead of payment date.
Once it ties for one month, check a second month with a known refund or split payment in it. That’s where these queries usually break.
Getting the numbers out of Altru
If the net calculation ends up living in a spreadsheet, the weak point becomes the manual export every week or month. Data Bridge syncs Altru query results to Google Sheets, so a revenue query like the one above can feed a sheet where the refund math and any fee matching happen automatically, with Altru remaining the source of the underlying data.
Quick answers
Why does my sales order query show the full amount after a refund?
Because the refund is a separate transaction. The original order isn’t reduced, so you need a query that includes refund transactions to see net.
Can a query show net of credit card fees?
Generally not from Altru alone, since processor fees are usually recorded by your payment processor rather than in Altru. Match against processor statements for that figure.
