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

Описание исходных данных

На текущий момент (в силу большой загрузки IT-отдела) не реализован доступ к серверу с ХД, маркетинговые данные выгружаются в виде 5 таблиц формата CSV. Из данных таблиц формируется единый файл формата XLS (см. Приложение 3). В нем находится 5 таблиц:

    - Leads. Таблица заявок - содержит информацию о поступающих заявках на сайт - Missions. Таблица миссий - содержит информацию по текущим задачам. Миссия формируется автоматически при создании заявки. Одна миссия соответствует одному лиду (человеку, который оставил заявку), одному лиду соответствует одна миссия (связь 1 к 1). Если лид становится клиентом, то в соответствующее поле заносится его индикационный номер как клиента. - Tasks. Таблица задач - напрямую связана с таблицей миссий. Для каждой миссии создаются текущие задачи (связь 1 ко многим). Например, сделать звонок, запланировать отправку электронного письма, перезвонить лиду и др. Задачи формируются автоматически исходя из результата по выполнению прошлой задачи. Также в задачах хранится их статус и результат их выполнения - Customers. Таблица клиентов - содержит личную информацию о клиенте такую как: имя клиента, его почтовый адрес, телефон и др. Также содержит сведения о предпочтениях клиента, которые влияют на его индивидуальный рацион. Например, есть клиенты с сахарным диабетом, клиенты вегетарианцы, клиенты, которые не любят морепродукты. - Orders. Несмотря на перевод с английского термина "order" как "заказ", таблица Orders содержит информацию о доставках, а не о заказах. В одном заказе может быть множество доставок.

Компания ELEMENTAREE быстро растет и стремительно развивается. Поэтому она является склонной к большому количеству внутренних и внешних изменений в сравнительно малые сроки. Это сказывается и на целостности данных. Например, добавляются новые поля в таблицы, старые поля подвергаются изменениям и др. Также сами поля могут быть заполнены неправильно. Так, например, в поле "улица" может попасть номер дома и квартиры, а таблица заказов не предусмотрена вовсе. Поэтому важной и значимой задачей является предварительной обработки данных.

Предварительная обработка данных является довольно трудоемкой задачей, а местами и невозможной, при использовании методов и инструментария MS Excel. Поэтому для нее будет использоваться MS SQL Server и методы SQL.

Разработка архитектуры Базы Данных и импорт данных

Для последующей работы в среде MS SQL Server и методов SQL необходимо создать БД и проект SSIS для переноса данных из XLS файла в БД.

Для построения архитектуры БД использовался ErWin Data Modeler. Созданная схема БД имеет следующий вид:

схема базы данных маркетингового отдела

Рис 5. Схема базы данных маркетингового отдела

Было принято решение отказаться от 3НФ и от связей между таблицами по следующим причинам:

    - Задача построения 3НФ является трудоемкой и не несет практического смысла для дальнейшего анализа данных. - Само хранилище данных, из которого выгружаются исходные данные имеет ряд недочетов как по архитектуре, так и по практическому смыслу. Так, например, существуют отдельно объекты ID_Lead (идентификационный номер лида) и ID_Customer (идентификационный номер клиента), хотя необходимости в объекте ID_Customer нет. ID_Lead также может определять уникального клиента, а наличие определенного ID_Lead в таблице доставок будет определять лида как клиента компании. - Как уже упоминалось, сырые данные содержат в себе большое количество неудовлетворяющих моментов. Как следствие сбоев и периодических преобразований ХД, встречаются случаи того, что клиенту может не найтись в соответствие лид, а лиду миссия. Таким образом связывание таблиц вызвало бы ошибку.

Из полученной схемы БД был сгенерирован скрипт, с помощью которого в пустой БД были созданы все таблицы объектов и свойств.

Перенос данных осуществляется с применением методов ETL с помощью проекта SQL Server Integration Services (SSIS). В рамках одного проекта SSIS было создано 5 задач потоков данных - по каждой из исходных таблиц. Все 5 задач потока данных соединены последовательно, имеют одинаковую структуру и предназначены для переноса данных из одной таблицы в соответствующую ей другую. Структура потоков данных выглядит следующим образом:

структура потока данных проекта ssis

Рис 6. Структура потока данных проекта SSIS

При запуске блока потока данных идет подключение к XLS файлу с определенной таблицей, после чего - преобразование форматов объектов. Уточняющий запрос проверяет наличие строки в БД и передает строку на блок "OLE DB" в том случае, если таковой в БД еще нет. Назначение "OLE DB" производит перенос данных в БД. реляционный маркетинг кластер

Описание полей и таблиц

Таблица "Customers" (клиенты)

    - Поле ID_Customer - идентификационный номер клиента. Тип данных - bigint. Является первичным ключом (PK) - Поле Registered_On - дата регистрации клиента в системе (дата создания записи о клиенте). Тип данных - date - Поле Name - имя клиента (всегда заполняется полное имя, иногда имя и отчество). Тип данных - char(255) - Поле Email - адрес электронной почты клиента. Тип данных - char(255) - Поле Phone - номер телефона клиента. Тип данных - char(50) - Поле Address - полный адрес клиента (для доставки). Тип данных - char(255) - Поле Street - улица клиента (для доставки). Тип данных - char(255) - Поле House - номер дома клиента (для доставки). Тип данных - char(255) - Поле Number_Apartament - номер квартиры (для доставки). Тип данных - char(30) - Поле [Mnogo. Ru_Card] - номер карты сервиса Mnogo. ru (остается незаполненным при неимении клиентом карты). Тип данных - char(18) - Поле Do_Not_Contact - условие связи с клиентом (только звонки, или только СМС сообщения, или запрет на любые рекламные предложения). Тип данных - char(5) - Поле ID_Lead_Source - название ресурса, откуда клиент перешел на сайт компании. Тип данных - char(18) - Поле Referred_By - идентификационный номер другого клиента, который пригласил клиента для осуществления заказа. Тип данных - char(18) - Поле Birthday - дата рождения клиента. Тип данных - date - Поле Age - возраст клиента. Тип данных - int - Поле Weight - вес клиента. Тип данных - int - Поле Add_Fish - информация о предпочтениях клиента касательно рыбы (по умолчанию остается незаполненным). Тип данных - char(18) - Поле Add_Chicken - информация о предпочтениях клиента касательно мяса курицы (по умолчанию остается незаполненным). Тип данных - char(18) - Поле Add_Cottage_Cheese - информация о предпочтениях клиента касательно козьего сыра (по умолчанию остается незаполненным). Тип данных - char(18) - Поле No_Meat - комментарий при отказе клиента от мяса (по умолчанию остается незаполненным). Тип данных - char(18) - Поле No_Fish - комментарий при отказе клиента от рыбы (по умолчанию остается незаполненным). Тип данных - char(18) - Поле No_Seafood - комментарий при отказе клиента от морепродуктов (по умолчанию остается незаполненным). Тип данных - char(18) - Поле Height - рост клиента. Тип данных - int

Таблица "Leads" (заявки)

    - Поле ID_Lead - идентификационный номер лида. Тип данных - bigint. Является первичным ключом (PK) - Поле Imported_On - дата и время импорта строки в CRM систему компании ELEMENTAREE. Тип данных - datetime - Поле Recorded_On - дата и время осуществления заявки. Тип данных - datetime - Поле Status_Lead - статус лида. Тип данных - char(18) - Поле Name - указанное лидом имя. Тип данных - char(255) - Поле Email - указанный лидом электронный почтовый адрес. Тип данных - char(255) - Поле Phone - указанный лидом номер телефона. Тип данных - char(50) - Поле Survey - выбранный тип продукта. Тип данных - char(255) - Поле ID_Referrer - идентификационный номер интернет страницы, на которой была оставлена заявка. Тип данных - int - Поле Variant - выбранный тип подпродукта. Тип данных - char(60) - Поле UTM_Sourse - название ресурса, откуда клиент перешел на сайт компании. Тип данных - char(255) - Поле UTM_Campaign - название рекламной кампании, на которую откликнулся лид. Тип данных - char(255) - Поле UTM_Medium - тип связи с клиентом. Тип данных - char(255) - Поле UTM_Term - название группы рекламных кампаний. Тип данных - char(255) - Поле UTM_Content - тип контента рекламной кампании. Тип данных - char(20) - Поле ID_Customer - идентификационный номер клиента (по умолчанию проставляется "0"). Тип данных - bigint

Таблица "Missoins" (миссии)

    - Поле ID_Mission - идентификационный номер миссии. Тип данных - bigint. Является первичным ключом (PK) - Поле Type - тип продукта. Тип данных - char(20) - Поле ID_Customer - идентификационный номер клиента (по умолчанию проставляется "0"). Тип данных - bigint - Поле Customer_Name - актуальное имя клиента. Тип данных - char(255) - Поле Customer_Phone - актуальный номер телефона клиента. Тип данных - char(50) - Поле ID_Lead - идентификационный номер лида. Тип данных - bigint - Поле Status_Mission - статус миссии. Тип данных - char(20) - Поле Created_On - дата создания миссии. Тип данных - datetime - Поле Finished_On - дата окончания миссии. Тип данных - datetime - Поле Allocated_To - имя ответственного менеджера сопровождения клиентов. Тип данных - char(50)

Таблица "Orders" (доставки)

    - Поле ID_Order - идентификационный номер заказа. Тип данных - bigint. Является первичным ключом (PK) - Поле Created_By - имя менеджера, который создал доставку. Тип данных - char(100) - Поле Created_On - дата создания доставки. Тип данных - date - Поле ID_Customer - идентификационный номер клиента. Тип данных - bigint - Поле Customer_Name - имя человека, принимающего заказ. Тип данных - char(180) - Поле Email - актуальный адрес электронной почты. Тип данных - char(180) - Поле Phone - актуальный номер телефона. Тип данных - char(50) - Поле Subscriber - согласие или запрет клиента на рассылку электронных писем. Тип данных - char(5) - Поле [Mnogo. Ru_Card] - номер карты сервиса Mnogo. ru (остается незаполненным при неимении клиентом карты). Тип данных - char(30) - Поле Total_Before_Discount - сумма заказа до вычета скидки. Тип данных - money - Поле Discount_Type - тип скидки (процентный, абсолютный). Тип данных - char(14) - Поле Discount_Amount - размер скидки. Тип данных - money - Поле Campaign - название компании, если доставка осуществляется в офис. Тип данных - char(70) - Поле Total - итоговая сумма к оплате (дебиторская задолженность). Тип данных - money - Поле Total_Collected - сумма, которую клиент оплатил. Тип данных - money - Поле Payment_Method - тип оплаты. Тип данных - char(10) - Поле Delivery_Date - дата доставки. Тип данных - date - Поле Status_Order - статус доставки. Тип данных - char(18) - Поле Opened_By - имя последнего редактировавшего информацию по заказу. Тип данных - int - Поле Opened_On - дата последнего редактирования. Тип данных - char(30) - Поле Delivery_to - номер района Москвы, куда будет доставка. Тип данных - int - Поле Short_Delivery_Window - наличие усеченного временного интервала доставки. Тип данных - char(5) - Поле Delivery_H0 - начало временного интервала доставки. Тип данных - char(50) - Поле Delivery_H1 - конец временного интервала доставки. Тип данных - char(50) - Поле Delivery_Area - отражает информацию внутри МКАДа доставка или во вне. Тип данных - char(18) - Поле Delivery_By - имя курьера, осуществляющего доставку. Тип данных - char(50) - Поле Delivered_On - фактическое время доставки. Тип данных - char(30) - Поле Box_Count - количество коробок в заказе. Тип данных - int - Поле Box_Marking_# - наличие в коробке дополнительной бесплатной продукции от ELEMENTAREE. Тип данных - int - Поле Diet_Plan_Group - идентификационный номер заказа с продукцией DIET. Тип данных - int - Поле WOW_Plan_Group - идентификационный номер заказа с продукцией WOW. Тип данных - int - Поле Payment_Status - подтверждение платежа. Тип данных - char(28)

Таблица "Tasks" (задачи)

    - Поле ID_Task - идентификационный номер задачи. Тип данных - bigint. Является первичным ключом (PK) - Поле ID_Mission - идентификационный номер миссии. Тип данных - bigint - Поле Mission_Type - тип миссии. Тип данных - char(30) - Поле ID_Customer - идентификационный номер клиента. Тип данных - bigint - Поле Customer_Name - актуальное имя клиента. Тип данных - char(255) - Поле Customer_Phone - актуальный номер телефона клиента. Тип данных - char(50) - Поле Type_Task - тип задачи. Тип данных - char(255) - Поле Created_On - дата создания задачи. Тип данных - datetime - Поле Date_Of_Action - запланированная дата выполнения задачи. Тип данных - date - Поле Time_From - начало временного интервала выполнения задачи. Тип данных - char(50) - Поле Time_To - конец временного интервала выполнения задачи. Тип данных - char(50) - Поле Priority_Of_Task - приоритет задачи. Тип данных - int - Поле [User] - ответственный менеджер. Тип данных - char(50) - Поле Outcome - результат выполнения задачи. Тип данных - char(255) - Поле Shift_Count - количество раз откладывание задачи на будущее время. Тип данных - int

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

В рамках предварительной обработки данных было сделано следующее:

    - Формирование таблицы "Заказы" (назв. "InterM5F") (см. Приложение 4). Как уже упоминалось выше, среди пяти таблиц выгрузки, нет таблицы заказов. Но именно данная таблица представляет для анализа наибольший интерес. Таблица по заказам была сформирована на базе других таблиц, путем преобразования и агрегации данных. В результате, с точки зрения реализации, таблица по заказам была сформирована в форме представления БД, а не таблицы БД. Данный метод был выбран по некоторым причинам, таким как: автоматическое обновление таблицы при добавлении новых записей в БД, легкость преобразования таблицы, изменение или добавление объектов в таблицу. - Для анализа данных помимо представления "Заказы" были также созданы дополнительные представления:
      O InterM6F - отражает информацию заказов по клиенту. Одна строка таблицы соответствует одному клиенту. Содержит личную информацию о клиенте, даты его первого и последнего заказов, суммарное количество заказов, суммарный счет к оплате, суммарное количество доставок. Представление также содержит вспомогательный логический столбец, отражающий находится ли клиент в оттоке, или нет. O ThirdTimeColPlus - отражает полную информацию по первым трем заказам клиентов, у которых имеется 3 и более заказов. Предназначено для анализа поведенческой сегментации.
    - Очистка данных. Согласно разработанному алгоритму из созданной и заполненной БД были удалены все тестовые строки, а также другие строки, которые нельзя использовать для анализа. Также для анализа были оставлены данные начиная с 2015-й года в связи с непригодностью данных 2014-го года. - Заполнение пропущенных строк. Согласно разработанному алгоритму, в основе которого подстановка средних или релевантных значений, были заполнены пустые поля строк. Ни по одному из важных для анализа объектов не было более 8% пропущенных значений, поэтому заполнение пропущенных значений не должно сильно сказаться "шумом" при построении итоговых моделей анализа - Избавление от выбросов. Среди данных иногда встречаются так называемые "экстремальные" значения (выбросы). Было выявлено две причины подобных выбросов: O ошибка при ручном вводе данных менеджерами; O ошибки в настройке алгоритмов автоматического формирования данных.

Данные выбросы влияют на качество анализа тем, что создают "шум" в данных, что влияет на результат работы алгоритмов. Например, в одном заказе могло оказаться 16 доставок, что говорит о том, что одна строка заказа содержала агрегированную информацию по двум заказам. Строки подобного рода дробились пополам, остальные выбросы заменялись заранее определенными максимально и минимально допустимыми значениями объекта.

Похожие статьи




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

Предыдущая | Следующая