学ぶ Retention Curve Analysis | Analytical SQL Techniques

セクション 1. 章 16

single

メニューを表示するにはスワイプしてください

Retention curve analysis is a crucial technique for understanding how users or customers continue to engage with a product or service over time. In SQL, retention curves are typically calculated using cohort analysis, where users are grouped by their signup or acquisition date, and their continued activity is tracked in subsequent periods. By visualizing retention curves, you can identify how well your platform retains users after their initial interaction, spot trends in user engagement, and measure the effectiveness of product changes or marketing campaigns.

Definition

A retention curve shows the proportion of a cohort (a group of users who started using a product at the same time) that remains active or engaged at successive time intervals after their initial activity. In business terms, the retention curve helps you understand user loyalty, product stickiness, and the likelihood that users will return or continue to use your service over time. A steeply declining curve suggests poor retention, while a flatter curve indicates strong ongoing engagement.


              123456789101112131415161718192021222324252627282930313233343536373839404142434445
            
WITH user_cohorts AS (
  SELECT
    user_id,
    signup_date,
    DATE_TRUNC('week', signup_date) AS cohort_week
  FROM users
  WHERE signup_date >= '2023-01-01'
),
subscription_activity AS (
  SELECT
    u.user_id,
    u.cohort_week,
    s.start_date,
    FLOOR(
      (s.start_date - u.signup_date) / 7.0
    ) AS weeks_since_signup
  FROM user_cohorts u
  JOIN subscriptions s ON u.user_id = s.user_id
  WHERE s.start_date >= u.signup_date
    AND s.start_date < u.signup_date + INTERVAL '4 weeks'
),
retention_counts AS (
  SELECT
    cohort_week,
    weeks_since_signup,
    COUNT(DISTINCT user_id) AS active_users
  FROM subscription_activity
  GROUP BY cohort_week, weeks_since_signup
),
cohort_sizes AS (
  SELECT
    cohort_week,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM user_cohorts
  GROUP BY cohort_week
)
SELECT
  r.cohort_week,
  r.weeks_since_signup,
  r.active_users,
  c.cohort_size,
  ROUND(100.0 * r.active_users / c.cohort_size, 2) AS retention_rate_percent
FROM retention_counts r
JOIN cohort_sizes c ON r.cohort_week = c.cohort_week
ORDER BY r.cohort_week, r.weeks_since_signup;

Suppose the product team wants to see a retention curve for users who signed up in the last 6 months. This allows them to compare how different signup cohorts behave and identify if recent product changes have improved user retention.


              123456789101112131415161718192021222324
            
WITH signup_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('week', signup_date) AS cohort_week
  FROM users
  WHERE signup_date >= '2023-01-01'
),
activity_weeks AS (
  SELECT
    u.user_id,
    u.cohort_week,
    s.start_date,
    FLOOR((s.start_date - u.cohort_week::date) / 7.0) AS week_number
  FROM signup_cohorts u
  JOIN subscriptions s ON u.user_id = s.user_id
  WHERE s.start_date >= u.cohort_week
)
SELECT
  cohort_week,
  week_number,
  COUNT(DISTINCT user_id) AS retained_users
FROM activity_weeks
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;

タスク

スワイプしてコーディングを開始

Write a SQL query to calculate weekly retention rates for each signup cohort.

Use the users and subscriptions tables.
Group users into cohorts based on DATE_TRUNC('week', signup_date) as cohort_week. Only include users where signup_date >= '2023-01-01'.
For each cohort, calculate week_number as the number of full weeks between cohort_week and subscription start_date using FLOOR((start_date - cohort_week::date) / 7.0).
Calculate cohort_size as the count of distinct users per cohort_week.
Calculate retained_users as the count of distinct users with a subscription start_date in that week.
Calculate retention_rate_percent as ROUND(100.0 * retained_users / cohort_size, 2).
Return columns: cohort_week, week_number, retained_users, cohort_size, retention_rate_percent.
Order by cohort_week, week_number.

解答

実践的な練習のためにデスクトップに切り替える下記のオプションのいずれかを利用して、現在の場所から続行する

すべて明確でしたか？

フィードバックありがとうございます！

セクション 1. 章 16

single

AIに質問する

何でも質問するか、提案された質問の1つを試してチャットを始めてください