Building Financial Reports from Paystack Data

You could build reports by hitting the Paystack API every time someone asks "how much did we make last month?" But that is slow, rate-limited, and lacks your business context. Your payments ledger already has everything you need: amounts, fees, currencies, channels, timestamps, and order IDs.

The ledger is your single source of truth for financial data. If it is accurate (which your daily reconciliation job confirms), reports built on it are accurate too. No API calls needed, no rate limits to worry about, and queries complete in milliseconds instead of seconds.

This guide assumes you have the ledger schema from the payments ledger design guide with columns for gross_amount, fee_amount, net_amount, currency, channel, event_type, and created_at.

-- Monthly revenue summary
SELECT
  DATE_TRUNC('month', created_at) as month,
  currency,
  COUNT(*) as transaction_count,
  SUM(gross_amount) as gross_revenue,
  SUM(fee_amount) as total_fees,
  SUM(net_amount) as net_revenue,
  ROUND(AVG(gross_amount)) as avg_transaction_size,
  MAX(gross_amount) as largest_transaction
FROM payment_ledger
WHERE event_type = 'charge.success'
  AND created_at >= '2026-01-01'
GROUP BY DATE_TRUNC('month', created_at), currency
ORDER BY month DESC, currency;

// Generate and format the revenue report
async function generateRevenueReport(fromDate, toDate) {
  var result = await db.query(
    'SELECT DATE_TRUNC('month', created_at) as month, currency, '
    + 'COUNT(*) as count, SUM(gross_amount) as gross, '
    + 'SUM(fee_amount) as fees, SUM(net_amount) as net '
    + 'FROM payment_ledger WHERE event_type = $1 '
    + 'AND created_at >= $2 AND created_at < $3 '
    + 'GROUP BY DATE_TRUNC('month', created_at), currency '
    + 'ORDER BY month DESC',
    ['charge.success', fromDate, toDate]
  );

  // Format for display
  var report = 'REVENUE REPORT: ' + fromDate + ' to ' + toDate + '

';

  for (var i = 0; i < result.rows.length; i++) {
    var row = result.rows[i];
    report += row.month.toISOString().substring(0, 7) + ' (' + row.currency + '):
';
    report += '  Transactions: ' + row.count + '
';
    report += '  Gross Revenue: ' + formatCurrency(parseInt(row.gross), row.currency) + '
';
    report += '  Fees: ' + formatCurrency(parseInt(row.fees), row.currency) + '
';
    report += '  Net Revenue: ' + formatCurrency(parseInt(row.net), row.currency) + '

';
  }

  return report;
}

Understand which payment channels drive the most revenue and which cost the most in fees:

-- Channel performance for the current month
SELECT
  channel,
  currency,
  COUNT(*) as transaction_count,
  SUM(gross_amount) as total_volume,
  SUM(fee_amount) as total_fees,
  ROUND(SUM(fee_amount)::numeric * 100 / NULLIF(SUM(gross_amount), 0), 2) as effective_fee_rate,
  ROUND(AVG(gross_amount)) as avg_transaction_size,
  ROUND(COUNT(*)::numeric * 100 / SUM(COUNT(*)) OVER (PARTITION BY currency), 1) as pct_of_transactions
FROM payment_ledger
WHERE event_type = 'charge.success'
  AND created_at >= DATE_TRUNC('month', NOW())
GROUP BY channel, currency
ORDER BY total_volume DESC;

This report answers questions like: "What percentage of our revenue comes from card vs bank transfer?" and "Is USSD cost-effective given its fee rate and transaction volume?" If bank transfers have a lower fee rate but much smaller average transaction size, the overall fee savings might be negligible.

-- Refund analysis
SELECT
  DATE_TRUNC('month', created_at) as month,
  currency,
  COUNT(*) as refund_count,
  SUM(gross_amount) as total_refunded,
  (SELECT COUNT(*) FROM payment_ledger pl2
   WHERE pl2.event_type = 'charge.success'
   AND DATE_TRUNC('month', pl2.created_at) = DATE_TRUNC('month', pl.created_at)
   AND pl2.currency = pl.currency) as total_charges,
  ROUND(
    COUNT(*)::numeric * 100 /
    NULLIF((SELECT COUNT(*) FROM payment_ledger pl3
     WHERE pl3.event_type = 'charge.success'
     AND DATE_TRUNC('month', pl3.created_at) = DATE_TRUNC('month', pl.created_at)
     AND pl3.currency = pl.currency), 0), 2
  ) as refund_rate_pct
FROM payment_ledger pl
WHERE event_type LIKE 'refund%'
  AND created_at >= '2026-01-01'
GROUP BY DATE_TRUNC('month', created_at), currency
ORDER BY month DESC;

A rising refund rate needs investigation. Is it a product quality issue? A checkout UX problem where customers buy by mistake? A specific product or customer segment driving the refunds? Break down refunds by product or customer segment to find the root cause.

For the detailed refund accounting treatment, see the refund accounting guide.

The gap between "money earned" and "money in the bank" is the settlement delay. Track it explicitly:

-- Unsettled balance (money Paystack owes you)
SELECT
  currency,
  SUM(net_amount) as unsettled_amount,
  COUNT(*) as unsettled_count,
  MIN(created_at) as oldest_unsettled
FROM payment_ledger
WHERE event_type = 'charge.success'
  AND settled_at IS NULL
GROUP BY currency;

-- Settlement timeline (how long it takes to receive money)
SELECT
  currency,
  ROUND(AVG(EXTRACT(EPOCH FROM (settled_at - created_at)) / 86400), 1) as avg_days_to_settle,
  ROUND(MAX(EXTRACT(EPOCH FROM (settled_at - created_at)) / 86400), 1) as max_days_to_settle,
  COUNT(*) as sample_size
FROM payment_ledger
WHERE event_type = 'charge.success'
  AND settled_at IS NOT NULL
  AND created_at >= NOW() - INTERVAL '90 days'
GROUP BY currency;

The "unsettled balance" report shows how much cash is in transit between Paystack and your bank. If this number grows unexpectedly, settlements might be delayed. If the "oldest_unsettled" is more than a week old, investigate why that transaction has not been settled.

Send a daily financial summary to your team. This gives everyone visibility into payment health without logging into any dashboard:

// Daily summary job
async function dailySummary() {
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  var targetDate = yesterday.toISOString().split('T')[0];

  var charges = await db.query(
    'SELECT currency, COUNT(*) as count, '
    + 'SUM(gross_amount) as gross, SUM(fee_amount) as fees, '
    + 'SUM(net_amount) as net '
    + 'FROM payment_ledger WHERE event_type = $1 '
    + 'AND DATE(created_at) = $2 GROUP BY currency',
    ['charge.success', targetDate]
  );

  var refunds = await db.query(
    'SELECT currency, COUNT(*) as count, SUM(gross_amount) as total '
    + 'FROM payment_ledger WHERE event_type LIKE $1 '
    + 'AND DATE(created_at) = $2 GROUP BY currency',
    ['refund%', targetDate]
  );

  var pending = await db.query(
    'SELECT COUNT(*) as count FROM orders '
    + 'WHERE status IN ($1, $2) AND created_at < NOW() - INTERVAL '1 hour'',
    ['pending', 'processing']
  );

  var message = 'Daily Payment Summary - ' + targetDate + '

';
  message += 'CHARGES:
';
  for (var i = 0; i < charges.rows.length; i++) {
    var c = charges.rows[i];
    message += '  ' + c.currency + ': ' + c.count + ' transactions, '
      + 'Gross ' + formatCurrency(parseInt(c.gross), c.currency) + ', '
      + 'Fees ' + formatCurrency(parseInt(c.fees), c.currency) + ', '
      + 'Net ' + formatCurrency(parseInt(c.net), c.currency) + '
';
  }

  if (refunds.rows.length > 0) {
    message += '
REFUNDS:
';
    for (var j = 0; j < refunds.rows.length; j++) {
      var r = refunds.rows[j];
      message += '  ' + r.currency + ': ' + r.count + ' refunds, '
        + formatCurrency(parseInt(r.total), r.currency) + '
';
    }
  }

  message += '
PENDING ORDERS: ' + pending.rows[0].count;

  // Send via Slack or email
  await sendDailySummary(message);
}

-- Month-over-month growth
WITH monthly AS (
  SELECT
    DATE_TRUNC('month', created_at) as month,
    currency,
    SUM(net_amount) as revenue
  FROM payment_ledger
  WHERE event_type = 'charge.success'
    AND created_at >= NOW() - INTERVAL '6 months'
  GROUP BY DATE_TRUNC('month', created_at), currency
)
SELECT
  m.month,
  m.currency,
  m.revenue,
  LAG(m.revenue) OVER (PARTITION BY m.currency ORDER BY m.month) as prev_month_revenue,
  CASE
    WHEN LAG(m.revenue) OVER (PARTITION BY m.currency ORDER BY m.month) > 0
    THEN ROUND((m.revenue - LAG(m.revenue) OVER (PARTITION BY m.currency ORDER BY m.month))::numeric
      * 100 / LAG(m.revenue) OVER (PARTITION BY m.currency ORDER BY m.month), 1)
    ELSE NULL
  END as growth_pct
FROM monthly m
ORDER BY m.currency, m.month;

Growth trends matter more than absolute numbers for most audiences. "Revenue grew 15% month-over-month" tells a clearer story than "Revenue was NGN 5,000,000." Include both in your reports.

Different stakeholders need reports in different formats:

• Slack messages: For the daily summary, sent to a #payments channel. Keep it short and scannable.
• CSV exports: For your accountant who works in Excel. Include raw numbers, not formatted strings.
• JSON API: For your admin dashboard. Return structured data that the frontend can render as charts and tables.
• PDF reports: For board meetings and investor updates. Generate from a template with your branding.

// CSV export of monthly data
async function exportMonthlyCsv(year, month) {
  var result = await db.query(
    'SELECT reference, event_type, gross_amount, fee_amount, '
    + 'net_amount, currency, channel, customer_email, created_at '
    + 'FROM payment_ledger '
    + 'WHERE DATE_TRUNC('month', created_at) = $1 '
    + 'ORDER BY created_at',
    [year + '-' + String(month).padStart(2, '0') + '-01']
  );

  var header = 'Reference,Event,Gross,Fees,Net,Currency,Channel,Customer,Date
';
  var rows = result.rows.map(function(r) {
    return [
      r.reference, r.event_type, r.gross_amount, r.fee_amount,
      r.net_amount, r.currency, r.channel, r.customer_email,
      r.created_at.toISOString(),
    ].join(',');
  }).join('
');

  return header + rows;
}

For the data warehouse approach to report building, see the data warehouse export guide.