Кейс 2: разбор

Вернитесь к условию кейса, если нужно вспомнить задачу.

Гипотезы

Начните с вопросов, не с запросов.

Зачем поиск? Чтобы быстро найти человека, группу или обсуждение.
Как понять, что поиск справляется? Пользователь ищет и находит. После поиска он нажимает на результат. Потом возвращается к поиску снова.
Как измерить качество? Через сессию. Мы смотрим, что пользователь делает за один визит.

Наши гипотезы:

Гипотеза	Как проверить
Поиском пользуются часто, он нужен.	Доля сессий с подсказками и с полным поиском.
Если поиск хороший, пользователи нажимают на результаты.	Клики после `search_run`.
Если ранжирование хорошее, кликают в основном по первым результатам.	Клики по позициям 1–10.
Довольные пользователи возвращаются к поиску.	Сколько сессий с функцией в следующие 30 дней.

Что такое сессия

Поиск удобно оценивать по сессиям. В данных сессии нет, её нужно определить самим.

В этом разборе: сессия — это события одного пользователя без паузы в 10 минут и больше. Если пауза 10 минут или дольше, начинается новая сессия.

Алгоритм:

Для каждого события найдите время предыдущего события этого же пользователя (LAG).
Если предыдущего нет или пауза от 10 минут, пометьте событие как начало сессии.
Нарастающая сумма этих меток даёт номер сессии (SUM() OVER).
Сгруппируйте по пользователю и номеру сессии. Посчитайте события поиска.

Создадим временную таблицу sessions. Она живёт, пока открыта сессия DuckDB, и не меняет вашу базу.

CREATE TEMP TABLE sessions AS
WITH ordered AS (
  SELECT user_id, occurred_at, event_name,
         LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_at
  FROM tutorial.yammer_events
  WHERE event_type = 'engagement'
), flagged AS (
  SELECT *,
         CASE WHEN prev_at IS NULL OR occurred_at - prev_at >= INTERVAL 10 MINUTE
              THEN 1 ELSE 0 END AS is_new_session
  FROM ordered
), numbered AS (
  SELECT *,
         SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY occurred_at
                                   ROWS UNBOUNDED PRECEDING) AS session_no
  FROM flagged
)
SELECT user_id, session_no,
       MIN(occurred_at) AS session_start,
       COUNT(*) FILTER (WHERE event_name = 'search_autocomplete') AS autocompletes,
       COUNT(*) FILTER (WHERE event_name = 'search_run') AS runs,
       COUNT(*) FILTER (WHERE event_name LIKE 'search_click_result_%') AS clicks
FROM numbered
GROUP BY user_id, session_no;

Одна строка в sessions — одна сессия. Всего их 67 989. В колонках: сколько раз пользователь использовал подсказки (autocompletes), запустил полный поиск (runs) и нажал на результат (clicks).

Шаг 1. Как часто используют поиск

Доля сессий с подсказками и с полным поиском по неделям:

-- не запускать отдельно: нужна таблица sessions
SELECT DATE_TRUNC('week', session_start)::DATE AS week,
       COUNT(*) AS sessions,
       ROUND(AVG((autocompletes > 0)::INT), 3) AS share_autocomplete,
       ROUND(AVG((runs > 0)::INT), 3) AS share_search_run
FROM sessions
WHERE session_start >= '2014-06-30'
GROUP BY 1
ORDER BY 1;

Неделя	Сессий	Доля с подсказками	Доля с полным поиском
2014-07-21	4254	0.250	0.081
2014-07-28	4343	0.263	0.071
2014-08-04	3724	0.253	0.082
2014-08-11	3576	0.251	0.082
2014-08-25	3721	0.250	0.081

За весь период: подсказки — 25.2% сессий, полный поиск — 8.3%.

Вывод:

Доли стабильны по времени.
Подсказки используют в каждой четвёртой сессии. Значит, людям нужно искать информацию. Функция заслуживает внимания.
Полный поиск используют реже.

Шаг 2. Сколько раз за сессию

Сколько раз пользователь выбирает подсказку за сессию (только сессии, где он это делает):

-- не запускать отдельно: нужна таблица sessions
SELECT autocompletes, COUNT(*) AS sessions
FROM sessions
WHERE autocompletes > 0
GROUP BY 1
ORDER BY 1;

Подсказок за сессию	Сессий
1	10290
2	4590
3	1568
4	668

Подсказкой пользуются один или два раза. Это нормально.

Теперь то же для полного поиска:

-- не запускать отдельно: нужна таблица sessions
SELECT runs, COUNT(*) AS sessions
FROM sessions
WHERE runs > 0
GROUP BY 1
ORDER BY 1;

Запусков за сессию	Сессий
1	672
2	1224
3	1386
4	1016
5	558
6	393
7	204
8	157

Вывод: если человек запускает полный поиск, он обычно запускает его несколько раз подряд. Чаще всего 2–4 раза. Это странно для функции, которой пользуются реже. Возможные причины:

результаты плохие, и пользователь пробует другие слова;
есть маленькая группа людей, которым поиск нравится, и они ищут много.

Шаг 3. Кликают ли по результатам

Посмотрим клики в сессиях, где был полный поиск:

-- не запускать отдельно: нужна таблица sessions
SELECT clicks, COUNT(*) AS sessions
FROM sessions
WHERE runs > 0
GROUP BY 1
ORDER BY 1;

Кликов за сессию	Сессий
0	4364
1	950
2	296

Вывод: в 78% сессий с поиском (4364 из 5610) нет ни одного клика. Пользователь искал и ничего не выбрал.

Растут ли клики, если искать больше?

-- не запускать отдельно: нужна таблица sessions
SELECT runs,
       COUNT(*) AS sessions,
       ROUND(AVG(clicks), 2) AS avg_clicks
FROM sessions
WHERE runs > 0
GROUP BY 1
ORDER BY 1;

Запусков	Сессий	Кликов в среднем
1	672	0.26
2	1224	0.26
3	1386	0.30
4	1016	0.29
5	558	0.28
6	393	0.29
7	204	0.21
8	157	0.25

Вывод: среднее число кликов почти не растёт. Больше поисков не приносит больше кликов. Поиск не помогает находить.

Шаг 4. Куда кликают

Если ранжирование хорошее, клики собираются на первых 2–3 позициях. Проверим:

SELECT REPLACE(event_name, 'search_click_result_', '')::INT AS position,
       COUNT(*) AS clicks
FROM tutorial.yammer_events
WHERE event_name LIKE 'search_click_result_%'
GROUP BY 1
ORDER BY 1;

Позиция	Кликов
1	147
2	175
3	147
4	151
5	169
6	152
7	157
8	139
9	134
10	171

Вывод: клики распределены почти равномерно по всем 10 позициям. Первый результат не лучше десятого. Значит, ранжирование плохое.

Шаг 5. Возвращаются ли пользователи

Для каждого пользователя берём первую сессию с функцией. Считаем, в скольких сессиях с этой функцией он был за следующие 30 дней (включая первую). Берём только пользователей, чья первая сессия была до 1 августа. Так виден полный месяц.

-- не запускать отдельно: нужна таблица sessions
WITH firsts AS (
  SELECT user_id,
         MIN(session_start) FILTER (WHERE runs > 0) AS first_run,
         MIN(session_start) FILTER (WHERE autocompletes > 0) AS first_auto
  FROM sessions
  GROUP BY user_id
), run_ret AS (
  SELECT f.user_id, COUNT(*) AS n_sessions
  FROM firsts f
  JOIN sessions s
    ON s.user_id = f.user_id AND s.runs > 0
   AND s.session_start >= f.first_run
   AND s.session_start < f.first_run + INTERVAL 30 DAY
  WHERE f.first_run < '2014-08-01'
  GROUP BY f.user_id
), auto_ret AS (
  SELECT f.user_id, COUNT(*) AS n_sessions
  FROM firsts f
  JOIN sessions s
    ON s.user_id = f.user_id AND s.autocompletes > 0
   AND s.session_start >= f.first_auto
   AND s.session_start < f.first_auto + INTERVAL 30 DAY
  WHERE f.first_auto < '2014-08-01'
  GROUP BY f.user_id
)
SELECT LEAST(n_sessions, 6) AS sessions_in_30d, COUNT(*) AS users, 'search_run' AS feature
FROM run_ret GROUP BY 1
UNION ALL
SELECT LEAST(n_sessions, 6), COUNT(*), 'autocomplete'
FROM auto_ret GROUP BY 1
ORDER BY 3, 1;

Значение 6 означает «6 и больше».

Сессий за 30 дней	Пользователей, полный поиск	Пользователей, подсказки
1	2647	1453
2	366	1062
3	42	647
4	8	347
5	—	192
6 и больше	—	309