Cohort Analysis and Retention Metrics Questions

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from statsmodels.stats.proportion import proportion_confint

def plot_cohort_retention(retention_rates_df, counts_df, alpha=0.05, figsize=(10,6)):
    """
    retention_rates_df: DataFrame, index=cohort_label, columns=day offsets, values=rate (0..1)
    counts_df: DataFrame same shape, values = number retained at that day (integers)
               Alternatively, counts_df could be initial cohort sizes for day0 and retained counts per day.
    """
    sns.set_style("whitegrid")
    fig, ax = plt.subplots(figsize=figsize)

    days = retention_rates_df.columns.astype(int)
    for cohort in retention_rates_df.index:
        rates = retention_rates_df.loc[cohort].astype(float).values
        successes = counts_df.loc[cohort].astype(int).values
        # need denominators: use successes / rates -> avoid divide by zero; prefer explicit denom_df if available
        with np.errstate(divide='ignore', invalid='ignore'):
            denom = np.where(rates>0, (successes / rates), np.nan)
        # fallback: if denom constant (cohort size) provided in counts_df_day0
        if np.isnan(denom).all():
            denom = np.full_like(successes, counts_df.loc[cohort, days[0]])

        lower = np.empty_like(rates); upper = np.empty_like(rates)
        for i, (x, n, p) in enumerate(zip(successes, denom, rates)):
            if np.isnan(p) or n==0:
                lower[i], upper[i] = np.nan, np.nan
            else:
                lo, hi = proportion_confint(count=int(round(x)), n=int(round(n)), alpha=alpha, method='wilson')
                lower[i], upper[i] = lo, hi

        ax.plot(days, rates, label=str(cohort))
        ax.fill_between(days, lower, upper, alpha=0.2)

    ax.set_xlabel("Day offset")
    ax.set_ylabel("Retention rate")
    ax.set_ylim(0, 1)
    ax.legend(title="Cohort")
    plt.show()

-- cohort_week = DATE_TRUNC(created_at, WEEK(MONDAY))
WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC(CAST(created_at AS DATE), WEEK(MONDAY)) AS cohort_week,
    CAST(created_at AS DATE) AS created_date
  FROM dataset.users
),
-- restrict events to users' 0..12 week window to reduce scanned rows
user_events AS (
  SELECT
    e.user_id,
    DATE(CAST(e.event_time AS DATE)) AS event_date
  FROM dataset.events e
  JOIN cohorts c
    ON e.user_id = c.user_id
  WHERE DATE(e.event_time) BETWEEN c.created_date AND DATE_ADD(c.created_date, INTERVAL 12 WEEK)
),
-- compute week offset from cohort
user_week AS (
  SELECT
    c.cohort_week,
    u.user_id,
    DATE_DIFF(ue.event_date, c.cohort_week, WEEK) AS week_offset
  FROM cohorts c
  LEFT JOIN user_events ue
    ON c.user_id = ue.user_id
  WHERE DATE_DIFF(ue.event_date, c.cohort_week, WEEK) BETWEEN 0 AND 12
),
-- dedupe user per cohort-week and count uniques
cohort_retention AS (
  SELECT
    cohort_week,
    week_offset,
    COUNT(DISTINCT user_id) AS active_users
  FROM user_week
  GROUP BY cohort_week, week_offset
)
SELECT
  cohort_week,
  week_offset,
  COALESCE(active_users, 0) AS active_users
FROM cohort_retention
ORDER BY cohort_week, week_offset;