---
id: saas/conciliacao-faturamento
industry: SaaS and technology
usage: widely used
effort: high
grain: Invoice (billing document); receivable by transaction x installment; reconciliation by month
---

# Billing reconciliation

Billing, gateway, ERP and cash in a single table, with what was sold but not received and what was received without an invoice surfacing on their own.

## Executive summary

**For:** Finance and controllership at SaaS companies that charge through more than one gateway or billing account.

**What it solves**

- A single invoice table, with no invoice counted twice across accounts, gateways and retries.
- Billings, cash and delinquency reconcile with each other every month, to the cent.
- Sold but not received, received without an invoice and mismatched amounts become work lists, not end-of-quarter discoveries.

**Why it matters:** When revenue lives across several billing accounts, gateways and an ERP, each team closes the month with a different number and nobody knows which one is right. Reconciliation is what gives the board, accounting and the MRR calculation confidence, and it is where sales without a tax invoice and payments that never arrived show up.

**Business questions it answers**

- How much did we bill, how much reached cash and how much stayed open last month?
- Which customers bought and the money never arrived?
- Which approved sales have no tax invoice issued?
- How much will we receive from acquirers in the next 30 days, net of fees?
- How much do we pay in gateway fees per month and per payment method?
- Which sales were refunded but still have an active tax invoice?

**What you need**

- Billing and gateways connected to Nekt, every account and company
- The ERP or tax invoice issuer, with the billing invoice id stored in some field
- The company's rule: what counts as recurring revenue and on which date each number is counted

**What you get**

- An invoice table with source, a single status and all three dates
- A monthly billings x cash x open reconciliation that ties out
- A receivables base by acquirer with settled and to-be-received amounts
- Exception tables for finance to review every week

## Detailed

### The problem

Revenue is spread across several billing accounts, two or three gateways and an ERP. The same charge appears on several rows: one per attempt, one per account, one in the payments table and another in the balance transactions table. Whoever sums everything inflates billings; whoever matches by amount and date reconciles the wrong customer. And what matters most, sales without money and money without a tax invoice, only shows up when accounting complains.

### Business rules

1. **Every account in one table, with the source marked.** Companies with more than one billing account (per group company, product line or subsidiary) stack the accounts into a single table with a source column. The invoice id is only unique within the account: the key is source + id. Every new account goes into the same table on the day it is connected. (Avoid: One query per account and a sum in a spreadsheet, or stacking without marking the source.)
2. **One status per invoice, chosen by priority.** Gateways generate more than one record for the same charge (duplicate copy, reissue, retry). Keep one per charge in this order: paid, chargeback, refunded, in protest, pending, expired; on a tie, the most recent. That way a paid duplicate copy doesn't coexist with the expired original. (Avoid: Taking the most recent record or counting all of them.)
3. **An attempt is not a charge.** Each card attempt becomes a charge row, and an invoice can have several declines before it is paid. Billings and cash count the document (billing invoice, e-commerce order), never the attempt. Declines go into their own table, one row per attempt, with a standardized decline code. (Avoid: Summing charges to get billings.)
4. **Billings, cash and open amounts tie out.** Month billings = gross cash from the month's invoices + what remains open on them. Each number has its own date: billings by accrual or issue date, cash by payment date, open by due date. If the identity doesn't hold in a month, there is a duplicate or a lost invoice, and the report doesn't go out. (Avoid: Three reports with three different filters that never match.)
5. **Cash is the date the acquirer settles.** Card money lands in the balance weeks after payment, and installment sales land per installment. Build receivables by transaction x installment with a status of settled or to be received. Net = amount minus fee, advance fee and anti-fraud fee. In the projection, remove sales with a chargeback or refund. Pix (instant payment) and boleto (bank payment slip) with no recorded payout land on the next bank business day, using a bank holiday calendar (movable holidays computed from Easter). (Avoid: Treating the approved payment date as cash in.)
6. **Match systems by stored id, never by amount and date.** The ERP stores the billing invoice id in a custom field on the transaction and the billing customer id on the customer record. The product's invoice stores the gateway charge id (external_id). Match on those ids. To find sold but not received, bring only the paid side of the gateway into the join and list what is left unmatched. (Avoid: Matching invoice and payment by the same amount on the same day.)
7. **Use the payments table, not the balance transactions table.** Gateways have a charges table and a balance transactions table, which repeats every payment and adds fees and transfers. Revenue comes from the charges table. In Stripe, link charge to subscription through the invoice lines: the ready-made charges-by-subscription table only has the successful ones. (Avoid: Summing the gateway's transactions table as revenue.)
8. **Right unit and sign before summing.** Stripe, Pagar.me and *_cents fields come in cents: divide by 100 once, in the first layer. Sales order lines in some ERPs come with an inverted sign and must be negated. Reversals and refunds go in as negative in cash and positive in the refunds table, never both in the same total. (Avoid: Dividing by 100 in every report, or never.)
9. **Headquarters and branch are separate companies.** When sales are issued by more than one group company, each report comes from one token per company and carries a company column. A global sale code that appears in both companies is a duplicate. If the issuing company changed on a date, the rule for which company applies is by a declared cutoff date. (Avoid: Stacking headquarters and branch with no company column.)
10. **Exceptions become tables, not silent fixes.** The reconciliation doesn't delete or fix rows. It publishes one table per type of problem: approved outside the tax invoice issuer, approved with no tax invoice issued, product missing from the mapping, refunded with an active tax invoice, amount mismatch between billing and tax invoice, duplicate across companies. An empty table is the goal. (Avoid: Filtering out odd rows so the number matches.)

### Gateway status to category

In the priority order used to keep one record per charge.

- **paid, succeeded, approved** → Paid (priority 1) (`counts in gross cash`)
- **chargeback, dispute lost** → Chargeback (2) (`leaves cash, goes to refunds`)
- **refunded, amount_refunded > 0** → Refunded (3) (`full or partial by amount`)
- **in_protest, open dispute** → In dispute (4) (`cash received, still at risk`)
- **pending, waiting_funds** → Open (5) (`counts in the open amount`)
- **expired, canceled** → Expired or canceled (6) (`excluded from billing`)
- **failed, declined** → Decline (`attempt, never an invoice`)

### Definitions

| Metric | Definition |
|---|---|
| Month billings | sum of revenue invoice amounts, deduplicated by document, with accrual or issue date in the month, status paid or pending |
| Gross cash | sum of the paid amount on invoices, by payment date, before fees |
| Net cash | sum per installment of amount minus fee, advance fee and anti-fraud fee, by the acquirer's settlement date |
| Open | month billings minus the gross cash from that month's invoices, on the cutoff date |
| Sold but not received | invoice issued in the product or in billing with no matching paid charge in the gateway, by stored id |
| Received without a tax invoice | sale approved in the gateway with no tax invoice issued or outside the tax invoice issuer |
| Effective gateway fee | sum of fees / gross cash in the month, by gateway and payment method |
| Decline | a denied payment attempt, with a standardized code; the same sale can have several |

### Pitfalls

- **Gateway counted twice.** The first consolidated version summed one gateway's transactions table, which repeats every payment along with fees and transfers, and that gateway's revenue came out nearly double. How to avoid: Revenue only from the charges table; balance transactions only for cash.
- **Declines that disappeared.** The failed renewals report started from the ready-made charges-by-subscription table, which only keeps successful charges. No decline ever showed up. How to avoid: Link charge to subscription through the invoice lines.
- **Invisible partial refund.** The refunded flag only turns true when the full amount comes back. Partial refunds stayed as full revenue. How to avoid: Use the refunded amount, not the flag.
- **One boleto, several contracts.** A single receivable in finance covered more than one of the customer's contracts. Matching by customer repeated the receivable's amount on each contract. How to avoid: Count how many contracts each receivable covers and allocate or deduplicate by receivable.
- **Spreadsheet that turns into dates.** Dates came as Excel serial numbers (base 12/30/1899), quantities and unit prices arrived converted to dates, and some systems sent empty text instead of null. How to avoid: Convert serials from 12/30/1899, reverse values read as dates and treat empty text as null in the first layer.
- **Decline code that doesn't match.** The same reason arrived as 051 in one gateway and 51 in another, and new reasons silently fell outside the mapping. How to avoid: Strip the leading zero from three-character codes and keep a monitor for unmapped reasons.

Validated on real data: In Nekt's own Stripe account, about 7% of invoices had more than one charge attempt (up to 14 on the same invoice), almost all declines followed by payment, and none had two successful payments: summing charges would inflate the charge count, deduplicating by invoice solves it. Amounts come in cents, partial refunds don't set the refunded flag, and card money in reais becomes available a median of 29 days after the charge, already net of about 4.3% in fees.

### Acceptance test

- [ ] In each closed month, billings = gross cash + open, with zero difference.
- [ ] Each billing account's billings match the billing system's own dashboard for the same period (difference below 0.5%).
- [ ] The invoice table has no repeated source + id key, and no invoice has more than one status.
- [ ] An acquirer's settled net cash for the month matches the payouts received on the bank statement.
- [ ] Each exception table has an owner, and every row in it has a recorded reason or was resolved at the source.

### Context document

```text
Billing reconciliation
- The invoice table has one row per invoice, with a source column (billing account or gateway). The key is source + id.
- Each charge has one status, chosen by priority: paid > chargeback > refunded > in_protest > pending > expired; on a tie, the most recent.
- A payment attempt is not an invoice. Billings and cash count the document; declines are in the declines table, one row per attempt.
- Month billings = gross cash + open on the month's invoices. Billings use accrual or issue date; cash uses the payment date; open uses the due date.
- Net cash is per installment, on the acquirer's settlement date, minus fee, advance and anti-fraud fees.
- Amounts from Stripe and *_cents fields are already converted to reais in the cleaned table. Don't divide again.
- Partial refund: use the refunded amount, not the flag.
- Matching with the ERP is by the billing invoice id stored in the ERP. Never match by amount and date.
- For exception questions (sold but not received, no tax invoice, amount mismatch, duplicates), use the exception tables and don't recalculate.
- If the question doesn't specify the date, answer by accrual and say so.
Per-client exceptions:
- <client>: recurring revenue only when the invoice line has a subscription; one-off and implementation charges stay out of recurring billings.
- <client>: sales through <gateway> by external boleto (bank payment slip) stay out of the billing reconciliation.
- <client>: until <date>, the issuing company was <headquarters>; after that, <branch>.
```

### Variations

- **Receivables by acquirer:** A receivables hub by transaction x installment, with a daily report of settled and to-be-received amounts per acquirer.
- **Accounting sign-off:** Per-acquirer check using the gateway transaction's TID, NSU and authorization code against the statement.
- **Card declines:** A declines table with a standardized code, category and retryable flag, the foundation for dunning and card conversion.