Oppiskele Understanding Churn | Retention and Churn Analysis

Understanding churn is critical for product analysts aiming to maintain a healthy, engaged user base. Churn refers to the phenomenon where users stop engaging with a product or service. Users may churn for various reasons: lack of perceived value, better alternatives, poor user experience, or simply because their needs have changed. Measuring churn helps you identify how many users are leaving and when, giving you the opportunity to address underlying issues and improve retention. The most common way to measure churn is by tracking users who have not interacted with your product over a specific period, such as 30 days.


              123456789
            
-- Identify users who have not logged in for the past 30 days (potential churn)
SELECT u.user_id,
  MAX(l.login_date) AS last_login
FROM users u
LEFT JOIN logins l ON u.user_id = l.user_id
GROUP BY u.user_id
HAVING
  MAX(l.login_date) < CURRENT_DATE - INTERVAL '30 days'
   OR MAX(l.login_date) IS NULL;

The logic in this query works by joining the users table with the logins table, so you can see each user's login activity. By grouping by user_id and finding each user's most recent login date, you can compare this date to the current date minus 30 days. If the last login occurred more than 30 days ago, or if the user has never logged in, that user is considered to have potentially churned. This approach allows you to systematically flag users who may require re-engagement efforts.


              12345678910111213141516171819202122232425262728293031323334
            
-- Calculate monthly churn rate: percentage of users who churned each month
WITH last_logins AS (
  SELECT
    u.user_id,
    MAX(l.login_date) AS last_login
  FROM users u
  LEFT JOIN logins l ON u.user_id = l.user_id
  GROUP BY u.user_id
),
monthly_churn AS (
  SELECT
    DATE_TRUNC('month', last_login) AS churn_month,
    COUNT(user_id) AS churned_users
  FROM last_logins
  WHERE
    last_login IS NOT NULL
    AND last_login < CURRENT_DATE - INTERVAL '30 days'
  GROUP BY DATE_TRUNC('month', last_login)
),
monthly_active AS (
  SELECT
    DATE_TRUNC('month', login_date) AS month,
    COUNT(DISTINCT user_id) AS active_users
  FROM logins
  GROUP BY DATE_TRUNC('month', login_date)
)
SELECT
  m.churn_month,
  m.churned_users,
  a.active_users,
  ROUND(100.0 * m.churned_users / a.active_users, 2) AS churn_rate_percent
FROM monthly_churn m
JOIN monthly_active a ON m.churn_month = a.month
ORDER BY m.churn_month;

Interpreting churn rates is essential for shaping product strategy. A high churn rate may indicate that users are not finding lasting value in your product, or that there are barriers to continued engagement. By tracking churn over time, you can spot trends, evaluate the effectiveness of retention initiatives, and prioritize improvements. Regularly monitoring churn enables you to react quickly to negative changes and proactively invest in features or campaigns that keep users engaged.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 2. Luku 3

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Pyyhkäise näyttääksesi valikon


              123456789
            
-- Identify users who have not logged in for the past 30 days (potential churn)
SELECT u.user_id,
  MAX(l.login_date) AS last_login
FROM users u
LEFT JOIN logins l ON u.user_id = l.user_id
GROUP BY u.user_id
HAVING
  MAX(l.login_date) < CURRENT_DATE - INTERVAL '30 days'
   OR MAX(l.login_date) IS NULL;


              12345678910111213141516171819202122232425262728293031323334
            
-- Calculate monthly churn rate: percentage of users who churned each month
WITH last_logins AS (
  SELECT
    u.user_id,
    MAX(l.login_date) AS last_login
  FROM users u
  LEFT JOIN logins l ON u.user_id = l.user_id
  GROUP BY u.user_id
),
monthly_churn AS (
  SELECT
    DATE_TRUNC('month', last_login) AS churn_month,
    COUNT(user_id) AS churned_users
  FROM last_logins
  WHERE
    last_login IS NOT NULL
    AND last_login < CURRENT_DATE - INTERVAL '30 days'
  GROUP BY DATE_TRUNC('month', last_login)
),
monthly_active AS (
  SELECT
    DATE_TRUNC('month', login_date) AS month,
    COUNT(DISTINCT user_id) AS active_users
  FROM logins
  GROUP BY DATE_TRUNC('month', login_date)
)
SELECT
  m.churn_month,
  m.churned_users,
  a.active_users,
  ROUND(100.0 * m.churned_users / a.active_users, 2) AS churn_rate_percent
FROM monthly_churn m
JOIN monthly_active a ON m.churn_month = a.month
ORDER BY m.churn_month;

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 2. Luku 3

Understanding Churn

1. What is a typical SQL approach to identify churned users?

2. How is churn rate calculated in SQL?

3. Why is it important to monitor churn regularly?

Understanding Churn

1. What is a typical SQL approach to identify churned users?

2. How is churn rate calculated in SQL?

3. Why is it important to monitor churn regularly?