main.py - основной файл для обработки
data files - файлы в таблицами данных
archive -Пустой. Сюда идет отработанный код
sql_scripts - sql скрипты для dwh
py_scripts - скрипты для использования main.py
a. Предметная область для предоставленных данных связана с коммерцией и электронной коммерцией (e-commerce), конкретно сосредоточенной на магазине велосипедов под названием "99Bikers" в Австралии.
Данные включают информацию о продуктах (product_id, brand, product_line и др.), клиентах (first_name, last_name, gender и др.), транзакциях (transaction_date, online_order, order_status и др.) и адресах клиентов (address, postcode, state, country и др.).
Такой тип данных обычно встречается в магазинах и интернет-магазинах, продающих товары оффлайн и онлайн, например, в магазине велосипедов.
b. Применимость кейса. На основе магазина велосипедов "99Bikers" в Австралии можно выделить несколько потенциальных вариантов использования или сценариев для бизнеса. Созданный ETL пайплайн позволяет приступить к решению следующих задач:
-
Сегментация клиентов и Таргетированный маркетинг: Мы можем использовать демографические данные клиентов, их предыдущие покупки и интересы в области велосипедов для разделения клиентов на разные группы (например, случайные райдеры, энтузиасты) и создания таргетированных маркетинговых кампаний, которые будут соответствовать их предпочтениям и потребностям.
-
Управление запасами на складах и повторные заказы: Анализируя данные о продажах товаров для прогнозирования спроса и обеспечения оптимальных уровней запасов мы можем определить когда следует повторно заказать популярные товары, как управлять хранящимся на складах и избавляться от избыточного товара.
-
Анализ корзины покупателя: Изучая данные о транзакциях клиентов, мы можем выявить частые совместно покупаемые товары. Это может помочь создавать комплекты товаров или рекомендации, увеличивающие возможности кросс-продаж.
-
Прогнозирование оттока и удержание клиентов (Churn): Используя историю клиента, покупок и других атрибутов мы можем спрогнозировать возможный оттока клиентов и перспективы внедрения стратегий их удержания.
-
Оптимизация цен: Проанализировав список цен, себестоимость и истории покупок мы можем максимизировать доходы при сохранении конкурентоспособности.
-
Стратегия запуска продукта: Проанализировав исторические данные о продажах и предпочтениях клиентов мы можем выработать стратегию успешного запуска новых продуктов, например лучшее время и методы для введения новых товаров на рынок.
-
Улучшение обслуживания клиентов и user-experience: Используя данные о предыдущих покупках и взаимодействиях мы можем улучшить качество обслуживания клиентов и выявить общие проблемы или запросы и принять меры по их предотвращению.
-
Предсказание онлайн-оффлайн закупки: Используя данные о предыдущих покупках, пользователей, бренде, размере товара и стоимости, мы можем предсказывать каким образом будет осуществляться выдача заказа, что позволит правильнее распределять товары по магазинам и логистическую нагрузку.
c. Структура хранилища и парсинг Данные загружены с помощью парсинга и выгрузки из сети интернет в локальное в хранилище со следующей структурой:
Стейджинговая таблица STG_TRANSACTIONS
Стейджинговая таблица STG_CUSTOMER_DEMPGRAPHICS
Стейджинговая таблица STG_NEW_CUSTOMERS_LIST
Стейджинговая таблица STG_CUSTOMER_ADRESSES
Основная таблица DWH_FACT_TRANSACTIONS
Основная табл. DWH_DIM_CUSTOMER_DEMOGRAPHIC
Основная таблица DWH_DIM_CUSTOMER_ADRESSES
Основные таблицы имеют триггеры создания и дополнения данных, обновление осуществляется согласно SCD1.
Итоговая таблица-витрина выводится на основе объединения Основных таблиц в DWH_DATAMART.
Исходные данные представляют собой excel файл с четырьмя листами.
-
Лист с транзакциями операций. Включающий в себя 20 000 записей и 12 столбцов – айди продукта, клиента, дата продажи, онлайн\оффлайн заказ, статус заказа, бренд, линейка товаров, класс товара, размер, рекомендуемая цена продажи, ожидаемая цена продажи, дата первой продажи продукта.
-
Лист с информацией о клиентах. Включает в себя 4000 записей и 12 столбов – имя, фамилия, пол, покупки велосипедных товаров за последние три года, дата рождения, должность, сфера деятельности, класс обеспеченности, бинарный индикатор жив\мертв клиент, владеет ли авто, срок владения. Один параметр “default” содержит мусорные данные.
-
Лист с информацией о новых клиентах. Включает в себя 1000 записей и 23 столбца по большей части состоящий из двух листов – о клиентах и их адресах. Имя, фамилия, пол, покупки велосипедных товаров за последние три года, дата рождения, должность, сфера деятельности, класс обеспеченности, бинарный индикатор жив\мертв клиент, владеет ли авто, срок владения. Пять скрытых столбцов не имеют названия. Есть столбцы ранг и значение.
-
Лист с информацией об адресах клиентов. 3999 значений с 6 пропусками в индексах, 5 столбцов – адрес, почтовый код, штат, страна, стоимость недвижимости.
Блок трансформации представляет собой операции осуществляемые над данными перед и после подачи в стендинговый слой с помощью библиотек pandas и sqlite3. Данные приводятся к единым индексам, осуществляется компоновка и объединение данных.
Финальные данные представляют три таблицы – таблица транзакций, таблица клиентов и таблица адресов.
Стендинговая таблица новых клиентов была преобразована так, чтоб дополнить таблицы клиентов и адресов.
Далее эти три таблицы объединяются в витрину для дальнейшей работы модели машинного обучения и визуализации.
BPMN
a. База данных
SQLite b. Компоненты Python SQL Docker Airflow
Архитектура пайплайна представляет собой последовательную обработку исходных файлов формата (excel) с последующей записью данных в базу (SQLite).
Весь код обработки данных написан на языке python и SQL.
Раз в день запускается скрипт и в базу добавляются новые данные из нового файла.
Сама база данных представляет собой двухуровневое хранение данных (стейдинговый слой и основной). Отношения сущностей в базе представлено в 1НФ.
При анализе исходных данных были сделаны выводы, что табличную структуру хранить выгоднее в реляционной базе.
Так же, при недостатке ресурсов можно воспользоваться нормализаций и привести отношение сущностей к 4НФ.
Stage - это промежуточные данные, ждущие обработки, хранящиеся в виде файлов. Поэтому, нам удобнее всего использовать файловую СХД (например s3).
Дальнейшее преобразование и запись данных будет осуществляться в реляционной базе SQLite.
Вся работа автоматизируется и с использованием триггеров и шедулеров может быть запущена в любой выбранный или заданный заранее момент времени с помощью инструмента Airflow.
Все элементы – от загрузки данных, создания БД и СХД до трансформации предсказания необходимого результата моделью машинного обучения осуществляются автоматически используя контейнеризацию Docker для подтягивания зависимостей и создания окружения, а также Airflow для контроля процессов на всех этапах выполнения работ.
Процессы в Airflow решено выполнять последовательно, поскольку каждый последующий зависит от выполнения предыдущего
Для обработки исходных данных используется язык python и библиотека pandas. Дальнейшая трансформация и обновление данных происходит на стороне базы данных с помощью запросов SQL.
Взаимодействие оcуществляется с помощью sqlite3.
Для визуализации данных используется витрина DWH_DATAMART до предобработки данных и ОНЕ или Лейбл-энкодинга переменных, для лучшей интерпретируемости данных.
Инструментом визуализации выбран Yandex DataLens как мощный отечественный инструмент для создания чартов и дашбордов. Ниже приложены графики созданные в данном инструменте.
9.4. Распределение наиболее заказываемых брендов и размеров велосипедов (гипотеза – товар большего размера чаще заказывают онлайн чем оффлайн).
‘Approved’:179 ‘Cancelled’: 19821
Таким образом нам не помогут даже техники апсемплинга для создания синтетических данных из библиотек для апсемплинга （upsample, SMOTE, TOMEK, SMOTEEN, SMOTETOMEK. И единственный способ это даунсемплинг превалирующего класса до размеров минорного класса.
В данной задаче мы используем линейную модель классификации Логрег поскольку у нас бинарно разбиты классы. Учитывая приведенную выше инофрмацию о дисбалансе классов, мы получаем следующий результат на тесте:
На первый взгляд это выглядит как неплохой результат работы, но если взглянуть на макро по accuracy, можно увидеть, что модель угадывает лишь 50% из двух классов, что идентично тому если бы она всегда выбирала один класс (Approved), учитывая другие метрики и качество 0.99-1 модели «выгодно» назначать любые инпуты лишь одним классом.
Исходя из этого можно сделать вывод, что модель абсолютно бесполезна и отрабатывает не лучше чем выбор случайного значения значениями 0 и 1.
Таким образом мы применяем технику даунсемплинга для того чтобы модель имела «честное» представление о классах что представлены в равной мере.
В нашем пайплайне мы как и раньше используем докер и аирфлоу, но добавляем к нему следующие элементы представленные в отдельном DAGе аирфлоу:
Еще один этап препроцессинга и чистки данных, на котором мы проводим One-hot encoding и последние трансформации перед подачей в модель.
Разбиение данных на трейн и тест для обучения\переобучения модели.
Обучение и сохраниение модели используя млфлоу\пикл
Логгирование в млфлоу метрик и результатов работы модели
Загрузка модели из млфлоу model registery\пикл и предсказание на новых данных
Логгирование результатов работы модели в csv и sql базу данных
На тренировочном датасете модель неплохо выучивает сам датасет и показывает неплохие результаты даже при равном распределении классов.
Перечень угроз:
Несанкционированный доступ в базу (расположена во внутреннем контуре с ограниченным доступом)
Неверно заданные права доступа и подбор пароля
Права доступа к хранимой информации (ограниченный круг лиц, кто может взаимодействовать с этой информацией)
Защита от внешних атак (SQL injection)
Отсутствие резервного копирования и регулярных бекапов.
Магазин 99Bikes собирает большое количество данных о клиентах, не связанных непосредственно с коммерческой деятельностью, но способной создавать полный портер покупателя.
Результат ETL можно использовать для выполнения задач перечисленных в п. 1b. Выгрузка обработанных данных подходит для дальнейшего разведочного анализа данных и использования машинного обучения.
Для оптимизации базы данных, можно привести БД к 3 нормальной форме, разбив на большее количество сущностей. Также, с введением столбцов дат создания и обновления, можно привести к SCD Type 2.
Для предсказания целевой переменной статус заказа использовалась логистическая регрессия, но, учитывая отсутствие какой-либо корреляции в данных с целевой переменной ее работа оправдывает затраченные ресурсы.
Отдельно стоит указать на диcбаланс классов, где минорный представлен в 100 раз меньше мажорного.
Были использованы техники андерсемплинга для уравнивания классов. В пайплайне использовались докер, аирфлоу и млфлоу.
В коде представлено несколько вариантов сохранения и загрузки модели, а также логгирование результатов обучения и предсказания поступающих данных с последующей записью в базу данных.