Company Product Strategy and Roadmap Questions

WITH users AS (
  SELECT user_id, date_trunc('month', signup_at)::date AS cohort_month, signup_at::date AS signup_date
  FROM user_dim
  WHERE signup_at >= '2024-01-01'
),
activity AS (
  SELECT user_id, activity_date -- date of any qualifying event (login, open app, purchase)
  FROM events
  WHERE event_type = 'session' AND activity_date >= '2024-01-01'
),
cohort_activity AS (
  SELECT u.cohort_month,
         a.user_id,
         (a.activity_date - u.signup_date) AS days_after_signup
  FROM users u
  JOIN activity a ON u.user_id = a.user_id
  WHERE (a.activity_date - u.signup_date) BETWEEN 0 AND 89
)
SELECT cohort_month,
       days_after_signup,
       COUNT(DISTINCT user_id) AS retained_users,
       COUNT(DISTINCT user_id)::float / NULLIF((SELECT COUNT(*) FROM users u2 WHERE u2.cohort_month = ca.cohort_month),0) AS retention_rate
FROM cohort_activity ca
GROUP BY cohort_month, days_after_signup
ORDER BY cohort_month, days_after_signup;

WITH daily AS (
  SELECT event_date, SUM(value) AS total
  FROM events
  GROUP BY event_date
)
SELECT event_date, total,
       (total - AVG(total) OVER (ORDER BY event_date ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING))
       / NULLIF(STDDEV_POP(total) OVER (ORDER BY event_date ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING),0)
       AS zscore
FROM daily;

WITH cust AS (
  SELECT
    id AS customer_id,
    acquired_via AS channel,
    signup_date,
    signup_date + INTERVAL '12 months' AS signup_12m
  FROM customers
),

-- revenue from transactions in the 12 months after signup
tx AS (
  SELECT
    c.customer_id,
    c.channel,
    SUM(t.amount) AS tx_revenue
  FROM cust c
  JOIN transactions t
    ON t.customer_id = c.customer_id
    AND t.occurred_at >= c.signup_date
    AND t.occurred_at < c.signup_12m
  GROUP BY c.customer_id, c.channel
),

-- revenue from subscriptions that start within 12 months (use price at start)
subs AS (
  SELECT
    c.customer_id,
    c.channel,
    SUM(s.price) AS subs_revenue
  FROM cust c
  JOIN subscriptions s
    ON s.customer_id = c.customer_id
    AND s.start_date >= c.signup_date
    AND s.start_date < c.signup_12m
  GROUP BY c.customer_id, c.channel
),

-- per-customer 12m revenue (coalesce to 0 when no rows)
cust_revenue AS (
  SELECT
    c.customer_id,
    c.channel,
    COALESCE(t.tx_revenue, 0) + COALESCE(s.subs_revenue, 0) AS revenue_12m
  FROM cust c
  LEFT JOIN tx t USING (customer_id, channel)
  LEFT JOIN subs s USING (customer_id, channel)
),

-- per-channel aggregates: avg LTV and count of acquired customers in the period
channel_customers AS (
  SELECT
    channel,
    COUNT(*) AS num_customers,
    AVG(revenue_12m) AS avg_ltv,
    SUM(revenue_12m) AS total_revenue
  FROM cust_revenue
  GROUP BY channel
),

-- marketing spend aggregated by channel over months corresponding to customer signups.
-- We map signup_date to month for attribution; adjust as needed.
channel_spend AS (
  SELECT
    m.channel,
    SUM(m.amount) AS total_spend
  FROM marketing_spend m
  -- restrict spend to months where there were signups (optional)
  JOIN (
    SELECT DISTINCT channel, DATE_TRUNC('month', signup_date) AS month
    FROM customers
  ) cs ON cs.channel = m.channel AND cs.month = m.month
  GROUP BY m.channel
)

-- final: combine LTV and CAC
SELECT
  cc.channel,
  cc.num_customers,
  cc.avg_ltv,
  cc.total_revenue,
  COALESCE(cs.total_spend, 0) AS total_spend,
  CASE WHEN cc.num_customers > 0 THEN COALESCE(cs.total_spend,0)::numeric / cc.num_customers ELSE NULL END AS avg_cac
FROM channel_customers cc
LEFT JOIN channel_spend cs USING (channel)
ORDER BY cc.total_revenue DESC;