Crypto Payment Analytics: Track Revenue, Conversion Rates, and Customer LTV

Data You Have Available

Every Paychainly transaction includes: amount, timestamp, user ID, txHash, from address, to address, and block number. Your webhook handler should persist all of this for analytics.

Key Metrics to Track

1. Payment Conversion Rate

Sessions started vs. sessions that received payment:

SELECT
  DATE_TRUNC('day', created_at) as date,
  COUNT(*) as sessions_created,
  COUNT(CASE WHEN status = 'confirmed' THEN 1 END) as payments_received,
  ROUND(COUNT(CASE WHEN status = 'confirmed' THEN 1 END)::numeric
        / COUNT(*) * 100, 1) as conversion_rate
FROM payment_sessions
GROUP BY 1 ORDER BY 1;

2. Daily Revenue

SELECT
  DATE_TRUNC('day', confirmed_at) as date,
  SUM(amount_usdt) as revenue,
  COUNT(*) as transactions,
  AVG(amount_usdt) as avg_order_value
FROM payments
WHERE status = 'confirmed'
GROUP BY 1 ORDER BY 1;

3. Customer Lifetime Value

SELECT
  user_id,
  COUNT(*) as total_payments,
  SUM(amount_usdt) as lifetime_value,
  MIN(confirmed_at) as first_payment,
  MAX(confirmed_at) as last_payment
FROM payments
WHERE status = 'confirmed'
GROUP BY user_id
ORDER BY lifetime_value DESC;

Visualizing in the Dashboard

Recharts (React) or Chart.js makes it easy to turn these queries into line charts and bar graphs. The Paychainly frontend uses Recharts — you can follow the same patterns in your Reports page.

Alerting on Anomalies

Set up a cron job that alerts you if daily revenue drops more than 30% from the 7-day average. This catches integration failures before your customers notice.