Как получить количество уникальных пользователей по дате привлечения

Что проверяют

Понимаете ли вы, что дата привлечения это не любая дата активности, а первое появление пользователя, и её сначала нужно вычислить. Следом обычно просят добавить канал привлечения или посчитать то же по неделям.

Как отвечать

Сначала уточните определение: регистрация, первый визит или первая покупка. Если есть готовая users.signup_date, задача сводится к GROUP BY. Интереснее, когда есть только таблица событий:

WITH first_touch AS (
  SELECT user_id, MIN(event_ts)::date AS acq_date
  FROM events
  GROUP BY user_id
)
SELECT acq_date, COUNT(*) AS new_users
FROM first_touch
GROUP BY acq_date
ORDER BY acq_date;

Что стоит проговорить:

• *`COUNT() вместо COUNT(DISTINCT)**: после GROUP BY user_id каждый пользователь уже одна строка, DISTINCT` здесь лишний и только дорогой;
• канал первого касания: MIN не отдаёт остальные колонки строки. Нужен ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts) с фильтром по первому номеру, в ClickHouse короче через argMin(source, event_ts);
• часовой пояс: события обычно хранятся в UTC, а отчёт ждут по московскому дню. AT TIME ZONE применяют до взятия даты;
• дни без привлечений: GROUP BY их не покажет, для графика нужен календарь через generate_series и LEFT JOIN;
• аддитивность: у каждого пользователя одна дата привлечения, поэтому дневные числа можно складывать в недельные. С DAU так нельзя.

На больших объёмах стоит упомянуть приближённый подсчёт, uniq в ClickHouse или APPROX_COUNT_DISTINCT в BigQuery, но для отчёта о новых пользователях обычно нужна точная цифра.

Где спотыкаются

Считают COUNT(DISTINCT user_id) по дате события. Это количество активных пользователей за день, а не привлечённых.

Фильтруют период до вычисления первой даты. Пользователь, пришедший в прошлом месяце, внутри окна выглядит новым. Сначала MIN по всей истории, потом фильтр по acq_date.

Не чистят данные: тестовые аккаунты, боты и анонимные идентификаторы, которые позже склеились с user_id, дают лишних «новых» пользователей.