Реклама.Гуру или как стать маркетологом

2018-05-30T06:37:27.057Z

Здесь все предельно просто: задачка на sql, задачка на анализ и логический квестик.

Задача на Sql:

Есть три таблицы:

1. Справочник категорий (id, name). Тут просто лежат названия категорий и айдишки, ничего сложного.

2. Справочник сущностей (id, category_id, name). Сущности, как я понимаю, что-то вроде подкатегорий или типов транзакций, но для нас они просто склейка между таблицами. Внутри одной категории может быть несколько сущностей, а к одной сущности может относиться несколько операций.

3. Основная таблица записи операций (id, date_time, item_id, cost, revenue, profit). Здесь item_id=id сущности, а profit это cost-revenue. Также сказано, что прибыль функционально зависит от цен покупки и продажи, таблица умышленно денормализована для упрощения выборок (кажется, это просто пояснение колонки profit).

Задача:

получить топ-10 по прибыльности операций за текущий день в разрезе категорий.

Решение:

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

Допустим, что:

справочник категорий - categories_table,
справочник сущностей - items_table,
таблица записи операций - operations_table.

Теперь нам нужна база данных, чтобы проверять на ней наши запросы. Я использую MariaDB. Insert code:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Дамп структуры для таблица reclama_guru.categories_table
CREATE TABLE IF NOT EXISTS `categories_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Дамп данных таблицы reclama_guru.categories_table: ~3 rows (приблизительно)
/*!40000 ALTER TABLE `categories_table` DISABLE KEYS */;
INSERT INTO `categories_table` (`id`, `name`) VALUES
	(1, ' Category 1'),
	(2, ' Category 2'),
	(3, ' Category 3'),
  (4, ' Category 4');

/*!40000 ALTER TABLE `categories_table` ENABLE KEYS */;

-- Дамп структуры для таблица reclama_guru.items_table
CREATE TABLE IF NOT EXISTS `items_table` (
  `id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- Дамп данных таблицы reclama_guru.items_table: ~3 rows (приблизительно)
/*!40000 ALTER TABLE `items_table` DISABLE KEYS */;
INSERT INTO `items_table` (`id`, `category_id`, `name`) VALUES
	(1, 1, 'Item 100001'),
	(2, 1, 'Item 100002'),
	(3, 2, 'Item 100003'),
  (4, 5, 'Item 100004'),
  (5, 3, 'Item 100005');
/*!40000 ALTER TABLE `items_table` ENABLE KEYS */;

-- Дамп структуры для таблица reclama_guru.operations_table
CREATE TABLE IF NOT EXISTS `operations_table` (
  `id` int(11) DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `cost` float DEFAULT NULL,
  `revenue` float DEFAULT NULL,
  `profit` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Дамп данных таблицы reclama_guru.operations_table: ~3 rows (приблизительно)
/*!40000 ALTER TABLE `operations_table` DISABLE KEYS */;
INSERT INTO `operations_table` (`id`, `date_time`, `item_id`, `cost`, `revenue`, `profit`) VALUES
	(1,'2018-04-18 0:00:01',1,3.5,10,6.5),
(2,'2018-04-18 0:00:01',1,3.8,9.8,6.0),
  (3,'2018-04-18 0:00:02',3,5,4.9,-0.1),
  (4,'2018-04-18 0:00:02',3,8,12,4.0),
  (5,'2018-04-18 0:00:01',2,2,9.5,7.5),
  (6,'2018-04-18 0:00:01',2,8,9.2,1.2),
  (7,'2018-04-18 0:00:02',8,12,4.7,-7.3),
  (8,'2018-04-18 0:00:04',1,3,3.4,0.4),
  (9,'2018-04-18 0:00:01',1,0,9,9.0),
  (10,'2018-04-17 0:00:02',2,4.3,1.2,-3.1),
  (11,'2018-04-18 0:00:01',2,7.7,4.1,-3.6),
  (13,'2018-04-18 0:00:02',3,0.3,9,8.7),
  (14,'2018-04-19 0:00:02',9,5,7,2.0);

/*!40000 ALTER TABLE `operations_table` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

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

Подготовка на этом закончена, пора и код писать.

Основа кода здесь это соединение 3х таблиц. Я выбрала inner join для справочников, поскольку нам нужны только те категории, в которых прописаны сущности и наоборот. Для соединения с таблицей операций я использовала left join, поскольку операции без категорий тоже могут быть полезны при анализе. В случае если такие появлялись в выборке, они помечались как 'no_catagory' и считались отдельной категорией.

Для выборки топ 10, я использовала функцию row_number() с сортировкой по прибыли и выбрала только те строки, где значение было ниже 10.

Вот и все . Никакой магии.

select id as transaction_id, coalesce(category,'no_catagory') as category_name, item_id, date_time, profit, top
from
(select *,
row_number() over (partition by category order by profit desc) as top
from
(select *
from operations_table
where date(date_time)=date('2018-04-18'))a
left join
(select a.name as category,a.id as category_id, b.id as item_id from
(select * from categories_table)a
inner join
(select * from items_table)b
on a.id=b.category_id
group by a.name,a.id,b.id)b
using(item_id))a where top<=10
group by id,category, item_id, date_time, profit, top
order by category_name, top

Задача на статистику:

Представлено 200 измерений (первые 200 строк), каждое содержит значение 5 признаков(A,B,C,D,E) и 1 показатель (R).

Задача:

1. построить модель зависимости показателя от признаков (подсказка: не все из признаков могут влиять на показатель),

2. предсказать значение показателя на основе значений признаков для последних 10 строк.

Решение:

Для начала посмотрим, чем это можно решать.

Первое, что приходит в голову, кончено же, excel, а точнее google spreadsheets. Для них есть хороший пакет XLMiner Analysis ToolPak с полным набором всякой аналитической всячины. Тут тебе и регрессии, и Anova, и вообще много чего. Но как и всякий пакет на таблицы, эта штука работает сложно и поверить ей - не самое легкое дело.

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

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

В основном я пользовалась статьей о множественной регрессии с Хабра.

Итак мы смотрим на корреляцию показателей.

Тут явно видно, что B и D имеют хоть какой то вес и влияние. В принципе, E тоже ничего, но маловато. Посмотрим через тепловую карту.

И снова с R сильно коррелируют B и D. Далее все вычисления производились в Ipython notebook: проект в Git.

В итоге победила KNeighborsRegressor модель. Предсказанные значения R:

Задача на логику:

Василий каждый день приходит на работу в офис. Каждый раз когда Василий заходит в свой кабинет в офисе раздаётся звук кукушки, оповещающий сотрудников о начале рабочего дня.

Задача:

Является ли приход Василия на работу причиной звука кукушки?
Как можно доказать наличие или отсутствие причинно-следственной связи?

Решение:

Итак, у нас есть событие A - появление Василия в офисе с целью немного поработать, и событие B - крик пушки - кукушки.

Возможно, Василий и есть кукушка?

Так или иначе, для доказательства связи между событиями, нам нужно определить есть ли ситуации, когда кукушка выполняет свой долг без Василия или, может, Василий пришел на работу раньше и кукушка сработала до срока?

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

Метод сопутствующих изменений заключается в том, что, если какое-либо отдельно взятое явление изменяется каждый раз при изменении другого явления, с определенной степенью вероятности можно предположить, что второе явление влечет изменение первого и, следовательно, они находятся в причинной взаимозависимости.

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

@vachana

Реклама.Гуру или как стать маркетологом

Решение:

Задача на статистику:

Решение:

Задача на логику:

Решение: