Реклама.Гуру или как стать маркетологом
Здесь все предельно просто: задачка на 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.