Как сделать когортный анализ в excel
В прошлой статье я описал использование когортного анализа для выяснения причин динамики клиентской базы. Сегодня пришло время поговорить про трюки подготовки данных для когортного анализа.
Легко рисовать картинки, но для того, чтобы они считались и отображались правильно “под капотом” нужно проделать немало работы. В этой статье мы поговорим о том, как реализовать когортный анализ. Я расскажу про реализацию при помощи Excel, а в другой статье при помощи R.
Хотим мы этого или нет, но по факту Excel это инструмент анализа данных. Более “высокомерные” аналитики будут считать, что это слабый и не удобный инструмент. С другой стороны по факту сотни тысяч людей делают анализ данных в Excel и в этом отношении он легко побьет R / python. Конечно, когда мы говорим о advances analytics и машинном обучении, мы будем работать на R / python. И я был бы за то, чтобы большая часть аналитики делалась именно этими инструментами. Но стоит признать факты, в Excel обрабатывают и представляют данные подавляющее большинство компаний и именно этим инструментом пользуются обычные аналитики, менеджеры и product owners. Вдобавок Excel трудно победить в части простоты и наглядности процесса, т.к. вы мастерите свои расчеты и модельки буквально руками.
И так, как же нам сделать когортный анализ в Excel? Для того, чтобы решать подобные задачи нужно определить 2 вещи:
Какие данные у нас в начале процесса
Как должны выглядеть наши данные в конце процесса.
Чтобы собрать когортный анализ нам не будет достаточно только оборотный данных по датам и подразделениям. Нам нужны данные на уровне отдельных клиентов. В начале процесса нам понадобится:
Дата регистрации клиента
Объем продаж этого клиента в эту календарную дату
Первая сложность, которую предстоит преодолеть — это получить эти данные. Если у вас правильное хранилище, то они уже должны быть у вас. С другой стороны, если пока реализовали только запись данных о совокупных продажах по дням, то данные по клиентам у вас есть только на “проде”. Для когортного анализа вам придется реализовать ETL и сложить в ваше хранилище данные в разрезе клиентов, иначе у вас ничего не выйдет. И лучше всего если вы разделите “прод” и аналитику в разные базы, т.к. У аналитических задач и задач функционирования вашего продукта разные цели конкуренция за ресурсы. Аналитикам нужны быстрые агрегаты и расчеты на по многим пользователям, продукту нужно быстро обслужить конкретного пользователя. Об организации хранилища я напишу отдельную статью.
Итак, вы имеете стартовые данные:
Первое, что нам нужно сделать это преобразовать их в “лесенки”. Для этого нужно над этой таблицей построить сводную таблицу, по строкам — дата регистрации, по столбцам — календарная дата, в качестве значений — кол-во id клиентов. Если вы верно извлекли данные, то у вас должен получится вот такой треугольник/лесенка:
В целом лесенка это наш когортный график, в котором каждая строка отображает динамику отдельной когорты. Клиенты во времени в этой отображении двигаются только внутри одной строки. Таким образом динамика когорты отображает развитие отношений с группой клиентов пришедший в один период времени. Часто для удобства и без потери качества, можно объединить когорты в “блоки” строк. Например, вы можете сгруппировать их по неделям и месяцам. Точно так же вы можете сгруппировать и колонку, т.к. Возможно ваш темп развития продукта не требует детализации до дней.
На основе этой лесенки вы можете влоб построить график из моей статьи (я правда указывал, что сгруппировал несколько строк в одну, чтобы когорт было поменьше):
Это график с накопительными областями, где каждый ряд — это строка, по горизонтали даты.
Чуть сложнее логика для реализации графика “потоков”. Для потоков мы должны сделать некоторые дополнительные вычисления. В логике потоков каждый клиент прибывает в различных состояниях:
- Новый — любой клиент, у кого разница между датой регистрации и календарной дате
- Реактивированный — любой клиент, кто уже не новый, но в прошлом календарном месяце не генерировал выручку
- Действующий — любой клиент, кто не новый, но в в календарном месяце генерировал выручку
- Ушедший — любой клиент, кто не генерирует выручку 2 месяца подряд
Во-первых вам стоит в компании закрепить эти определения, чтобы вы могли корректно реализовать эту логику и автоматически рассчитывать состояния. Эти 4 определения имеют далеко идущие последствия в целом и для маркетинга. Ваши стратегии по привлечению, удержанию и возвращению будут базироваться на том, в каком состоянии вы считаете находится клиент. А если вы начнете внедрять модели машинного обучения в прогнозировании ухода клиентов, то определения станут вашим краеугольным камнем успешности этих моделей. Вообще про организацию работы и важность аналитической методологии я напишу отдельную статью. Выше я привел просто пример того, какими могут быть эти определения.
В Excel вам нужно создать дополнительную колонку, куда вписать описанную выше логику. В нашей случае нам придется “попотеть”. У нас есть 2 типа критериев:
- Разница между датой регистрацией и календарной датой — эти данные есть у каждой строки и тут просто нужно ее посчитать (вычитание дат в Excel просто дает разницу в днях)
- Данные о выручке в текущем и прошлом месяце. Эти данные нам не доступны в строке. Более того, с учетом того, что в нашей таблице не гарантирован порядок, то вы не можете точно сказать, где у вас данные по другим дням месяца для этого клиента.
Решить проблему 2 типа критериев можно 2 способами:
- Попросите сделать это в базе данных. SQL позволяет при помощи аналитической функции вычислить для каждого клиента сумму выручки за текущий и прошлый месяц (для текущего месяца SUM(revenue) OVER (PARTITION BY client_id, calendar_month, а потом LAG, чтобы получить смещение по прошлому месяцу):
- В экселе вам придется реализовать это так:
- Для текущего месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня
- Для прошлого месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня минус ровно 1 календарный месяц. При этом обращу внимание, что вы должны вычесть именно календарный месяц, а не 30 дней. Иначе вы рискуете получить смазанную картину из-за неодинакового числа дней в месяцах. Также используйте функцию ЕСЛИОШИБКА, чтобы заменить ошибочные значения для клиентов у кого не было прошлого месяца.
Добавив колонки выручки текущего месяца, прошлого месяца вы можете построить вложенное условие ЕСЛИ, учитывающие все факторы (разницу дат и суммы выручки в текущем/прошлом месяце):
ЕСЛИ( разница дат ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца > 0); “реактивация”;
ЕСЛИ( И (выручка прошлого месяца > 0; выручка текущего месяца > 0); “действующий”
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца = 0); “ушедший”; “ошибка”))))
У вас должно получится вот так:
Теперь эту таблицу можно пересобрать при помощи сводной таблицы в таблицу для построения графика. Вам нужно трансформировать ее в таблицу:
Календарная дата (колонки)
Состояние (строки)
Кол-во id клиентов (значения в ячейках)
Далее мы просто должны на основе данных построить диаграмму столбчатую диаграмма с накоплениями, по оси Х календарная дата, ряды это состояния, кол-во клиентов это высота столбцов. Вы можете поменять порядок состояний на графике, изменив порядок рядов в меню “выбрать данные”. В итоге мы получим такую картину:
Когортный анализ возвратности пользователей является мощным способом для понимания разных групп клиентов — их поведения и значимости для бизнеса. Однако итоговые таблицы бывает трудно понять с первого раза, а с ходу придумать, как их построить, ещё сложнее.
В статье будет описан относительно простой, но полезный алгоритм построения когортой таблицы, а также приведены наброски кода с Python/Pandas и SQL. Если Вам необходимо программно реализовать построение когортного отчёта или просто интересно узнать этот алгоритм — прошу под кат.
Одна из моих разработок — приложение для корпоративной аналитики. И когортный отчёт по возвратности вероятно является важнейшей функцией. Мне не раз приходилось его переписывать, делая более настраиваемым, гибким.
Во время разработки я не нашёл примеров реализации такого отчёта, поэтому изложенный ниже алгоритм был создан мной из понимания сути когортного анализа. Если кто-то обнаружит косяки или знает варианты получше, пожалуйста, сообщите об этом в комментариях.
Условные определения:
- Когортный анализ (Cohort analysis) — метод оценки каких-либо метрик с разделением пользователей на независимые группы — когорты.
- Возвратность (Retention) — характеристика группы пользователей, вычисляемая соотношением активных пользователей в определённые временные промежутки. Пример: некоторое приложение установили 50 человек, через неделю активными пользователями остались лишь 5 человек; возвратность: 5 / 50 * 100% = 10%.
Когорты по возвратности — таблица когортного анализа, в которой каждая строка описывает отдельную когорту по дате её появление, а столбцы показывают время наблюдения за когортой.
На примере ниже видно, что из клиентов, пришедших в августе, на следующий месяц остались только 60% от их начального количества. А число активных клиентов в сентябре составляют: 100% от пришедших в сентябре + 60% от пришедших в августе + 30% от пришедших в июле. Аналогично, число активных клиентов в августе это 100% от новых клиентов в августе и 50% клиентов, пришедших в июле. То есть, сами когорты мы смотрим по строкам, а всех клиентов, активных в некоторый месяц, — по диагонали, по разным когортам.
- Life-Time Value (LTV) — характеристика группы клиентов, которая показывает, сколько дохода в среднем приносит клиент из этой группы.
Мы построим когортную таблицу за несколько шагов, на каждом из которых будем получать новую таблицу, каждой из которых я дал своё название.
Есть таблица заказов Orders, по которой нужно провести когортный анализ. Структура следующая:
Нам нужно получить таблицу Clients с датой прихода клиента:
Возможно, у кого-то она уже есть (например, с датой регистрации пользователя или с датой установки приложения), но её также можно посчитать её как дату первого заказа.
Python:
MySQL:
Производим объединение таблиц Orders и Clients по типу Left через общее поле clientID. Дабы избежать путаницы, поле date из первой таблицы называем dateOr, а у второй — dateCl.
Теперь нам необходимо округлить даты, чтобы получились группы. Можно округлить до месяца, откинув число, можно посчитать число недель до какой-нибудь даты. В итоге, из даты должны получиться строки.
Структура таблицы Mix:
Python:
MySQL:
Наконец приближаемся к когортам! Произведём группировку сразу по трём полям: dateCl, dateOr и clientID.
Без этого мы не сможем найти число активных клиентов в какой-либо промежуток времени. Количество строк (функция Count() ) дала бы нам лишь количество заказов в когорте в данный временной промежуток, а число активных клиентов найти было бы никак.
К этим полям добавляем следующие:
- Число заказов, которые сделал клиент в этот временной промежуток. Находим как количество сгруппированных строк:
ordersCount = Count() - Сумма, на которую этот клиент сделал заказы в этот временной промежуток. Находим как сумму стоимостей отдельных заказов:
total = Sum(price)
Python:
MySQL:
Теперь уже можем обезличить наши данные, сгруппировав только по dateCl и dateOr. Добавляем другие поля:
- Число активных клиентов в данный временной промежуток. Находим как количество сгруппированных строк:
clientsCount = Count() - Число заказов, которые сделали все клиенты в этот временной промежуток. Находим как сумму числа заказов по отдельным клиентам:
ordersCount = Sum(ordersCount) - Сумма, на которую все клиенты сделали заказы в этот временной промежуток. Находим как сумму сумм по каждому клиенту:
total = Sum(total)
Получается такая таблица:
Python:
MySQL:
Теперь нам остаётся лишь преобразовать структуру таблицы: по строкам должны располагаться значения dateCl, по столбцам — dateOr, а в ячейках — желаемая величина (clientsCount, ordersCount, total или нечто иное).
Эту операцию называют Pivot Table (на русском, кажется, нет нормального термина). Простой пример:
Из значений x получились столбцы, а из y – строки. Вот была у нас строка x=1, y=3, val=8, а стала ячейка в столбце (x) 1, в строке (y) 3 со значением (val) 8. Или строка x=2, y=2, val=7, стала ячейкой столбце (x) 2, в строке (y) 2 со значением (val) 7.
Ячейки, чья значения не описаны строками из изначальной таблицы, обычно заполняются значением NULL или чем-то логически эквивалентным.
Python:
MySQL:
К сожалению, MySQL не умеет простым способом превращать ячейки в строки и столбцы, поэтому я с этой целью использовал другие языки. Но если кто-то знает такой способ — напишите в комментариях, буду признателен.
- Переименование колонок
Сейчас dateOr и dateCl описывают дату независимо, а центр когортной таблицы направлен вправо-вверх. Но если на Шаге 3, 4 или 5 произвести операцию dateOr -= dateCl, то данное поле будет отображать дату с начала существования когорты, а центр таблицы будет направлен влево-вверх, что лучше воспринимается: - Дополнительные параметры
Что делать, если у Вас в таблице заказов есть другие интересные параметры? Например, тип оплаты или ID филиала? Довольно просто: эти параметры также будут присутствовать в таблицах на Шагах 3,4,5 (Mix, Preresult, Result), и они должны быть добавлены в поля обеих группировок. Затем, на Шаге 6 для каждой возможной комбинации параметров нужно построить свою таблицу Cohort. Например, у Вас 3 филиала и 2 типа оплаты, будет 3*2 = 6 когортных таблиц. - Нахождение LTV
Имея продолжительную статистику, можно рассчитать Life-Time Value когорт пройдясь по строкам таблицы Cohort.
Когортная таблица по возвратности — не единственное применение когортного анализа, есть и другие, более наглядные применения. Например, разделение пользователей на две группы по некоторому признаку (когорты) и отображение их характеристик на графике как двух независимых линий.
В прошлой статье я погрузился в анализ выручки и разбил ее на 2 компоненты — MRPU и кол-во клиентов. Сегодня рассмотрим дальнейшие шаги в анализе и разложим на составляющие кол-во клиентов и их динамику.
Теперь общая схема анализа выглядит так:
Когортный анализ позволяет объяснить тенденции, протекающие в клиентской базе и пробрасывает прямой мост в воронку продаж и действия по удержанию и возвращению клиентов.
Что такое когортный анализ? Это разложение клиентов по датам их "прихода". Для разных продуктов это может быть различные события, например:
- Первая покупка
- Подписанный контракт на абонентское обслуживание
- Оформленная платная услуга в скачанном приложении.
- Первое зачисление денег на лицевой счет
Все зависит в итоге от вашего определения, когда вы считаете, что у вас появился клиент. Логичнее всего привязываться к моменту получения дохода или появления у клиента обязательств что-то заплатить. Хотя у каждого продукта могут быть свои особенности и клиентом можно уже считать и того, кто подписал какой-то договор, еще без внесения денег.
Если мы разобьем всех клиентов по датам "прихода", сгруппируем по месяцам (или неделям, дням зависит от типичных циклов жизни клиентов) и посчитаем кол-во клиентов, кто все еще продолжает быть клиентом (все еще платит, не расторгнул контракт) мы получим примерно такую картину:
Для простоты анализа часто близкие по датам начала когорты объединяют, чтобы диаграмма не выглядела как лапша.
В моем примере с привлечением клиентов все хорошо и клиентская база прирастает за счет привлечения новых клиентов. При этом в какой-то момент удается вернуть старых клиентов (мы видим, что самая старая когорта увеличивается к концу периода).
В анализе когорт у нас есть ряд важных производных характеристик, на которые стоит обратить внимание:
- Размеры новых когорт — это прямая характеристика ваших усилий по привлечению клиентов. Новые когорты формируются из новых клиентов
- Скорость распада когорт — это средняя величина, с которой во времени убывают ваши новые клиенты по мере возрастания их срока жизни. Обычно это процент, на который убывает когорта за период жизни.
- Размеры "старых" когорт. В "старую" когорту обычно помещают клиентов, которых вы уже не считаете новыми. Это люди, кто должны в теории быть вашими постоянными клиентами. Чаще всего эта когорта формирует основной объем выручки и самая большая по численности. Динамика размера "старой когорты" определяет ваши перспективы как продукта. Сокращение "старой когорты" или ее стагнация — звоночек, что у вас проблемы с продуктом, с продажами или лояльностью.
Хочу отметить, что обычно не существует "срока жизни" клиента, т.к. чаще всего когорты дляться и дляться, просто в них становится все меньше и меньше клиентов. В этом смысле бытовое значение слова "срок жизни" оказывается неверной интерпретацией распада когорт. Если мы говорим о 3 месяцах "среднего срока жизни", то не верно это понимать, что у вас не остается клиентов через 3 месяца. Употребление термина "средний срок жизни" становится некоторым математическим трюком. Дело в том, что распад когорты характеризует именно темп убывания клиентов. И вы можете перевести этот темп в термины: я теряю 50% когорты за 3 месяца. Или еще более жесткий — я теряю 95% когорты за 12 месяцев. Но возможно, что типичный срок потери всей когорты растянется на года. Поэтому хорошо прояснять в своей аналитике, какую метрику вы хотите использовать.
Использования метрики "Х% за Y периодов" хороший количественный способ для сравнения качества когорт между собой. Дело в том, что любая когорта — это небольшой "эксперимент". Люди в каждой когорте проходят встречу и ознакомление с вашим продуктом с нуля. И историческая ретроспектива когорт показывает ваши успехи и неудачи в отношении onboarding, а затем и в отношении retention\churn. Если вы методически добиваетесь повышения характеристики Y, то это означает, что вы хорошо развиваете продукт и взаимоотношения с клиентами. В целом это дело вкуса, вы можете как оперировать "средним сроком жизни" в том смысле в котором я его обозначил выше, либо же использовать оценку распада когорты в процентах.
Еще одним хорошим методом "взгляда" на когорты является анализ потоков клиентской базы. Это более наглядная свертка данных в когортах. Мы соединяем наши когорты и их динамику следующим образом:
- Сколько пришло новых клиентов в отчетном периоде (просто новые когорты)
- Сколько вернулось старых клиентов в отчетном периоде (клиенты из старых когорт, то возобновил отношения)
- Сколько клиентов в действующей базе (были и остались)
- Сколько клиентов ушло из старых когорт
Такая картинка наглядно показывает баланс притоков и оттока клиентской базы. И если ваш отток выше притока, ты вы сразу понимаете, что у вас проблемы.
В этом примере баланс потоков клиентов сильно в сторону притоков и поэтому клиентская база растет быстрыми темпами.
Что дает нам разложение клиентов на когорты:
- Мы можем увидеть, насколько быстро наша клиентская база обновляется, какую часть в ней составляют "новички", а в какую "старички"
- Если основа клиентской базы это старички и ваши новые клиенты дают 1% к базе в месяц, то как-то странно ожидать прироста клиентов в 50% к концу года. Вы или должны увеличивать входящий поток клиентов (что обычно проще) или вернуть значительную часть ранее потерянных клиентов (что обычно сложнее).
- Если же срок жизни ваших клиентов короткий и у вас почти не накапливаются "старички", то это наоборот означает, что ваши усилия в отношении старичков должны быть на втором плане. И вам нужно работать над увеличением срока жизни клиента, onboaring или продолжать увеличивать входящий поток.
- Анализ когорт дает вам возможность прогнозировать будущее состояние вашей экономики и ответить на вопрос "Сможете ли выполнить план, если при тех же показателях распада, вы увеличите в 2 раза привлечение клиентов"?
- Мы можем оценить количественно успешность наших усилий по привлечению и удержанию клиентов через сравнение скорости распада когорт.
- По итогам анализа вы можете понять где проблема в продукте в отношении клиентской базы (привлечение, удержание, "срок жизни" и т.п.)
Я уже замечал, что пока мы находимся на анализе всех средних. Но ваша клиентская база может иметь различные сегменты и кластеры. Ваш анализ по когортам станет еще продуктивнее, если вы проведете сегментацию и кластеризацию клиентской базы.
В следующих паре статей я напишу по подготовку данных для когортного анализа и про прогнозирование когорт в будущее для прогноза динамики клиентской базы.
Когортный анализ — это очень эффективный инструмент продуктовой и маркетинговой аналитики. К сожалению, о нем немногие знают, а те кто знают, крайне редко его используют.
Из статьи вы узнаете:
- В чем состоит суть когортного анализа;
- Чем метрики роста отличаются от метрик продукта;
- Почему попытки построить продуктовую аналитику, основываясь на метриках роста, заканчиваются плачевно;
- Как я использую когортный анализ в маркетинге и в продуктовой аналитике;
- За какими метриками продукта необходимо следить и почему.
↓ Чтобы глубже разобраться в том, как создаются, развиваются и масштабируются продукты, пройдите обучение в симуляторах GoPractice.
→ «Симулятор управления продуктом на основе данных» поможет научиться принимать решения с помощью данных и исследований при создании продукта.
→ «Симулятор управления ростом продукта» поможет найти пути управляемого роста и масштабирования продукта. Вы построите модель роста и составите стратегию развития продукта.
→ Не знаете с чего начать? Пройдите бесплатный тест для оценки навыков управления продуктом. Вы определите свои сильные стороны и слепые зоны, получите план профессионального развития.
→ Еще больше ценных материалов и инсайтов — в телеграм-канале GoPractice .
Когортный анализ в маркетинге и продуктовой аналитике
Давайте попробуем сравнить два автомобиля и узнать, какой из них лучше?
- Первый проехал 2000 км, а второй — 12 000 км;
- Первым автомобилем сейчас пользуются 5 раз в неделю, а вторым 4 раза в неделю;
- Первый автомобиль в последний месяц в среднем проезжал 10 км, а второй — 20 км;
- В данный конкретный момент первый автомобиль едет на скорости 100 км/ч, а второй автомобиль едет на скорости 70км/ч.
К сожалению, на основе имеющейся информации невозможно ответить на поставленный вопрос. Но почему-то как только доходит до интернет-проектов или мобильных приложений, то все начинают следить за метриками вроде DAU, MAU, дохода, общего количества регистраций, и пытаться на их основе делать выводы о продукте, влиянии изменений и эффективности маркетинговых активностей.
Перечисленные выше метрики являются метриками роста. За ними полезно следить для общего понимания ситуации, но применительно к работе над продуктом подобные метрики бесполезны, так как на их основе невозможно принимать продуктовые решения, равно как и оценивать влияние продуктовых изменений.
Когда вы работаете над продуктом, то вас должны интересовать, в первую очередь, его «объем» и «плотность», а не его «масса». «Масса» просто констатирует факт, не объясняя, откуда она взялась и как на нее повлиять. Вы же должны стремиться к тому, чтобы разложить ключевые метрики на составляющие, декомпозировать их, определяя рычаги воздействия на них. Основной задачей при работе над продуктом является определение рычагов воздействия и поиск способов влияния на них.
В этой деятельности вам не обойтись без аналитики. Аналитика является обратной связью на ваши действия, вашими глазами в продуктовом мире. Сначала аналитика позволяет вам понять, где вы находитесь, что за продукт вы сделали, как им пользуются в реальном мире, а затем позволяет увидеть то, как ваши действия, вносимые изменения влияют на ваш продукт. Аналитикой на картинке ниже я называю этапы: Measure, Data, Learn.
Одним из наиболее эффективных инструментов продуктовой аналитики является когортный анализ. Именно о нем сегодня пойдет речь.
Почему метрики роста бессмысленны для аналитики продукта
Давайте рассмотрим следующую модельную ситуацию. Есть продукт, который обладает следующими характеристикам:
- Стоимость привлечения пользователя составляет $1;
- Средний доход с одного пользователя составляет $2 в течение следующих 4 месяцев;
- 30% новых пользователей продолжают пользоваться продуктом спустя месяц (далее доля постепенно снижается до 15%);
- Команда продвижения привлечет 10 тысяч новых пользователей в первый месяц после запуска, 15 тысяч во второй, 20 тысяч в третий и так далее;
- Продакт-менеджер, который отвечает за развитие продукта, вносит в него изменения каждый месяц. Изменения неудачные, поэтому после каждого из изменений доход с пользователя падает на $0,1, а доля пользователей, продолжающих использовать продукт, падает на 2%.
В компании, где разрабатывается этот продукт, принято следить за месячной аудиторией (MAU или Monthly Active Users) и прибылью каждого из проектов. На основе этих метрик выставляются KPI и оцениваются успехи команды, работающей над продуктом.
Следя за выбранными метриками, спустя первые 9 месяцев руководство было очень довольно результатами нового продукта, в том числе и успехами продакт-менеджера. Но вспомните — наш продакт-менеджер каждый месяц портит продукт! При этом метрики роста уверенно идут вверх.
Ниже приведены те же самые графики, но уже за 16 месяцев. На этих графиках мы, наконец, видим первые признаки неудачных изменений продукта. Но лишь спустя 12 месяцев!
Дело в том, что на метрики роста влияют две составляющие: продукт и продвижение. Следя за метриками роста, вы не можете просто отделить эти два фактора. Именно по этой причине метрики роста совершенно не подходят для продуктовой аналитики.
При правильно построенной аналитике мы бы увидели неудачное влияние обновлений продукта еще в первые недели или месяцы.
Суть когортного анализа
В каждый конкретный день аудитория вашего продукта представляют из себя смесь тех, кто начал использовать ваш сервис сегодня, вчера, месяц назад и так далее. Следить за этой неоднородной массой и пытаться делать выводы — крайне неблагодарное занятие.
Идея когортного анализа состоит в том, чтобы резделить пользоватей на группы по определенным признакам, и отслеживать поведение этих групп во времени.
Обычно группы пользователей (когорты) выделяют на основе недели (месяца), когда пользователи пришли в приложение. Выделив такие группы пользователей (когорты), вы следите за ними в течение времени и измеряете ключевые метрики для каждой отдельной когорты. Таким образом, сравнивая показатели мартовской и майской когорт пользователей, вы можете объективно сравнивать соответствующие этим периодам времени версии продукта.
Для более глубокой аналитики выделенные когорты необходимо дополнительно сегментировать на основе источника трафика, платформы, страны и других факторов, которые имеют смысл в вашем конкретном продукте.
Скорее всего, значения ваших ключевых метрик будут отличаться для разных сегментов, ровно как и разные продуктовые изменения будут по-разному влиять на разные сегменты пользователей.
Ключевые метрики продукта — LTV и CAC
Две ключевые метрики, которые в конечном итоге определяют финансовую успешность вашего продукта — это LTV (Lifetime Value) и CAC (Customer Acquisition Cost).
LTV — прибыль, которую средний пользователь приносит за все время использования приложения. CAC — ваши затраты на привлечение среднего пользователя.
↓ Почему эти две метрики так важны для вашего продукта и как они влияют на ваши бизнес показатели вы можете прочитать в материалах:
В рамках же этой статьи важность этих метрик будет принята по умолчанию, а более подробно будут освещены способы работы с этими метриками.
Я не буду подробно останавливаться на вопросе измерения и работы с CAC, так как это не ключевая компетенция классического продакт-менеджера. Намного больший интерес представляет LTV — это как раз ключевая компетенция любого ответственного за продукт.
LTV — это ключевая метрика, отражающая ценность (пользу) вашего продукта для ваших пользователей и клиентов. Именно эта метрика должна стоять во главе угла при работе над продуктом.
LTV — замечательная метрика, но у нее есть один минус. Она высокоуровневая. Чтобы понимать, как на нее воздействовать, вам необходимо ее декомпозировать на более простые и приземленные на продукт метрики.
Декомпозиция LTV на метрики продукта
Обычно метрики привязываются к ключевым точкам жизненного цикла пользователя в приложении. Тем самым мы создаем возможность отслеживать успешность продвижения пользователей в приложении и находить узкие места, требующие нашего внимания.
Я обычно отслеживаю путь пользователя в продукте с точки зрения его вовлеченности и монетизации.
Вовлеченность описывается следующими этапами в жизненном цикле пользователя:
- Активация в приложении;
- Залипание в приложении (или активность использования); (сколько пользователей продолжают использовать продукт спустя месяц, два месяца и так далее после регистрации).
Монетизация же описывается следующей последовательностью этапов жизненного цикла пользователя:
- Активация в приложении;
- Увидел продающий экран;
- Совершил первую покупку;
- Совершил вторую покупку;
- …
Ниже я привел метрики, соответствующие каждому из этапов жизненного цикла пользователя в продукте (метрики могут отличаться для разных продуктов):
- Активация в приложении (% тех, кто прошел туториал или совершил ключевое целевое действие в приложении, например, зарегистрировался и добавил первых друзей);
- Залипание в приложении (% пользователей, который дошли до N уровня или, например, добавили N друзей: число N определяется экспериментальным путем);
- Пользователь увидел предложение о покупке (% пользователей, которые увидели предложение о покупке);
- Пользователь совершил первую покупку (% покупающих что-либо в приложении, средняя сумма первой покупки);
- Пользователь совершил повторную покупку (% совершивших повторную покупку, средняя сумма повторной покупки, среднее количество повторных покупок); (% пользователей, которые используют приложение спустя месяц/два/три/четыре после регистрации).
Все эти метрики влияют в конечном итоге на LTV. В каждом из продуктов могут быть свои особенности, но для большинства подобные базовые этапы или метрики подойдут.
Метрики продукта: как они влияют на LTV
Давайте подробнее рассмотрим описанные выше метрики продукта и то, как они влияют на LTV, на примере абстрактной игры.
Активация в приложении
В любой игре пользователя сначала обучают, проводя его через туториал. Те, кто не прошли туториал, скорее всего, не будут дальше играть и тем более платить. Именно поэтому для нас критично отслеживать долю пользователей, успешно прошедших этот этап.
Также полезно отслеживать долю тех, кто смог выполнить ряд целевых действий по окончании туториала (то есть обучился и теперь может самостоятельно играть). Такая метрика будет отражать насколько качественно спроектирован процесс обучения.
Пользователь «залип» в приложении
Пользователь, скорее всего, не будет платить, если он не увлекся игрой. Именно поэтому нам надо отслеживать долю тех, кто залип в приложении. С этой целью мы измеряем долю тех, кто прошел до N уровня или тех, кто заходил в приложении более 5 раз в течение недели с момента установки.
Обычно метрику для факта залипания определяют опытным путем. В этой статье есть примеры подобных метрик для ряда популярных сервисов.
Пользователь увидел предложение о покупке, сделал первую покупку
Одной из наших целей является получение дохода, поэтому нам надо стимулировать первую покупку в приложении. Но покупка совершается с определенного экрана нашего приложения (например, с экрана магазина), поэтому необходимо отслеживать долю пользователей, которые увидели этот экран.
Если экран о продаже видят 10% приходящих пользователей, то это автоматически ограничивает сверху долю пользователей, которые могут сделать первую покупку в нашей игре.
Повторные покупки
Первая покупка — это хорошо, но финансово успешные продукты обычно отличаются высокой долей повторных покупок. Часто первая покупка — это определенный кредит доверия пользователя приложению: если он удовлетворен результатом и полученной пользой, то, скорее всего, он совершит и повторную покупку. Поэтому еще одной важной метрикой становится, доля пользователей, совершающих повторные покупки, а также среднее количество повторных покупок.
Retention
Для того, чтобы пользователи имели шанс совершить несколько покупок, они должны продолжать играть в нашу игру в течение длительного времени, а не бросать ее спустя день. Для отслеживания этого явления мы будем измерять Retention.
Построение продуктовой аналитики и пример использования когортного анализа
Самым простым в реализации вариантом построения аналитики продукта будет создание воронок на каждое из описанных выше событий. В большинстве случаев у вас получатся воронка монетизации и воронка вовлеченности пользователей.
Далее необходимо будет сравнивать показатели вашего продукта для когорт пользователей, сформированных на основе недели, когда они пришли в приложение. Для такой аналитики идеально подходят инструменты Mixpanel и Localytics.
Я здесь рассмотрю более сложный, но и более продуктивный подход — использование когортного анализа для продуктовой аналитики. Использование когортного анализа углубит ваше понимание продукта и того, как ваши пользователи используют его во времени.
Будем формировать когорты пользователей на основе недели, когда они пришли в приложение. Для простоты в примере рассмотрены только следующие метрики: CAC, LTV, Retention, % совершивших первую покупку, % совершивших повторную покупку. Также для простоты когорты не сегментировались ни по каким дополнительным признакам.
Ниже приведена таблица когортного анализа рассматриваемого продукта (можете считать, что это игра или туристическое приложение). Ознакомьтесь с таблицей.
В первую неделю в первую версию нашего приложения пришло 3000 пользователей. На конец Week 0 25% из них прошли туториал, но еще никто не заплатил. К концу Week 1 еще 5% прошли туториал (то есть всего уже 30%), при этом 1,2% совершили первую покупку. К концу Week 2 туториал прошли 34% из рассматриваемой когорты, а первую покупку совершили 1,4%.
Спустя неделю мы выпустили новую версию приложения, где изменили туториал. Как мы видим из таблицы когортного анализа — это сработало! К концу Week 4 уже 47% прошли туториал (ранее лишь 34%). Расширение воронки монетизации на уровне туториала увеличило и долю тех, кто совершил покупку. К сожалению, наши пользователи не совершают повторные покупки, что не позволяет выйти на операционную безубыточность продукта, даже несмотря на то, что команда продвижения смогла существенно снизить CAC (пусть и сократив приток новых пользователей). Тратим на привлечение мы $0,8, а зарабатываем лишь $0,5 со среднего пользователя спустя 8 недель.
В третьей версии приложения мы доработали туториал и добавили новые покупки в приложение, увеличив разнообразие. Это позволило нам увеличить долю повторных покупок и сравнять LTV с CAC.
Примерно так когортный анализ позволяет нам понимать свой продукт, а также то, какие улучшения работают, а какие нет.
Как часто вы натыкались на вкладку «Когортный анализ» в Google Analytics? Использовали его или проходили мимо. В любом случае вы понимаете, что в этом отчете можно увидеть возвращаемость пользователей по какому-либо временному признаку (дата первого посещения сайта, дата первой транзакции или любого другого события). Но что делать, если вы используете другие системы, в интерфейсе которых такого отчета нет? Я расскажу о том, как его можно собрать в MS Excel.
Основные понятия
Прежде, чем приступать к разбору когортного анализа, давайте разберем, что такое когорта.
Когорта — это группа лиц, объединенных общим признаком и датой совершения действия.
Соответственно, метод исследования этих когорт называют когортным анализом.
Постановка задачи
Изначально, я использую данные из системы, в которую падают оформленные заявки с нашего сайта. Иногда пользователи возвращаются и оставляют заявки снова спустя некоторое время. Основная задача — понять, сколько пользователей повторно оформили заявки через месяц, два и более после оформления первой.
Что для этого нужно
- Дата оформления заявок
- Дата первой оформленной заявки
- Уникальный номер пользователя (в данном случае это номер телефона или e-mail)
- Столбец с единицами (засчитывается факт отправленной заявки, который будет суммироваться для отображения результата)
Реализация
Итак, раз мы будем смотреть возвращаемость по месяцам, нам понадобится довольно большой период, я возьму 12 месяцев. Вот, что из себя представляет моя табличка с данными:
Система, из которой я сделала выгрузку заявок, не предоставляет данные по дате первой, поэтому, чтобы найти эту дату, в столбце B я использовала следующую формулу:
Функция ЕСЛИ проверяет каждую ячейку массива из столбца Field phone на предмет равенства текущему телефону (Field phone). Если это так, то выдается соответствующее ему значение из столбца Date. В противном случае – логическое значение ЛОЖЬ (FALSE).
Таким образом внешняя функция МИН выбирает минимальное не из всех дат, а только из тех, где был использован текущий телефон, т.к. ЛОЖЬ функцией МИН игнорируется.
Не забудьте поставить курсор в формулу и нажать Ctrl + Shift + Enter вместо Enter! Это сочетание клавиш обозначит формулу, как формулу массива. Далее, ячейку можно растянуть по всем ячейкам вниз.
Составление сводной таблицы
Итак, все нужные столбцы мы получили, выделяем нашу таблицу и составляем сводную.
Раскидываем поля таким образом:
Поля Годы и Месяцы собрались автоматически.
После небольших разукрашиваний и доработок получаем итоговый вид:
Справа я отразила долю повторных заявок от всех заявок, которые были оформлены в соответствующем месяце.
Как читать отчет?
Очень просто. Смотрим по столбцу Первая заявка. Выбираем, например, апрель. В апреле 1432 пользователя первый раз оформили заявку, 19 из них вернулось и повторно оформило заявку в мае, 7 из них повторно в октябре и так далее.
Подходя к концу
Когортный анализ можно использовать для оценки окупаемости рекламных каналов, особенно для бизнеса с отложенной конверсией. В целом, он поможет оценить, какие из ваших решений в прошлом смогли повлиять на повышение конверсии, будь то подключение нового канала трафика или улучшение юзабилити сайта. Анализируйте прошлое, чтобы повышать конверсию в будущем. 🙂
Екатерина Шипова
Магистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.
Читайте также:
- Как пользоваться программой один для прошивки самсунг
- Сканер hp laserjet m1522nf установить программу для сканирования
- Synaptics touchpad driver как правильно установить в какую папку чтобы не сбивался
- Как сделать цветной выпадающий список в excel
- Не открывается установка и удаление программ windows 10