Advanced Data Analysis and Statistics Questions

import pandas as pd
import statsmodels.formula.api as smf
import numpy as np

def did_banner_effect(df, rollout_date, user_col='user_id',
                      time_col='timestamp', treat_col='is_feature_banner_shown',
                      outcome_col='converted', cluster_col='user_id'):
    """
    Estimate DiD effect of banner exposure on conversion using individual-level OLS
    with Post, Treated, and interaction. Returns summary and coef for interaction.
    df: DataFrame with one row per pageview (or session) containing user, timestamp,
        binary treat_col (1 if banner was shown on that session), and binary outcome_col.
    rollout_date: str or pd.Timestamp marking policy/treatment start (post period starts here)
    """
    df = df.copy()
    df[time_col] = pd.to_datetime(df[time_col])
    rollout_date = pd.to_datetime(rollout_date)
    # Define Post indicator (1 if on/after rollout_date)
    df['post'] = (df[time_col] >= rollout_date).astype(int)
    # Define Treated group: users who ever saw the banner in post period (alternatively use per-session treat_col)
    treated_users = df.loc[(df['post']==1) & (df[treat_col]==1), user_col].unique()
    df['treated'] = df[user_col].isin(treated_users).astype(int)
    # Interaction
    df['did'] = df['treated'] * df['post']
    # Optionally collapse to one row per user-day to avoid repeated sampling bias
    df['date'] = df[time_col].dt.date
    agg = df.groupby([user_col, 'date', 'treated', 'post', 'did'], as_index=False).agg(
        converted = (outcome_col, 'max')  # 1 if user converted that day
    )
    # OLS with cluster-robust SEs by user (or by date)
    model = smf.ols("converted ~ treated + post + did", data=agg).fit(
        cov_type='cluster', cov_kwds={'groups': agg[cluster_col.split('.')[0]] if '.' in cluster_col else agg[user_col]}
    )
    return model, model.params['did'], model.summary()

# Example usage:
# model, did_coef, summary = did_banner_effect(df, rollout_date='2025-01-15')
# print("DiD estimate (interaction):", did_coef)
# print(summary)

WITH u AS (
  SELECT
    user_id,
    DATE_TRUNC('month', signup_date)::date AS signup_month
  FROM users
),
orders_with_cohort AS (
  SELECT
    o.user_id,
    u.signup_month,
    -- months difference between signup and order
    ((DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_month)) * 12
     + (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_month)))::int AS month_diff
  FROM orders o
  JOIN u ON o.user_id = u.user_id
),
cohort_active AS (
  -- distinct users per cohort and month_diff (avoid double counting within month)
  SELECT
    signup_month,
    month_diff,
    COUNT(DISTINCT user_id) AS active_users
  FROM orders_with_cohort
  WHERE month_diff BETWEEN 0 AND 6
  GROUP BY 1,2
),
cohort_size AS (
  SELECT
    signup_month,
    COUNT(DISTINCT user_id) AS cohort_users
  FROM u
  GROUP BY 1
)
SELECT
  c.signup_month,
  ROUND(100.0 * COALESCE(MAX(CASE WHEN month_diff = 0 THEN active_users END), 0) / cohort_users, 2) AS month_0,
  ROUND(100.0 * COALESCE(MAX(CASE WHEN month_diff = 1 THEN active_users END), 0) / cohort_users, 2) AS month_1,
  ROUND(100.0 * COALESCE(MAX(CASE WHEN month_diff = 2 THEN active_users END), 0) / cohort_users, 2) AS month_2,
  ROUND(100.0 * COALESCE(MAX(CASE WHEN month_diff = 3 THEN active_users END), 0) / cohort_users, 2) AS month_3,
  ROUND(100.0 * COALESCE(MAX(CASE WHEN month_diff = 4 THEN active_users END), 0) / cohort_users, 2) AS month_4,
  ROUND(100.0 * COALESCE(MAX(CASE WHEN month_diff = 5 THEN active_users END), 0) / cohort_users, 2) AS month_5,
  ROUND(100.0 * COALESCE(MAX(CASE WHEN month_diff = 6 THEN active_users END), 0) / cohort_users, 2) AS month_6
FROM cohort_size c
LEFT JOIN cohort_active a ON c.signup_month = a.signup_month
GROUP BY c.signup_month, cohort_users
ORDER BY c.signup_month;