Design and Product Analytics Questions

import pandas as pd
import numpy as np

def funnel_completion(events_df: pd.DataFrame, funnel_steps: list, 
                      user_col='user_id', event_col='event_name', time_col='event_time'):
    """
    events_df: columns [user_id, event_name, event_time] where event_time is datetime-like
    funnel_steps: ordered list of event names e.g. ['view', 'signup', 'purchase']
    Returns: (per_user_df, agg_counts_df)
    per_user_df: DataFrame with user_id, highest_step_index (-1 if none), highest_step_name (None if none), and matched_times for steps
    agg_counts_df: DataFrame with step_index, step_name, users_reached (cumulative counts)
    """
    # ensure timestamptype
    events = events_df.copy()
    events[time_col] = pd.to_datetime(events[time_col])
    
    # Keep earliest time per user+event (dedupe duplicates)
    first_times = (events
                   .sort_values(time_col)
                   .drop_duplicates([user_col, event_col], keep='first')
                   .loc[:, [user_col, event_col, time_col]])
    
    # pivot to wide for faster lookups: index user, columns event -> earliest time or NaT
    pivot = first_times.pivot(index=user_col, columns=event_col, values=time_col)
    
    # Prepare per-user record of matched times for funnel steps
    users = pivot.index
    matched = pd.DataFrame(index=users, columns=funnel_steps, dtype='datetime64[ns]')
    
    # For each step, find earliest time greater than previous matched time
    prev_time = pd.Series(pd.NaT, index=users)
    for step in funnel_steps:
        if step in pivot.columns:
            candidate_times = pivot[step]  # earliest time user did this event (may be NaT)
            # We need candidate_times > prev_time (if prev_time is NaT, allow any time)
            valid = candidate_times.where(candidate_times.notna() & (
                prev_time.isna() | (candidate_times > prev_time)
            ))
            matched[step] = valid
            # update prev_time to matched time (so next step must be after this matched)
            prev_time = matched[step].fillna(prev_time)  # if no match for this step, keep prev_time
        else:
            # no user performed this event at all -> remains NaT
            matched[step] = pd.NaT
            # prev_time stays unchanged
    
    # Determine highest step index reached per user
    def highest_step(row):
        # find last non-null in order
        for i in range(len(funnel_steps)-1, -1, -1):
            if pd.notna(row[funnel_steps[i]]):
                return i, funnel_steps[i]
        return -1, None
    
    hs = matched.apply(lambda r: pd.Series(highest_step(r), index=['highest_step_index','highest_step_name']), axis=1)
    per_user = pd.concat([hs, matched], axis=1).reset_index().rename(columns={user_col: 'user_id'})
    
    # Aggregate counts: number of users who reached at least step i (cumulative)
    totals = []
    n_users = len(per_user)
    for i, step in enumerate(funnel_steps):
        reached = (per_user['highest_step_index'] >= i).sum()
        totals.append({'step_index': i, 'step_name': step, 'users_reached': int(reached)})
    agg = pd.DataFrame(totals)
    return per_user, agg

-- 1) cohort per user
WITH user_cohort AS (
  SELECT user_id, DATE(MIN(event_time)) AS cohort_date
  FROM user_events
  GROUP BY user_id
),

-- 2) events shifted to event_date
user_events_dates AS (
  SELECT user_id, DATE(event_time) AS event_date
  FROM user_events
),

-- 3) mark if user was active on cohort_date + 7
retention_flag AS (
  SELECT uc.cohort_date,
         uc.user_id,
         CASE WHEN EXISTS (
           SELECT 1 FROM user_events_dates ued
           WHERE ued.user_id = uc.user_id
             AND ued.event_date = uc.cohort_date + INTERVAL '7 day'
         ) THEN 1 ELSE 0 END AS retained_d7
  FROM user_cohort uc
)

-- 4) cohort-level retention %
SELECT cohort_date,
       COUNT(user_id) AS cohort_size,
       SUM(retained_d7) AS retained_d7,
       ROUND(100.0 * SUM(retained_d7) / NULLIF(COUNT(user_id),0),2) AS retention_pct_d7
FROM retention_flag
GROUP BY cohort_date
ORDER BY cohort_date;