Lernen KPI Calculations: Conversion, Retention, Churn, ARPU

Abschnitt 1. Kapitel 10

single

Swipe um das Menü anzuzeigen

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;

Aufgabe

Wischen, um mit dem Codieren zu beginnen

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.

Lösung

Wechseln Sie zum Desktop, um in der realen Welt zu übenFahren Sie dort fort, wo Sie sind, indem Sie eine der folgenden Optionen verwenden

War alles klar?

Danke für Ihr Feedback!

Abschnitt 1. Kapitel 10

single

Fragen Sie AI

Fragen Sie alles oder probieren Sie eine der vorgeschlagenen Fragen, um unser Gespräch zu beginnen