DoorDash Business Model & Trade-offs Questions

-- 1) build cohort (signup month) and cohort size
WITH user_acq AS (
  SELECT
    u.user_id,
    u.channel,
    COALESCE(u.signup_date, MIN(o.created_at) OVER (PARTITION BY u.user_id))::date AS signup_date
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.user_id
),
cohort AS (
  SELECT
    user_id,
    channel,
    date_trunc('month', signup_date) AS cohort_month,
    signup_date
  FROM user_acq
),
cohort_sizes AS (
  SELECT
    channel,
    cohort_month,
    COUNT(*) AS new_users
  FROM cohort
  GROUP BY 1,2
),
-- 2) monthly marketing spend by channel/month
monthly_spend AS (
  SELECT
    channel,
    date_trunc('month', date)::date AS month,
    SUM(spend) AS spend
  FROM marketing_spend
  GROUP BY 1,2
),
-- 3) aggregate spend for cohort: map spend for channel & cohort_month
cohort_spend AS (
  SELECT
    c.channel,
    c.cohort_month,
    COALESCE(ms.spend,0) AS total_spend
  FROM (
    SELECT DISTINCT channel, cohort_month FROM cohort
  ) c
  LEFT JOIN monthly_spend ms
    ON ms.channel = c.channel
    AND ms.month = c.cohort_month
),
-- 4) revenue from cohort users in their first 12 months
orders_12m AS (
  SELECT
    c.channel,
    c.cohort_month,
    o.user_id,
    o.order_id,
    o.created_at,
    o.total_amount
  FROM cohort c
  JOIN orders o
    ON o.user_id = c.user_id
   AND o.created_at >= c.signup_date
   AND o.created_at < (c.signup_date + interval '12 months')
),
cohort_revenue AS (
  SELECT
    channel,
    cohort_month,
    SUM(total_amount) AS revenue_12m
  FROM orders_12m
  GROUP BY 1,2
)
-- final: CAC and 12m LTV per channel/cohort_month
SELECT
  cs.channel,
  cs.cohort_month,
  cs.new_users,
  COALESCE(sp.total_spend,0) AS total_spend,
  CASE WHEN cs.new_users>0 THEN sp.total_spend::numeric / cs.new_users ELSE NULL END AS cac,
  COALESCE(rev.revenue_12m,0) AS revenue_12m,
  CASE WHEN cs.new_users>0 THEN rev.revenue_12m::numeric / cs.new_users ELSE NULL END AS ltv_12m
FROM cohort_sizes cs
LEFT JOIN cohort_spend sp
  ON sp.channel = cs.channel AND sp.cohort_month = cs.cohort_month
LEFT JOIN cohort_revenue rev
  ON rev.channel = cs.channel AND rev.cohort_month = cs.cohort_month
ORDER BY cs.channel, cs.cohort_month;