SQL, Python и Tableau для анализа данных

Это подход к решению проекта реляционной базы данных с использованием sql для очистки данных, python для анализа данных и инструмента BI (таблица) для визуализации и выделения основных KPI и прогнозируемой метрики (дохода).

Давайте начнем с запуска наборов данных через Excel, чтобы посмотреть, как выглядят данные. Набор данных — это данные о доходах, маркетинге и посетителях.

Инструмент, используемый для запроса набора данных, — это mysql, я использую datagrip от Jetbrains.

create database sqlanalysis;
use sqlanalysis;

-- use describe to look at the data type of Revenue Datasets
desc `Revenue Data 2`;
desc `Revenue Data 1`;

-- now lets look at the dataset
select * from `Revenue Data 1`;
select * from `Revenue Data 2`;

Приведенный выше запрос создает базу данных и использует ее. Затем мы смотрим на типы данных в наборах данных Revenue и смотрим на столбцы и строки в наборах данных. Теперь давайте посмотрим на наличие нулевых значений в обоих наборах данных. — теперь первое, что нужно сделать, это посмотреть, есть ли пустые строки в наборе данных

-- now the first thing to do is to look if there are any empty rows in the dataset
-- there are null values in WeekID, Month Number, MonthID, Day Name and Revenue
select
  sum(IF(Date is null, 1, 0)) as NullValuesDateColumn,
  sum(if(Week_ID is null, 1, 0)) as NullValuesInWeekID,
  sum(if(`Month Number` is null, 1, 0)) as NullValuesInMonthNumber,
  sum(if(Month_ID is null, 1, 0)) as NullValuesInMonthID,
  sum(if(YEAR is null, 1, 0)) as NullValuesInYear,
  sum(if(Day_Name is null, 1, 0)) as NullValuesInDayName,
  sum(if(Revenue is null, 1, 0)) as NullValuesInRevenue
  from `Revenue Data 1`;
-- lets look at Revenue Data 2
-- null values in Revenue Column
select
  sum(IF(Date is null, 1, 0)) as NullValuesDateColumn,
  sum(if(Week_ID is null, 1, 0)) as NullValuesInWeekID,
  sum(if(`Month Number` is null, 1, 0)) as NullValuesInMonthNumber,
  sum(if(Month_ID is null, 1, 0)) as NullValuesInMonthID,
  sum(if(YEAR is null, 1, 0)) as NullValuesInYear,
  sum(if(Day_Name is null, 1, 0)) as NullValuesInDayName,
  sum(if(Revenue is null, 1, 0)) as NullValuesInRevenue
  from `Revenue Data 2`;

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

-- after running this query, there are null text values which will be deleted
select * from `Revenue Data 2`;
-- QUERY THAT DELETES TEXT NULL VALUES
delete from `Revenue Data 2` where Week_ID = 'NULL';

-- query to look at the dataset
select * from `Revenue Data 2`;

Теперь давайте снова посмотрим на таблицы

-- now lets look at the tables again
select * from `Revenue Data 1`;
select * from `Revenue Data 2`;

Теперь давайте создадим новую таблицу, объединяющую оба набора данных о доходах.

-- now we create a new table join both revenue data based on Date column
create table `FinalRevenueData` select * from `Revenue Data 1` union all select * from `Revenue Data 2`;
select * from FinalRevenueData;

Теперь давайте проверим нулевые значения в FinalRevenueData.

-- now lets check if there null values in this final revenue data
-- null values in weekid, monthnumber, monthid, day name, revenue column
select
  sum(IF(Date is null, 1, 0)) as NullValuesDateColumn,
  sum(if(Week_ID is null, 1, 0)) as NullValuesInWeekID,
  sum(if(`Month Number` is null, 1, 0)) as NullValuesInMonthNumber,
  sum(if(Month_ID is null, 1, 0)) as NullValuesInMonthID,
  sum(if(YEAR is null, 1, 0)) as NullValuesInYear,
  sum(if(Day_Name is null, 1, 0)) as NullValuesInDayName,
  sum(if(Revenue is null, 1, 0)) as NullValuesInRevenue
  from `FinalRevenueData`;

Давайте посмотрим на данные по маркетингу и посетителям.

-- now lets look at Marketing data and Visitors data
desc `Marketing Data`;
desc `Visitors Data`;

-- now it can be observed that there are null values in last three rows of Marketing and Visitors data
select * from `Marketing Data`;
select * from `Visitors Data`;

Теперь давайте проверим нулевые значения в данных о посетителях и маркетинговых данных.

-- now lets check for null values
-- 5 null values in visitors column
select
  sum(if(Visitors is null, 1, 0))  as NullValuesVisitorsColumn from `Visitors Data`;
-- null values in marketing spend
select
   sum(if(`Marketing Spend` is null, 1, 0)) as NullValuesInMarketingSpend,
   sum(if(Promo is null, 1, 0)) as NullValuesInPromo from `Marketing Data`;

Сейчас есть дубликаты.

-- now before we can see there are duplicate rows in 130 - 132
select * from FinalRevenueData;
-- we are going to remove it via week id
delete from FinalRevenueData where Week_ID is null;
select * from FinalRevenueData;

Теперь давайте приступим к объединению данных по маркетингу и доходам.

-- now lets proceed to joining Marketing and Revenue data
-- create a new table
create table `TheeFinalDataset`
select FinalRevenueData.Date, Week_ID, `Month Number`, Month_ID, Year, Day_Name, Revenue,`Marketing Spend`, Promo, Visitors from FinalRevenueData
inner join `Marketing Data` `M D` on FinalRevenueData.Date = `M D`.Date
inner join `Visitors Data` `V D` on FinalRevenueData.Date = `V D`.Date;

Давайте посмотрим на окончательный набор данных

select * from TheeFinalDataset;

Теперь давайте проверим нулевые значения в TheeFinalDataset.

-- now lets check for null values in TheeFinalDataset
-- there are null values in Revenue, Marketing Spend and Visitors
select
  sum(IF(Date is null, 1, 0)) as NullValuesDateColumn,
  sum(if(Week_ID is null, 1, 0)) as NullValuesInWeekID,
  sum(if(`Month Number` is null, 1, 0)) as NullValuesInMonthNumber,
  sum(if(Month_ID is null, 1, 0)) as NullValuesInMonthID,
  sum(if(YEAR is null, 1, 0)) as NullValuesInYear,
  sum(if(Day_Name is null, 1, 0)) as NullValuesInDayName,
  sum(if(Revenue is null, 1, 0)) as NullValuesInRevenue,
  sum(if(`Marketing Spend` is null, 1, 0)) as NullValuesInMarketingSpend,
  sum(if(Promo  is null, 1, 0)) as NullValuesInPromo,
  sum(if(Visitors is null, 1, 0)) as NullValuesInVisitors
  from TheeFinalDataset;

Теперь у нас есть проблема, мы вменяем нулевые значения, используя mysql или python.

Мы можем использовать оба инструмента, но давайте воспользуемся python из-за популярности, не стесняйтесь исследовать и использовать mysql, если хотите.

Итак, давайте экспортируем окончательный набор данных в CSV-файл и продолжим наш анализ, используя Python и Jupyter. Вы можете скачать Anaconda или использовать расширение Jupyter в vscode. Не стесняйтесь скачать репозиторий и запустить на своем компьютере.

Во второй части мы проведем визуализацию некоторых данных в таблице и поймем рентабельность инвестиций (ROI), чтобы узнать, какое продвижение работает и куда инвестировать больше.

ЧАСТЬ 2

Теперь, когда дело доходит до BI, нам нужно то, что мы хотим от данных. На какие вопросы мы хотим получить ответы, и в этой части мы рассмотрим доходы и маркетинг. Некоторые вопросы, которые мы могли бы задать, т. е. Какие усилия по продвижению являются наиболее эффективными и наименее эффективными, и сколько вложено средств в каждое из них?

Теперь давайте представим это в виде таблицы, мы ответим на некоторые из этих вопросов с помощью информационных панелей.

Панель доходов

Общий доход от всех рекламных акций составляет около 2 миллионов фунтов стерлингов. Promotion Blue доминирует в доходах, составляя 46% от общего дохода, что составляет 876 514 фунтов стерлингов. «Красный промоушн» занимает второе место, а «Без промо» — третье соответственно. Теперь сравнение из года в год показывает, что промо-синий имеет более высокий процентный рост на +157,82 с 2020 по 2021 год. За ним следуют Promotion Red и No Promo соответственно. Теперь, основываясь на этой панели, мы можем сказать, что Promotion Blue более эффективна, но так ли это? Мы не можем сделать вывод на основе одной панели. Теперь давайте посмотрим на панель управления расходами на маркетинг, чтобы понять, сколько мы тратим на каждое продвижение.

Панель управления маркетингом

Общие деньги, потраченные на маркетинг, составляют 254 тысячи фунтов стерлингов. 39% маркетинговых инвестиций, которые составляют 99 000 фунтов стерлингов, пошли на продвижение синего цвета, за которым следуют No Promo и красный цвет Promotion соответственно. Теперь сравнение из года в год показывает увеличение маркетинговых инвестиций в продвижение синего цвета с 2020 по 2021 год на 118%. За ним следуют No Promo и Promotion red соответственно. Теперь, несмотря на то, что мы инвестируем больше в No Promo, чем в красный цвет Promotion, приборная панель доходов показывает, что мы зарабатываем больше денег с помощью красного цвета, чем без промо. Теперь единственный способ убедиться в этом — посмотреть на окупаемость инвестиций во все маркетинговые акции.

Рентабельность инвестиций

Возврат инвестиций используется для оценки эффективности инвестиций или сравнения эффективности различных инвестиций. Теперь 0% ROI означает отсутствие прибыли и убытков, 100% ROI означает, что вы потратите X и заработаете в 2 раза больше. 1000% ROI означает, что вы потратили X и получили 11X дохода. -100% ROI означает, что вы потратили X и получили 0 дохода. Чем выше процент, тем лучше инвестиции.

Панель рентабельности инвестиций

Приборная панель ROI показывает высокую норму прибыли, указывающую на эффективное использование ресурсов. Синий цвет акции имеет более высокий ROI, что указывает на то, что мы получаем 782% возврата дохода, за которым следует красный цвет акции и отсутствие рекламы. Теперь нам нужно определить, насколько успешными были маркетинговые акции на протяжении многих лет. Теперь, глядя на промо-синий, мы видим увеличение рентабельности инвестиций с 686% до 826%. Тем не менее, реклама красного цвета и отсутствие рекламы зафиксировали снижение окупаемости инвестиций. No Promo зафиксировал снижение рентабельности инвестиций на -15,79% с 2020 по 2021 год, а Promotion red зафиксировал снижение рентабельности инвестиций на -2,50% с 2020 по 2021 год. Promotion blue зафиксировал увеличение рентабельности инвестиций на +20,49% с 2020 по 2021 год, что означает продвижение синего — это солидная инвестиция, и все усилия и инвестиции должны быть направлены на продвижение синего.

Теперь данные можно дополнительно изучить, чтобы выявить средние ежедневные расходы на маркетинг, доход и т. д. Не стесняйтесь клонировать репозиторий с моей страницы GitHub: https://github.com/mensalytics.

До встречи