Define the records before counting
Record payment ID, job reference, date, amount and adjustment status. Keep the billed or agreed amount in its own field when the source provides it. Do not infer an unpaid balance without knowing the relevant amount and payment coverage.
A worked example
Fictional job J-17 receives $70 on September 29 and $80 on October 3. It has two payment events and $150 total recorded collection. September’s collection view shows $70 and October’s shows $80. If the expected amount is unknown, the report cannot conclude the account is fully paid merely because two receipts exist.
Resolve the gap in evidence
Preserve both rows and use explicit job IDs to calculate the total. Keep refunds as separate negative adjustments. If a payment lacks a job reference, queue it for review rather than attaching it to the nearest amount. The lab sample contains this split-payment pattern for inspection.
Check whether it worked
Reconcile the sum back to the source transactions and state whether the view uses payment dates or a job cohort. Check for repeated export rows before summing. Show known collection and unresolved balance separately. Formal accounting and customer payment obligations remain outside this simple analytical model.
Put it to work
A record you can use
- Job ID
- Separate payment IDs and dates
- Known collection total
- Expected amount or unknown
- Duplicate and adjustment checks
Plain text. No email required. Store completed records in your approved business systems.
Next useful step
Need help applying this to your marketing? A strategy call starts with your business and the evidence you have.
Talk through your next step →For a general editorial question, email Chris. Please leave out customer details. Questions are private, and no response time is promised.