8513

Расчёт программы лояльности, наш подход: Power BI + 400 таблиц в Excel

Наша команда бизнес-аналитиков умеет собирать данные о бизнесе: количестве и суммах покупок, клиентах и их лояльности, кампаниях и вложенных бюджетах — в единый отчёт, и на цифрах разбираться, что происходит в маркетинге и продажах.

Эта работа позволяет посмотреть на бизнес с точки зрения реальных данных. В том числе — рассчитать финансовую модель программы лояльности, которая будет поддерживать бизнес-цели.

Такой проект мы осуществили в прошлом году.

Команда проекта

Владимир Ильин
Владимир Ильин
консультант
Мария Терентьева
Мария Терентьева
ведущий менеджер
проекта
Валерий Куракин
Валерий Куракин
руководитель отдела
аналитики

Выбрали тип программы лояльности

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

Для этого типа нужно посчитать:

  • при каких суммах скидка будет расти;
  • какие будут скидки;
  • стартовая сумма, при которой клиент вступает в программу лояльности;
  • сколько дополнительной прибыли бизнесу может принести такая программа лояльности.

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

Проанализировали данные о клиентах и покупках

Мы выгрузили данные о покупках из онлайн-магазина и офлайн-касс и на их основе нашли соотношение лояльных и нелояльных клиентов:

За лояльных принимали тех, кто покупал больше одного раза.

А дальше разложили их на более детальный график — рассмотрели временной промежуток между покупками, чтобы видеть, как часто клиенты совершают покупки:

По горизонтали — длительность между первой и последней покупкой, по вертикали — количество клиентов. Цвет означает количество покупок.

Потом уже рассмотрели лояльных по количеству покупок и временному промежутку между покупками:

Здесь на столбцы добавили точное количество клиентов. Теперь детально видно поведение лояльных клиентов. Читается следующим образом. Сиреневый сегмент — те, кто покупал дважды: 3018 клиента совершили две покупки за один месяц, 827 — две покупки за два месяца, 615 — за три и так далее. Бирюзовый сегмент: 285 клиентов совершили три покупки за один месяц, 192 — три покупки за два месяца, 194 — три покупки за три месяца.

Отдельный фильтр позволял узнать количество лояльных клиентов с определёнными суммами покупок:

Построили прогноз продаж

У нас были данные:

  • количество клиентов в прошлый период за каждый месяц;
  • тенденция продаж;
  • какой процент клиентов сколько покупок совершает.

Из этих данных мы собрали прогноз на период с октября 2019 по сентябрь 2020 — как раз тогда, когда новая программа лояльности должна быть внедрена.

Это был «голый» прогноз — какими будут продажи, если не внедрять программу лояльности.

Прикинули возможные финансовые модели

Следующий шаг — гипотеза по поводу сумм покупок и процентов скидок.

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

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

Мы с клиентом выбрали три варианта для расчёта:

Модель программы лояльности №1

Скидка Суммы перехода в новый статус: 0% 0 – 2 999 руб. 5% 3 000 – 9 999 руб. 10% 10 000 – 16 999 руб. 15% от 17 000 руб.

Модель программы лояльности №2

Скидка Суммы перехода в новый статус: 5% 0 – 2 999 руб. 7% 3 000 – 9 999 руб. 12% 10 000 – 16 999 руб. 15% от 17 000 руб.

Модель программы лояльности №3

Скидка Суммы перехода в новый статус: 5% 0 – 2 999 руб. 10% 3 000 – 9 999 руб. 15% 10 000 – 16 999 руб. 20% от 17 000 руб.

Однако в расчёте надо учитывать, сколько новых клиентов придёт. Мы не знаем точно, какой будет прирост, но можем предположить из прогнозных данных. Чтобы наши предположения работали лучше, мы предположили три варианта притока новых клиентов: пессимистичный, умеренный и оптимистичный.

Однако это может зависеть и от скидок тоже. Возможно, чем больше скидка, тем больше клиентов придёт. Поэтому в зависимости от модели гипотезы приток может быть разным:

То есть, каждую модель мы в результате просчитывали в трёх вариантах: пессимистичном, умеренном и оптимистичном. Плюс отдельно для онлайна и офлайна, поскольку динамика продаж в них различалась.

Посчитали количество клиентов в потенциальных статусах программы лояльности

Уже имеющиеся данные по клиентам, покупкам и суммам покупок мы разложили по статусам потенциальных программ лояльности. Теперь мы понимали, какой процент людей на каких статусах находится:

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

В выделенных группах людей есть те, кто совсем далёк от нового статуса, и те, кто близок к получению скидки. Например, статус начинается с 3000 рублей, а покупатель потратил 700 рублей — такой покупатель далёк от получения статуса. Те, кто потратил 2800 рублей, гораздо ближе к скидке. Их проще сконвертировать в новый статус. Так что мы выделили группы клиентов по признаку близости к статусам:

Чем больше клиент покупал, тем он лояльнее. Те, кто уже купил на 15 000 рублей, лояльнее, чем те,у кого накопилось 2400 рублей. Им допродать проще, поэтому шаги приближения к статусам тем крупнее, чем выше статус.

Спрогнозировали дополнительный доход от внедрения программы лояльности

Заключительная часть — посчитать аплифт — какой дополнительный доход может получить компания от внедрения программы лояльности.

Для этого в таблицу собрали данные:

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

Эти расчёты мы провели по месяцам для каждого варианта программы лояльности в трёх прогнозах (пессимистический, умеренный, оптимистический), отдельно для офлайна и онлайна. В результате получили аплифт по месяцам на будущий период для каждого варианта.

Плюс отдельно брали покупки до 4000 рублей, по ним считали аплифт сразу за год. Хотя 4000 рублей не даёт статуса, этим клиентам полагается подарок, поэтому их тоже надо было учесть.

Выбрали самую выгодную финансовую модель программы лояльности

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

Под словом «выгодный» мы понимаем соотношение затрат на подарки, скидки и доставку и аплифт.

Поскольку расчёты — это только прогноз, работа на них не заканчивается. После внедрения нужно следить, как новая программа лояльности работает для клиента и насколько она совпадает с прогнозом, а при необходимости вносить изменения.

Результат

101 024 082 рублей

такой аплифт даст рассчитанная программа лояльности при пессимистичном развитии событий

400 таблиц в Excel

потребовалось для расчёта