Как просто реализовать расчет LTV в Power BI?

Сегодня мы поговорим о такой важной в продуктовой аналитике метрике, как LTV. В момент, когда я в первый раз столкнулся с потребностью ее расчета, у меня разбежались глаза от обильности статей с вариантами расчета этого показателя. Я не знал, какой из вариантов расчета наиболее точный, а самое главное, все они предлагали мне считать его вручную. Меня это категорически не устраивало, и я продолжал искать возможность считать все просто и быстро, не выгружая кучу транзакций клиентов каждый раз, когда мне хотелось узнать, что там происходит с LTV.

Спойлер: способ я нашел, и он вполне меня устраивает.

Перейдем сразу к делу. Мой вариант автоматического расчета показателя LTV базируется на статье из блога GoPractice. В ней хорошо описывается способ расчета, показываются примеры того, какие показатели лучше не использовать при его расчете, а также приводятся аргументы в пользу описанного способа.

Если при прочтении данной статьи у вас возникнут вопросы к методологии, то уверен, что вы найдете все ответы в статье из блога GoPractice.

В нашей модели данных потребуется всего три таблицы. Первая таблица с всеми транзакциями и клиентами, которые их совершали (DBOrder). Вторая с датой регистрации клиентов для создания когорт (DBClient), и третья — для создания сводной таблицы для клиентов (pivot_table). Связь таблиц происходит по столбцу client_id:

DBOrder[client_id]* <> (1)DBClient[client_id] и DBOrder[client_id]* <> *pivot_table[client_id].

Для начала проведем подготовку и создадим несколько столбцов с датами по 30 дней с момента первого заказа. Это делается для удобства представления временных периодов.

В таблице DBOrder создадим столбец с датой первого заказа, чтобы в дальнейшем узнать, сколько дней прошло с момента первого заказа.

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

И в заключении, разбиваем промежутки на группы по 30 дней для того чтобы в дальнейшем знать, какой показатель LTV на 30, 60, 90 и т.д. день. Можно разбить и на другие промежутки, например, дни или недели, все зависит от специфики вашего бизнеса.

С подготовкой таблицы DBOrder мы закончили. В итоге у вас должно получиться нечто подобное.

Можно приступать к созданию сводной таблицы клиентов. По сути, она нужна для удобства отображения и уже будет включать в себя данные об LTV. В моем случае, я создал ее следующим образом.

Таким образом, мы получаем таблицу, в которой весь доход с клиента распределен по периодам с момента первого заказа.

Далее необходимо посчитать накопительную сумму этого дохода для каждого клиента, последовательно суммируя все периоды. Для этого сначала необходимо проранжировать по порядку каждый период для каждого клиента.

Теперь можно получить накопительный итог дохода.

А чтобы в дальнейшем мы могли использовать показатель LTV в разного рода визуализациях, создадим меру, в которой считается сам показатель.

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

Предварительно в таблице DBClient необходимо создать меру, которая при добавлении в pivot_table будет давать нам дату регистрации клиента, которая будет выступать в качестве когорты.

Добавляем меру в pivot_table.

В конце концов таблица pivot_table должна принять следующий вид.

В области представления отчета теперь мы можем добавить различные элементы визуализации, такие как сводные таблицы с показателем LTV как для всей когорты, так и для отдельного клиента.

Для более наглядной визуализации можно построить график.

Вывод: таким образом, использование данного подхода обеспечивает эффективный и гибкий метод расчета LTV, что может быть крайне полезным для более глубокого понимания стоимости клиентов в течение их жизненного цикла. Здесь не требуется постоянное жонглирование выгрузками клиентов и их транзакциями. По нажатию одной кнопки можно узнавать об изменении LTV даже после каждого нового заказа клиента. Этот способ обеспечивает постоянный и бесперебойный доступ к одной из самых важных продуктовых метрик.