Оптимизация ассортимента с помощью Power BI

Бизнес-контекст

Американская компания по производству велосипедов Pro Bikes Inc. продает различные велосипеды и аксессуары для них, такие как шлемы, шины, наколенники, бутылки для воды и т. д. У компании есть два основных канала продаж: сеть магазинов и Интернет-магазин. В рамках онлайн-канала Pro Bikes продает велосипеды со своего собственного сайта, а также с других популярных онлайн-платформ.

Обозначение проблематики

Компания производит и продает 3 модельных ряда велосипедов, каждый из который имеет несколько размеров, цветовых решений, а также технических характеристик. В общей сложности компания реализует 125 уникальных велосипедов. Управление таким большим количеством SKU (единиц хранения) сопряжено со значительными затратами на инвентаризацию. Руководство компании хочет оптимизировать продуктовый ряд, чтобы скорректировать ассортимент продукции. Эта задача была поручена ее главному бизнес-аналитику.

Бизнес-анализ

Как бизнес-аналитик, я в первую очередь определил рамки задачи. Она включает в себя создание методики по определению продукции, которая имеют финансовый смысл для компании. Я выделил три группы, на которые необходимо разделить продукцию. Главная цель распределения продуктов по категориям заключается в выработке рекомендаций на основе данных о том, стоит ли выводить из эксплуатации продукты, которые не приносят прибыли, чтобы эффективнее использовать ресурсы компании на другие продукты. В данном случае речь идет о следующих категориях.

Топовые позиции – продукция с большим объемом продаж и высокой наценкой;
Позиции нижнего уровня – продукция с небольшим объемом продаж и низкой наценкой;
Продукция среднего уровня – продукция со средним объемом продаж и средней наценкой

Определение основных требований

Эти три категории являются частью основных требований:

Доступность данных

Поскольку информация о клиентах является конфиденциальной, данные хранятся на защищенном SQL-сервере компании. Для проведения анализа мне предоставили доступ к базе данных SQL только для чтения. Информация о продажах доступна в таблице "dbo.FactInternetSales". В дополнение к базе данных SQL я буду использовать лист Excel, содержащий сопоставление продукции с категориями, обозначенными выше.

Предварительные требования

Помимо пакета приложений Microsoft Office, нам понадобятся еще несколько важных ПО:

Power BI Desktop - программное обеспечение для визуализации данных с открытым исходным кодом, созданное Microsoft как часть средств Microsoft Business Intelligence.
1.1 Загрузить Power BI можно здесь.
1.2 Пошаговая инструкция по установке доступна по ссылке.
Microsft SQL Server - реляционная система управления базами данных, разработанная компанией Microsoft. Используется для хранения, поиска и преобразования данных.
2.1 Загрузить SQL Server 2019 можно здесь
2.2 Пошаговая инструкция по установке SQL Server 2019 доступна по ссылке.
Microsoft SQL Server Management Studio - программное приложение, впервые запущенное с помощью Microsoft SQL
3.1 Загрузить SQL Server Management Studio 18.10 можно здесь
3.2 Пошаговая инструкция по установке продукта доступна по ссылке.

Извлечение, преобразование и загрузка данных

1. Создайте запрос для получения данных из БД SQL

SELECT
      [CustomerKey]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[DiscountAmount]
      ,[CustomerPONumber]
      ,[OrderDate]
  FROM [AdventureWorksDW2019].[dbo].[FactInternetSales]

2. Загрузите данные в Power BI и примените необходимые преобразования данных в Power Query Editor

2.1 Изменение типа данных: Тип данных, содержащихся в столбце “Дата Заказа” в таблице Клиентов - Дата\Время

Для анализа компонент времени значения не имеет. Используя опцию трансформации изменяем тип данных - “Дата\время” превращается в “Дата”

2.2 Подключите опции качества столбцов и распределение столбцов: Параметры «Качество столбцов» и «Распределение столбцов» активируются в разделе Вид. Эти параметры помогают проверить имеющиеся данные на наличие ошибок или пустых значений.

3. Добавьте таблицу с календарем

Для того, чтобы при анализе данных применить срез по годам, месяцам и дням, необходима таблица с календарем. DAX предоставляет функцию "Календарь", которой необходимо ввести начальную и конечную даты. Для создания динамической таблицы используйте следующий код DAX:

Calendar_Table =
//Calculate Start Date
var start_date = MIN(Sales_Table[OrderDate])

//Calculate End Date
var end_date = MAX(Sales_Table[OrderDate])

RETURN
    // Create a dynamic calendar table
    CALENDAR ( start_date, end_date)

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

4. Создайте модель данных

Для одновременного использования всех трех таблиц между ними необходимо определить отношения. Календарь и таблица "Продажи" связаны отношением Orderkey-Date. Таблица «Продажи» и таблица «Категория продукт» связаны за счет ключа productkey (общему для них). Окончательная модель данных выглядит так, как показано ниже.

Создание визуализаций

1. Слайсеры для выбора данных из Продукции и Категорий продукции: Столбцы "НаименованиеПродукта" и "КатегорияПродукта" отображены в слайсерах:

2. Карточка «Общий объем продаж»: "Продажи" отражены в карточке:

3. Карточка «Средняя маржа»: Для отображения маржи необходим новый столбец. Маржа может быть рассчитана следующим образом: маржа продукта = продажи - себестоимость - налог. Новый столбец отображается в виде карточки

Product Margin =
var margin = CALCULATE(SUM('Sales Table'[SalesAmount]) - SUM('Sales Table'[TotalProductCost]) - SUM('Sales Table'[TaxAmt]))
RETURN
IF(margin = 0, 0, margin)

4. Карточки «Продукты с большим объемом продаж и высокой маржой» и «Продукция с небольшим объемом продаж и низкой маржой»: Для определения продукции с самыми высокими показателями продаж и маржи необходимо ранжировать как объем продажи, так и маржу. Новый столбец называется «Рейтинг продаж»:

Далее создаются два новых столбца под названием «Процент маржи» и «Рейтинг маржи»:

Теперь, для объединения рейтинга продаж и рейтинга маржи создается еще один новый столбец – «Совокупный рейтинг»:

Фильтр Top N используется для отображения товаров с большим объемом продаж и высокой маржой, а также для товаров с небольшим объемом продаж и низкой маржой.

Код DAX представлен ниже:

Sales Rank =
var bike_found = SEARCH("Bike", 'Product Category'[Product Category],1,0)
RETURN
IF(bike_found =1, RANK.EQ('Product Category'[Product Sales], 'Product Category'[Product Sales], DESC), -1)
Margin Percentage = DIVIDE('Product Category'[Product Margin],'Product Category'[Product Sales],  0)
var bike_found = SEARCH("Bike", 'Product Category'[Product Category],1,0)
RETURN
IF(bike_found =1, RANK.EQ('Product Category'[Margin Percentage], 'Product Category'[Margin Percentage], DESC), -1)
Cumulative Rank =
IF(OR('Product Category'[Sales Rank] = -1, 'Product Category'[Margin Rank] = -1) = FALSE, 'Product Category'[Sales Rank] * 'Product Category'[Margin Rank], -1)

5. Круговая диаграмма «Вклад в продажи по категориям»: Для идентификации вклада категорий продукции в общий объем продаж в таблице «Категория продукта» создаем новый столбец. С его помощью отображается следующая круговая диаграмма:

Sales Contributor Category =
SWITCH(TRUE(),
'Product Category'[Sales Rank] = -1, "NA",
'Product Category'[Sales Rank] <= 25, "Top Contributor",
'Product Category'[Sales Rank] >= 75, "Bottom Contributor",
"Middle Contributor"
)

6. Дерево декомпозиции «Вклад в общий объем продаж по брендам» : Объем Продаж продукции анализируются по Категориям продуктов и Наименованиям продуктов с помощью визуального дерева декомпозиции:

7. Таблица «Продажи и маржа»: Визуальная таблица Метрикс показывает объем продаж и маржу для каждого продукта.

Вывод

Из 150 моделей первые 25 продуктов по объему продаж обеспечивают почти 60 % продаж компании. Согласно полученным данным, "Road 150 Red 48" является самым продаваемым продуктом компании. Объем продаж данного продукта составил $1,2 млн, а прибыль - $0,3 млн;
50 товаров попадают в категорию "Позиции нижнего уровня", которые приносят менее 5% продаж. «Mountain 300 Black 38" - это продукт, который генерирует небольшой объем продаж и низкую маржу. Основная рекомендация - рассмотреть возможность снятия продукта с производства. В случае наличия запасов данного продукта компании рекомендовано провести распродажу.
Продукты категории «Позиции среднего уровня» генерируют 36 % продаж. Подробная информация об этих продуктах передается отделу продаж и маркетинга. Основная рекомендация - определить факторы, обеспечивающие успех продукции, относящейся к категории «Позиции верхнего уровня» и проработать их для товаров из категории «Позиции среднего уровня».