Invoice Reports

Track invoices, analyze payment patterns, and optimize accounts receivable

Invoice reports help you monitor outstanding balances, track payment performance, and identify collection issues. These examples demonstrate common invoice analysis patterns from status summaries to aging reports. Use these as templates for managing accounts receivable, forecasting cash flow, and optimizing collection strategies.

Prerequisites

Before building invoice reports, understand:

Invoices by Status

import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
# Calculate invoice totals grouped by status
invoices_by_status = (
    pl.Invoice.select(
        pl.attr.status,
        pl.attr.totals.total.sum(),
        pl.attr.totals.balance_due.sum(),
        pl.attr.id.count(),
    )
    .group_by(pl.attr.status)
    .order_by(pl.attr.status)
    .all()
)

Calculate invoice totals and outstanding balances grouped by status.

What this query does

This query groups invoices by their status (paid, open, draft, etc.) and calculates total invoice amounts, outstanding balances, and invoice counts for each status to provide a financial overview.

Query components:

Query breakdown

results = (
    pl.Invoice.select(
        pl.attr.status,  # Grouping dimension
        pl.attr.totals.total.sum(),  # Total invoice value
        pl.attr.totals.balance_due.sum(),  # Outstanding balance
        pl.attr.id.count(),  # Invoice count
    )
    .group_by(pl.attr.status)  # Group by status
    .order_by(pl.attr.status)
    .all()
)

Result structure:

[
  {
    "status": "paid",
    "sum(totals.total)": 450000.0,
    "sum(totals.balance_due)": 0.0,
    "count(id)": 245
  },
  {
    "status": "unpaid",
    "sum(totals.total)": 125000.0,
    "sum(totals.balance_due)": 125000.0,
    "count(id)": 68
  },
  {
    "status": "partially_paid",
    "sum(totals.total)": 50000.0,
    "sum(totals.balance_due)": 15000.0,
    "count(id)": 22
  }
]

Analysis tips:

• Total AR = sum of balance_due for unpaid and partially_paid
• Collection rate = paid total / (paid + unpaid + partially_paid totals)
• Average invoice value = sum(totals.total) / count(id)
• Monitor draft count for billing backlog

Aging Report

from datetime import datetime
 
import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
# Calculate days overdue and group invoices by aging bucket
today = datetime.now().date()
 
aging_report = (
    pl.Invoice.select(
        pl.attr.id,
        pl.attr.number,
        pl.attr.payer.account_id,
        pl.attr.totals.balance_due,
        pl.attr.due_date,
        pl.attr.status,
    )
    .filter_by(
        pl.attr.status == "|".join(["open", "unpaid", "partially_paid"]),
        pl.attr.totals.balance_due > 0,
    )
    .order_by(pl.attr.due_date)
    .all()
)
 
# Categorize by aging bucket
current = []
days_30 = []
days_60 = []
days_90_plus = []
 
for invoice in aging_report:
    due_date = datetime.strptime(invoice.due_date, "%Y-%m-%d").date()
    days_overdue = (today - due_date).days
 
    if days_overdue <= 0:
        current.append(invoice)
    elif days_overdue <= 30:
        days_30.append(invoice)
    elif days_overdue <= 60:
        days_60.append(invoice)
    else:
        days_90_plus.append(invoice)
 
print(
    f"Current (not due): {len(current)} invoices, ${sum(inv.totals.balance_due for inv in current):,.2f}"
)
print(
    f"1-30 days overdue: {len(days_30)} invoices, ${sum(inv.totals.balance_due for inv in days_30):,.2f}"
)
print(
    f"31-60 days overdue: {len(days_60)} invoices, ${sum(inv.totals.balance_due for inv in days_60):,.2f}"
)
print(
    f"90+ days overdue: {len(days_90_plus)} invoices, ${sum(inv.totals.balance_due for inv in days_90_plus):,.2f}"
)

Categorize outstanding invoices by how long they've been overdue.

What this query does

This query retrieves all open invoices with outstanding balances and their due dates, then calculates days overdue to categorize invoices into aging buckets (current, 1-30 days, 31-60 days, 60-90 days, 90+ days).

Query components:

Query breakdown

# Get all open invoices
aging_report = (
    pl.Invoice.select(
        pl.attr.id,
        pl.attr.number,
        pl.attr.payer.account_id,
        pl.attr.totals.balance_due,
        pl.attr.due_date,
        pl.attr.status,
    )
    .filter_by(
        pl.attr.status == "open|unpaid|partially_paid",
        pl.attr.totals.balance_due > 0,
    )
    .order_by(pl.attr.due_date)
    .all()
)
 
# Categorize by aging (application logic)
today = date.today()
for invoice in aging_report:
    due = date.fromisoformat(str(invoice.due_date)[:10])
    days_overdue = (today - due).days
    # Assign to aging bucket based on days_overdue

Result structure:

{
  "current": {
    "count": 45,
    "total": 125000.0
  },
  "1-30_days": {
    "count": 18,
    "total": 35000.0
  },
  "31-60_days": {
    "count": 8,
    "total": 15000.0
  },
  "90+_days": {
    "count": 5,
    "total": 12000.0
  }
}

Analysis tips:

• Focus collection efforts on 30+ day bucket
• Monitor trends in aging distribution over time
• Calculate Days Sales Outstanding (DSO)
• Set bad debt reserves based on 90+ day bucket

Extended analysis

Calculate aging percentages:

total_ar = sum(invoice["totals.balance_due"] for invoice in aging_report)
 
for bucket_name, bucket_data in aging_buckets.items():
    bucket_total = bucket_data["total"]
    percentage = (bucket_total / total_ar) * 100
    print(f"{bucket_name}: ${bucket_total:,.2f} ({percentage:.1f}%)")

Days Sales Outstanding (DSO):

# DSO = (Accounts Receivable / Total Credit Sales) * Number of Days
dso = total_ar / average_daily_sales
print(f"Days Sales Outstanding: {dso:.1f} days")

Monthly Billing Trends

import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
# Track monthly invoice totals and counts
monthly_billing = (
    pl.Invoice.select(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.totals.total.sum(),
        pl.attr.totals.paid.sum(),
        pl.attr.totals.balance_due.sum(),
        pl.attr.id.count(),
    )
    .group_by(pl.attr.created_at.year(), pl.attr.created_at.month())
    .order_by(pl.attr.created_at.desc())
    .all()
)

Track invoice volume and values over time.

What this query does

This query groups invoices by year and month, calculating total billed amounts, collected amounts, outstanding balances, and invoice counts to track billing and collection trends over time.

Query components:

Query breakdown

results = (
    pl.Invoice.select(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.totals.total.sum(),  # Total billed
        pl.attr.totals.paid.sum(),  # Total collected
        pl.attr.totals.balance_due.sum(),  # Outstanding
        pl.attr.id.count(),  # Invoice count
    )
    .group_by(pl.attr.created_at.year(), pl.attr.created_at.month())
    .order_by("desc(year(created_at))", "desc(month(created_at))")
    .all()
)

Result structure:

[
  {
    "year(created_at)": 2024,
    "month(created_at)": 3,
    "sum(totals.total)": 185000.0,
    "sum(totals.paid)": 165000.0,
    "sum(totals.balance_due)": 20000.0,
    "count(id)": 92
  },
  {
    "year(created_at)": 2024,
    "month(created_at)": 2,
    "sum(totals.total)": 172000.0,
    "sum(totals.paid)": 168000.0,
    "sum(totals.balance_due)": 4000.0,
    "count(id)": 85
  }
]

Analysis tips:

• Collection rate = paid / total
• Growth rate = (current - previous) / previous
• Average invoice size = total / count
• Outstanding trend indicates collection issues

Overdue Invoices

from datetime import datetime
 
import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
# Find all overdue invoices with outstanding balances
today = datetime.now().date()
 
overdue_invoices = (
    pl.Invoice.select(
        pl.attr.id,
        pl.attr.number,
        pl.attr.payer.account_id,
        pl.attr.totals.balance_due,
        pl.attr.due_date,
        pl.attr.created_at,
    )
    .filter_by(
        pl.attr.status == "|".join([".open", "unpaid", "partially_paid"]),
        pl.attr.totals.balance_due > 0,
        pl.attr.due_date < str(today),
    )
    .order_by(pl.attr.due_date)
    .all()
)

Identify all invoices past their due date.

What this query does

This query filters invoices to those with open status, outstanding balances, and due dates in the past, ordered by due date to prioritize the oldest overdue invoices for collection efforts.

Query components:

Query breakdown

today = datetime.now().date()
 
overdue = (
    pl.Invoice.select(
        pl.attr.id,
        pl.attr.number,
        pl.attr.payer.account_id,
        pl.attr.totals.balance_due,
        pl.attr.due_date,
        pl.attr.created_at,
    )
    .filter_by(
        pl.attr.status == "open|unpaid|partially_paid",
        pl.attr.totals.balance_due > 0,
        pl.attr.due_date < str(today),  # Past due date
    )
    .order_by(pl.attr.due_date)  # Oldest first
    .all()
)

Result structure:

[
  {
    "id": "inv_abc123",
    "number": "INV-2024-001",
    "payer.account_id": "acct_customer1",
    "totals.balance_due": 5000.0,
    "due_date": "2024-01-15",
    "created_at": "2024-01-01"
  }
]

Analysis tips:

• Calculate days overdue for each invoice
• Group by customer to identify problem accounts
• Track overdue percentage over time
• Compare overdue rates by customer segment

Extended analysis

Calculate overdue metrics:

total_overdue = sum(inv["totals.balance_due"] for inv in overdue)
avg_days_overdue = sum((today - inv["due_date"]).days for inv in overdue) / len(overdue)
 
print(f"Total overdue: ${total_overdue:,.2f}")
print(f"Average days overdue: {avg_days_overdue:.1f}")

Group by customer:

from collections import defaultdict
 
by_customer = defaultdict(list)
for invoice in overdue:
    by_customer[invoice["payer.account_id"]].append(invoice)
 
# Find customers with most overdue amount
for customer, invoices in sorted(
    by_customer.items(),
    key=lambda x: sum(i["totals.balance_due"] for i in x[1]),
    reverse=True,
):
    total = sum(inv["totals.balance_due"] for inv in invoices)
    print(f"{customer}: ${total:,.2f} across {len(invoices)} invoices")

Collection Performance

import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
from datetime import datetime, timedelta
 
# Calculate payment collection metrics
thirty_days_ago = datetime.now() - timedelta(days=30)
 
collection_performance = (
    pl.Invoice.select(
        pl.attr.status,
        pl.attr.totals.total.sum(),
        pl.attr.totals.paid.sum(),
        pl.attr.totals.balance_due.sum(),
        pl.attr.id.count(),
    )
    .filter_by(pl.attr.created_at >= thirty_days_ago)
    .group_by(pl.attr.status)
    .all()
)
 
# Calculate collection rate
for row in collection_performance:
    total = row["sum(totals.total)"]
    paid = row["sum(totals.paid)"]
    if total > 0:
        collection_rate = (paid / total) * 100
        print(f"{row['status']}: {collection_rate:.1f}% collected")

Analyze payment collection rates and effectiveness.

What this query does

This query groups invoices from a recent time period by status, calculating total billed amounts, collected amounts, and outstanding balances to measure collection effectiveness and payment velocity.

Query components:

Query breakdown

thirty_days_ago = (datetime.now(timezone.utc) - timedelta(days=30)).isoformat()
 
sum_total = pl.attr.totals.total.sum()
sum_paid = pl.attr.totals.paid.sum()
sum_balance = pl.attr.totals.balance_due.sum()
 
performance = (
    pl.Invoice.select(
        pl.attr.status,
        sum_total,
        sum_paid,
        sum_balance,
        pl.attr.id.count(),
    )
    .filter_by(pl.attr.created_at >= thirty_days_ago)
    .group_by(pl.attr.status)
    .all()
)
 
# Calculate collection rate
for row in performance:
    total = getattr(row, str(sum_total)) or 0
    if total > 0:
        paid = getattr(row, str(sum_paid)) or 0
        rate = (paid / total) * 100
        print(f"{row.status}: {rate:.1f}% collected")

Result structure:

[
  {
    "status": "paid",
    "sum(totals.total)": 125000.0,
    "sum(totals.paid)": 125000.0,
    "collection_rate": 100.0
  },
  {
    "status": "partially_paid",
    "sum(totals.total)": 50000.0,
    "sum(totals.paid)": 35000.0,
    "collection_rate": 70.0
  }
]

Analysis tips:

• Overall collection rate = total paid / total billed
• Track collection rate trends over time
• Compare by customer segment or product
• Set targets based on historical performance

Multi-Dimensional Analysis

import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
# Analyze invoices across multiple dimensions
multi_dimensional = (
    pl.Invoice.select(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.status,
        pl.attr.payer.account_id,
        pl.attr.totals.total.sum(),
        pl.attr.totals.paid.sum(),
        pl.attr.totals.balance_due.sum(),
        pl.attr.id.count(),
        pl.attr.totals.total.avg(),
    )
    .group_by(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.status,
        pl.attr.payer.account_id,
    )
    .order_by(
        pl.attr.created_at.desc(),
    )
    .limit(100)
    .all()
)

Analyze invoices across multiple dimensions simultaneously.

What this query does

This query combines temporal, status, and customer dimensions to provide comprehensive invoice analysis, grouping by year, month, status, and customer to calculate billing, collection, and outstanding balance metrics across all dimensions.

Query components:

Query breakdown

multi = (
    pl.Invoice.select(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.status,
        pl.attr.payer.account_id,
        pl.attr.totals.total.sum(),
        pl.attr.totals.paid.sum(),
        pl.attr.totals.balance_due.sum(),
        pl.attr.id.count(),
        pl.attr.totals.total.avg(),
    )
    .group_by(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.status,
        pl.attr.payer.account_id,
    )
    .order_by(
        "desc(year(created_at))",
        "desc(month(created_at))",
        "desc(sum(totals[total]))",
    )
    .limit(100)
    .all()
)

Result structure:

[
  {
    "year(created_at)": 2024,
    "month(created_at)": 3,
    "status": "paid",
    "payer.account_id": "acct_customer1",
    "sum(totals.total)": 25000.0,
    "sum(totals.paid)": 25000.0,
    "sum(totals.balance_due)": 0.0,
    "count(id)": 5,
    "avg(totals.total)": 5000.0
  }
]

Analysis tips:

• Identify customers with declining payment rates
• Compare customer invoice sizes over time
• Spot customers with growing balances
• Segment customers by payment performance

Common Patterns

Reusable patterns for invoice analysis.

• Customer Summary
• Days to Payment
• Quarterly Analysis
• Recurring vs One-Time

# Comprehensive view of each customer's invoice history
customer_summary = (
    pl.Invoice.select(
        pl.attr.payer.account_id,
        pl.attr.totals.total.sum(),
        pl.attr.totals.paid.sum(),
        pl.attr.totals.balance_due.sum(),
        pl.attr.id.count(),
        pl.attr.totals.total.avg(),
    )
    .group_by(pl.attr.payer.account_id)
    .order_by("desc(sum(totals[balance_due]))")
    .all()
)

# Calculate average time from invoice creation to payment
paid_invoices = (
    pl.Invoice.select(pl.attr.created_at, pl.attr.paid_timestamp)
    .filter_by(pl.attr.status == "paid", q='paid_timestamp != null')
    .all()
)
 
total_days = 0
for invoice in paid_invoices:
    created = datetime.fromisoformat(str(invoice.created_at).replace("Z", "+00:00")).replace(tzinfo=None)
    paid = datetime.fromisoformat(str(invoice.paid_timestamp).replace("Z", "+00:00")).replace(tzinfo=None)
    days = (paid - created).days
    total_days += days
 
avg_days_to_payment = total_days / len(paid_invoices)
print(f"Average days to payment: {avg_days_to_payment:.1f}")

quarterly = (
    pl.Invoice.select(
        pl.attr.created_at.year(),
        pl.attr.created_at.quarter(),
        pl.attr.totals.total.sum(),
        pl.attr.totals.paid.sum(),
        pl.attr.id.count(),
    )
    .group_by(pl.attr.created_at.year(), pl.attr.created_at.quarter())
    .order_by("desc(year(created_at))", "desc(quarter(created_at))")
    .all()
)

# Compare recurring subscription invoices to one-time invoices
invoice_types = (
    pl.Invoice.select(
        pl.attr.billing_schedule_id.is_null(),  # True = one-time
        pl.attr.totals.total.sum(),
        pl.attr.id.count(),
        pl.attr.totals.total.avg(),
    )
    .group_by(pl.attr.billing_schedule_id.is_null())
    .all()
)

Best Practices

Tips for effective invoice reporting.

• Check Balance Due
• Consider Drafts
• Consistent Date Fields
• Document Assumptions

# Correct: Check balance_due for true outstanding amount
ar_total = (
    pl.Invoice.select(pl.attr.totals.balance_due.sum())
    .filter_by(
        pl.attr.status == "open|unpaid|partially_paid",
        pl.attr.totals.balance_due > 0,
    )
    .all()
)
 
# Incomplete: Status alone may be misleading
incomplete = (
    pl.Invoice.select(pl.attr.totals.total.sum())
    .filter_by(
        pl.attr.status == "unpaid|partially_paid",
    )
    .all()
)

# Monitor draft invoices not yet sent to customers
draft_backlog = (
    pl.Invoice.select(
        pl.attr.created_at.date(), pl.attr.totals.total.sum(), pl.attr.id.count()
    )
    .filter_by(pl.attr.status == "draft")
    .group_by(pl.attr.created_at.date())
    .all()
)

# created_at = when invoice was created
# due_date = when payment is expected
# paid_timestamp = when invoice was fully paid
 
# For billing volume, use created_at
billing_volume = (
    pl.Invoice.select(pl.attr.created_at.month(), pl.attr.id.count())
    .group_by(pl.attr.created_at.month())
    .all()
)
 
# For payment analysis, use paid_timestamp
payment_timing = (
    pl.Invoice.select(pl.attr.paid_timestamp.month(), pl.attr.id.count())
    .filter_by(pl.attr.status == "paid")
    .group_by(pl.attr.paid_timestamp.month())
    .all()
)

# Document what "accounts receivable" means in your context
# AR = sum of balance_due for invoices where:
# - status is 'open', 'unpaid', or 'partially_paid'
# - balance_due > 0
# - not including closed or draft invoices
# - using due_date for aging calculations
 
accounts_receivable = (
    pl.Invoice.select(pl.attr.totals.balance_due.sum())
    .filter_by(
        pl.attr.status == "open|unpaid|partially_paid",
        pl.attr.totals.balance_due > 0,
    )
    .all()
)

Next Steps

Explore related reporting topics

Revenue Reports

Analyze transaction revenue and payment trends with Revenue Reports documentation, track monthly revenue trends, and understand payment method performance over time.

Customer Analytics

Build customer lifetime value and behavior analysis with Customer Analytics guide, identify high-value customers, and track customer payment patterns for improved retention strategies.

Schema Reference

Key invoice fields commonly used in reporting.

Invoice schema

Common field combinations

Accounts receivable:

• totals.balance_due, status, due_date
• Filter to unpaid/partially_paid with positive balance
• Calculate aging based on due_date

Collection analysis:

• totals.total, totals.paid, totals.balance_due
• Calculate collection rate: paid / total
• Track payment velocity

Customer analysis:

• payer.account_id for grouping
• billing_schedule_id to identify recurring vs one-time
• created_at for customer timeline

Financial reporting:

• created_at for billing period
• paid_timestamp for collection period
• status for invoice lifecycle tracking