Revenue Reports

Track and analyze revenue across dimensions like status, time, and payment methods

Revenue reports help you understand financial performance, identify trends, and make data-driven decisions. These examples demonstrate common revenue analysis patterns from simple status breakdowns to complex multi-dimensional analysis. Use these as templates for your own custom revenue reporting needs.

Revenue reports focus on transaction amounts aggregated by various dimensions. Always filter to processed payments when calculating actual revenue, as pending or failed transactions don't represent realized income. Consider fees when calculating net revenue.

Prerequisites

Before building revenue reports, understand:

Report query mechanics

Learn how to build queries with select, filter, and group by.

Building report queries

Aggregate functions

Understand sum, avg, count for calculating revenue metrics.

Functions reference

Date functions

Use date functions for time-based revenue analysis.

Date functions

Revenue by Status

report.py

import payload
pl = payload.Session('secret_key_3bW9...', api_version='v2')
 
# Calculate total revenue grouped by transaction status
 
print("Revenue by Status Report")
print("=" * 50)
print()
 
results = pl.Transaction.select(
    pl.attr.status,
    pl.attr.amount.sum()
).filter_by(
    pl.attr.type == 'payment'
).group_by(
    pl.attr.status
).all()
 
print(f"{'Status':<15} | {'Total Revenue':>15}")
print("-" * 50)
 
for row in results:
    print(f"{row.status:<15} | ${getattr(row, 'sum(amount)'):>14,.2f}")
 
print(f"\nTotal transactions analyzed: {len(results)} status groups")

Calculate total revenue grouped by transaction status.

What this query does

This query aggregates payment transaction amounts by their status, showing how much revenue is in each transaction state (processed, pending, failed, etc.).

Query components:

Component	Description
Select	Transaction status and sum of amounts
Filter	Payment transactions only
Group	Results by status field
Output	Total amount per status

Query breakdown

results = (
    pl.Transaction.select(
        pl.attr.status, pl.attr.amount.sum()  # Grouping dimension  # Revenue aggregate
    )
    .filter_by(pl.attr.type == "payment")  # Only payment transactions
    .group_by(pl.attr.status)  # Group by status
    .all()
)

Result structure:

[
  {
    "status": "processed",
    "sum(amount)": 125000.0
  },
  {
    "status": "pending",
    "sum(amount)": 15000.0
  },
  {
    "status": "failed",
    "sum(amount)": 2500.0
  }
]

Analysis tips:

Compare processed vs pending amounts
Calculate success rate: processed / (processed + failed)
Monitor failed amount trends for issues
Track pending amounts for cash flow planning

Monthly Revenue Trend

report.py

import payload
pl = payload.Session('secret_key_3bW9...', api_version='v2')
from datetime import datetime
 
# Track revenue over time with monthly aggregation
 
print("Monthly Revenue Trend Report")
print("=" * 70)
print()
 
results = pl.Transaction.select(
    pl.attr.created_at.year(),
    pl.attr.created_at.month(),
    pl.attr.amount.sum(),
    pl.attr.id.count()
).filter_by(
    pl.attr.type == 'payment',
    pl.attr.status == 'processed'
).group_by(
    pl.attr.created_at.year(),
    pl.attr.created_at.month()
).order_by(
    pl.attr.created_at.year().desc(),
    pl.attr.created_at.month().desc()
).all()
 
months = ['', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
          'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
 
print(f"{'Year-Month':<15} | {'Revenue':>15} | {'Transactions':>12}")
print("-" * 70)
 
for row in results:
    year = getattr(row, 'year(created_at)')
    month = getattr(row, 'month(created_at)')
    revenue = getattr(row, 'sum(amount)')
    count = getattr(row, 'count(id)')
 
    month_name = months[month]
    print(f"{year}-{month_name:<10} | ${revenue:>14,.2f} | {count:>12,}")
 
print(f"\nTotal periods: {len(results)}")

Track revenue over time with monthly aggregation.

What this query does

This query groups processed payment transactions by year and month, calculating total revenue and transaction count for each period to identify trends and seasonal patterns.

Query components:

Component	Description
Select	Year, month, sum of amounts, transaction count
Filter	Processed payment transactions
Group	Results by year and month
Order	Chronologically (newest first)

Query breakdown

results = (
    pl.Transaction.select(
        pl.attr.created_at.year(),  # Year dimension
        pl.attr.created_at.month(),  # Month dimension
        pl.attr.amount.sum(),  # Revenue total
        pl.attr.id.count(),  # Transaction count
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .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(amount)": 125000.0,
    "count(id)": 450
  },
  {
    "year(created_at)": 2024,
    "month(created_at)": 2,
    "sum(amount)": 118000.0,
    "count(id)": 425
  }
]

Analysis tips:

Calculate month-over-month growth: (current - previous) / previous
Identify seasonal patterns across years
Compare average transaction size: sum(amount) / count(id)
Track transaction volume alongside revenue

Extended analysis

Calculate growth rates:

# Get results and calculate growth
results = list(monthly_revenue)  # Convert to list
for i in range(len(results) - 1):
    current = results[i]["sum(amount)"]
    previous = results[i + 1]["sum(amount)"]
    growth = ((current - previous) / previous) * 100
    print(f"Month {i}: {growth:.1f}% growth")

Visualize trends:

Plot monthly revenue on line chart
Add trend line to identify direction
Highlight outlier months for investigation

Transaction Patterns by Day of Week

report.py

import payload
pl = payload.Session('secret_key_3bW9...', api_version='v2')
 
# Analyze transaction patterns across days of the week
 
print("Daily Transaction Patterns Report")
print("=" * 70)
print()
 
results = pl.Transaction.select(
    pl.attr.created_at.dayname(),
    pl.attr.amount.avg(),
    pl.attr.id.count()
).filter_by(
    pl.attr.type == 'payment',
    pl.attr.status == 'processed'
).group_by(
    pl.attr.created_at.dayofweek()
).order_by(
    pl.attr.created_at.dayofweek()
).all()
 
print(f"{'Day of Week':<15} | {'Avg Amount':>12} | {'Count':>10}")
print("-" * 70)
 
max_count = 0
busiest_day = ''
 
for row in results:
    day = getattr(row, 'dayname(created_at)')
    avg_amt = getattr(row, 'avg(amount)')
    count = getattr(row, 'count(id)')
 
    print(f"{day:<15} | ${avg_amt:>11,.2f} | {count:>10,}")
 
    if count > max_count:
        max_count = count
        busiest_day = day
 
print(f"\nBusiest day: {busiest_day} with {max_count:,} transactions")

Analyze transaction patterns across days of the week.

What this query does

This query analyzes transaction patterns by grouping processed payments by day of week, showing average transaction size and volume for each day to reveal weekly patterns.

Query components:

Component	Description
Select	Day name, average amount, transaction count
Filter	Processed payment transactions
Group	Results by day of week
Order	By day of week (Monday through Sunday)

Query breakdown

results = (
    pl.Transaction.select(
        pl.attr.created_at.dayname(),  # Day name (Monday, Tuesday, etc.)
        pl.attr.amount.avg(),  # Average transaction size
        pl.attr.id.count(),  # Transaction volume
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(pl.attr.created_at.dayofweek())  # Group by numeric day (0-6)
    .order_by(pl.attr.created_at.dayofweek())  # Order Monday through Sunday
    .all()
)

Result structure:

[
  {
    "dayname(created_at)": "Monday",
    "avg(amount)": 285.5,
    "count(id)": 450
  },
  {
    "dayname(created_at)": "Tuesday",
    "avg(amount)": 312.75,
    "count(id)": 380
  }
]

Analysis tips:

Identify busiest day: highest count(id)
Find highest value day: highest avg(amount) * count(id)
Compare weekday vs weekend patterns
Look for unusual patterns requiring investigation

Extended analysis

Calculate daily revenue totals:

for row in results:
    day = row["dayname(created_at)"]
    avg = row["avg(amount)"]
    count = row["count(id)"]
    total = avg * count
    print(f"{day}: ${total:,.2f} total revenue from {count} transactions")

Identify opportunities:

Boost marketing on slow days
Scale resources for peak days
Investigate unusual patterns

Multi-Dimensional Revenue Analysis

report.py

import payload
 
pl = payload.Session("secret_key_3bW9...", api_version="v2")
 
from collections import defaultdict
 
# Multi-dimensional analysis by time and payment method
 
print("Multi-Dimensional Revenue Analysis")
print("=" * 100)
print()
 
results = (
    pl.Transaction.select(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.payment_method.type,
        pl.attr.amount.sum(),
        pl.attr.id.count(),
        pl.attr.amount.avg(),
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.payment_method.type,
    )
    .order_by(pl.attr.created_at.desc(), pl.attr.payment_method.type)
    .all()
)
 
months = [
    "",
    "Jan",
    "Feb",
    "Mar",
    "Apr",
    "May",
    "Jun",
    "Jul",
    "Aug",
    "Sep",
    "Oct",
    "Nov",
    "Dec",
]
 
print(
    f"{'Period':<12} | {'Method':<8} | {'Revenue':>12} | {'Count':>6} | {'Avg Amount':>12}"
)
print("-" * 100)
 
method_totals = defaultdict(float)
 
for row in results:
    year = getattr(row, "year(created_at)")
    month = getattr(row, "month(created_at)")
    method = (
        row.payment_method.get("type", "unknown")
        if hasattr(row, "payment_method")
        else "unknown"
    )
    revenue = getattr(row, "sum(amount)")
    count = getattr(row, "count(id)")
    avg = getattr(row, "avg(amount)")
 
    period = f"{year}-{months[month]}"
    print(
        f"{period:<12} | {method:<8} | ${revenue:>11,.2f} | {count:>6,} | ${avg:>11,.2f}"
    )
 
    method_totals[method] += revenue
 
print("\n" + "=" * 100)
print("Payment Method Summary:")
for method, total in sorted(method_totals.items(), key=lambda x: x[1], reverse=True):
    print(f"  {method}: ${total:,.2f}")

Analyze revenue across multiple dimensions simultaneously.

What this query does

This query combines time-based and payment method analysis by grouping processed transactions by year, month, and payment method type, calculating revenue totals, transaction counts, and average transaction sizes across all dimensions.

Query components:

Component	Description
Select	Year, month, payment method type, multiple aggregates
Filter	Processed payment transactions
Group	Results by year, month, and payment method
Order	Chronologically, then by method

Query breakdown

# Query monthly revenue with sender payment method expanded
txns = (
    pl.Transaction.select(
        pl.attr.created_at,
        pl.attr.amount,
        pl.attr.sender.method,  # Expanded payment method
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .all()
)
 
# Group by year, month, and payment method type client-side
breakdown = {}
for txn in txns:
    d = datetime.fromisoformat(str(txn.created_at).replace("Z", "+00:00"))
    method_type = getattr(getattr(txn, "sender", None) or object(), "method", None)
    method_type = getattr(method_type, "type", None) or "unknown"
    key = f"{d.year}-{d.month}-{method_type}"
 
    if key not in breakdown:
        breakdown[key] = {
            "year": d.year,
            "month": d.month,
            "method_type": method_type,
            "total": 0,
            "count": 0,
        }
    breakdown[key]["total"] += txn.amount or 0
    breakdown[key]["count"] += 1
 
results = sorted(
    breakdown.values(),
    key=lambda x: (-x["year"], -x["month"], x["method_type"]),
)

Result structure:

[
  {
    "year(created_at)": 2024,
    "month(created_at)": 3,
    "payment_method.type": "card",
    "sum(amount)": 95000.0,
    "count(id)": 380,
    "avg(amount)": 250.0
  },
  {
    "year(created_at)": 2024,
    "month(created_at)": 3,
    "payment_method.type": "ach",
    "sum(amount)": 30000.0,
    "count(id)": 70,
    "avg(amount)": 428.57
  }
]

Analysis tips:

Compare revenue contribution by method
Track method preference trends over time
Identify high-value methods (high avg amount)
Calculate method processing costs

Extended analysis

Calculate method market share:

from collections import defaultdict
 
# Group by month
monthly_totals = defaultdict(float)
method_revenue = defaultdict(lambda: defaultdict(float))
 
for row in results:
    month_key = f"{row['year(created_at)']}-{row['month(created_at)']}"
    method = row["payment_method.type"]
    revenue = row["sum(amount)"]
 
    monthly_totals[month_key] += revenue
    method_revenue[month_key][method] = revenue
 
# Calculate market share
for month, total in monthly_totals.items():
    print(f"\n{month} Market Share:")
    for method, revenue in method_revenue[month].items():
        share = (revenue / total) * 100
        print(f"  {method}: {share:.1f}%")

Cost analysis:

# Calculate net revenue after processing fees
FEES = {
    "card": 0.029,  # 2.9% for cards
    "ach": 0.008,  # 0.8% for bank transfer
}
 
for row in results:
    method = row["payment_method.type"]
    revenue = row["sum(amount)"]
    fee_rate = FEES.get(method, 0)
    fees = revenue * fee_rate
    net = revenue - fees
    print(f"{method}: ${revenue:,.2f} gross, ${net:,.2f} net")

Common Patterns

Reusable patterns for revenue analysis.

current_year = datetime.now().year
 
yoy = (
    pl.Transaction.select(
        pl.attr.created_at.year(), pl.attr.created_at.month(), pl.attr.amount.sum()
    )
    .filter_by(
        pl.attr.type == "payment",
        pl.attr.status == "processed",
        pl.attr.created_at.year() == f"{current_year - 1}|{current_year}",
    )
    .group_by(pl.attr.created_at.year(), pl.attr.created_at.month())
    .order_by("desc(year(created_at))", "month(created_at)")
    .all()
)

quarterly = (
    pl.Transaction.select(
        pl.attr.created_at.year(),
        pl.attr.created_at.quarter(),
        pl.attr.amount.sum(),
        pl.attr.id.count(),
        pl.attr.amount.avg(),
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(pl.attr.created_at.year(), pl.attr.created_at.quarter())
    .order_by("desc(year(created_at))", "desc(quarter(created_at))")
    .all()
)

top_days = (
    pl.Transaction.select(
        pl.attr.created_at.date(), pl.attr.amount.sum(), pl.attr.id.count()
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(pl.attr.created_at.date())
    .order_by("desc(sum(amount))")
    .limit(10)
    .all()
)

# Get gross revenue (all processed payments)
payments = (
    pl.Transaction.select(pl.attr.amount.sum())
    .filter_by(
        pl.attr.type == "payment",
        pl.attr.status == "processed",
        pl.attr.created_at.year() == 2024,
    )
    .all()
)
 
# Get refunds in the same period
refunds = (
    pl.Transaction.select(pl.attr.amount.sum())
    .filter_by(
        pl.attr.type == "refund",
        pl.attr.status == "processed",
        pl.attr.created_at.year() == 2024,
    )
    .all()
)
 
# Calculate net (gross payments minus refunds)
gross_revenue = payments[0]["sum(amount)"] or 0
total_refunds = refunds[0]["sum(amount)"] or 0
net_revenue = gross_revenue - total_refunds
 
print(f"Gross Revenue: ${gross_revenue:,.2f}")
print(f"Refunds: ${total_refunds:,.2f}")
print(f"Net Revenue: ${net_revenue:,.2f}")

Best Practices

Tips for effective revenue reporting.

Only processed transactions represent realized revenue:

# Correct: Only processed transactions
revenue = (
    pl.Transaction.select(pl.attr.amount.sum())
    .filter_by(
        pl.attr.type == "payment", pl.attr.status == "processed"  # Critical filter
    )
    .all()
)
 
# Incomplete: Includes pending/failed
incomplete = (
    pl.Transaction.select(pl.attr.amount.sum())
    .filter_by(
        pl.attr.type
        == "payment"
        # Missing status filter - overstates revenue
    )
    .all()
)

Consider refund transactions in revenue calculations:

# Net revenue including refunds
net = (
    pl.Transaction.select(pl.attr.amount.sum())
    .filter_by(
        (pl.attr.type == "payment") | (pl.attr.type == "refund"),
        pl.attr.status == "processed",
    )
    .all()
)

Be explicit about time zones for accurate reports:

from datetime import datetime, timezone
 
# Use UTC consistently
utc_revenue = (
    pl.Transaction.select(pl.attr.created_at.date(), pl.attr.amount.sum())
    .filter_by(pl.attr.created_at >= datetime(2024, 1, 1, tzinfo=timezone.utc))
    .group_by(pl.attr.created_at.date())
    .all()
)

Make revenue definitions explicit:

# Document what "revenue" means in this context
# Gross revenue = sum of processed payment amounts
# Net revenue = gross revenue minus refunds
# Excludes: pending transactions, failed attempts
# Includes: all processed payments regardless of settlement status
# Time zone: UTC
# Date field: created_at (transaction creation time)
 
start_date = "2024-01-01T00:00:00Z"
 
gross_revenue = (
    pl.Transaction.select(pl.attr.amount.sum())
    .filter_by(
        pl.attr.type == "payment",
        pl.attr.status == "processed",
        pl.attr.created_at >= start_date,
    )
    .all()
)
 
refunds = (
    pl.Transaction.select(pl.attr.amount.sum())
    .filter_by(
        pl.attr.type == "refund",
        pl.attr.status == "processed",
        pl.attr.created_at >= start_date,
    )
    .all()
)
 
net_revenue = (gross_revenue[0]["sum(amount)"] or 0) - (refunds[0]["sum(amount)"] or 0)

Next Steps

Explore related reporting topics

Customer Analytics

Analyze customer behavior, lifetime value, and segmentation with Customer Analytics documentation, segment customers by spending patterns, and identify high-value customers for retention strategies.

Query Mechanics

Deep dive into query building techniques with Building Report Queries guide, master select, filter, and group by operations, and optimize report performance for large datasets.

Building Report Queries Customer Analytics