Как рассчитать пожизненную ценность клиента (LTV) из CSV (пошаговое руководство)
Чтобы рассчитать пожизненную ценность клиента на основе CSV-файла транзакций, определите средний чек, частоту покупок и продолжительность жизни клиента. Перемножьте эти три показателя, а затем примените валовую маржу. Арифметика здесь проста. Но именно экспертные решения — кого считать ушедшим клиентом и стоит ли смотреть в прошлое или будущее — определяют, будет ли полученный ответ полезным.
Две команды могут применить одну и ту же формулу к одному и тому же файлу и получить результаты, отличающиеся в три раза. И ни одна из них не ошибется. Просто они, сами того не замечая, отвечали на разные вопросы.
В этом руководстве мы разберем необходимые исходные данные, три этапа ручного расчета и моменты, когда табличный метод перестает работать.
Что вам понадобится перед началом работы
Вам понадобится выгрузка на уровне транзакций: одна строка на каждый заказ с идентификатором клиента, датой и суммой заказа. Сводный отчет по месяцам не подойдет, поскольку пожизненная ценность принципиально строится на поведении отдельных клиентов во времени.
Перед применением любой формулы необходимо принять два решения, которые повлияют на результат сильнее, чем любые математические расчеты.
Исторический или прогнозный. Исторический показатель пожизненной ценности клиента суммирует то, что клиенты уже фактически потратили к настоящему моменту. Прогнозный — оценивает, сколько они потратят в будущем. Исторический показатель легко обосновать, но он всегда занижен; прогнозный — более полезен для бюджетирования, но полностью зависит от ваших предположений об удержании.
Кого считать ушедшим. Для подписок все просто — это дата отмены. В транзакционном бизнесе факта отмены нет, поэтому нужно правило. Например, клиент, не совершавший покупок в течение 12 месяцев, считается ушедшим. Выберите этот интервал и зафиксируйте его.
Хороший результат выглядит так: вы получаете таблицу по каждому клиенту с общей суммой расходов, количеством заказов, датами первого и последнего заказов и меткой сегмента. Рядом находится итоговый показатель с указанием заложенной маржинальности.
Как сделать расчет вручную
Вариант 1: Определите средний чек и частоту покупок
Сгруппируйте файл транзакций по идентификатору клиента. Для каждого клиента просуммируйте стоимость заказов и посчитайте их количество. Средний чек — это общая выручка, деленная на общее количество заказов по всей базе. Частота покупок — это общее количество заказов, деленное на число уникальных клиентов за период.
Ловушка здесь кроется в периоде. Частота, измеренная за 12 месяцев, и частота за всю историю — это разные цифры, и их смешивание в одной формуле приведет к бессмысленному результату. Сначала зафиксируйте временное окно и используйте его последовательно.
Вариант 2: Добавьте кривую удержания
Продолжительность жизни клиента — это показатель, который чаще всего берут наугад. Обоснованный вариант строится на ваших собственных данных: сгруппируйте клиентов по месяцу привлечения, а затем посчитайте, сколько из них оставались активными в каждый последующий месяц.
Так вы получите кривую удержания. Средняя продолжительность жизни — это примерно площадь под ней (в таблице — сумма ежемесячных показателей удержания). Если из 100 клиентов во второй, третий и четвертый месяцы удерживаются 80%, 65% и 55% соответственно, вы накапливаете продолжительность жизни месяц за месяцем, а не берете округленное число с потолка. В нашем руководстве по построению графика когортного удержания описана техническая сторона создания такого графика, а в статье о том, что такое когортный анализ, подробно разбирается сама концепция.
Вариант 3: Рассчитайте LTV и соотношение LTV:CAC
Умножьте средний чек на частоту покупок и на продолжительность жизни клиента, а затем — на валовую маржу. Расчет пожизненной ценности на основе выручки искажает показатель в большую сторону (иногда очень сильно), так как приравнивает низкомаржинальные продукты к высокомаржинальным.
Затем разделите полученное значение на стоимость привлечения клиента (CAC). Именно ради этого соотношения и проводятся все расчеты. Пожизненная ценность сама по себе — это просто статистика; пожизненная ценность в сопоставлении с затратами на привлечение — это основа для принятия решений.
В чем слабые стороны ручного расчета
Все ломается на сегментации. Единый усредненный показатель объединяет ваши лучшие и худшие когорты в одну неинформативную цифру. По-настоящему полезные данные — это показатели в разрезе каналов, тарифов или месяцев привлечения. Для каждого сегмента приходится заново выстраивать всю цепочку формул на основе отфильтрованной выборки. Сегментов, которые стоило бы проверить, обычно больше, чем терпения у аналитиков, поэтому большинство из них так и остаются неизученными.
Вторая проблема — регулярное обновление данных. В файле за следующий квартал будет больше строк и, зачастую, другие названия колонок. В сетке удержания, на построение которой ушел целый день, придется расширять диапазоны и перепроверять формулы. Пропущенный диапазон никак не заметен в итоговом отчете, и именно в этом кроется опасность.
Кроме того, исходные допущения нигде не зафиксированы. Через полгода в файле останется просто цифра, и никто уже не вспомнит, рассчитывалась она по выручке или по марже и какое окно оттока использовалось. Именно так устаревшие данные попадают в презентации для совета директоров.
Есть и менее очевидная проблема. Поскольку перестраивать модель долго и сложно, ее обычно рассчитывают один раз, а затем ссылаются на эти цифры целый год. Но пожизненная ценность — величина переменная. Она меняется при изменении цен, смещении фокуса на более дешевые каналы привлечения или когда доработки продукта влияют на удержание. Показатель, рассчитанный прошлой весной, может описывать бизнес, которого уже не существует.
Как рассчитать пожизненную ценность клиента с помощью Powerdrill Bloom
Шаг 1: Загрузите данные
Загрузите CSV-файл с транзакциями напрямую. Powerdrill Bloom проанализирует структуру колонок, чтобы вы могли убедиться в наличии корректного идентификатора клиента и чистого столбца с датами еще до начала группировки.
Шаг 2: Опишите логику расчета простыми словами
Сформулируйте правила так, как если бы вы объясняли задачу коллеге. Считайте ушедшими клиентов, не совершавших покупок в течение 12 месяцев, примените указанный процент валовой маржи и рассчитайте пожизненную ценность по каналам привлечения. Затем задайте важные уточняющие вопросы — например, как отличается показатель между верхним и нижним квартилями или как он менялся по когортам привлечения.
Шаг 3: Экспортируйте график, отчет или презентацию
Выгрузите таблицу по сегментам, лежащую в ее основе кривую удержания или текстовый отчет, в котором все допущения зафиксированы рядом с итоговыми цифрами.
Почему это лучше, чем перестраивать модель каждый квартал
| Работа в таблицах | Powerdrill Bloom | |
|---|---|---|
| Сегментация по каналам | Перестраивать цепочку формул для каждого сегмента | Просто добавить это в запрос |
| Изменение окна оттока | Переделывать сетку удержания | Сделать новый запрос с другим окном |
| Фиксация допущений | Вручную (часто забывается) | Зафиксировано в самом запросе |
| Файл за следующий квартал | Расширять диапазоны, проверять формулы | Загрузить файл и повторить запрос |
Вторая строка таблицы полностью меняет подход к анализу. Окно оттока в 12 месяцев и в 6 месяцев могут дать совершенно разные показатели пожизненной ценности. Честный способ работы с данными — запустить оба варианта и посмотреть, останутся ли ваши выводы в силе. В таблицах никто не станет делать это дважды.
Распространенные ошибки
Использование выручки вместо маржи. Расчет пожизненной ценности на основе выручки льстит любому бизнесу, но сильно искажает сравнение со стоимостью привлечения.
Объединение всех клиентов в одну цифру. Средний показатель по базе с сильно различающимися сегментами не описывает ни одного реального клиента. Сначала сегментируйте, а потом делайте выводы.
Определение продолжительности жизни наугад. «Примерно два года» — самое популярное допущение и самое необоснованное. Вместо этого выведите его из собственной кривой удержания, даже если она пока короткая.
Игнорирование эффекта выжившего. Исторический показатель пожизненной ценности учитывает только тех клиентов, которые уже совершали покупки, что создает перекос в сторону «старожилов». Когортный расчет позволяет избежать этой ошибки.
Анализ показателя без привязки к CAC. Пожизненная ценность в размере $900 ничего не значит, пока вы не узнаете, сколько стоило привлечение — $90 или $800.
Отношение к показателю как к константе. Пересчитывайте его каждый раз, когда существенно меняются цены, продукт или структура каналов привлечения. Цифра, перенесенная из прошлого года, — это просто догадка с запятой и знаками после нее.
Заключение
Расчет пожизненной ценности клиента на основе CSV-файла состоит из трех этапов: определение среднего чека и частоты покупок, построение кривой удержания по вашим когортам, а затем учет маржи и расчет соотношения со стоимостью привлечения. В таблицах этот метод работает — ровно до тех пор, пока вам не понадобится сегментация или проверка другого окна оттока.
Если модель перестраивается только тогда, когда кто-то просит об этом на совещании, попробуйте Powerdrill Bloom для анализа вашей выгрузки транзакций. На странице нашего CSV AI assistant описан весь рабочий процесс, а в статье о преобразовании CSV в график рассказывается о визуализации результатов.
Часто задаваемые вопросы
Какова формула расчета пожизненной ценности клиента?
Умножьте средний чек на частоту покупок и на среднюю продолжительность жизни клиента, а затем — на валовую маржу. Использование выручки вместо маржи завышает результат и делает любое сравнение со стоимостью привлечения недостоверным.
Как рассчитать пожизненную ценность клиента в Excel?
Сгруппируйте файл транзакций по идентификатору клиента, чтобы получить количество заказов и итоговые суммы. Постройте сетку когортного удержания для определения продолжительности жизни клиента, а затем объедините эти три показателя. Построение сетки удержания — самая трудоемкая часть, которую приходится переделывать при каждом изменении окна оттока.
В чем разница между историческим и прогнозным LTV?
Исторический показатель суммирует то, что клиенты уже потратили, поэтому он всегда занижен, но его легко обосновать. Прогнозный — оценивает будущие расходы на основе допущения об удержании, что делает его более полезным для планирования, но повышает риск ошибки.
Какое соотношение LTV к CAC считается хорошим?
Общепринятым целевым ориентиром считается соотношение три к одному, однако все зависит от структуры вашей маржи и периода окупаемости. Более полезный вопрос — за сколько месяцев клиент окупает затраты на свое привлечение.
Что делать с клиентами, которые давно ничего не покупали?
Определите окно оттока и последовательно применяйте его — для транзакционного бизнеса стандартным правилом является отсутствие покупок в течение 12 месяцев. Зафиксируйте это окно рядом с итоговым показателем, так как его изменение может существенно повлиять на пожизненную ценность.