Impara KPI Calculations: Conversion, Retention, Churn, ARPU

Sezione 1. Capitolo 10

single

Scorri per mostrare il menu

Understanding and calculating key performance indicators (KPIs) is crucial for monitoring business health and evaluating growth strategies. In SQL-based analytics, you can directly compute metrics like conversion rate, retention rate, churn rate, and average revenue per user (ARPU) by aggregating and joining appropriate tables. These KPIs are foundational for SaaS, e-commerce, and other recurring-revenue businesses, and they help you answer questions such as "How many leads become customers?", "How many users stay active month-to-month?", "How much revenue does each user generate?", and "What percentage of users leave each month?"

Definition

Conversion Rate: The percentage of leads that become paying customers.
Formula: (Number of converted leads) / (Total leads) * 100;
Retention Rate: The percentage of users who remain active after a given period (e.g., 30 days).
Formula: (Number of users active after period) / (Number of users at start of period) * 100;
Churn Rate: The percentage of users who cancel or do not renew in a period.
Formula: (Number of users lost during period) / (Number of users at start of period) * 100;
ARPU (Average Revenue Per User): The average revenue generated per user in a period.
Formula: (Total revenue in period) / (Number of active users in period).


              1234567891011121314
            
SELECT
  camp.campaign_id,
  camp.name AS campaign_name,
  COUNT(DISTINCT l.lead_id) AS total_leads,
  COUNT(DISTINCT conv.conversion_id) AS converted_leads,
  ROUND(
    COUNT(DISTINCT conv.conversion_id)::decimal
    / NULLIF(COUNT(DISTINCT l.lead_id), 0) * 100, 2
  ) AS conversion_rate_percent
FROM campaigns camp
JOIN leads l ON l.campaign_id = camp.campaign_id
LEFT JOIN conversions conv ON conv.lead_id = l.lead_id
GROUP BY camp.campaign_id, camp.name
ORDER BY conversion_rate_percent DESC;

Suppose the SaaS CFO wants to review monthly ARPU and churn rate for the last year. These metrics provide insights into revenue efficiency and customer retention. ARPU is typically calculated by dividing total subscription revenue in a month by the number of active users that month. Churn rate is found by dividing the number of users who cancel (or whose subscriptions expire and are not renewed) during the month by the number of users at the start of the month.


              123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657
            
-- Calculate monthly ARPU and churn rate for the last 12 months
WITH months AS (
  SELECT
    date_trunc('month', d)::date AS month_start
  FROM generate_series(
    (SELECT date_trunc('month', CURRENT_DATE) - INTERVAL '11 months'),
    date_trunc('month', CURRENT_DATE),
    INTERVAL '1 month'
  ) AS d
),
monthly_revenue AS (
  SELECT
    date_trunc('month', payment_date)::date AS month_start,
    SUM(amount) AS total_revenue
  FROM payments
  WHERE status = 'Completed'
  GROUP BY month_start
),
monthly_active_users AS (
  SELECT
    m.month_start,
    COUNT(DISTINCT s.user_id) AS active_users
  FROM months m
  JOIN subscriptions s
    ON s.start_date <= (m.month_start + INTERVAL '1 month' - INTERVAL '1 day')
    AND (s.end_date IS NULL OR s.end_date >= m.month_start)
  GROUP BY m.month_start
),
monthly_churned_users AS (
  SELECT
    date_trunc('month', end_date)::date AS month_start,
    COUNT(DISTINCT user_id) AS churned_users
  FROM subscriptions
  WHERE status IN ('Expired', 'Cancelled')
    AND end_date >= (SELECT MIN(month_start) FROM months)
    AND end_date < (SELECT MAX(month_start) + INTERVAL '1 month' FROM months)
  GROUP BY month_start
)
SELECT
  m.month_start,
  COALESCE(r.total_revenue, 0) AS total_revenue,
  COALESCE(a.active_users, 0) AS active_users,
  ROUND(
    COALESCE(r.total_revenue, 0) / NULLIF(a.active_users, 0),
    2
  ) AS arpu,
  COALESCE(c.churned_users, 0) AS churned_users,
  ROUND(
    COALESCE(c.churned_users, 0)::decimal
    / NULLIF(a.active_users, 0) * 100,
    2
  ) AS churn_rate_percent
FROM months m
LEFT JOIN monthly_revenue r ON r.month_start = m.month_start
LEFT JOIN monthly_active_users a ON a.month_start = m.month_start
LEFT JOIN monthly_churned_users c ON c.month_start = m.month_start
ORDER BY m.month_start;

Compito

Scorri per iniziare a programmare

Write a query to calculate the retention rate for users 30 days after signup.

For each signup date, count the number of users who signed up on that date.
For each signup date, count how many of those users still have an active subscription 30 days after their signup date.
Calculate the retention rate as the percentage of users retained after 30 days, rounded to two decimal places.

Soluzione

Cambia al desktop per esercitarti nel mondo realeContinua da dove ti trovi utilizzando una delle opzioni seguenti

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 1. Capitolo 10

single

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione