В данном тестовом задании предлагается выполнить несколько прикладных задач, связанных с псевдоданными УЖЦК.
В приложении к заданию есть файл database.db. Он представляет собой файл БД на фреймворке sqlite3. Синтаксис SQL, используемый в данной БД почти не отличается от ORACLE DB (более подробно можно почитать тут. Запросы к базе можно сохранять сразу в pandas.Dataframe, используя следующий код:
import pandas as pd
import sqlite3 as sql
query = 'SELECT * FROM pl LIMIT 5'
con = sql.connect('database.db')
df = pd.read_sql_query(sql = query, con=con)
con.close()
df
| client_id | segment | period | product | pl | |
|---|---|---|---|---|---|
| 0 | yxwtZv530y | Массовый | p_1 | Кредит | 0.374924 |
| 1 | YFaxw7Fn7N | Массовый | p_1 | Кредит | 1.419884 |
| 2 | TIqbQo6hvx | Массовый | p_1 | Кредит | 110.548341 |
| 3 | r5EpnGGW6A | Стандартный | p_1 | Кредит | 10.361832 |
| 4 | mVQkt2IbTH | Массовый | p_1 | Кредит | 108.225665 |
В базе данных хранится 2 таблицы: pl и campaign. Ключами для связи этих таблиц является поле client_id. В таблице pl находится информация о ЧОД (чистый операционный доход) от клиента с продуктом в разрезе периодов. В таблице campaign хранятся данные о прошедшей между отчетными периодами "p_1" и "p_2" маркетинговой кампании.
Атрибутный состав таблицы pl:
- client_id - id клиента
- segment - сегмент клиента внутри СМБ(Средний и малый бизнес)
- period - период расчета ЧОД банка от продукта, используемого клиентом
- product - продукт
- pl - ЧОД в тысячах рублей
Пример: строка 0 на срезе выше - в периоде "p_1" за продукт "Кредит" с клиента "yxwtZv530y" массового сегмента банк заработал 0.374924 тыс. рублей.
Если в каком-либо периоде информация о клиенте/продукте у клиента отсутствует, значит в этот период клиента в банке не было/клиент продуктом не пользовался.
Атрибутный состав таблицы campaign:
- client_id - id клиента
- product - продукт кампании
- response - статус отклика на предложение
- manager_id - id менеджера, на которого назначен клиент
- Посчитать средний ЧОД по продуктам и количество клиентов с продуктом для каждого сегмента в разрезе периодов.
- Посчитать средний ЧОД и количество клиентов в каждом сегменте в разрезе периодов.
- Оценить отток клиентов с продуктов в периоде p_2.
- Посчитать сумму ЧОД, привлеченную каждым менеджером в ходе маркетинговой кампании
- Оценить эффективность маркетинговой кампании.