Матрица кросс-продаж в Power BI и Power Pivot
Все вы наверняка много раз видели в маркетплейсах блок "вместе с этим товаром обычно покупают", где к пиву вам услужливо предложат чипсы, к вину - сыр, а к мылу - верёвку. Подобный подход называют ещё кросс-продажами (cross-sell) и, при правильном использовании, он позволяет ощутимо увеличить средний чек и общую удовлетворенность клиента.
Основным инструментом анализа в таких случаях является матрица кросс-продаж (cross-selling matrix) - таблица, которая помогает визуализировать возможности взаимных продаж между различными продуктами или категориями товаров. Она показывает, какие продукты хорошо продаются вместе, а какие - нет, и стоит ли, например, клиенту, купившему товар А, рекомендовать товар Б, или поставить эти товары рядом на сайте или на полке в магазине.
Структура этой таблицы предельно проста - по строчкам и по столбцам откладываются один и тот же набор интересующих нас товаров (или категорий), а на пересечении - числовой показатель, характеризующий кросс-продажи, например, количество клиентов, купивших оба товара, отложенных по осям X и Y:
Само собой, эта квадратная матрица будет симметричной относительно диагонали, ячейки на которой обычно оставляют пустой.
Давайте разберём как можно рассчитать подобную таблицу с помощью языка DAX в Power BI или Power Pivot в Microsoft Excel.
Модель данных
Чтобы не усложнять, рассмотрим всё на предельно простой модели данных, состоящей всего из двух таблиц. Первая - таблица продаж Sales , состоящая из столбцов с кодом товара (ProductID), кодом клиента (CusomerID) и количества проданных штук (Units):
Вторая таблица Products - примитивный справочник по товарам, состоящий только из списка уникальных ID товаров:
Обе таблицы связанны по артикулу товара ProductID:
Строим матрицу
Первое, что нам придётся сделать - это продублировать справочник по товарам, чтобы их можно было отложить по обеим осям нашей будущей матрицы кросс-продаж. Для этого выберем команду Главная - Создать таблицу (Home - Create table) и введем в строку формул:
Products2 = Products
Полученная таблица-дубликат является, по сути, вспомогательной и привязывать её к другим таблицам в модели мы не будем.
Затем можно вернуться в режим отчёта и добавить визуализацию Матрица (Matrix) - аналог сводной таблицы из Microsoft Excel. В область строк закинем поле Product ID из исходной таблицы Products, а в область столбцов - одноименное поле из таблицы-клона Products2. Получим заготовку будущей матрицы кросс-продаж:
Обратите внимание, что поскольку таблицы Products и Products2 у нас не связаны, Power BI не сможет полноценно отобразить матрицу на странице и выдаст предупреждение. Игнорируем его и танцуем дальше - наша таблица отобразится, когда мы зададим логику её вычисления в виде меры.
Пишем меру кросс-продаж
Дальше самое интересное - мы должны создать меру, которая будет вычислять количество клиентов, купивших оба товара не пересечении каждой строки и столбца в нашей матрице. Для этого щёлкнем правой кнопкой мыши по таблице, где мы хотим сохранить нашу меру (например по таблице Sales или по любой другой - это роли не играет) и выберем команду Создать меру (New measure).
Общая идея меры такова - для каждой ячейки в области значений нашей матрицы мы:
- Формируем список уникальных клиентов, купивших товар по оси X
- Формируем список уникальных клиентов, купивших товар по оси Y
- Ищем количество пересечений этих двух списков, т.е. количество клиентов, купивших оба товара.
В строке формул введём следующую конструкцию:
CrossSales =
VAR CustomersPurchasedProduct1 = VALUES( Sales[CustomerID] )
VAR CustomersPurchasedProduct2 =
CALCULATETABLE (
VALUES( Sales[CustomerID] ),
ALL( Products ),
TREATAS (
VALUES( Products2[ProductID] ),
Sales[ProductID]
)
)
RETURN
IF(
SELECTEDVALUE( Products[ProductID] ) = SELECTEDVALUE( Products2[ProductID] ),
BLANK(),
COUNTROWS(
INTERSECT(
CustomersPurchasedProduct1,
CustomersPurchasedProduct2
)
)
)
Логика тут следующая:
- Создаем переменную CustomersPurchasedProduct1, куда с помощью функции VALUES извлечём список уникальных ID клиентов, купивших первый товар, который мы положили в область строк нашей матрицы.
- Чтобы найти список клиентов купивших второй товар CustomersPurchasedProduct2 (из шапки матрицы), нужна конструкция похитрее, т.к. таблица Products2 у нас с таблицей продаж Sales не связана. Так что нам придется использовать функцию CALCULATEDTABLE, чтобы создать таблицу, где мы сформируем список уникальных клиентов с помощью функции VALUES, затем уберем с неё все фильтры функцией ALL и, наконец, добавим фильтр по второму товару с помощью функции TREATAS.
- Останется найти общие элементы этих двух списков с помощью функции INTERSECT и подсчитать количество строк в полученном пересечении с помощью функции COUNTROWS.
- Чтобы в ячейках по диагонали не отображать ничего - добавим проверку на одинаковость названий товаров в первой и второй покупке с помощью функции IF.
Ну и для наглядности тут, конечно, стоит убрать из нашей таблицы итоги и добавить к ней подсветку маленьких и больших значений цветом с помощью условного форматирования. Для этого развернем меню созданной меры в области значений и выберем команду Условное форматирование - Цвет фона (Conditional formatting - Background color), а затем настроим палитру любым желаемым образом.
Наша матрица кросс-продаж готова:
Матрица кросс-продаж в Microsoft Excel
Все сделанное можно легко повторить и в Microsoft Excel, используя надстройку Power Pivot. Для этого мы:
- По очереди загружаем исходные таблицы с помощью кнопки Добавить в Модель данных (Add to Data Model).
- Затем связываем таблицу продаж Sales со справочником по товарам Products по столбцам Customer ID.
- Создавать таблицы "с нуля" формулами Power Pivot, к сожалению, не умеет, так что просто дублируем справочник по товарам, переименовываем в Products2 и грузим его повторно (но уже ни с чем не связываем)
- Строим сводную таблицу по собранной Модели данных кнопкой Главная - Сводная таблица (Home - Pivot table) и помещаем поле Products ID из таблицы Products в область строк, а поле Products ID из таблицы Products2 в область столбцов.
- Создаем меру с той описанной выше формулой на DAX, используя команду Power Pivot - Меры - Создать меру (Power Pivot - Measures - New measure) и помещаем её в область значений сводной таблицы.
Ссылки по теме
- Основы работы в Microsoft Power BI
- Как сравнить два любых объекта (товара, периода) в Power BI
- План-факт анализ в Microsoft Excel с Power Pivot и Power Query
в 365 офисе такой результат
не распознает TREATAS и SELECTEDVALUE
Можно посмотреть через Файл - Учетная запись.
Скачиваю архив, обновляю сводную, всё исчезает(
Сайт пишет, что эта функция появилась в Excel, начиная с v16.0.9.17
Можно попробовать заменить на связку IF + HASONEVALUE + VALUES:
Это оффтоп конечно, но вдруг кому пригодится.
А я эти два события не связывал.
Когда только начинал пользоваться PowerPivot в Excel все меры были в таблице данных, а не отдельно и действительно данные были. Первые 1000 строк вроде как.
А потом они пропали...Понятно почему 🥸
По-моему в тексте есть опечатка в имени функции CALCULATETABLE. Так в блоке текста "Логика тут следующая:" имя функции приводится как CALCULATEDTABLE (с лишней буквой D в середине).