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

Клиентская аналитика в 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

0 комментариев

Коннектор к Яндекс Директ на Python с сохранением в csv

Делюсь коннектором к Яндекс Директ на Python. Скрипт получает статистику по кампаниям с первого числа текущего месяца по вчерашний день и сохраняет в csv.

Какие данные на выходе:

— Дата
— Идентификатор кампании
— Название кампании
— Показы по поисковым кампаниям
— Показы по РСЯ
— Клики по поисковым кампаниям
— Клики по РСЯ
— Расходы по поисковым кампаниям в условных единицах
— Расходы по РСЯ в условных единицах

Скачать скрипт с GitHub

0 комментариев
,

Как объединить данные из MCF API и CORE API

Так сложилось, что данные по многоканальным последовательностям в Google Analytics выделены в отдельное API: Multi-Channel Funnels Reporting.
В интерфейсе Google Analytics нельзя построить отчет по параметрам и метрикам MCF API → нужно выгружать сторонними инструментами.

Покажу, как объединить данные MCF API и CORE API, и как построить отчеты на основе этих данных.

Запросы и обработка данных

Используем аддон Google Analytics для Spreadsheet.
1

Параметры MCF:


mcf:transactionId
mcf:conversionDate
mcf:basicChannelGroupingPath
mcf:pathLengthInInteractionsHistogram
mcf:timeLagInDaysHistogram

Метрики MCF:

mcf:totalConversionValue

Параметры CORE:

ga:transactionId
ga:productName

Метрики CORE:

ga:itemRevenue

Фильтруем только нужные конверсии:

mcf:conversionType==Transaction

Получили данные → теперь обработаем их. Например, в Power Query.

Получаем данные from web:

2

После обработки присоединяем таблицу MCF к таблице CORE через функцию Merge:
3

Выгружаем на лист и строим сводную.

Отчеты

Отчет показывает зависимость между ценностью корзины и количеством дней до транзакции:
dayshistogram

Видим, что наибольший доход приходится на корзины в диапазоне 500-2499 и длиной пути в 1 день.

Отчет показывает зависимость между ценностью корзины и количеством взаимодействий до транзакции:
interactionshistogram

Можно построить и другие отчеты. Например, исследовать зависимость между параметрами ga:productName, mcf:timeLagInDaysHistogram и метрикой mcf:totalConversionValue.

0 комментариев
, ,

Шпаргалка по семантике

1-2 раза в месяц сталкиваюсь с задачей проверки семантики на охват и качество проработки. Для упрощения жизни сделал себе шпаргалку по типам ключевых слов. Делюсь:

— Напрямую сформулированные ключевые слова
— Ключевые слова, отражающие суть проблемы
— Ключевые слова, описывающие симптомы
— Названия или маркировка продуктов и моделей
— Информационные
— Транзакционные
— Смежные тематики
— Искусственная семантика (генерация)
— Брендовые ключевые слова
— Конкуренты
— ЭХО Wordstat
— СВЧ
— Подсказки прогноза бюджета
— ГЕО (вплоть до улицы)
— Другие части речи
— Синонимы
— Транслит
— Ошибки
— Сленг
— Аббревиатуры

0 комментариев
,