MAU +22%,
revenue плоский —
кто эти новые юзеры

Начинаем с декомпозиции MAU по типу пользователей. Нас интересует: кто из 218K — новые (первое посещение за месяц), кто — возвращающиеся (был раньше).

WITH first_visits AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS first_month
  FROM events
  GROUP BY user_id
),
march_users AS (
  SELECT DISTINCT
    e.user_id,
    CASE
      WHEN fv.first_month = '2024-03-01' THEN 'new'
      ELSE 'returning'
    END AS user_type
  FROM events e
  JOIN first_visits fv USING (user_id)
  WHERE DATE_TRUNC('month', e.event_at) = '2024-03-01'
),
revenue_march AS (
  SELECT user_id, SUM(amount) AS rev
  FROM orders
  WHERE DATE_TRUNC('month', created_at) = '2024-03-01'
  GROUP BY user_id
)
SELECT
  mu.user_type,
  COUNT(DISTINCT mu.user_id)             AS users,
  COALESCE(SUM(rm.rev), 0)               AS total_rev,
  COALESCE(SUM(rm.rev), 0)
    / COUNT(DISTINCT mu.user_id)::float  AS arpu
FROM march_users mu
LEFT JOIN revenue_march rm USING (user_id)
GROUP BY mu.user_type
ORDER BY users DESC;

Если новые пользователи «нормальные» — они должны возвращаться с похожим retention, что и прошлые когорты. Смотрим retention 7-day: доля зарегистрировавшихся в марте, которые вернулись хотя бы раз в первую неделю.

WITH cohort_march AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events
  WHERE DATE_TRUNC('month', event_at) = '2024-03-01'
  GROUP BY user_id
  HAVING MIN(event_at) >= '2024-03-01'
    AND MIN(event_at) < '2024-03-25'   -- чтобы было окно 7 дней
),
returned AS (
  SELECT DISTINCT c.user_id
  FROM cohort_march c
  JOIN events e USING (user_id)
  WHERE e.event_at BETWEEN c.first_at + INTERVAL '1 day'
                         AND c.first_at + INTERVAL '7 days'
)
SELECT
  COUNT(DISTINCT c.user_id)   AS cohort_size,
  COUNT(DISTINCT r.user_id)   AS returned_7d,
  ROUND(
    COUNT(DISTINCT r.user_id)::numeric
    / COUNT(DISTINCT c.user_id) * 100, 1
  )                             AS retention_7d_pct
FROM cohort_march c
LEFT JOIN returned r USING (user_id);

Низкий retention — плохо. Но может, они хотя бы покупают активно в первый визит? Смотрим ARPU за первые 30 дней после регистрации — отдельно для «аномальных» мартовских новых и органических новых.

WITH new_march AS (
  SELECT user_id, MIN(event_at) AS registered_at
  FROM events
  GROUP BY user_id
  HAVING DATE_TRUNC('month', MIN(event_at)) = '2024-03-01'
),
revenue_30d AS (
  SELECT
    nm.user_id,
    COALESCE(SUM(o.amount), 0) AS rev_30d
  FROM new_march nm
  LEFT JOIN orders o ON o.user_id = nm.user_id
    AND o.created_at BETWEEN nm.registered_at
                           AND nm.registered_at + INTERVAL '30 days'
  GROUP BY nm.user_id
),
-- Сегментируем: paid_channel — из платного канала, остальные — органика
segmented AS (
  SELECT
    u.acquisition_channel,
    CASE
      WHEN u.acquisition_channel = 'cashback_partner'
      THEN 'cashback'
      ELSE 'organic'
    END AS segment,
    r.rev_30d
  FROM revenue_30d r
  JOIN users u USING (user_id)
)
SELECT
  segment,
  COUNT(*)              AS users,
  ROUND(AVG(rev_30d), 1) AS arpu_30d,
  ROUND(SUM(rev_30d), 0) AS total_rev_30d
FROM segmented
GROUP BY segment
ORDER BY arpu_30d DESC;

Теперь финальный разрез: откуда пришли эти 37K аномальных новых? Смотрим по acquisition_channel — тегу, который проставляется при регистрации из UTM или SDK атрибуции.

SELECT
  u.acquisition_channel,
  COUNT(DISTINCT u.user_id)                AS new_users,
  ROUND(
    COUNT(DISTINCT u.user_id)::numeric
    / SUM(COUNT(DISTINCT u.user_id)) OVER () * 100,
  1)                                         AS share_pct,
  ROUND(AVG(r.rev_30d), 1)                  AS arpu_30d,
  ROUND(AVG(ret.ret7d::int) * 100, 1)      AS retention_7d_pct
FROM users u
JOIN (
  SELECT user_id,
         DATE_TRUNC('month', MIN(event_at)) AS first_month
  FROM events GROUP BY user_id
) fm ON fm.user_id = u.user_id
  AND fm.first_month = '2024-03-01'
LEFT JOIN revenue_30d r ON r.user_id = u.user_id
LEFT JOIN retention_flags ret ON ret.user_id = u.user_id
GROUP BY u.acquisition_channel
ORDER BY new_users DESC;