Explain the difference before choosing a dashboard total
Build a reconciliation dashboard by defining each measure, mapping the underlying records and calculating a visible bridge between the two totals. A gateway's completed payments, an accounting system's recognised revenue and a bank's received cash can all be different legitimate measures. Do not average their totals or invent an adjustment to make them agree. Show the explained differences and any unresolved remainder, with an owner responsible for investigating it.
The practical output below is a proposed data contract and a filled hypothetical reconciliation. It is a specification for a dashboard team, not an accounting policy or a claim about results achieved for a client. Your finance owner must approve the business definitions, including tax treatment and the basis for recognising revenue.
Name the measures that each system actually reports
Start with the exact label and definition of both figures. "Revenue" is often used loosely for orders, invoices, successful payments, net gateway credit or bank receipts. These are different questions:
- What did customers order?
- What revenue does the approved accounting policy recognise in this period?
- What payments completed?
- What did the gateway deduct or retain?
- What cash reached the bank?
Give each measure its own label. A useful dashboard may need several of them rather than one universal total. If two systems claim to report the same measure, their definitions and record populations should match. If they report different measures, explain the relationship instead of presenting one as an error.
For a provider-specific example, Stripe's balance transaction definitions distinguish charges, refunds, fees, payouts and other balance movements. They also distinguish pending funds from available balance. This is evidence about Stripe's reporting model, not a claim that its products are available to every South African merchant.
Likewise, Payfast's subscription feature concerns scheduled card billing. A subscription schedule does not establish that an attempted charge completed, that cash reached the bank or that the payment should be recognised as accounting revenue.
Avoid two common definition errors
Do not remove historical successful payments merely because a subscription is now cancelled. Determine whether there was a refund, reversal or another event relevant to the selected measure.
Do not count an unsuccessful retry as money received. Keep attempted charges, completed payments and outstanding customer balances separate. If an accounting ledger records an amount receivable, label that measure explicitly rather than silently adding it to gateway cash collections.
Write the data contract before combining records
The following filled contract is for a fictional comparison of completed customer payments and bank cash. Every amount and operating rule in the worked example is hypothetical.
| Contract field | Proposed definition for this example | Evidence or owner |
|---|---|---|
| Measures | Gateway gross completed payments; bank cash received from gateway payouts | Finance approves the labels and formulas |
| Period | One calendar month in Africa/Johannesburg | Record the period start and exclusive next-month boundary |
| Currency | ZAR only; currency conversion excluded from this example | Preserve currency on every source record |
| Completed payment population | Unique completed customer payments in the selected period | Provider transaction records and internal payment mapping |
| Refunds | Recorded refund movements affecting the period's gateway balance | Link each refund to its original payment |
| Fees | Observed gateway fee movements in the period | Use actual source amounts, not an estimated percentage |
| Timing | Keep payment completion, refund, payout and bank-posting timestamps separate | Source fields and documented timezone conversion |
| Coverage | One gateway account and one receiving bank account | Named account scope, extraction timestamp and completeness checks |
| Identity | Stable source record IDs plus explicit cross-system mapping | Data owner approves the mapping and one-to-many relationships |
| Exceptions | Unmatched or ambiguous records remain visible | Named investigation owner and evidence reference |
| Acceptance | The cash bridge balances; unresolved amounts are shown separately | Finance and product owners review the result |
For the real product, add the approved treatment of discounts, tax, chargebacks, credits and opening balances. Do not infer these from field names. If a source amount includes a component that the selected measure excludes, record the adjustment and its supporting record.
Use half-open period boundaries: include timestamps at or after the start and before the next period's start. A rule ending at "23:59" can accidentally omit transactions in the remaining seconds of that minute. Store the source timestamp and timezone alongside the normalised value so the decision is reproducible.
Reconcile at record level before explaining totals
Extract the required records from both systems without overwriting their original values. Retain a source ID, account scope, amount, currency, event type, effective timestamp, extraction timestamp and relevant parent reference.
Match records using stable identifiers. A single invoice may have several payments, a payment may have a partial refund, and a payout may contain many payments. Invoice number plus date is not automatically a unique key. Document the intended relationships rather than forcing every record into a one-to-one join.
Classify each difference into a concrete category:
- A measure difference, such as gross payment versus cash after fees.
- A timing difference, such as a payout not yet posted to the bank.
- A coverage difference, such as an account omitted from the extract.
- A duplicate or missing source record.
- An unresolved exception requiring investigation.
For each category, retain record references and the adjustment direction. A narrative explanation without amounts cannot prove that the bridge balances. Preserve the unresolved remainder even when it is below an alert threshold.
Source freshness still matters. A sound contract cannot compensate for a missing export page, a failed connector or an extract taken before late records arrived. Display when each source was extracted and which period is complete.
A balanced hypothetical bridge from payments to bank cash
Suppose a fictional gateway extract shows R500,000 in gross completed customer payments. The bank shows R470,000 received from that gateway during the same month.
For this simplified example, the opening gateway balance is zero. There are no older-period payouts, chargebacks, reserves, foreign-currency movements or other adjustments. Refunds and fees below are invented observed-record amounts for the illustration; they are not current provider prices.
| Bridge item | Amount | Running balance | Evidence required in a real dashboard |
|---|---|---|---|
| Gross completed customer payments | R500,000 | R500,000 | Unique completed payment records |
| Less refunds recorded in the period | R5,000 | R495,000 | Refund records linked to original payments |
| Less gateway fees recorded in the period | R17,000 | R478,000 | Fee records or reconciled provider statement |
| Less ending funds not received by the bank | R8,000 | R470,000 | Pending balance, retained funds or in-transit payout evidence |
| Bank cash received | R470,000 | R470,000 | Matched bank statement entries |
| Unresolved difference | R0 | R0 | Comparison of expected and observed bank receipts |
The R30,000 difference is explained by R5,000 + R17,000 + R8,000. The dashboard should show both original measures and this bridge. It must not report their average, R485,000, as "combined revenue".
For a real period, the R8,000 line needs a precise classification. Pending gateway funds, available but unpaid-out funds and a payout already sent but not bank-posted are different states. If several apply, split the line and avoid counting the same amount twice.
This bridge explains cash movements. It does not determine accounting revenue. Show the finance-approved revenue measure separately, with its own source and recognition rules.
Keep an unexplained remainder visible
If the bank extract instead contains R469,000, the same supported adjustments leave R1,000 unexplained. Record "Expected bank cash: R470,000; observed: R469,000; unresolved: R1,000". Investigate the unmatched movement.
Do not invent another R1,000 fee or change the period boundary simply to balance the report. When evidence establishes the cause, add its record reference, adjustment and approval to the reconciliation history.
Build a procedure that fails clearly when evidence is incomplete
A practical proposed runbook is:
- Confirm the period, account scope and finance-approved definitions.
- Extract source records and retain extraction evidence.
- Check pagination, record counts, currencies and required identifiers.
- Normalise timestamps and event classifications without changing source values.
- Match payments, refunds, fees and payouts through documented relationships.
- Calculate each bridge line using deterministic arithmetic.
- Compare expected cash with bank records and retain the remainder.
- Assign exceptions to an owner and publish the review state.
If a spreadsheet is part of the workflow, use stable ranges and a defined column contract. The Google Sheets batch-update documentation describes structured spreadsheet updates. An accepted update does not establish that the source extract was complete or that the figures were classified correctly. Read back the relevant ranges and compare them with the approved reconciliation result.
AI can help write a plain-language explanation from validated figures and exception categories. It should not choose financial definitions or create balancing adjustments. OpenAI's Structured Outputs guidance explicitly warns that structured outputs can still contain mistakes. Validate any generated explanation against the calculated bridge before displaying it.
Acceptance and recovery checklist
| Test | Expected result | Recovery if it fails |
|---|---|---|
| Replay the same source record | Totals remain unchanged; the record is deduplicated by its stable identity | Fix ingestion identity and recalculate the affected period |
| Load a partial refund | Refund amount and original payment remain separately traceable | Repair the parent mapping; do not delete the original payment |
| Add a late bank posting | It appears in the correct period under the agreed timestamp rule | Review the mapping and retain the prior report revision |
| Omit an extract page | Coverage check fails; the dashboard shows incomplete data | Re-extract and reconcile before marking the period reviewed |
| Supply another currency | It is rejected from this ZAR-only example or handled by an approved conversion rule | Obtain the correct currency treatment and source rate |
| Produce an unexplained difference | The remainder remains visible with an owner | Investigate records rather than averaging totals |
| Compare explanation with calculation | Every amount and direction agrees | Correct the explanation before customer display |
An alert threshold is an operating choice, not permission to erase smaller discrepancies. Define who receives alerts, how exceptions are resolved and when a reporting period may be marked reviewed.
Make the dashboard's practical output reviewable
Deliver the completed contract, source-field mapping, record-matching rules, filled bridge, exception register and acceptance results together. This gives a reviewer enough evidence to reproduce the totals and understand their limits.
If your business needs help turning this specification into a reliable interface, our dashboard development service can support that work. For a scoped implementation discussion, get in touch with the two source reports and agreed measures.
Connect the report to relevant decisions using marketing dashboard metrics, and consider SaaS development where the workflow needs a custom product. If CRM automation is involved, the AI CRM integration guide and AI CRM integration glossary explain that separate integration concept.
Frequently asked questions
Should we choose one total and hide the other?
Keep legitimate measures visible with clear definitions. Gross completed payments and bank cash answer different questions. Reconcile their relationship; choose the finance-approved measure for each business decision.
Can failed subscription retries count as collected cash?
No unsuccessful attempt establishes collected cash. Track payment attempts separately from completed payments and receivables. Verify the provider's actual records before classifying an event.
Should a cancelled subscription's earlier payments be excluded?
Do not exclude them solely because of current cancellation status. Determine whether a refund, reversal or another relevant adjustment occurred and apply the selected measure's rules.
How often should we reconcile and what if differences remain?
Choose a proposed schedule based on reporting needs and source availability. Daily checks may suit some teams. Retain unresolved differences, source completeness and review status until evidence supports a correction.