Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Learn Measuring User Retention | Retention and Churn Analysis
SQL for Product Analysts

bookMeasuring User Retention

User retention is a key metric for product analysts, measuring how many users continue to engage with your product after their initial signup. Retention helps you understand user loyalty, product stickiness, and the effectiveness of onboarding. Retention is usually measured over specific periods, such as day 1 (users who return the day after signup), day 7 (users who return a week later), or day 30 (users who return a month later). These time frames help you track how engagement changes as users become more familiar with your product.


              
123456789101112131415

            
-- Calculate day 7 retention: users who logged in exactly 7 days after signup
SELECT
  u.user_id,
  u.signup_date,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM logins l
      WHERE l.user_id = u.user_id
        AND l.login_date = u.signup_date + INTERVAL '7 days'
    )
    THEN 1
    ELSE 0
  END AS day_7_retained
FROM users u;
copy

The logic behind retention queries relies on comparing dates and filtering users based on their activity. In the previous example, you calculate day 7 retention by checking if each user has a login exactly seven days after their signup date. Date arithmetic is used to add seven days to the signup date, and the EXISTS clause checks if a matching login exists. This approach allows you to filter out users who did not return on the specific retention day, giving you a clear view of who is retained.


              
123456789101112131415161718192021222324252627282930313233

            
-- Calculate weekly cohort retention rates for day 7
SELECT
  DATE_TRUNC('week', u.signup_date) AS signup_week,
  COUNT(u.user_id) AS cohort_size,
  COUNT(
    DISTINCT CASE
      WHEN EXISTS (
        SELECT 1
        FROM logins l
        WHERE l.user_id = u.user_id
          AND l.login_date = u.signup_date + INTERVAL '7 days'
      )
      THEN u.user_id
      ELSE NULL
    END
  ) AS day_7_retained,
  ROUND(
    COUNT(
      DISTINCT CASE
        WHEN EXISTS (
          SELECT 1
          FROM logins l
          WHERE l.user_id = u.user_id
            AND l.login_date = u.signup_date + INTERVAL '7 days'
        )
        THEN u.user_id
        ELSE NULL
      END
    )::numeric / COUNT(u.user_id) * 100, 2
  ) AS day_7_retention_rate
FROM users u
GROUP BY signup_week
ORDER BY signup_week;
copy

Cohort analysis is a step-by-step approach to understanding how different groups of users behave over time. First, you group users by a shared characteristic, such as their signup week. Next, you track each group's retention by checking how many users from each cohort return on a specific day, such as day 7. Finally, you calculate the retention rate by dividing the number of retained users by the total cohort size. This method reveals patterns in user behavior, helping you identify whether recent product changes impact retention or if certain signup periods produce more loyal users.

Note
Definition

A cohort is a group of users who share a common characteristic, such as signing up during the same week.

1. What does 'day 7 retention' measure?

2. Why is cohort analysis useful in retention studies?

3. Which SQL clause is essential for grouping users by signup week?

question mark

What does 'day 7 retention' measure?

Select the correct answer

question mark

Why is cohort analysis useful in retention studies?

Select the correct answer

question mark

Which SQL clause is essential for grouping users by signup week?

Select the correct answer

Everything was clear?

How can we improve it?

Thanks for your feedback!

SectionΒ 2. ChapterΒ 1

Ask AI

expand

Ask AI

ChatGPT

Ask anything or try one of the suggested questions to begin our chat

bookMeasuring User Retention

Swipe to show menu

User retention is a key metric for product analysts, measuring how many users continue to engage with your product after their initial signup. Retention helps you understand user loyalty, product stickiness, and the effectiveness of onboarding. Retention is usually measured over specific periods, such as day 1 (users who return the day after signup), day 7 (users who return a week later), or day 30 (users who return a month later). These time frames help you track how engagement changes as users become more familiar with your product.


              
123456789101112131415

            
-- Calculate day 7 retention: users who logged in exactly 7 days after signup
SELECT
  u.user_id,
  u.signup_date,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM logins l
      WHERE l.user_id = u.user_id
        AND l.login_date = u.signup_date + INTERVAL '7 days'
    )
    THEN 1
    ELSE 0
  END AS day_7_retained
FROM users u;
copy

The logic behind retention queries relies on comparing dates and filtering users based on their activity. In the previous example, you calculate day 7 retention by checking if each user has a login exactly seven days after their signup date. Date arithmetic is used to add seven days to the signup date, and the EXISTS clause checks if a matching login exists. This approach allows you to filter out users who did not return on the specific retention day, giving you a clear view of who is retained.


              
123456789101112131415161718192021222324252627282930313233

            
-- Calculate weekly cohort retention rates for day 7
SELECT
  DATE_TRUNC('week', u.signup_date) AS signup_week,
  COUNT(u.user_id) AS cohort_size,
  COUNT(
    DISTINCT CASE
      WHEN EXISTS (
        SELECT 1
        FROM logins l
        WHERE l.user_id = u.user_id
          AND l.login_date = u.signup_date + INTERVAL '7 days'
      )
      THEN u.user_id
      ELSE NULL
    END
  ) AS day_7_retained,
  ROUND(
    COUNT(
      DISTINCT CASE
        WHEN EXISTS (
          SELECT 1
          FROM logins l
          WHERE l.user_id = u.user_id
            AND l.login_date = u.signup_date + INTERVAL '7 days'
        )
        THEN u.user_id
        ELSE NULL
      END
    )::numeric / COUNT(u.user_id) * 100, 2
  ) AS day_7_retention_rate
FROM users u
GROUP BY signup_week
ORDER BY signup_week;
copy

Cohort analysis is a step-by-step approach to understanding how different groups of users behave over time. First, you group users by a shared characteristic, such as their signup week. Next, you track each group's retention by checking how many users from each cohort return on a specific day, such as day 7. Finally, you calculate the retention rate by dividing the number of retained users by the total cohort size. This method reveals patterns in user behavior, helping you identify whether recent product changes impact retention or if certain signup periods produce more loyal users.

Note
Definition

A cohort is a group of users who share a common characteristic, such as signing up during the same week.

1. What does 'day 7 retention' measure?

2. Why is cohort analysis useful in retention studies?

3. Which SQL clause is essential for grouping users by signup week?

question mark

What does 'day 7 retention' measure?

Select the correct answer

question mark

Why is cohort analysis useful in retention studies?

Select the correct answer

question mark

Which SQL clause is essential for grouping users by signup week?

Select the correct answer

Everything was clear?

How can we improve it?

Thanks for your feedback!

SectionΒ 2. ChapterΒ 1
some-alt