Oppiskele Visualizing Retention and Churn | Retention and Churn Analysis

When working to understand user behavior, summarizing retention and churn data is a crucial step before creating any visualization. You need to organize your data so trends and patterns become clear. This often means transforming raw login activity into tables that show how many users return week after week (retention) or how many drop off (churn). With the right summary queries, you can quickly spot which cohorts are sticking around and which are leaving, making it much easier to communicate these trends to others.


              1234567891011
            
-- Pivot retention data: Number of users retained by week after signup (cohort matrix)
SELECT
  u.signup_date AS cohort_start,
  EXTRACT(WEEK FROM l.login_date) - EXTRACT(WEEK FROM u.signup_date) AS week_number,
  COUNT(DISTINCT l.user_id) AS retained_users
FROM users u
JOIN logins l
  ON u.user_id = l.user_id
  AND l.login_date >= u.signup_date
GROUP BY u.signup_date, week_number
ORDER BY u.signup_date, week_number;

Pivoting data like this means reorganizing it so each row represents a different signup cohort and each column shows a different week after signup. This format, often called a cohort matrix, is especially useful for visualization because it allows you to see retention patterns at a glance. You can quickly compare how different signup groups perform over time, spot weeks where drop-off is high, and identify strong or weak cohorts.


              12345678910111213141516171819202122232425
            
-- Summarize churn rates by month for reporting
SELECT
  DATE_TRUNC('month', u.signup_date) AS signup_month,
  COUNT(DISTINCT u.user_id) AS users_signed_up,
  COUNT(DISTINCT CASE
    WHEN l_last.last_login_month = DATE_TRUNC('month', u.signup_date)
    THEN u.user_id
  END) AS churned_users,
  ROUND(
    100.0 * COUNT(DISTINCT CASE
      WHEN l_last.last_login_month = DATE_TRUNC('month', u.signup_date)
      THEN u.user_id
    END) / COUNT(DISTINCT u.user_id),
    2
  ) AS churn_rate_percent
FROM users u
LEFT JOIN (
  SELECT
    user_id,
    DATE_TRUNC('month', MAX(login_date)) AS last_login_month
  FROM logins
  GROUP BY user_id
) l_last ON u.user_id = l_last.user_id
GROUP BY DATE_TRUNC('month', u.signup_date)
ORDER BY signup_month;

Once you have summarized your retention and churn data in SQL, the next step is to prepare it for visualization tools. This usually means exporting your query results as a CSV file or connecting your SQL database directly to a business intelligence (BI) dashboard. Tools like Tableau, Power BI, or even spreadsheet software can easily turn your cohort matrices and churn summaries into charts or heatmaps. Clean, aggregated output from SQL makes this process seamless and ensures that your visuals are both accurate and easy to interpret.

Definition

A pivot table is a data summary tool that reorganizes and aggregates data for analysis, making it easier to spot patterns and trends in large datasets.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 2. Luku 5

Kysy tekoälyä

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

Suggested prompts:

Can you explain how the cohort matrix helps identify retention trends?

How is churn rate calculated in the SQL example?

What are some best practices for visualizing retention and churn data?

Pyyhkäise näyttääksesi valikon


              1234567891011
            
-- Pivot retention data: Number of users retained by week after signup (cohort matrix)
SELECT
  u.signup_date AS cohort_start,
  EXTRACT(WEEK FROM l.login_date) - EXTRACT(WEEK FROM u.signup_date) AS week_number,
  COUNT(DISTINCT l.user_id) AS retained_users
FROM users u
JOIN logins l
  ON u.user_id = l.user_id
  AND l.login_date >= u.signup_date
GROUP BY u.signup_date, week_number
ORDER BY u.signup_date, week_number;


              12345678910111213141516171819202122232425
            
-- Summarize churn rates by month for reporting
SELECT
  DATE_TRUNC('month', u.signup_date) AS signup_month,
  COUNT(DISTINCT u.user_id) AS users_signed_up,
  COUNT(DISTINCT CASE
    WHEN l_last.last_login_month = DATE_TRUNC('month', u.signup_date)
    THEN u.user_id
  END) AS churned_users,
  ROUND(
    100.0 * COUNT(DISTINCT CASE
      WHEN l_last.last_login_month = DATE_TRUNC('month', u.signup_date)
      THEN u.user_id
    END) / COUNT(DISTINCT u.user_id),
    2
  ) AS churn_rate_percent
FROM users u
LEFT JOIN (
  SELECT
    user_id,
    DATE_TRUNC('month', MAX(login_date)) AS last_login_month
  FROM logins
  GROUP BY user_id
) l_last ON u.user_id = l_last.user_id
GROUP BY DATE_TRUNC('month', u.signup_date)
ORDER BY signup_month;

Definition

A pivot table is a data summary tool that reorganizes and aggregates data for analysis, making it easier to spot patterns and trends in large datasets.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 2. Luku 5

Visualizing Retention and Churn

1. What is the purpose of pivoting retention data?

2. How can SQL output be used in visualization tools?

3. Why is summarizing churn data important for stakeholders?

Visualizing Retention and Churn

1. What is the purpose of pivoting retention data?

2. How can SQL output be used in visualization tools?

3. Why is summarizing churn data important for stakeholders?