Funnel analysis and clv

Funnel analysis tracks drop-off at each stage of a process: how many orders were placed, approved, shipped, and delivered? CLV (Customer Lifetime Value) estimates the total revenue a customer generates over their relationship with you. To count several funnel stages in one pass we will use COUNT(*) FILTER (WHERE ...), a PostgreSQL clause that makes an aggregate count only the rows matching its condition. Treat it as a sneak peek; it gets full treatment alongside the other PostgreSQL power features.

funnel.sql
sql
-- Order status funnel with conversion rates
WITH status_counts AS (
  SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE order_status IN ('approved', 'invoiced', 'processing', 'shipped', 'delivered')) AS approved,
    COUNT(*) FILTER (WHERE order_status IN ('shipped', 'delivered')) AS shipped,
    COUNT(*) FILTER (WHERE order_status = 'delivered') AS delivered
  FROM orders
)
SELECT
  total_orders,
  approved,
  ROUND(100.0 * approved / total_orders, 1) AS approved_pct,
  shipped,
  ROUND(100.0 * shipped / total_orders, 1) AS shipped_pct,
  delivered,
  ROUND(100.0 * delivered / total_orders, 1) AS delivered_pct
FROM status_counts;

FILTER clause counts rows matching each condition. The percentage shows conversion at each funnel stage.

Order funnel

clv.sql
sql
-- Customer Lifetime Value calculation
SELECT
  o.customer_id,
  COUNT(DISTINCT o.order_id) AS total_orders,
  SUM(p.payment_value) AS total_revenue,
  ROUND(AVG(p.payment_value)::NUMERIC, 2) AS avg_order_value,
  MIN(o.order_purchase_timestamp) AS first_order,
  MAX(o.order_purchase_timestamp) AS last_order,
  EXTRACT(DAY FROM MAX(o.order_purchase_timestamp) - MIN(o.order_purchase_timestamp)) AS lifespan_days
FROM orders o
JOIN order_payments p ON o.order_id = p.order_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT o.order_id) > 1
ORDER BY total_revenue DESC
LIMIT 20;

CLV components: total orders, total revenue, average order value, and customer lifespan. Filtering to customers with >1 order shows repeat buyers.

Another critical business metric is year-over-year growth. We can use LAG with an offset of 12 to compare each month to the same month last year.

yoy_growth.sql
sql
-- Year-over-Year growth using LAG
WITH monthly_rev AS (
  SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
    SUM(oi.price) AS revenue
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
)
SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
  ROUND(
    ((revenue - LAG(revenue, 12) OVER (ORDER BY month))
    / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100)::NUMERIC, 1
  ) AS yoy_growth_pct
FROM monthly_rev
ORDER BY month;

LAG with offset 12 looks back 12 months. NULLIF prevents division by zero when last year has no data.

SQL-based CLV is great for estimation and segmentation because it shows you who your most valuable customers are and their spending patterns. For predictive CLV (forecasting future value), you would use ML models. But SQL CLV is where every data team starts, and it provides 80% of the insight with 20% of the effort.

AI prompt: Generate advanced analytics queries

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Order status funnel

Loading practice…