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

Осторожно

Открывайте после того, как попробовали сами. Сначала составьте свой список гипотез и запросов. Потом сравните с нашим. Если прочитаете разбор раньше, вы потеряете самое полезное в кейсе.

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

Гипотезы и порядок проверки

Главная часть кейса — не запросы, а список гипотез. Хороший список экономит время.

Полного списка не существует. Вот наш:

Гипотеза	Как проверить
Праздник. В какой-то стране люди работают меньше.	Разбить вовлечённость по странам.
Сломана функция. Что-то в продукте не работает.	Разбить метрику по устройствам и событиям. Ломается часть продукта — падает только часть метрики.
Сломан код трекинга. События перестали записываться.	Искать падение до нуля по одному типу события. Сначала исключить сломанную функцию.
Боты. Бот-трафик стало труднее создавать.	Искать подозрительные шаблоны поведения. Сложно проверить.
Сайт заблокирован у провайдеров.	Смотреть на падение по странам и сетям. Бывает редко.
Маркетинговая акция. Всплеск новых пользователей, потом спад.	Спросить маркетинг. Посмотреть рост регистраций.
Плохие данные. Тестовые данные попали в боевую базу.	Искать события от очень малого числа пользователей. Здесь это маловероятно.
Поисковые системы изменили выдачу.	Смотреть на трафик новых пользователей.

Как расставить гипотезы по порядку:

• Опыт. Вы уже видели такие проблемы. Знаете, какие встречаются чаще.
• Общение. Спросить коллегу из маркетинга быстро и дёшево. Делайте это первым.
• Скорость. Если две гипотезы одинаково вероятны, проверяйте ту, что быстрее.
• Зависимость. Если результат одной проверки упрощает другую, идите в таком порядке.

Ход решения

Ниже один из возможных путей. Он не единственный.

Шаг 0. Проверьте график

Сначала воспроизведите цифры. Недельное число вовлечённых пользователей:

SELECT DATE_TRUNC('week', occurred_at)::DATE AS week,
       COUNT(DISTINCT user_id) AS engaged_users
FROM tutorial.yammer_events
WHERE event_type = 'engagement'
GROUP BY 1
ORDER BY 1;

Последние 8 недель:

Неделя	Вовлечённые пользователи
2014-07-14	2050
2014-07-21	2088
2014-07-28	2138
2014-08-04	1848
2014-08-11	1740
2014-08-18	1720
2014-08-25	1835

Заметка

Первая строка результата — неделя 2014-04-28. Это неполная неделя, данные начинаются 1 мая. Не принимайте её за рост.

Вывод: пик в неделю 2014-07-28 (2138). Потом падение на 14–20% и уровень не восстанавливается.

Шаг 1. Рост: новые пользователи

Рост проверить легко. Компания его и так считает. Смотрим активации по неделям:

SELECT DATE_TRUNC('week', activated_at)::DATE AS week,
       COUNT(*) AS activated_users
FROM tutorial.yammer_users
WHERE activated_at >= '2014-06-23'
  AND activated_at < '2014-09-01'
GROUP BY 1
ORDER BY 1;

Результат стабильный: от 164 до 184 активаций в неделю. В августе падения нет.

Ещё проверим, что рост такой же по будням и выходным:

SELECT CASE WHEN DAYOFWEEK(activated_at) IN (0, 6)
            THEN 'weekend' ELSE 'weekday' END AS day_kind,
       ROUND(COUNT(*) / COUNT(DISTINCT activated_at::DATE), 1) AS avg_activations_per_day
FROM tutorial.yammer_users
WHERE activated_at >= '2014-05-01'
  AND activated_at < '2014-09-01'
GROUP BY 1;

Тип дня	Активаций в день
weekday	30.4
weekend	8.4

Вывод: рост нормальный. Будни высокие, выходные низкие. Это обычная картина для рабочего продукта.

Шаг 2. Когорты по возрасту аккаунта

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

SELECT DATE_TRUNC('week', e.occurred_at)::DATE AS week,
       COUNT(DISTINCT CASE WHEN e.occurred_at - u.activated_at < INTERVAL 70 DAY
                           THEN e.user_id END) AS younger_10w,
       COUNT(DISTINCT CASE WHEN e.occurred_at - u.activated_at >= INTERVAL 70 DAY
                           THEN e.user_id END) AS older_10w
FROM tutorial.yammer_events e
JOIN tutorial.yammer_users u ON u.user_id = e.user_id
WHERE e.event_type = 'engagement'
  AND e.occurred_at >= '2014-06-30'
GROUP BY 1
ORDER BY 1;

Неделя	Младше 10 недель	10 недель и старше
2014-07-21	1025	1070
2014-07-28	1058	1089
2014-08-04	1016	840
2014-08-11	1063	684
2014-08-18	1051	672
2014-08-25	1101	742

Вывод: у новых пользователей всё ровно. Падение у старых: с 1089 до 672–742, это около минус 35%.

Что это значит для гипотез:

• Маркетинговый всплеск маловероятен. Он бы дал спад у новых.
• Блокировка сайта или смена выдачи поисковиков тоже маловероятны. Они бы ударили по притоку новых.
• Проблема связана с удержанием давних пользователей.

Шаг 3. Устройства

Теперь разделим по типу устройства. Телефон, планшет и компьютер:

SELECT DATE_TRUNC('week', occurred_at)::DATE AS week,
       COUNT(DISTINCT CASE WHEN device IN ('iphone 5', 'iphone 5s', 'iphone 4s',
              'samsung galaxy s4', 'nexus 5', 'htc one', 'nokia lumia 635',
              'samsung galaxy note', 'amazon fire phone')
              THEN user_id END) AS phone,
       COUNT(DISTINCT CASE WHEN device IN ('ipad air', 'ipad mini', 'nexus 7',
              'nexus 10', 'kindle fire', 'samsung galaxy tablet')
              THEN user_id END) AS tablet,
       COUNT(DISTINCT CASE WHEN device NOT IN ('iphone 5', 'iphone 5s', 'iphone 4s',
              'samsung galaxy s4', 'nexus 5', 'htc one', 'nokia lumia 635',
              'samsung galaxy note', 'amazon fire phone', 'ipad air', 'ipad mini',
              'nexus 7', 'nexus 10', 'kindle fire', 'samsung galaxy tablet')
              THEN user_id END) AS computer
FROM tutorial.yammer_events
WHERE event_type = 'engagement'
  AND occurred_at >= '2014-06-30'
GROUP BY 1
ORDER BY 1;

Неделя	Телефон	Планшет	Компьютер
2014-07-21	657	154	1697
2014-07-28	619	214	1743
2014-08-04	497	160	1542
2014-08-11	367	152	1494
2014-08-18	403	152	1473
2014-08-25	430	148	1568

Заметка

Устройство samsung galaxy tablet в данных написано именно так. Его нужно отнести к планшетам.

Вывод: телефоны упали сильнее всего: с 619 до 367–430, это примерно минус 30–40%. Компьютеры упали примерно на 10–15%. Планшеты колеблются, явного падения нет.

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

Шаг 4. Праздники и страны

Проверим и гипотезу про праздник. Сравним четыре недели июля и четыре недели августа по странам:

SELECT location,
       COUNT(DISTINCT CASE WHEN occurred_at >= '2014-07-07'
                            AND occurred_at < '2014-08-04' THEN user_id END) AS jul_4w,
       COUNT(DISTINCT CASE WHEN occurred_at >= '2014-08-04'
                            AND occurred_at < '2014-09-01' THEN user_id END) AS aug_4w,
       ROUND(aug_4w / jul_4w - 1, 2) AS change
FROM tutorial.yammer_events
WHERE event_type = 'engagement'
GROUP BY 1
HAVING jul_4w >= 50
ORDER BY jul_4w DESC;

Пять крупнейших стран:

Страна	Июль	Август	Изменение
United States	1157	1094	-5%
United Kingdom	265	257	-3%
Japan	229	207	-10%
India	225	184	-18%
France	191	163	-15%

Вывод: падение есть во всех больших странах. Одной страны-виновника нет. Праздник не объясняет проблему.

Шаг 5. Письма

Дайджест-письмо должно возвращать пользователей в продукт. Проблема связана с давними пользователями, поэтому проверим письма. Считаем события по неделям:

SELECT DATE_TRUNC('week', occurred_at)::DATE AS week,
       COUNT(*) FILTER (WHERE action = 'sent_weekly_digest') AS digests,
       COUNT(*) FILTER (WHERE action = 'email_open') AS opens,
       COUNT(*) FILTER (WHERE action = 'email_clickthrough') AS clickthroughs
FROM tutorial.yammer_emails
WHERE occurred_at >= '2014-06-30'
GROUP BY 1
ORDER BY 1;

Неделя	Дайджестов	Открытий	Кликов
2014-07-21	3566	1641	971
2014-07-28	3598	1705	1021
2014-08-04	3634	1722	481
2014-08-11	3684	1770	192
2014-08-18	3759	1738	191
2014-08-25	3834	1853	209

Вывод: письма отправляются и открываются. Но кликов стало в 5 раз меньше. Проблема где-то между открытием письма и кликом.

Посчитаем доли. Открытие — в течение 5 минут после отправки. Клик — в течение 5 минут после открытия:

SELECT DATE_TRUNC('week', e1.occurred_at)::DATE AS week,
       ROUND(COUNT(e2.user_id) / COUNT(*), 3) AS open_rate,
       ROUND(COUNT(e3.user_id) / COUNT(e2.user_id), 3) AS clickthrough_rate
FROM tutorial.yammer_emails e1
LEFT JOIN tutorial.yammer_emails e2
  ON e2.user_id = e1.user_id
 AND e2.action = 'email_open'
 AND e2.occurred_at >= e1.occurred_at
 AND e2.occurred_at < e1.occurred_at + INTERVAL 5 MINUTE
LEFT JOIN tutorial.yammer_emails e3
  ON e3.user_id = e2.user_id
 AND e3.action = 'email_clickthrough'
 AND e3.occurred_at >= e2.occurred_at
 AND e3.occurred_at < e2.occurred_at + INTERVAL 5 MINUTE
WHERE e1.action = 'sent_weekly_digest'
  AND e1.occurred_at >= '2014-06-30'
GROUP BY 1
ORDER BY 1;

Неделя	Доля открытий	Доля кликов после открытия
2014-07-21	0.446	0.593
2014-07-28	0.464	0.600
2014-08-04	0.460	0.271
2014-08-11	0.469	0.097
2014-08-18	0.451	0.096
2014-08-25	0.473	0.101

Вывод: доля открытий не изменилась, около 46%. Доля кликов упала с 60% до 10%. Люди открывают дайджест, но не нажимают на ссылки. Возможно, ссылки сломаны или письмо выглядит по-другому.

Совет

В таблице есть и sent_reengagement_email. Эти письма тоже стоит посмотреть отдельно. В наших данных их мало, поэтому доля кликов по ним шумная. Она не упала. Так видно, что проблема именно у дайджеста.

Рекомендация

Что сказать руководителю продукта:

• Падение вовлечённости реально. Его нельзя объяснить ростом, праздником или страной.
• Падение у давних пользователей. Новые пользователи ведут себя как раньше.
• Есть два места, где видна проблема: мобильные приложения и дайджест-письма. Доля кликов по дайджесту упала с 60% до 10%, а число отправок выросло.
• Данные не показывают, что именно сломано.

Что делать:

1. Команде мобильной разработки: проверить релизы начала августа.
2. Команде писем: проверить, что изменилось в дайджесте в первую неделю августа (шаблон, ссылки, трекинг кликов).
3. Следить за метриками после исправления.

Ваша работа здесь — показать, где искать. Это может сэкономить другим командам много времени.

Заметка

Некоторые вопросы нельзя закрыть данными. Например, что изменилось в релизе приложения. Такие вопросы вы задаёте людям: разработчикам, маркетингу, поддержке.