Batch Reports

Analyze settlement batches and track deposit patterns for accurate reconciliation

Batch reports help you understand how payment transactions are grouped into deposit transactions, track settlement timing, and ensure accurate bank reconciliation. These examples demonstrate how to analyze batch composition, monitor batch performance, and identify discrepancies between expected and actual deposits. Use these patterns to maintain accurate financial records and quickly resolve reconciliation issues.

Batch reporting requires understanding the relationship between payment transactions and deposit transactions through the transfers table. Each transfer links a payment to its funding deposit, enabling you to trace which payments settled in which batch and when they hit your bank account.

Prerequisites

Before building batch reports, understand:

Batched payments

Learn how payments batch into deposits through transfers.

Handling batched payments

Report query mechanics

Master query building with select, filter, and group by.

Building report queries

Funding transactions

Understand how funding transactions appear on statements.

Match funding transactions

Batch Summary Report

report.py

import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
from datetime import datetime, timedelta
 
# Analyze deposit batches and their composition
 
print("Batch Summary Report")
print("=" * 60)
print()
 
# Get recent deposits
start_date = datetime.now() - timedelta(days=30)
 
deposits = (
    pl.Transaction.filter_by(
        pl.attr.type == "deposit", pl.attr.created_at >= start_date
    )
    .order_by(pl.attr.created_at.desc())
    .limit(10)
    .all()
)
 
print(f"Analyzing {len(deposits)} recent deposits...")
print()
 
# Analyze each deposit
for deposit in deposits:
    print(f"Deposit: {deposit.id}")
    print(f"  Amount: ${deposit.amount:,.2f}")
    print(f"  Date: {deposit.created_at}")
 
    # Get transfers for this deposit
    transfers = pl.Transfer.filter_by(pl.attr.assoc_transaction_id == deposit.id).all()
 
    payment_count = len(transfers)
    print(f"  Payments in batch: {payment_count}")
 
    if payment_count > 0:
        # Get payment transactions
        payment_ids = [t.transaction_id for t in transfers]
        payments = pl.Transaction.filter_by(pl.attr.id == "|".join(payment_ids)).all()
 
        # Calculate total from payments
        payment_total = sum(p.amount for p in payments)
        print(f"  Payment total: ${payment_total:,.2f}")
 
        # Calculate variance
        variance = deposit.amount - payment_total
        print(f"  Variance: ${variance:,.2f}", end="")
 
        if variance < 0:
            print(" (Fees deducted)")
        elif variance > 0:
            print(" (Credits/adjustments)")
        else:
            print(" (Exact match)")
 
        # Calculate effective fee rate
        if payment_total > 0:
            fee_rate = abs(variance) / payment_total * 100
            print(f"  Effective fee rate: {fee_rate:.2f}%")
 
    print()
 
print("Summary complete")

Analyze deposit transaction batches and their composition.

What this query does

This query joins deposit transactions to their constituent payment transactions through the transfers table, grouping by deposit to show how many payments are in each batch and calculate total amounts.

Query components:

Component	Description
Select	Deposit ID, amount, date, payment count, total payments
Join	Links deposits to payments through transfers
Group	Results by deposit transaction
Calculate	Batch composition and totals

Batch Timing Analysis

report.py

from datetime import datetime, timedelta
 
import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
# Track settlement timing for payments
 
print("Batch Timing Analysis")
print("=" * 60)
print()
 
# Get payments from last 30 days
start_date = datetime.now() - timedelta(days=30)
 
payments = (
    pl.Transaction.filter_by(
        pl.attr.type == "payment",
        pl.attr.status == "processed",
        pl.attr.created_at >= start_date,
    )
    .limit(100)
    .all()
)
 
timing_data = []
 
for payment in payments:
    # Get transfer to find deposit
    transfer = pl.Transfer.filter_by(pl.attr.transaction_id == payment.id).first()
 
    if transfer and transfer.assoc_transaction_id:
        # Get deposit transaction
        deposit = pl.Transaction.get(transfer.assoc_transaction_id)
 
        # Calculate timing
        days_to_settle = (deposit.created_at - payment.created_at).days
 
        timing_data.append(
            {
                "payment_id": payment.id,
                "payment_date": payment.created_at,
                "deposit_date": deposit.created_at,
                "days_to_settle": days_to_settle,
                "amount": payment.amount,
            }
        )
 
if timing_data:
    # Calculate statistics
    avg_days = sum(d["days_to_settle"] for d in timing_data) / len(timing_data)
    min_days = min(d["days_to_settle"] for d in timing_data)
    max_days = max(d["days_to_settle"] for d in timing_data)
 
    print(f"Settlement Timing Statistics:")
    print(f"  Payments analyzed: {len(timing_data)}")
    print(f"  Average: {avg_days:.1f} days")
    print(f"  Fastest: {min_days} days")
    print(f"  Slowest: {max_days} days")
 
    # Find delayed settlements (>3 days)
    delayed = [d for d in timing_data if d["days_to_settle"] > 3]
 
    if delayed:
        print(f"\n⚠️  {len(delayed)} delayed settlements (>3 days):")
        for d in sorted(delayed, key=lambda x: x["days_to_settle"], reverse=True)[:5]:
            print(
                f"  {d['payment_id']}: {d['days_to_settle']} days - ${d['amount']:.2f}"
            )
else:
    print("No settlement data found")

Track settlement timing and identify patterns.

What this query does

This query analyzes how long payments take to settle into deposit batches by calculating the time difference between payment and deposit dates, revealing settlement timing patterns and consistency.

Query components:

Component	Description
Select	Payment date, deposit date, time difference
Calculate	Days from payment to settlement
Group	By settlement day of week or time period
Analyze	Settlement timing patterns

Batch Reconciliation

report.py

from datetime import date
 
import dateutil
import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
# Match deposits to bank statement
 
print("Batch Reconciliation Report")
print("=" * 60)
print()
 
# Define reconciliation period
recon_date = date(2024, 3, 1)
 
# Get all deposits for the period
deposits = (
    pl.Transaction.filter_by(
        pl.attr.type == "deposit",
        pl.attr.created_at.month() == recon_date.month,
        pl.attr.created_at.year() == recon_date.year,
    )
    .order_by(pl.attr.created_at)
    .all()
)
 
print(f"Reconciling {len(deposits)} deposits for {recon_date.strftime('%B %Y')}")
print()
 
# Example bank statement data (would come from bank file/API)
bank_statement = [
    {"date": date(2024, 3, 1), "amount": 15000.00, "reference": "REF001"},
    {"date": date(2024, 3, 2), "amount": 12500.00, "reference": "REF002"},
    {"date": date(2024, 3, 3), "amount": 18750.00, "reference": "REF003"},
]
 
# Match by amount and approximate date
matched = []
unmatched_deposits = []
unmatched_bank = list(bank_statement)
 
for deposit in deposits:
    deposit_date = dateutil.parser.parse(
        deposit.est_cleared_date or deposit.created_at
    ).date()
 
    # Try to find matching bank entry
    match_found = False
    for bank_entry in list(unmatched_bank):
        # Match criteria
        amount_match = abs(deposit.amount - bank_entry["amount"]) < 1.00
        date_diff = abs((deposit_date - bank_entry["date"]).days)
        date_match = date_diff <= 2
 
        if amount_match and date_match:
            matched.append(
                {
                    "deposit_id": deposit.id,
                    "deposit_amount": deposit.amount,
                    "bank_amount": bank_entry["amount"],
                    "deposit_date": deposit_date,
                    "bank_date": bank_entry["date"],
                }
            )
            unmatched_bank.remove(bank_entry)
            match_found = True
            break
 
    if not match_found:
        unmatched_deposits.append(
            {"deposit_id": deposit.id, "amount": deposit.amount, "date": deposit_date}
        )
 
# Report results
print(f"✓ Matched: {len(matched)} deposits")
print(f"⚠  Unmatched Deposits: {len(unmatched_deposits)}")
print(f"⚠  Unmatched Bank Entries: {len(unmatched_bank)}")
 
if unmatched_deposits:
    print("\nUnmatched Deposits (in Payload, not on statement):")
    for d in unmatched_deposits[:5]:
        print(f"  {d['deposit_id']}: ${d['amount']:,.2f} on {d['date']}")
 
if unmatched_bank:
    print("\nUnmatched Bank Entries (on statement, not in Payload):")
    for b in unmatched_bank:
        print(f"  {b['reference']}: ${b['amount']:,.2f} on {b['date']}")
 
# Calculate match rate
match_rate = (len(matched) / len(deposits) * 100) if deposits else 0
print(f"\nReconciliation confidence: {match_rate:.1f}%")

Match deposit batches to bank statement entries.

What this query does

This query retrieves deposit transactions with their amounts and dates formatted for matching against bank statement entries, enabling reconciliation by comparing expected deposits to actual bank activity.

Query components:

Component	Description
Select	Deposit transactions with key matching fields
Filter	Deposits within reconciliation period
Match	Compare to bank statement data
Identify	Matched, unmatched, and discrepancies

Batch Performance Metrics

report.py

import payload
pl = payload.Session('secret_key_3bW9...', api_version='v2')
from datetime import datetime, timedelta
 
from collections import defaultdict
 
# Track batch processing performance metrics
 
print("Batch Performance Metrics")
print("=" * 60)
print()
 
# Get deposits for last 30 days
start_date = datetime.now() - timedelta(days=30)
 
deposits_by_date = pl.Transaction.select(
    pl.attr.created_at.date(),
    pl.attr.id.count(),
    pl.attr.amount.sum(),
    pl.attr.amount.avg()
).filter_by(
    pl.attr.type == 'deposit',
    pl.attr.created_at >= start_date
).group_by(
    pl.attr.created_at.date()
).order_by(
    pl.attr.created_at.date()
).all()
 
print(f"Analyzing {len(deposits_by_date)} days of batch data...")
print()
 
# Calculate payments per batch
batch_metrics = []
 
for day_data in deposits_by_date:
    day = getattr(day_data, 'date(created_at)')
    batch_count = getattr(day_data, 'count(id)')
    total_amount = getattr(day_data, 'sum(amount)')
    avg_amount = getattr(day_data, 'avg(amount)')
 
    # Get deposits for this date
    deposits = pl.Transaction.filter_by(
        pl.attr.type == 'deposit',
        pl.attr.created_at.date() == day
    ).all()
 
    # Calculate payments per deposit
    payments_per_batch = []
    for deposit in deposits:
        transfers = pl.Transfer.filter_by(
            pl.attr.assoc_transaction_id == deposit.id
        ).all()
        payments_per_batch.append(len(transfers))
 
    avg_payments = sum(payments_per_batch) / len(payments_per_batch) if payments_per_batch else 0
 
    batch_metrics.append({
        'date': day,
        'batch_count': batch_count,
        'total_amount': total_amount,
        'avg_batch_amount': avg_amount,
        'avg_payments_per_batch': avg_payments
    })
 
# Summary statistics
total_batches = sum(m['batch_count'] for m in batch_metrics)
avg_batches_per_day = total_batches / len(batch_metrics) if batch_metrics else 0
avg_batch_size = sum(m['avg_payments_per_batch'] for m in batch_metrics) / len(batch_metrics) if batch_metrics else 0
 
print(f"Period: Last {len(batch_metrics)} days")
print(f"Total Batches: {total_batches}")
print(f"Avg Batches per Day: {avg_batches_per_day:.1f}")
print(f"Avg Payments per Batch: {avg_batch_size:.1f}")
 
print("\nLast 7 Days:")
for metric in batch_metrics[-7:]:
    print(f"  {metric['date']}: {metric['batch_count']} batches, "
          f"{metric['avg_payments_per_batch']:.0f} payments/batch, "
          f"${metric['avg_batch_amount']:,.2f} avg")
 
# Calculate trend
if len(batch_metrics) >= 14:
    sizes = [m['avg_payments_per_batch'] for m in batch_metrics]
    first_week_avg = sum(sizes[:7]) / 7
    last_week_avg = sum(sizes[-7:]) / 7
    change = ((last_week_avg - first_week_avg) / first_week_avg) * 100 if first_week_avg > 0 else 0
 
    print(f"\nBatch Size Trend:")
    print(f"  First Week Avg: {first_week_avg:.1f} payments/batch")
    print(f"  Last Week Avg: {last_week_avg:.1f} payments/batch")
    print(f"  Change: {change:+.1f}%")

Track batch processing efficiency and identify trends.

What this query does

This query aggregates batch metrics over time periods to track average batch sizes, batch frequency, and processing patterns, revealing trends in settlement efficiency and consistency.

Query components:

Component	Description
Select	Batch metrics over time
Calculate	Batch size, frequency, processing times
Group	By time period (daily, weekly, monthly)
Analyze	Performance trends and patterns

Best Practices

Tips for effective batch reporting.

Use the transfers table to link payments to deposits:

# Correct: Use transfers table
payment_id = 'txn_example123'
transfer = pl.Transfer.filter_by(pl.attr.transaction_id == payment_id).first()
 
if transfer:
    deposit_id = transfer.assoc_transaction_id
    deposit = pl.Transaction.get(deposit_id)

Deposits may clear on different dates:

# Use est_cleared_date for bank reconciliation
deposit_id = 'txn_example123'
deposit = pl.Transaction.get(deposit_id)
internal_date = deposit.created_at
bank_date = deposit.est_cleared_date
 
print(f"Created: {internal_date}, Cleared: {bank_date}")

Funding style affects batch structure:

Check funding style determines batch expectations:

- Netted: One deposit per period (credits - debits)
- Gross: Separate deposits for credits and debits
- Itemized: One deposit per transaction
- Manual: Funds held until released

Next Steps

Explore related reporting topics

Revenue Reports

Analyze revenue trends and financial performance with Revenue Reports documentation, track monthly revenue trends, and calculate multi-dimensional revenue analysis across different dimensions.

Customer Analytics

Understand customer behavior and lifetime value with Customer Analytics guide, segment customers by spending patterns, and identify high-value customers for targeted retention strategies.

Customer Analytics Invoice Reports