Growth Metrics & Unit Economics Questions

import pandas as pd
import numpy as np

# users: columns ['user_id', 'signup_date']
# events: columns ['user_id', 'event_date'] where event indicates a "return" (could be filtered already)

# ensure datetime
users = users.copy()
events = events.copy()
users['signup_date'] = pd.to_datetime(users['signup_date'])
events['event_date'] = pd.to_datetime(events['event_date'])

# create signup cohort month (YYYY-MM)
users['cohort_month'] = users['signup_date'].dt.to_period('M').dt.to_timestamp()

# join events to signup info
ev = events.merge(users[['user_id', 'signup_date', 'cohort_month']], on='user_id', how='inner')

# compute months since signup (0 for same-month)
ev['months_since_signup'] = ((ev['event_date'].dt.year - ev['signup_date'].dt.year) * 12 +
                             (ev['event_date'].dt.month - ev['signup_date'].dt.month))
# keep 0..11
ev = ev[(ev['months_since_signup'] >= 0) & (ev['months_since_signup'] <= 11)]

# count distinct users per cohort & month (to avoid double-counting multiple returns)
cohort_month_user = ev.groupby(['cohort_month', 'months_since_signup'])['user_id'].nunique().reset_index()

# cohort sizes (users who signed up that cohort)
cohort_sizes = users.groupby('cohort_month')['user_id'].nunique().rename('cohort_size').reset_index()

# pivot counts
retention_counts = cohort_month_user.pivot(index='cohort_month', columns='months_since_signup', values='user_id').fillna(0).astype(int)

# align columns 0..11
for m in range(12):
    if m not in retention_counts.columns:
        retention_counts[m] = 0
retention_counts = retention_counts[sorted(retention_counts.columns)]

# convert to retention %
retention_pct = retention_counts.divide(cohort_sizes.set_index('cohort_month')['cohort_size'], axis=0).fillna(0)

# format index and columns nicely
retention_pct.index = retention_pct.index.to_series().dt.strftime('%Y-%m')
retention_pct.columns = [f'month_{m}' for m in retention_pct.columns]

retention_pct = retention_pct.round(3)  # e.g., 0.123 = 12.3%

-- Keep earliest 'purchase' per (user_id, order_id); mark others as retries
WITH purchases AS (
  SELECT
    user_id,
    order_id,
    event_type,
    occurred_at,
    event_id,
    /* rank events by occurred_at then event_id as tie-breaker */
    ROW_NUMBER() OVER (
      PARTITION BY user_id, order_id, event_type
      ORDER BY occurred_at ASC, event_id ASC
    ) AS rn
  FROM events
  WHERE event_type = 'purchase'
),
dedup AS (
  SELECT
    p.*,
    CASE WHEN rn = 1 THEN TRUE ELSE FALSE END AS is_first,
    CASE WHEN rn = 1 THEN 'deduplicated' ELSE 'retry' END AS dedup_flag
  FROM purchases p
)
SELECT * FROM dedup;