Metric Hierarchies & Leading/Lagging Indicators Questions

WITH events AS (
  SELECT user_id, event_date::date AS dt, amount
  FROM payments
),
cohort AS (
  SELECT user_id, MIN(dt) AS signup_date, DATE_TRUNC('month', MIN(dt)) AS cohort_month
  FROM events GROUP BY user_id
),
timeline AS (
  SELECT c.user_id, c.cohort_month, e.dt,
         DATE_PART('month', AGE(e.dt, c.signup_date))::int AS month_since_signup,
         e.amount
  FROM cohort c JOIN events e USING (user_id)
)
SELECT cohort_month, user_id, month_since_signup,
       SUM(amount) AS revenue
FROM timeline
GROUP BY cohort_month, user_id, month_since_signup;

from lifelines import KaplanMeierFitter, CoxPHFitter
kmf = KaplanMeierFitter()
kmf.fit(durations, event_observed)
surv_probs = kmf.survival_function_at_times(range(0, horizon_months))

cph = CoxPHFitter()
cph.fit(df[['duration','event','plan','channel']], duration_col='duration', event_col='event')
# baseline survival:
base_surv = cph.baseline_survival_
# get predicted survival for cohort covariate profile

# compute mean revenue per active user per month
rev_by_month = df_active.groupby('month_since_signup')['revenue'].mean()

import pandas as pd
from sklearn.model_selection import TimeSeriesSplit
from sklearn.linear_model import LinearRegression, Ridge
from sklearn.metrics import mean_squared_error, mean_absolute_error
import numpy as np

# load data: cols = ['week_start','revenue','signups','active_7d','feature_adopt_rate']
df = pd.read_csv('weekly.csv', parse_dates=['week_start']).sort_values('week_start').set_index('week_start')

# feature engineering: lags and rolling windows
for col in ['signups','active_7d','feature_adopt_rate']:
    df[f'{col}_lag1'] = df[col].shift(1)
    df[f'{col}_lag2'] = df[col].shift(2)
    df[f'{col}_roll3'] = df[col].rolling(window=3).mean().shift(1)  # avoid leakage

df = df.dropna()

# train/test split (time-aware)
split_date = df.index[int(len(df)*0.8)]
train = df.loc[:split_date]
test = df.loc[split_date + pd.Timedelta(days=7):]

X_cols = [c for c in df.columns if c != 'revenue']
X_train, y_train = train[X_cols], train['revenue']
X_test, y_test = test[X_cols], test['revenue']

# baseline OLS
model = LinearRegression()
model.fit(X_train, y_train)
pred = model.predict(X_test)

# evaluation
rmse = mean_squared_error(y_test, pred, squared=False)
mae = mean_absolute_error(y_test, pred)
mape = (np.abs((y_test - pred) / y_test)).mean() * 100
r2 = model.score(X_test, y_test)

SELECT date(day) AS day,
       COUNT(*) AS events,
       COUNT(distinct user_id) AS users,
       COUNT(case when event_prop IS NULL then 1 end) AS null_props
FROM events
WHERE event_name = 'target_event'
  AND day BETWEEN '2025-01-01' AND '2025-02-28'
GROUP BY day
ORDER BY day;

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name='events' AND column_name IN ('event_prop','platform');

SELECT day, SUM(CASE WHEN sampled=1 THEN 1 ELSE 0 END) AS sampled,
             SUM(CASE WHEN processed=0 THEN 1 ELSE 0 END) AS failed
FROM events_raw GROUP BY day;

-- user-level metric averages
SELECT 'pre' AS period, AVG(M) AS mean_m, STDDEV_POP(M) AS sd, COUNT(*) AS n FROM user_metrics WHERE day < '2025-01-15'
UNION
SELECT 'post', AVG(M), STDDEV_POP(M), COUNT(*) FROM user_metrics WHERE day >= '2025-01-15';