Этап 2. Проектирование хранилища данных.

Этап 1. Подготовка данных.

Для выполнения задания нами был выбран датасетTheCompleteJourneyс сайта dunnhumbу. Этот датасет содержит информацию о покупках, совершаемых домохозяйствами. Поставленная нами цель – возможность ответить на такие вопросы, как:

· Какие демографические факторы влияют на уровень потребления домохозяйств?

· Насколько наличие скидок на товары выгодно продавцам и покупателям?

· Какие товары являются наиболее популярными на рынке?

· Домохозяйства с каким уровнем дохода извлекают наибольшую выгоду из использования скидок?

И так далее.

Для ответов на эти вопросы требуется знать информацию о:

· Времени покупки

· Объему покупок

· Цене покупок

· Скидках

· Времени действия купона

· Демографического состава домохозяйства

· Дохода домохозяйств

· Связи скидочных купонов и скидочных кампаний

И так далее

Датасет содержит несколько файлов форматов .csvи .xlsx. Давайте рассмотрим структуру разработанного нами хранилища данных.

 

 

Этап 2. Проектирование хранилища данных.

Для выполнения задания нами была разработана витрина данных в формате «снежинка». Ее диаграмма представлена на картинке:

Также была использована таблица Staging. Сюда загружаются сырые данные, которые при последующей обработке попадают в основные таблицы. Рассмотрим каждую из них поподробнее:

1) DimProduct – таблица измерений, содержащая следующую информацию:

1.1) DWHProductID – IDпродукта

1.2) ProductID – IDпродукта в первоначальных данных

1.3) Department – подразделение, которому принадлежит продукт

1.4) CommodityDesc - категория

1.5) SubCommodityDesc – подкатегория

1.6) Manufacturer - производитель

1.7) Brand – Брэнд

2) DimHousehold – таблица измерений, содержащая следующую информацию:

2.1) DWHHouseholdID – IDдомохозяйства

2.1) HouseholdID - – IDдомохозяйствав первоначальных данных

2.3) AgeDesc – возрастная категория людей, проживающих в данном домохозяйстве

2.4) MartialStatus – семейное положение

2.5) IncomeDesc – доходная категория людей, проживающих в данном домохозяйстве

2.6) HomeownerDesc – домовладение или съёмное жилье

2.7) HouseholdSize – количество людей

2.8) HouseholdSizeDesc – демографические данные домохозяйства (например, если ли дети)

3) DimTime – таблица измерений, содержащая информацию о времени, и необходимая для создания снимков состояний:

3.1) DWHTimeID – IDвремени

3.1) DateID - IDвременив первоначальных данных

3.3) Day - день

3.4) TransTime – время транзакции

3.5) Month - месяц

3.6) Year - год

3.7) [DayofCampaign] – день со дня начала скидочной кампании

4) DimCampaign – таблица измерений, содержащая информацию о маркетинговых кампаниях, связанных с распространением скидочных купонов.

4.1) DWHCampaignID – ID кампании

4.1) CamaignID - – ID кампаниив первоначальных данных

4.3) CampDescription – описание кампании

4.4) StartDay – день начала

4.5) EndDay – день окончания

5) DimCoupon – таблица измерений, содержащая информацию о скидочных купонах.

5.1) DWHCouponID – IDкампании

5.1) CouponID - IDкампаниив первоначальных данных

5.3) CampDescription – описание кампании

5.4) StartDay – день начала

5.5) EndDay – день окончания

5.5) ProductID – IDпродуктов, на которые распространяется скидка

6) FactTransactionData– таблица фактов, содержащая информацию о транзакциях

6.1) FactID– IDфакта

5.1) HosueholdID – ID домохозяйство, участвовавшего в транзакции

5.3) BasketID – ID потребительской корзины

5.4)DateID - дата покупки

5.5) ProductID – IDприобретенных товаров

5.5) CouponID – ID использованных купонов на скидку

5.6) Quantity – количество товаров

6.7) SalesValue – цена покупки

6.8) CampaignID – IDскидочной кампании

6.8) RetailDiscount – обыкновенная скида

6.9) CouponDiscount – скидка по купону

 

 

Этап 3. Загрузка данных и ETL-процедуры.

Для загрузки данных была реализована следующая схема:

 

Рассмотрим каждый из этапов подробнее.

1) Отчистка – отчищает все существующие таблицы во избежание конфликтов загрузки новых данных:

 

2) Буферная область Staging

 

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

3) Загрузка измерения DimProduct:

 

4) Загрузка измерения DimCampaign

5) Загрузка измерения DimTime

 

6) Загрузка измерения DimHousehold

 

7) Загрузка измерений DimCoupon

 

Загрузка этого измерения несколько отличается от предыдущих. Дело в том, что для соответствия требованиям аналитический отчетности, нам необходимо учитывать связь информации о скидочных кампаниях и купонах, которая отсутствует в исходных данных в явном виде. Потом сначала производится загрузка информации непосредственно о купонах, а затем – загрузка данных о связи:

 

8) Загрузка фактовпроисходит стандартным образом:

 

 

Итоговый скриншот, подтверждающий работоспособность схемы загрузки данных:

 

Этап 4. OLAP-кубы, MDX-запросы, информационные панели, сводная таблица.

 

Представление источника данных:

 

Четыре измерения, каждое из которых обладает набором атрибутов.

 

1) ИзмерениеDimCoupon.

 

Атрибуты:

· CampCampDescription – описание скидочной кампании

· CoupCampDescription – описание скидочного купона

· Coupon ID – ID купона

· CouponIDDist – необходим для избавления от повторяющихся купонов при составлении иерархий, поскольку в первоначальных данных они повторяются.

· End Day – день начала

· Start Day – день окончания

· TotalDays – именованное вычисление, представляющее собой разность EndDayи StartDay

 

Иерархии:

1) Campaign-Coupons

· Camp Camp Description

· Coupon ID Desc

При выборе кампании появляется возможность выбрать принадлежащие ей купоны.

2) Days-Coupons

· Total Days

· Coupon ID Dist

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

 

2 ) Измерение DimHousehold

Атрибуты

· Age Desc – возраст

· HomeownerDesc– является ли человек владельцем или съемщиком

· Household ID – ID домохозяйства

· HouseholdSize – номер от 1 до 5 – число проживающих людей.

· HouseholdSizeDesc – уточняющий атрибут, предоставляющий информацию о том, какие люди живут в домохозяйстве. Например, два взрослых и один ребенок.

· HouseholdSizeDescInformative – необходимодляпреобразования некорректных данных из первоначального датасета. Например, бывали случаи, когда в домохозяйстве размере 2 проживает человек категории SingeFemale.

· IncomeCategory–именованное вычисление, связанное с IncomeDescription, представляет собой категорию заработка.

· IncomeDesc- заработок в тысячах условных единиц.

· Marital Status – семейное положение

· MaritalStatusInformative– именованное вычисление, необходимое для исключения из поступающих данных некорректные, а также расширить информативность предыдущего атрибута.

Иерархии:

1) Age – Income

· AgeDesc

· Income Category

При выборе возраста, можем посмотреть категории домохозяйств по заработку.

2) Size- Desc

· Household Size

· Household Size Desc Informative

По размеру домохозяйства, можем посмотреть категории домохозяйств по демографии.

3) Income-Desc

· Income Category

· Income Desc

По категории дохода можем посмотреть доход в численном выражении, принадлежащий этой категории.

4) Income – Id

· Income Category

· Household ID

По категории дохода можем посмотреть домовладения, располагающих таким доходом.

3) Измерение DimProduct

 

Атрибуты:

· Brand - брэнд

· Department - подразделение

· Commodity Desc - категория

· Sub Commodity Desc - подкатегория

· Product ID – ID продукта

· Manufacturer - производитель

Иерархии:

1) Brand-Dep-Prod

· Brand

· Department

· Commodity Desc

· Sub Commodity Desc

По брэнду посмотреть отделение, категорию, подкатегорию, и сами продукты.

4) Измерение DimTime:

Атрибуты:

· Date id – ID даты

· Day - день

· Month - месяц

· Trans Time – время транзакции

· Year – год

Иерархии:

1) Date

· Year

· Month

· Day

· Trans Time

По году, месяцу и дню можно посмотреть конкретное время транзакции

 

 

Рассмотрим структуру куба:

 

Использование измерений:

 

Измерения связаны с таблицей фактов по ключевым атрибутам – Coupon ID, Product ID, Household ID, Date ID.

В обозревателе можно составить, например, такой отчет:

 

MDX ЗАПРОСЫ

 

1) Первый запрос посчитывает общее значение скидки на подкатегории товаров (а скидки у нас могут быть двух видов: обычные и по купону), и демонстрирует, какой могла бы быть цена на подкатегории товаров за отдельный год, если бы этих скидок не было:

 

 

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

 

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

 

3)
С помощью третьего запроса можно узнать скидку за 2013 год, сравнить ее со скидкой предыдущего года, выведя минимальное значение скидки в столбец Minimum, а также рассмотреть разницу цен за 2012 и 2013 годы:

 

 

ИНФОРМАЦИОННЫЕ ПАНЕЛИ

 

Мы подготовили следующую информационную панель с пятью элементами визуализации:

Рассмотрим каждый из ее элементов подробнее.

1) Первый пай чарт показывает долю покупок, совершенных людьми в зависимости от их семейного положения. Заметим, что наибольший процент покупок был произведен людьми, состоящими в браке. Эта информация может быть полезна маркетологам для составления целевых групп – если наиболее часто товары приобретаются людьми семейными, то продвигать всякий товар выгоднее всего таким образом, чтобы он был привлекателен семейным людям. Например – при продвижении автомобиля уместно сказать, что он имеет вместительный багажник и позволить совершать покупки на всю семью.

2) Данный пай чарт показывает, какая доля продаж приходится на государственные (National)и частные (Private)бренды. Можно заметить, что больше двух третей всех покупок приходятся на государственные бренды. Данная информация может быть интересна представителем частных компаний для оценки предполагаемого потребления их товара, а также может быть полезна при выборе конкурентной политики (конкурировать в первую очередь с государственными компаниями).

3) На представленном ниже лайнчарте можно посмотреть динамику объема продаж по возрасту покупателей. Нетрудно заметить, что больше всего продаж приходится на покупателей возрастом от 45 до 54 лет. Эта информация может быть полезна социологам для определения наиболее экономически активных возрастных групп.

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

5) На последнем элементе – таблице представлена информация о том, какие категории домохозяйств в каком году совершили то или иное количество покупок, сколько денег они принесли, и сколько денег потеряли фирмы, прибегнув к скидочной политике. Данная информация полезна фирмам при выборе собственной ценовой и скидочной политик – можно заметить, что существует значительная положительная корреляция между размером скидки и количеством совершенных покупок.

 

Напоследок, рассмотрим подготовленную нами сводную таблицу Excel:

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