Работа с данными для анализа - Интеллектуальный анализ данных, который способствует поддержке маркетинга в компании
Описание исходных данных
На текущий момент (в силу большой загрузки 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 задач потока данных соединены последовательно, имеют одинаковую структуру и предназначены для переноса данных из одной таблицы в соответствующую ей другую. Структура потоков данных выглядит следующим образом:
Рис 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 и более заказов. Предназначено для анализа поведенческой сегментации.
Данные выбросы влияют на качество анализа тем, что создают "шум" в данных, что влияет на результат работы алгоритмов. Например, в одном заказе могло оказаться 16 доставок, что говорит о том, что одна строка заказа содержала агрегированную информацию по двум заказам. Строки подобного рода дробились пополам, остальные выбросы заменялись заранее определенными максимально и минимально допустимыми значениями объекта.
Похожие статьи
-
Теоретические предпосылки исследования Системы поддержки принятия решений Системы поддержки принятия решений (СППР), представляют собой приложения узкого...
-
Задача поведенческой сегментации, формирование портретов клиентов по поведению Одними из основных задач анализа являлись: поведенческая сегментация...
-
Прогнозирование оттока клиентов Отделом маркетинга компании ELEMENTAREE было выявлено, что практически все клиенты, у которых отсутствовали заказы в...
-
Объектом исследования является производственная компания ООО "Элементари" (ELEMENTAREE) (http://www. elementaree. ru/). Исследования, отображаемые в...
-
Заключение - Интеллектуальный анализ данных, который способствует поддержке маркетинга в компании
В рамках проведенного исследования была проделана работа по разработке системы интеллектуального анализа данных для поддержки маркетинга производственной...
-
Введение - Интеллектуальный анализ данных, который способствует поддержке маркетинга в компании
В связи возникших условий экономического кризиса наблюдается рост издержек маркетинговой деятельности. Отдел маркетинга компании "ELEMENTAREE" испытывает...
-
Предложенный подход к решению задач исследования Используя в качестве основы присутствующее в наличии программное обеспечение, которое применимо к...
-
Увеличение эффективности деятельности отделов В рамках данной задачи производился анализ данных с целью выявления любых знаний, на базе которых можно...
-
Для того, чтобы разработать оптимальный метод интеграции сторонних систем в существующую ИТ-инфраструктуру систем компании, требуется точно поставить...
-
Для осуществления первой задачи данной работы был проведен проведение подробный анализ семи наиболее распространенных технологий работы с Большими...
-
Необходимо исследовать зависимость влияния различных факторов на параметр, характеризующий производство. В качестве такого параметра было выбрано...
-
Решения компании IBM - Технологии больших данных: анализ и выбор решения для реализации проекта
Технологии анализа больших данных являются прекрасным дополнением к средам хранения больших данных. Множество применений включает в себя, например,...
-
1.2.1 Уточнение постановки задачи Для анализа в рамках проекта выберем очень простые, но от этого не менее актуальные , задачи БД отеля. 1. Добавление...
-
Полное наименование разрабатываемой системы - корпоративная информационная система "Бюджетное планирование и отчетность" группы компаний, занимающейся...
-
Подход NoSQL - Технологии больших данных: анализ и выбор решения для реализации проекта
Понятие NoSQL означает "Не только SQL" или "Не SQL". Термин получил известность, начиная с 2009 год, когда развитие интернет-технологий и социальных...
-
Введение - Технологии больших данных: анализ и выбор решения для реализации проекта
В конце 2000х годов были уже сформированы решения класса Business Intelligence, которые являются альтернативой традиционным методам управления базами...
-
Для написания АИС использовались следующие языки программирования, программные средства и библиотеки: - Язык программирования PHP 5.4; -...
-
Корпоративная интеграционная подсистема на базе IBM WebSphere Business Integration Message Broker [28] отвечает за выстраивание корпоративной...
-
SPSS Modeler [29] - это программный комплекс, позволяющий строить прогностические модели и применять эту информацию при принятии решений на уровне...
-
Язык программирования R - Технологии больших данных: анализ и выбор решения для реализации проекта
Язык программирования R является универсальным и разработан для применения в следующих областях: разведочный анализ данных, классические статистические...
-
По результатам данного исследования необходимо выявить недостатки и ограничения существующих технологий интеграции. Для проведения исследования...
-
Для достижения цели, поставленной в данной работе, необходимо проанализировать текущую ситуацию в области информационных систем, сравнить информационные...
-
В данном разделе была разработана функциональная схема работы программного комплекса, которая в общем виде описывает состав комплекса, характер и виды...
-
В выпускной квалификационной работе предметом исследования является деятельность по учету и управлению доставкой корреспонденции. Для того, чтобы...
-
Системная архитектура проекта Архитектура данных При организации любой управленческой работы, в том числе и работы по управлению заказами клиентов, очень...
-
Основные конструкции для разработки базы данных - База данных "Кинотеатр"
База данных - это организованная структура, предназначенная для хранения информации. Систему управления базой данных (СУБД) можно определить, как...
-
Каждая СУБД имеет особенности в представлении структуры таблиц, связей, определении типов данных и т. д. которую необходимо учитывать при проектировании....
-
Информационная система (ИС) ГИБДД должна обеспечивать хранение информации об автомобилях (марка, номер кузова, номер двигателя, цвет кузова, гос. номер),...
-
Описание предметной области ООО ИСК "Волгастройинвест" является официальным представителем ряда отечественных и зарубежных фирм, предлагающих на...
-
Программный интерфейс для базы данных я разрабатывал в объектно-ориентрованной среде Delphi, с помощью Embarcadero RAD Studio. Конструктор форм Delphi в...
-
Программная модель данных, получившая название "MapReduce", была создана несколько лет назад в компании Google, и там же была осуществлена первая...
-
В данном пункте представлено описание подключаемых к общей архитектуре ИС компании систем. Описание систем является справочной информацией для...
-
Построение модели предметной области с помощью описания структур данных и программного кода является классическим подходом в разработке ИС. Зачастую...
-
Обоснование выбора средств разработки проекта Для реализации корпоративной информационной системы "Бюджетное планирование и отчетность" в исследуемой...
-
Известно, что создание систем "с нуля" приводит к глобальным затратам компании на фонд оплаты труда, на поддержание созданного решения. К тому же, чем...
-
Комплекс инструментов Oracle Exalytics Комплексное решение Oracle Exalytics создано для обеспечения высокой производительности аналитических систем и...
-
В данной главе представлено описание возможных вариантов совершенствования архитектуры предприятия в части гибкого подключения сторонних систем и их...
-
Задачей проекта По внедрению Campaign Management является автоматизация полного цикла проведения кампаний, включая: Ѕ сегментацию и отбор Клиентов; Ѕ...
-
Информационная система крупной организации, как правило, представляет собой исторически сложившуюся совокупность отдельно работающих систем, которые...
-
Современные технологии обработки Больших данных Большой проект бюджетирование автоматизация С приходом новых технологий, инструментов и средств...
Работа с данными для анализа - Интеллектуальный анализ данных, который способствует поддержке маркетинга в компании