Сергей Макаров

Клиентская аналитика в Power Pivot

Полгода назад познакомился с блогом Павла Левчука. Павел рассказывает об удержании клиентов, LTV, CAC. Рекомендую. Чтобы глубже погрузиться в тему, прочитал книгу «Маркетинг на основе баз данных». Теперь делюсь опытом: расскажу, как считать retention rate, churn rate, LTV и строить когорты в Power Pivot.

Для начала понадобятся:
— Выгрузка из БД
— Excel с Power Pivot

Обязательные сущности для выгрузки: идентификатор клиента (client_id), дата покупки (order_date), доход (order_revenue).

Как организовать данные

  1. Добавляем выгрузку из БД в модель данных Power Pivot. Называем таблицу orders.
  2. Копируем уникальные client_id в отдельную таблицу Power Pivot и считаем дату первой покупки. Называем таблицу clients.
  3. Для каждой таблицы создаем календарь: calendar_orders и calendar_clients.

Что получилось:

customer data

Расчет метрик и когорт

Считаем уникальное количество клиентов:


#clients:=
CALCULATE(
	DISTINCTCOUNT([client_id])
)

Строим сводную: дату первой покупки ставим в строки, дату текущей покупки в столбцы, количество клиентов в значения.

#clients

Создаем меру ретеншн:


retention:=
DIVIDE([#clients];[#clients_all])

[#clients_all] — суммарное количество клиентов в когорте.


#clients_all:=
CALCULATE(
	DISTINCTCOUNT([client_id]);
	ALL(calendar_orders)
)

Добавляем ретеншн в сводную:
retention

Теперь посчитаем Churn rate:


churn:=
DIVIDE([#clients];[#clients]) - [retention])

retention

Чтобы рассчитать ltv нам нужна мера аккумулятивного дохода:


#revenue_acc:=
CALCULATE(
	SUM(orders[order_revenue]);
	FILTER(
		ALL(calendar_orders);
		calendar_orders[order_date]<= MAX(calendar_orders[order_date])
	)
)

Мера ltv:


ltv:=
DIVIDE([#revenue_acc];[#clients_all])

Добавляем ltv в сводную:
ltv

Поделиться
Отправить