Пример из реальной жизни аналитика данных
Таблица содержания
- Проблема
- Возможные причины мозгового штурма
- Данные
- Анализ: агрегирование на уровне событий
- Анализ: агрегирование на уровне пользователя
- Анализ: вовлеченность по географическому признаку
- Анализ: активность электронной почты и CTR
- Анализ: CTR по устройствам
- Анализ: еженедельный дайджест и электронные письма с повторным вовлечением
- Резюме
- Рекомендация
- Дальнейшие шаги
Проблема
Этот кейс можно найти здесь, если вы хотите попробовать его сами или продолжить.
Yammer - это социальная сеть для общения с коллегами. Люди обмениваются документами, обновлениями и идеями, публикуя их в группах. Yammer можно использовать бессрочно, но компании должны платить лицензионные сборы, если им нужен доступ к административным элементам управления, включая интеграцию с системами управления пользователями, такими как ActiveDirectory.
Yammer заметил снижение Weekly Active Users, «количества пользователей, которые зарегистрировали хотя бы одно мероприятие взаимодействия в течение недели, начиная с этой даты». Вовлеченность определяется как выполнение определенного типа обращения к серверу путем взаимодействия с продуктом (отображается в данных как события типа «взаимодействие».
Моя цель - определить причину провала на изображении ниже.
Возможные причины мозгового штурма
Возможные причины снижения вовлеченности включают следующее:
- Проблемы, связанные с продуктом. В продукте могла быть ошибка, которая удерживала пользователей от использования продукта. Точно так же Yammer, возможно, выпустил обновление продукта примерно в начале августа, которое не понравилось значительному числу пользователей.
- Время года. Август может быть просто месяцем, когда многие пользователи уезжают в отпуск. Это может иметь место, если значительная часть пользователей - родители, поскольку они, как правило, уезжают в отпуск до сентября, когда начинается школа.
- Конкуренты. Вполне возможно, что конкурент запустил конкурирующий продукт в начале августа, или он выпустил значительные обновления существующего продукта, что привело к оттоку пользователей.
- Данные не отслеживаются должным образом. Возможно, конвейеры данных не работают, и, следовательно, данные не отслеживаются должным образом. Это внутренняя проблема, для устранения которой потребуется помощь инженеров по обработке данных.
- Маркетинговое мероприятие. Когда проводятся крупные рекламные акции, это нормально, что компания привлекает клиентов более низкого качества из-за предоставленных скидок. Следовательно, не исключено, что спад в августе - это последствие крупного маркетингового события в июле.
Данные
Таблица 1: Пользователи
Каждая строка представляет одного пользователя с информацией, связанной с каждым пользователем.
Таблица 2: События
Каждая строка представляет событие, которое представляет собой действие, выполненное пользователем в Yammer. Эти события включают в себя события входа в систему, события обмена сообщениями, события поиска, события, регистрируемые по мере продвижения пользователей по воронке регистрации, и события, связанные с полученными электронными письмами.
Таблица 3: События электронной почты
Эта таблица содержит данные о событиях, связанных с отправленными письмами. По структуре он аналогичен приведенной выше таблице событий.
Таблица 4: Периоды накопления
Последняя таблица - это таблица поиска, которая используется для создания скользящих периодов времени.
Анализ
Агрегация на уровне событий
Первое, что я хотел увидеть, это то, какие события относятся к категории «помолвка».
SELECT DISTINCT event_name, event_type FROM tutorial.yammer_events ORDER BY event_type, event_name ASC
К «помолвке» относятся следующие события:
- home_page
- like_message
- авторизоваться
- search_autocomplete
- search_click_result_1
- search_click_result_2
- search_click_result_3
- search_click_result_4
- search_click_result_5
- search_click_result_6
- search_click_result_7
- search_click_result_8
- search_click_result_9
- search_click_result_10
- search_run
- Отправить сообщение
- view_inbox
with one as (
SELECT
EXTRACT('month' FROM occurred_at) as month,
count(event_name) as event_count
FROM tutorial.yammer_events
GROUP BY
month
)
SELECT
*,
(event_count - LAG(event_count) OVER (ORDER BY month ASC)) as diff
FROM one
Используя приведенный выше запрос, мы можем сделать вывод, что разница в вовлеченности между июлем и августом составляет 18 037 человек.
Следующее, что я хотел сделать, это подсчитать количество повторений каждого события «взаимодействия» за месяц, чтобы увидеть, есть ли какие-либо значительные события, которые привели к падению после 28 июля.
with two as (
with one as (
SELECT
CONCAT( EXTRACT('month' FROM occurred_at), '-', EXTRACT('year' FROM occurred_at)) as month_year,
event_name,
count(event_name) as event_count
FROM tutorial.yammer_events
WHERE event_type = 'engagement'
GROUP BY
event_name,
month_year
)
SELECT
*,
CASE
WHEN month_year = '5-2014'
THEN 0
WHEN month_year != '5-2014'
THEN (event_count - LAG(event_count) OVER (ORDER BY event_name ASC, month_year ASC))
ELSE NULL END AS abs_change
FROM one
)
SELECT *
FROM two
WHERE
month_year = '8-2014'
AND abs_change < 0
ORDER BY abs_change asc
Снижение вовлеченности в значительной степени связано с домашней_страницей, like_message_ view_inbox, send_message и входом в систему. Похоже, что падение всех этих событий просто связано с тем, что пользователи меньше входят в систему.
Затем я хотел проанализировать данные на уровне пользователей, чтобы увидеть, было ли падение вызвано падением количества пользователей или уменьшением количества взаимодействий на пользователя.
Агрегация на уровне пользователя
SELECT
EXTRACT('month' from occurred_at) as month,
count(DISTINCT user_id) as num_users
FROM tutorial.yammer_events
WHERE event_type = 'engagement'
GROUP BY month
Используя приведенный выше код, я определил количество отдельных активных пользователей на ежемесячной основе.
Мы видим, что количество пользователей снизилось с 3058 в июле до 2795 в августе, то есть на 8,6%. Вероятно, это причина снижения количества событий взаимодействия, в частности домашней страницы, like_message, view_inbox, send_message и входа в систему.
Я также посмотрел, снизилась ли вовлеченность на пользователя.
SELECT
EXTRACT('month' from occurred_at) as month,
count(event_name) as num_events,
count(DISTINCT user_id) as num_users,
count(event_name)/count(DISTINCT user_id) as events_per_user
FROM tutorial.yammer_events
WHERE event_type = 'engagement'
GROUP BY month
Количество событий на пользователя упало с 30 до 26, что соответствует падению вовлеченности на 13%, что также является довольно значительным числом.
Следовательно, нам необходимо выяснить, почему произошло снижение количества активных пользователей, а также снижение вовлеченности в расчете на одного пользователя.
Электронная почта и CTR
SELECT
action,
EXTRACT('month' FROM occurred_at) AS month,
count(action) as num_emails
FROM tutorial.yammer_emails
GROUP BY
action, month
ORDER BY
action, month
Я хотел получить совокупный обзор активности электронной почты, чтобы увидеть, изменились ли отправленные электронные письма, рейтинг кликов (CTR) или что-то еще, что могло привести к сокращению неактивных пользователей.
Я сразу заметил, что количество отправленных писем (еженедельный дайджест и повторное вовлечение) и открытых писем постоянно растет, но показатели CTR снизились. С июля по август количество открытых писем увеличилось на 6,5%, а рейтинг кликов снизился на 27%.
CTR по устройствам
Я хотел глубже изучить рейтинг кликов и посмотреть, связано ли снижение с устройствами. Это могло быть связано с типом операционной системы (IOS против Android) или мобильной версией против настольной.
with emails as(
SELECT
*,
CONCAT(EXTRACT('day' FROM occurred_at), '-', EXTRACT('month' FROM occurred_at), '-', EXTRACT('year' FROM occurred_at)) as date,
EXTRACT('month' FROM occurred_at) as month
FROM tutorial.yammer_emails emails
), events as (
SELECT DISTINCT
user_id,
CONCAT(EXTRACT('day' FROM occurred_at), '-', EXTRACT('month' FROM occurred_at), '-', EXTRACT('year' FROM occurred_at)) as date,
device,
EXTRACT('month' FROM occurred_at) as month
FROM tutorial.yammer_events
ORDER BY user_id ASC
)
SELECT
device,
emails.month,
count(emails.user_id)
FROM emails
LEFT JOIN events ON
emails.user_id = events.user_id
AND emails.date = events.date
WHERE action = 'email_clickthrough'
GROUP BY device, emails.month
Используя приведенный выше запрос, я заметил, что рейтинг кликов на ноутбуках и компьютерах был стабильным с июля по август, но не на планшетах и мобильных телефонах.
Разделив названия устройств на «мобильные», «планшеты» и «ноутбуки», я смог определить, так ли это на самом деле.
with emails as(
SELECT
*,
CONCAT(EXTRACT('day' FROM occurred_at), '-', EXTRACT('month' FROM occurred_at), '-', EXTRACT('year' FROM occurred_at)) as date,
EXTRACT('month' FROM occurred_at) as month
FROM tutorial.yammer_emails emails
), events as (
SELECT DISTINCT
user_id,
CONCAT(EXTRACT('day' FROM occurred_at), '-', EXTRACT('month' FROM occurred_at), '-', EXTRACT('year' FROM occurred_at)) as date,
device,
EXTRACT('month' FROM occurred_at) as month
FROM tutorial.yammer_events
ORDER BY user_id ASC
)
SELECT
CASE
WHEN device IN ('amazon fire phone', 'nexus 10', 'iphone 5', 'nexus 7', 'iphone 5s', 'nexus 5', 'htc one', 'iphone 4s', 'samsung galaxy note', 'nokia lumia 635', 'samsung galaxy s4') THEN 'mobile'
WHEN device IN ('ipad mini', 'samsung galaxy tablet', 'kindle fire', 'ipad air') THEN 'tablet_ipad'
WHEN device IN ('dell inspiron desktop', 'macbook pro', 'asus chromebook', 'windows surface', 'macbook air', 'lenovo thinkpad', 'mac mini', 'acer aspire desktop', 'acer aspire notebook', 'dell inspiron notebook', 'hp pavilion desktop') THEN 'laptop_comp'
ELSE null end as device_type,
emails.month,
count(emails.user_id)
FROM emails
LEFT JOIN events ON
emails.user_id = events.user_id
AND emails.date = events.date
WHERE action = 'email_clickthrough'
GROUP BY device_type, emails.month
Действительно, похоже, что падение рейтинга кликов было связано именно с мобильными устройствами и планшетами.
Еженедельный дайджест и электронные письма с повторным вовлечением
На данный момент я определил, что недостаток взаимодействия вызван снижением CTR электронной почты с июля по август. Чтобы собрать больше информации, я хочу посмотреть, сможем ли мы еще больше сузить проблему по типу электронной почты.
with one as (
SELECT
*,
EXTRACT('month' from occurred_at) as month,
CASE WHEN (LEAD(action, 1) OVER (PARTITION BY user_id ORDER BY occurred_at ASC)) = 'email_open' THEN 1 ELSE 0 END AS opened_email,
CASE WHEN (LEAD(action, 2) OVER (PARTITION BY user_id ORDER BY occurred_at ASC)) = 'email_clickthrough' THEN 1 ELSE 0 END AS clicked_email
FROM
tutorial.yammer_emails
)
SELECT
action,
month,
count(action),
sum(opened_email) as num_open,
sum(clicked_email) as num_clicked
FROM
one
WHERE action in ('sent_weekly_digest','sent_reengagement_email')
GROUP BY
action,
month
ORDER BY
action,
month
Очевидно, что снижение количества кликов связано с еженедельным электронным дайджестом, а не с электронным письмом о повторном привлечении.
Резюме
Проведя свой анализ, я могу сделать следующие выводы:
Снижение вовлеченности в основном связано с падением пяти событий взаимодействия (home_page, like_message_ view_inbox, send_message и login).
Затем я обнаружил, что уменьшение количества событий было вызвано сокращением общего количества активных пользователей MoM, а также уменьшением вовлеченности на пользователя.
После того, как я проанализировал сводную таблицу электронных писем, я заметил, что с июля по август произошло значительное снижение рейтинга кликов, хотя количество открытых писем увеличилось.
Сегментируя рейтинг кликов по типу устройства (мобильное устройство, планшет, ноутбук), я заметил, что падение рейтинга произошло за счет мобильных и планшетных устройств.
Наконец, снижение рейтинга кликов связано с еженедельным дайджестом электронной почты, а не с электронным письмом о повторном привлечении.
Рекомендация
Я рекомендую сразу же более внимательно изучить еженедельные электронные письма с дайджестом специально для мобильных устройств и планшетов. Возможно, это техническая проблема, из-за которой пользователи не могут щелкнуть электронное письмо, или просто проблема UX, когда содержание и макет письма не побуждают пользователей щелкнуть. Хорошим первым шагом было бы посмотреть, какие изменения были внесены с июля по август и работать в обратном направлении.
Дальнейший анализ
Некоторые другие вещи, на которые можно было обратить внимание, включают следующее:
- Проверьте, не связано ли изменение с небольшим количеством пользователей (принцип Парео)
- Когортный анализ, чтобы выяснить, связана ли причина с коротким жизненным циклом пользователя.
- Анализ по языку
- Анализ по географии
