Цель проекта: рассчитать параметры А/В теста, оценить корректность его проведения и проанализировать результаты эксперимента.

Описание данных:
В работе три таблицы:
1. sessions_project_history.csv — таблица с историческими данными по сессиям пользователей за период: с 2025-08-15 по 2025-09-23
2. sessions_project_test_part.csv — таблица с данными за первый день проведения A/B-теста, за 2025-10-14
3. sessions_project_test.csv — таблица с данными за весь период проведения A/B-теста за период: с 2025-10-14 по 2025-11-02

Поля таблиц (совпадает структура и содержание колонок):
· user_id — идентификатор пользователя;
· session_id — идентификатор сессии в приложении;
· session_date — дата сессии;
· session_start_ts — дата и время начала сессии;
· install_date — дата установки приложения;
· session_number — порядковый номер сессии для конкретного пользователя;
· registration_flag — является ли пользователь зарегистрированным;
· page_counter — количество просмотренных страниц во время сессии;
· region — регион пользователя;
· device — тип устройства пользователя;
· test_group — тестовая группа.

Этапы выполнения:
1. Работа с историческими данными (EDA)
2. Подготовка к тесту
3. Мониторинг А/В-теста
4. Проверка результатов A/B-теста
5. Выводы по результатам A/B-эксперимента

1. Работа с историческими данными (EDA)

1.1 Загрузка исторических данных

На первом этапе работаю с историческими данными приложения:

- Импортирую библиотеки, используемые в проекте.
- Считываю и сохраняю в датафрейм sessions_history CSV-файл с историческими данными о сессиях пользователей sessions_project_history.csv.

Вывожу на экран первые пять строк полученного датафрейма.

import pandas as pd

# Загружаю библиотеку для визуализации
import matplotlib.pyplot as plt

# Импорт библиотеки для статистического t-теста
from scipy.stats import ttest_ind

# Импорт библиотеки для статистического z-теста
from statsmodels.stats.proportion import proportions_ztest

# Импорт функции округления до большего числа
from math import ceil

# Настройки для полного отображения
pd.set_option('display.max_rows', None)      # Все строки
pd.set_option('display.max_columns', None)  # Все столбцы
pd.set_option('display.width', 1000)        # Ширина вывода (подбирается под экран)
pd.set_option('display.max_colwidth', None) # Полный текст в ячейках

sessions_history = pd.read_csv('путь к файлу/sessions_project_history.csv')
display(sessions_history.head())

Результат:
Вывод:
Структура и содержание таблицы совпадает с описанием. Названия столбцов в нужном формате.

1.2. Знакомство с данными

· Для каждого уникального пользователя user_id рассчитываю количество уникальных сессий session_id.
· Вывожу на экран все данные из таблицы sessions_history для одного пользователя с наибольшим количеством сессий. Если таких пользователей несколько, выбираю любого из них.
· Изучаю таблицу для одного пользователя, чтобы лучше понять логику формирования каждого столбца данных.

# Группирую по user_id и считаю уникальные session_id
df_grp = sessions_history.groupby('user_id')['session_id'].nunique().sort_values(ascending=False)

# Вывожу ТОП-5 пользователей по количеству сессий
display(df_grp.head())

Результат:

user_id
10E0DEFC1ABDBBE0    10
6A73CB5566BB494D    10
8A60431A825D035B    9
D11541BAC141FB94    9
5BCFE7C4DCC148E9    9
Name: session_id, dtype: int64

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

# Нахожу пользователя с максимальным количеством сессий
max_sessions = df_grp.max()
user_with_max_sessions = df_grp[df_grp == max_sessions].index[0]

# Выбираю все записи для этого пользователя
user_data = sessions_history[sessions_history['user_id'] == user_with_max_sessions]

# Вывожу результат
print(f"Пользователь с максимальным количеством сессий ({max_sessions}): {user_with_max_sessions}")
display(user_data)

Результат:

Пользователь с максимальным количеством сессий (10): 10E0DEFC1ABDBBE0

Вывод:
Пользователь с наибольшим количеством уникальных сессий не зарегистрирован. Максимальное число посещенных страниц за одну сессию составляет 4. После регистрации пользователь заходил почти каждый день с 14 по 25 августа.

1.3. Анализ числа регистраций

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

· Агрегирую исторические данные, считаю число уникальных пользователей и число зарегистрированных пользователей для каждого дня наблюдения. Для простоты считаю, что у пользователя в течение дня бывает одна сессия максимум и статус регистрации в течение одного дня не может измениться.
· Далее строю линейные графики общего числа пользователей и общего числа зарегистрированных пользователей по дням. Отображаю обе линии на одном графике.
· Строю отдельный линейный график доли зарегистрированных пользователей от всех пользователей по дням.
· На обоих графиках включаю элементы оформления: заголовок, подписанные оси X и Y, сетку и легенду.

# Агрегация данных по дням
daily_stats = sessions_history.groupby('session_date').agg(total_users=('user_id', 'nunique'),
                                                      registered_users=('registration_flag', 'sum')).reset_index()

# Вывожу результат
display(daily_stats)

Результат:

Вывод:
На первый взгляд видно, что количество зарегистрированных пользователей по дням сильно отличается: от 32 до 1253. Сильный спад начинается с 9 сентября.

# График общего числа пользователей и зарегистрированных пользователей
plt.figure(figsize=(18, 5))
plt.plot(daily_stats['session_date'], daily_stats['total_users'], label='Все пользователи')
plt.plot(daily_stats['session_date'], daily_stats['registered_users'], label='Зарегистрированные пользователи')
plt.title('Динамика количества пользователей по дням')
plt.xlabel('Дата')
plt.xticks(rotation=45)
plt.ylabel('Количество пользователей')
plt.legend()
plt.grid(True)
plt.show()

Результат:

Вывод:
Количество зарегистрированных пользователей заметно ниже относительно всех активных. Есть несколько пиков по количеству сессий, которые я наблюдаю на графике: 14 и 21 и 28 августа, 4 и 9 сентября.

# Расчет доли зарегистрированных пользователей от всех пользователей по дням
daily_stats['registered_ratio'] = daily_stats['registered_users'] / daily_stats['total_users']

plt.figure(figsize=(18, 5))
plt.plot(daily_stats['session_date'], daily_stats['registered_ratio'])
plt.title('Доля зарегистрированных пользователей по дням')
plt.xlabel('Дата')
plt.xticks(rotation=45)
plt.ylabel('Доля зарегистрированных пользователей')
plt.grid(True)
plt.show()

# Вывожу максимальное и минимальное количество
ratio_min = round(daily_stats['registered_ratio'].min(),2)
ratio_max = round(daily_stats['registered_ratio'].max(),2)
print( f'За рассматриваемый период минимальная доля зарегистрированных пользователей составляла:{ratio_min},\nмаксимальная доля составляла:{ratio_max}')

Результат:

За рассматриваемый период минимальная доля зарегистрированных пользователей составляла:0.04,
максимальная доля составляла:0.12

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

1.4. Анализ числа просмотренных страниц

Другая важная метрика продукта — число просмотренных страниц в приложении. Чем больше страниц просмотрено, тем сильнее пользователь увлечён контентом, а значит, выше шансы, что он зарегистрируется и оплатит подписку.

· Нахожу количество сессий для каждого значения количества просмотренных страниц. Например: одну страницу просмотрели в 29 160 сессиях, две страницы — в 105 536 сессиях и так далее.
· Строю столбчатую диаграмму, где по оси X будет число просмотренных страниц, по оси Y — количество сессий.
· На диаграмме должны быть заголовок, подписанные оси X и Y.

# Подсчет количества сессий для каждого значения просмотренных страниц
page_views_counts = sessions_history.groupby('page_counter').agg(sessions=('page_counter', 'count')).reset_index()

# Вывод результата
print(page_views_counts)

Вижу, что от 2 до 4 страниц смотрят в большем количестве сессий. Далее строю столбчатую диаграмму.

# Настройки размеров для отображения
plt.figure(figsize=(12, 5))

# Создаю столбчатую диаграмму
bars = plt.bar(page_views_counts['page_counter'], page_views_counts['sessions'])

# Добавляю подписи значений над столбцами
for bar in bars:
    height = bar.get_height()
    plt.text(bar.get_x() + bar.get_width()/2., height,
             f'{int(height):,}',
             ha='center', va='bottom', fontsize=10)

# Настройка графика
plt.title('Распределение сессий по количеству просмотренных страниц')
plt.xlabel('Количество просмотренных страниц')
plt.ylabel('Количество сессий')
plt.grid(axis='y', linestyle='--', alpha=0.7)

# Форматирование оси Y для удобства чтения больших чисел
plt.gca().yaxis.set_major_formatter(plt.FuncFormatter(lambda x, loc: "{:,}".format(int(x))))

# Вывод диаграммы
plt.show()

Результат:

Вывод:
По количеству страниц в ходе одной сессии пользователи чаще просматривают три. Две и четыре страницы просматривают тоже часто - это количество на втором месте.

1.5. Доля пользователей, просмотревших более четырёх страниц

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

· В датафрейме sessions_history создаю дополнительный столбец good_session. В него войдёт значение 1, если за одну сессию было просмотрено 4 и более страниц, и значение 0, если было просмотрено меньше.
· Далее строю график со средним значением доли успешных сессий от всех сессий по дням за весь период наблюдения.

sessions_history['good_session'] = sessions_history['page_counter'].apply(lambda x: 1 if x >= 4 else 0)

# Вывожу первые 5 строк
display(sessions_history.head())

Результат:

Столбец успешно создан.

# Группировка по дням и расчет средней доли good_session
daily_good_sessions = sessions_history.groupby('session_date')['good_session'].mean().reset_index()

# Настройка названий столбцов
daily_good_sessions.columns = ['session_date', 'good_session_ratio']

# Перевожу в проценты и округляю до 2х знаков после запятой
daily_good_sessions['good_session_ratio'] = round(daily_good_sessions['good_session_ratio']*100,2)

# Считаю общую среднюю долю пользователей с просмотром 4х и более страниц
mean_ratio_pages = round(daily_good_sessions['good_session_ratio'].mean(),2)

print( f'За рассматриваемый период в среднем доля пользователей просмотревших 4 и более страниц составила: {mean_ratio_pages}%')

# Вывод результата
display(daily_good_sessions.head())

За рассматриваемый период в среднем доля пользователей просмотревших 4 и более страниц составила: 30.86%

Результат:

На первый взгляд разброс в процентах небольшой, держится около 30%. Далее перехожу к визуализации.

plt.figure(figsize=(18, 6))

# Строю линейный график
plt.plot(daily_good_sessions['session_date'], 
         daily_good_sessions['good_session_ratio'], 
         marker='o', 
         linestyle='-'
        )

# Настройки графика
plt.title('Динамика доли успешных сессий (≥4 страниц) по дням', pad=20)
plt.xlabel('Дата')
plt.xticks(rotation=45)
plt.ylabel('Доля успешных сессий (%)')
plt.grid(True, linestyle='--', alpha=0.7)

# регулировка отступов и расположения элементов на графике
plt.tight_layout()

# Вывод графика
plt.show()

Результат:

Вывод:
Видно по графику, что доли успешных сессий с количеством 4 и более страниц стали меняться - с 13 по 17 сентября шёл рост, а далее с 17 до 23 сентября пошли перепады: от 30,58 до 28,87.

Выводы по пункту 1 - итоги работы с историческими данными

· В ходе работы с данными с 11 августа по 23 сентября я выявила, что максимальное количество уникальных сессий для одного пользователя составляет 10.
· Доля зарегистрированных пользователей за рассматриваемый период плавно росла с 0,04 до 0,12.
· В ходе одной сессии пользователи просматривали от 1 до 7 страниц. Чаще всего 3 страницы.
· Доля пользователей, просмотревших 4 и более страниц держится в среднем на уровне 30,86%

2. Подготовка к тесту

Планируя тест, необходимо проделать несколько важных шагов:
· Формулирую нулевую и альтернативную гипотезы;
· Определяю целевую метрику;
· Рассчитываю необходимый размер выборки;
· Исходя из текущих значений трафика рассчитываю необходимую длительность проведения теста.

2.1 Формулирую нулевую и альтернативную гипотезы:

Нулевая гипотеза: новый алгоритм не повлияет на вовлеченность пользователей и доля сессий с просмотром 4х и более страниц не изменится.
Альтернативная гипотеза: новый алгоритм увеличит вовлеченность пользователей и доля сессий с просмотром 4х и более страниц станет выше.

2.2 Целевая метрика:

Доля сессий с 4-мя просмотренными страницами и выше.

2.3. Расчёт размера выборки

Рассчитываю необходимое количество пользователей для эксперимента.
Для этого устанавливаю следующие параметры:
· Уровень значимости — 0.05.
· Вероятность ошибки второго рода — 0.2.
· Мощность теста.
· Минимальный детектируемый эффект, или MDE, — 3%. Укажу десятичную дробь, а не процент, т.е. 0.03.
При расчёте размера выборки использую метод solve_power() из класса power.NormalIndPower модуля statsmodels.stats.
Запускаю ячейку и изучаю полученное значение.

from statsmodels.stats.power import NormalIndPower
from statsmodels.stats.proportion import proportion_effectsize

# Задаю параметры
alpha = 0.05  # Уровень значимости
beta = 0.2  # Ошибка второго рода, часто 1 - мощность
power = 1- beta  # Мощность теста (оптимально 80% и выше)
p = 0.3086 # Базовый уровень доли из пункта 2.1.5 1.5
mde = p*0.03  # Минимальный детектируемый эффект 3%
effect_size = proportion_effectsize(p, p + mde)

# Инициализирую класс NormalIndPower
power_analysis = NormalIndPower()

# Рассчитываю размер выборки
sample_size = power_analysis.solve_power(
    effect_size = effect_size,
    power = power,
    alpha = alpha,
    ratio = 1 # Равномерное распределение выборок
)

print(f"Необходимый размер выборки для каждой группы: {int(sample_size)}")

Результат:

Необходимый размер выборки для каждой группы: 39396

Вывод:
В расчёте я получила, что при установленных параметрах для эксперимента необходима выборка из 78792 пользователей (39396 х 2).

2.4. Расчёт длительности A/B-теста

Использую данные о количестве пользователей в каждой выборке и среднем количестве пользователей приложения. Рассчитываю длительность теста, разделив одно на другое. Для этого выполняю шаги:
· Рассчитываю среднее количество уникальных пользователей приложения в день.
· Определяю длительность теста исходя из рассчитанного значения размера выборок и среднего дневного трафика приложения. Количество дней округляю в большую сторону, используя метод ceil().

from math import ceil

# Формирую общее количество уникальных пользователей в столбце total_users за весь период наблюдения
daily_users = sessions_history.groupby('session_date').agg(total_users=('user_id', 'nunique')).reset_index()

# Среднее количество пользователей приложения в день по историческим данным
avg_daily_users = ceil(daily_users['total_users'].mean())

# Рассчитываю длительность теста в днях как отношение размера выборки к среднему числу пользователей, округляю вверх до целого
test_duration = ceil(sample_size*2 / avg_daily_users)

print(f"Рассчитанная длительность A/B-теста при текущем уровене трафика в {avg_daily_users} пользователей в день составит {test_duration} дней")

Результат:

Рассчитанная длительность A/B-теста при текущем уровене трафика в 9908 пользователей в день составит 8 дней

Вывод:
Расчёт показывает, что в среднем приложением пользуются 9908 уникальных пользователей. Мой расчёт длительности теста показал, что при выборке в 78792 человека для проведения теста требуется 8 дней.

Вывод по шагу 2 - подготовка к тесту

· Сформулировала нулевую и альтернативную гипотезы: измеряю долю пользователей с просмотром 4х и более страниц;
· Определила размер выборки и длительность проведения теста: 78792 человека и 8 дней тестирования.

3. Мониторинг А/В-теста

3.1. Проверка распределения пользователей

A/B-тест успешно запущен, и уже доступны данные за первые три дня. На этом этапе нужно убедиться, что всё идёт хорошо: пользователи разделены правильным образом, а интересующие нас метрики корректно считаются.

· Сохраняю в датафрейм sessions_test_part CSV-файл с историческими данными о сессиях пользователей sessions_project_test_part.csv.
· Рассчитываю количество уникальных пользователей в каждой из экспериментальных групп для одного дня наблюдения.
· Рассчитываю и вывожу на экран процентную разницу в количестве пользователей в группах A и B. Строю визуализацию, на которой будет видно возможное различие двух групп.
Для расчёта процентной разницы воспользуюсь формулой:
P=100⋅(|A−B|/A)

# Сохраняю в датафрейм данные
sessions_test_part = pd.read_csv('путь к файлу/sessions_project_test_part.csv')

# Вывожу случайные 5 строк датафрейма
display(sessions_test_part.sample(5))

Результат:

Вывод:
Табличные данные подтверждают: дата проведения одинаковая, есть деление на группы А и В.

# Группирую по тестовым группам уникальных пользователей
test_groups = sessions_test_part.groupby('test_group')['user_id'].nunique().reset_index()

# Вывожу результат с количеством пользователей по группам
display(test_groups)

# Считаю процентную разницу в группах
gr_difference = round(100*(test_groups.iloc[0, 1] - test_groups.iloc[1, 1]) / test_groups.iloc[0, 1],4)

# Вывожу результат 
print( f'За первый день теста процент разницы между группами А и В из уникальных пользователей составил : {gr_difference} %')

Результат:

За первый день теста процент разницы между группами А и В из уникальных пользователей составил : 0.7448 %

Строю визуализацию - столбчатую диаграмму.

# Создаю фигуру
plt.figure(figsize=(8, 6))

# Столбчатая диаграмма
plt.bar(
    x=test_groups['test_group'],  # Категории (группы)
    height=test_groups['user_id'], # Высота столбцов (кол-во пользователей)
    edgecolor='black',  # Границы столбцов
    alpha=0.7          # Прозрачность
)

# Подписи
plt.title('Количество уникальных пользователей по тестовым группам', fontsize=14)
plt.xlabel('Группа', fontsize=12)
plt.ylabel('Количество пользователей', fontsize=12)

# Добавляю значения на столбцы
for i, value in enumerate(test_groups['user_id']):
    plt.text(
        x=i,                     # Позиция по X
        y=value + 20,             # Позиция по Y (немного выше столбца)
        s=str(value),            # Текст (значение)
        ha='center',             # Горизонтальное выравнивание
        fontsize=11
    )

# Сетка и вывод
plt.grid(axis='y', linestyle='--', alpha=0.7)
plt.tight_layout()
plt.show()

Результат:

Менее 1% составила разница между группами А и В среди уникальных пользователей.

3.2. Проверка пересечений пользователей

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

· Рассчитываю количество пользователей, которые встречаются одновременно в группах A и B. В ином случае убеждаюсь, что таких нет.

# Получаю уникальные user_id для каждой группы
users_a = set(sessions_test_part[sessions_test_part['test_group'] == 'A']['user_id'].unique())
users_b = set(sessions_test_part[sessions_test_part['test_group'] == 'B']['user_id'].unique())

# Нахожу пересечение (пользователи в обеих группах)
common_users = users_a.intersection(users_b)

# Количество таких пользователей
num_common_users = len(common_users)

print(f"Количество пересекающихся уникальных пользователей в обеих группах: {num_common_users}")

Количество пересекающихся уникальных пользователей в обеих группах: 0

Вывод:
Пересекающиеся в группах пользователи не обнаружены.

3.3. Равномерность разделения пользователей по устройствам

Полезно также убедиться в том, что пользователи равномерно распределены по всем доступным категориальным переменным — типам устройств и регионам.
Визуализирую данные - строю две диаграммы:
· доля каждого типа устройства для пользователей из группы A,
· доля каждого типа устройства для пользователей из группы B.
Добавляю на диаграммы все необходимые подписи, пояснения и заголовки, которые позволят сделать вывод о том, совпадает ли распределение устройств в группах A и B.

# Оставляю данные только по уникальным пользователям
no_duplicates = sessions_test_part.drop_duplicates(subset=['user_id'], keep='first')

# Проверка результата
print(f"Было записей: {len(sessions_test_part)}")
print(f"Стало записей: {len(no_duplicates)}")
print(f"Удалено дубликатов: {len(sessions_test_part) - len(no_duplicates)}")

Результат:

Было записей: 3130
Стало записей: 2943
Удалено дубликатов: 187

Удалено 187 записей о пользовательских сессиях в связи с дублирующимися user_id.

# Фильтрация данных для групп A и B
group_a = no_duplicates[no_duplicates['test_group'] == 'A']
group_b = no_duplicates[no_duplicates['test_group'] == 'B']

# Подсчет долей устройств для каждой группы
device_counts_a = group_a['device'].value_counts(normalize=True)
device_counts_b = group_b['device'].value_counts(normalize=True)

# Создание фигуры с двумя подграфиками
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 5))

# Диаграмма для группы A
device_counts_a.plot.pie(autopct='%1.1f%%', ax=ax1, normalize=True)
ax1.set_title('Доля устройств в группе A')
ax1.set_ylabel('')  # Убираю лишнюю метку оси y

# Диаграмма для группы B
device_counts_b.plot.pie(autopct='%1.1f%%', ax=ax2, normalize=True)
ax2.set_title('Доля устройств в группе B')
ax2.set_ylabel('')  # Убираю лишнюю метку оси y

plt.tight_layout()  # Чтобы избежать наложения подписей

# Вывод результата
plt.show()

Результат:

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

3.4. Равномерность распределения пользователей по регионам

Теперь убеждаюсь, что пользователи равномерно распределены по регионам.
Строю две диаграммы:
· доля каждого региона для пользователей из группы A,
· доля каждого региона для пользователей из группы B.
Добавляю на диаграммы все необходимые подписи, пояснения и заголовки, которые позволят сделать вывод о том, совпадает ли распределение устройств в группах A и B. Целенаправленно использую другой тип диаграммы, не тот, что в прошлом пункте.

# Расчет долей устройств в группе A (деление на группы выполнила в прошлом шаге)
region_counts_a = group_a['region'].value_counts(normalize=True).reset_index()
region_counts_a.columns = ['region', 'proportion']

# Расчет долей устройств в группе B
region_counts_b = group_b['region'].value_counts(normalize=True).reset_index()
region_counts_b.columns = ['region', 'proportion']

# Задаю размер
plt.figure(figsize=(12, 5))

# Диаграмма для группы A
plt.subplot(1, 2, 1)
bars_a = plt.bar(region_counts_a['region'], region_counts_a['proportion'])
plt.title('Доля регионов в группе A')
plt.xlabel('Регион')
plt.ylabel('Доля')
plt.ylim(0, 1)  # Для одинакового масштаба с группой B

# Сетка
plt.grid(axis='y', linestyle='--', alpha=0.7)

# Добавляю подписи значений для группы A
for bar in bars_a:
    height = bar.get_height()
    plt.text(bar.get_x() + bar.get_width() / 2., height+0.01,
             f'{height:.2f}',
             ha='center', va='bottom', fontsize=11)

# Диаграмма для группы B
plt.subplot(1, 2, 2)
bars_b = plt.bar(region_counts_b['region'], region_counts_b['proportion'])
plt.title('Доля регионов в группе B')
plt.xlabel('Регион')
plt.ylabel('Доля')
plt.ylim(0, 1)

# Добавляю подписи значений для группы B
for bar in bars_b:
    height = bar.get_height()
    plt.text(bar.get_x() + bar.get_width() / 2., height+0.01,
             f'{height:.2f}',
             ha='center', va='bottom', fontsize=11)

# Сетка и вывод
plt.grid(axis='y', linestyle='--', alpha=0.7)
plt.tight_layout()  # Чтобы избежать наложения подписей
plt.show()

Результат:

Вывод:
По диаграмме видно одинаковое распределение по регионам в группах А и В.

Вывод по шагу 3 - мониторинг А/В-теста

На основе проведённого анализа данных для A/B-теста я формулирую выводы:
· Я обнаружила различие в количестве пользователей в двух группах на 11 уникальных пользователей (в контрольной группе А их больше). Однако при сравнении общих пропорций это отличие является незначительным и составляет 0,75%.
· Выборки являются независимыми; пересечений пользователей не обнаружено.
· Равномерное распределение пользователей по типам устройств и регионам сохраняется.
В заключении могу резюмировать: выборки для групп А и В независимые и подходят для проведения теста.

4. Проверка результатов A/B-теста

A/B-тест завершён, у нас есть результаты за все дни проведения эксперимента. Необходимо убедиться в корректности теста и верно интерпретировать результаты.

4.1. Получение результатов теста и подсчёт основной метрики

· Считываю и сохраняю в датафрейм sessions_test CSV-файл с данными о сессиях пользователей за весь период проведения A/B-теста, то есть с 2025-10-14 по 2025-11-02. Файл: sessions_project_test.csv.
· В датафрейме sessions_test создаю дополнительный столбец good_session. В него войдёт значение 1, если за одну сессию было просмотрено 4 и более страниц, и значение 0, если просмотрено меньше.

# Создаю датафрейм sessions_test на основании файла с полным тестом
sessions_test = pd.read_csv('путь к файлу/sessions_project_test.csv')

# Создаю дополнительный столбец
sessions_test['good_session'] = sessions_test['page_counter'].apply(lambda x: 1 if x >= 4 else 0)

# Вывожу первые 5 строк
display(sessions_test.head())

# Перевожу столбец session_date в формат даты
sessions_test['session_date'] = pd.to_datetime(sessions_test['session_date'])

min_date = sessions_test['session_date'].min()
max_date = sessions_test['session_date'].max()

# Вычисляю разницу в днях
days_diff = (max_date - min_date).days

print(f'Даты проведения эксперимента: {min_date} - {max_date}')
print(f"Количество дней между 14 октября 2025 и 2 ноября 2025: {days_diff} дней")

Результат:

Даты проведения эксперимента: 2025-10-14 00:00:00 - 2025-11-02 00:00:00
Количество дней между 14 октября 2025 и 2 ноября 2025: 19 дней

Эксперимент проводился с 14 октября по 2 ноября 2025 года в течение 19 дней.

4.2. Проверка корректности результатов теста

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

· Рассчитываю количество уникальных сессий для каждого дня и обеих тестовых групп, используя группировку.
· Проверяю, что количество уникальных дневных сессий в двух выборках не различается или различия не статистически значимы. Использую статистический тест, который позволит сделать вывод о равенстве средних двух выборок.
· В качестве ответа вывожу на экран полученное значение p-value и интерпретирую его.

# Импорт библиотеки для статистического t-теста
from scipy.stats import ttest_ind

# 1. Группировка по дням и тестовым группам
daily_sessions = sessions_test.groupby(['session_date', 'test_group'])['session_id'].nunique().reset_index()

# 2. Разделение на группы A и B
group_a = daily_sessions[daily_sessions['test_group'] == 'A']['session_id']
group_b = daily_sessions[daily_sessions['test_group'] == 'B']['session_id']

# 3. Проверка равенства средних с помощью t-теста
t_stat, p_value = ttest_ind(group_a, group_b, equal_var=False)

print(f"p-value: {p_value:.2f}")

# 4. Интерпретация
# уровень значимости, который определяет вероятность отвергнуть нулевую гипотезу = 5%
if p_value < 0.05:
    print("Отвергаю нулевую гипотезу: различия в количестве дневных сессий между группами статистически значимы.")
    print("Выборки НЕ сопоставимы по этому параметру. Требуется дополнительный анализ причин дисбаланса.")
else:
    print("Недостаточно доказательств для отклонения нулевой гипотезы: статистически значимых различий в количестве дневных сессий не обнаружено.")
    print("Группы сопоставимы по этому параметру, можно продолжать анализ продуктовых метрик.")

Результат:

p-value: 0.94
Недостаточно доказательств для отклонения нулевой гипотезы: статистически значимых различий в количестве дневных сессий не обнаружено.
Группы сопоставимы по этому параметру, можно продолжать анализ продуктовых метрик.

Вывод:
Полученное значение p-value больше, чем уровень значимости. Тест показал, что по итогу проверки равенства средних по количеству сессий, у меня недостаточно доказательств, чтобы отвергнуть нулевую гипотезу: отличий не обнаружено. Можно продолжать анализ продуктовых метрик.

4.3. Сравнение доли успешных сессий

Выше я убедилась, что количество сессий в обеих выборках не различалось, можно переходить к анализу ключевой метрики — доли успешных сессий.
Использую созданный на первом шаге задания столбец good_session и рассчитываю долю успешных сессий для выборок A и B, а также разницу в этом показателе. Полученный вывод отображаю на экране методом display().

# Рассчитываю долю успешных сессий для каждой группы
result = sessions_test.groupby('test_group')['good_session'].agg(
    total_sessions='count',
    good_sessions='sum',
    success_rate='mean'
).reset_index()

result['success_rate']=round(result['success_rate'],2)

display(result)

Результат:

# Размер успехов в группах
m_a = result.iloc[0, 2]
m_b = result.iloc[1, 2]

# Размер выборок в группах
n_a = result.iloc[0, 1]
n_b = result.iloc[1, 1]

# Считаю разницу между успешными сессиями в абсолютном количестве
difference_2 = m_b - m_a

# Рассчитываю доли успехов для каждой группы: A и B
p_a, p_b = round(m_a/n_a,2), round(m_b/n_b,2) 

# Вывожу результат 
print( f'Разница между группами В и А успешных сессий составила : {difference_2} сессий.')
print( f'Размер выборки А : {n_a} , размер выборки В: {n_b}')
print(f'Доля успешных сессий в группе А: {p_a}, доля успешных сессий в группе В:{p_b}')

Разница между группами В и А успешных сессий составила : 811 сессий.
Размер выборки А : 49551 , размер выборки В: 50454
Доля успешных сессий в группе А: 0.31, доля успешных сессий в группе В:0.32

Вывод:
Полученные данные показывают, что количество успешных сессий в тестовой выборке В примерно на 1% выше, чем в контрольной выборке А, разница в абсолютных значениях составляет 811.

4.4. Насколько статистически значимо изменение ключевой метрики

На предыдущем шаге я убедилась, что количество успешных сессий в тестовой выборке примерно на 1.1% выше, чем в контрольной, но делать выводы только на основе этого значения будет некорректно. Для принятия решения всегда необходимо отвечать на вопрос: является ли это изменение статистически значимым.
· Использую статистический z-тест пропорций и рассчитываю, является ли изменение в метрике доли успешных сессий статистически значимым.
· Вывожу на экран полученное значение p-value и пишу выводы о статистической значимости. Помню, что уровень значимости в эксперименте был выбран на уровне 0.05.
Проверяемая гипотеза будет выглядеть так:
· Доля успешных сессий не различается. H0: pВ=pА
· Доля успешных сессий больше после применения алгоритма H1: pВ>pА

# В прошлом шаге я нашла размеры выборок n_a, n_b и размеры успехов m_a, m_b в группах, а также доли успехов p_a, p_b
# До запуска z-теста проверяю кол-во данных 
if (p_a*n_a > 10)and((1-p_a)*n_a > 10)and(p_b*n_b > 10)and((1-p_b)*n_b > 10):
    print('Предпосылка о достаточном количестве данных выполняется!')
else:
    print('Предпосылка о достаточном количестве данных НЕ выполняется!')

Предпосылка о достаточном количестве данных выполняется!

Увидела, что размер вероятности успеха для группы A составляет 0.31 (p_a), а для группы B — 0.32(p_b). При количестве общем наблюдений n_a = 49551 и n_b=50454, предпосылка о достаточном количестве данных выполняется. Теперь можно применить Z-тест пропорций.

from statsmodels.stats.proportion import proportions_ztest

# уровень значимости проверки гипотезы о равенстве вероятностей = 5%
alpha = 0.05 

stat_ztest, p_value_ztest = proportions_ztest(
    [m_b, m_a],
    [n_b, n_a],
    alternative='larger' # так как H_1: p_b > p_a
)

# вывожу полученное p-value
print(f'Значение p-value={p_value_ztest}')  

if p_value_ztest > alpha:
    print(f'p-value > {alpha}')
    print('Нулевая гипотеза находит подтверждение!')
else:
    print(f'p-value < {alpha}')
    print('Нулевая гипотеза не находит подтверждения!')

Значение p-value=0.0001574739988036123
p-value < 0.05
Нулевая гипотеза не находит подтверждения!

Вывод:
Получила низкое значение p-value. Это означает, что нет оснований принимать нулевую гипотезу. Альтернативная гипотеза, согласно результатам статистического теста, находит подтверждение. Делаю вывод, что существует статистически значимое различие между долями успешных сессий (4 страницы просмотра и более) в группах A и B. Результаты подтверждают это: доля успешных сессий просмотра действительно больше для пользователей с новым алгоритмом рекомендаций контента.

5. Вывод по результатам A/B-эксперимента

На основе проведённого анализа результатов теста формулирую выводы для команды разработки приложения. В выводе акцентирую внимание на:
· Характеристики проведённого эксперимента, количество задействованных пользователей и длительность эксперимента.
· Повлияло ли внедрение нового алгоритма рекомендаций на рост ключевой метрики и как.
· Каким получилось значение p-value для оценки статистической значимости выявленного эффекта.
· Стоит ли внедрять нововведение в приложение.

Эксперимент проводился с 14 октября по 2 ноября 2025 года в течение 19 дней. Пользователи были разделены на 2 группы: А - контрольная группа, В - экспериментальная группа. Для экспериментальной группы был предложен новый алгоритм рекомендации развлекательного контента. Предполагалось, что в тестовой группе В доля просмотра контента с 4-мя страницами и выше будет больше, чем в группе А.
По итогам проверки могу сказать, что проверяемая теория подтвердилась и нет оснований для принятия нулевой гипотезы о том, что разницы в успешных просмотрах нет.
Значение p-value равно 0.00015, следовательно, существует статистически значимое различие между долями успешных сессий (4 страницы просмотра и более) в группах A и B.
Нововведение рекомендуется к внедрению.

Цель проекта

Собрать требования от заказчика, получить данные и связать их – создать инструмент (дашборд) для мониторинга.

Этапы выполнения

1. Сбор требований
2. Знакомство с данными
3. Описание данных
4. Планирование дашборда
5. Создание дашборда в Superset
6. Выводы на основании данных

1. Сбор требований от заказчика

Письмо от заказчика:

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

Выводы, подтверждённые в диалоге после письма:
Заказчик хочет понимать: насколько эффективно тратится бюджет на рекламу, какие каналы привлекают пользователей, какая выручка получается в итоге. Это означает, что ему важна вся цепочка: рекламные расходы → привлечение пользователей → активность → заказы → выручка.

Какие источники данных нужны: расходы на рекламу (costs), сессии пользователей (sessions), заказы и выручка (orders).

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

Общие требования ко всем визуализациям:

• Все чарты должны иметь возможность фильтрации по датам.
• KPI-чарты должны включать сравнение с предыдущим периодом (PoP, period-over-period).

2. Знакомство с данными в SQL

Подключаюсь к базе данных «data-analyst-advanced-sql» PostgreSQL, к схеме bi_practicum, формирую запросы в DBeaver для изучения доступных данных.

2.1 Знакомлюсь с данными из таблицы sessions

SELECT *
FROM bi_practicum.pa_sessions sess
LIMIT(10);

Результат:

Вывод: В таблице sessions 6 столбцов, в том числе channel.

Проверяю, есть ли пустые значения в channel.

SELECT channel
FROM bi_practicum.pa_sessions sess
GROUP BY channel
HAVING channel = 'NA';

Результат: пусто. Таких значений нет.

Проверяю, какие значения есть.

SELECT channel, count(channel) 
FROM bi_practicum.pa_sessions sess
GROUP BY channel;

Результат:

Вывод: Всего 11 каналов рекламы в таблице sessions.

2.2 Смотрю содержимое таблицы orders

SELECT *
FROM bi_practicum.pa_orders ord
LIMIT(10);

Результат:

Вывод: В таблице orders есть 4 столбца. Проверяю дальше на наличие пустых order_id и user_id.

SELECT 
    COUNT(order_id) as order_count, 
    user_id 
FROM bi_practicum.pa_orders ord 
WHERE user_id IS NULL
GROUP BY user_id;

Результат: пусто. Нулевых значений user_id нет.

Вывожу user_id первые 10 по возрастанию – проверяю на нулевые значения:

SELECT 
    user_id,
    COUNT(order_id) as order_count
FROM bi_practicum.pa_orders 
GROUP BY user_id 
ORDER BY user_id ASC 
LIMIT 10;

Результат:

Вывод: Нулевых значений в user_id нет.

Вывожу первые 5 номеров order_id по возрастанию – проверка на нули:

SELECT order_id
FROM bi_practicum.pa_orders 
GROUP BY order_id 
ORDER BY order_id ASC 
LIMIT 5;

Результат: 667, 668, 669, 670, 671. Нулей нет.

Проверяю, есть ли несколько сессий на пользователя в течение одного дня:

SELECT CAST (sess.session_start AS date) AS session_start,
       CAST (sess.session_end AS date) AS session_end,
       user_id, COUNT(sess.user_id) AS count_users
FROM bi_practicum.pa_sessions sess
GROUP BY CAST (sess.session_start AS date),
         CAST (sess.session_end AS date),
         user_id
HAVING COUNT(sess.user_id) >1;

Результат:

Вывод: 23 марта у одного клиента было 2 сессии за день.

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

SELECT CAST (session_start AS date) AS session_start,
       CAST (session_end AS date) AS session_end,
       session_id, COUNT(session_id) AS count_sessions
FROM bi_practicum.pa_sessions sess
GROUP BY CAST (session_start AS date),
         CAST (session_end AS date),
         session_id
HAVING COUNT(session_id) >1;

Результат: пусто. Дублей по сессиям в рамках одного дня нет.

Проверяю, как данные соотносятся с таблицей заказов (orders).

SELECT 
    event_dt,
    user_id,
    device,
    order_id,
    channel,
    COUNT(*) AS cnt_users
FROM (
    SELECT 
        CAST(COALESCE(ors.event_dt, sess.session_start) AS date) AS event_dt, -- получаю дату события
        DATE_PART('minute', sess.session_end - sess.session_start)
        + DATE_PART('hour', sess.session_end - sess.session_start) * 60 AS session_length, -- считаю длину одной сессии
        COALESCE(ors.user_id, sess.user_id) AS user_id, -- вывожу id пользователя
        ors.order_id AS order_id, -- вывожу id заказа
        COALESCE(sess.channel, 'N/A') AS channel,  -- вывод канала привлечения
        ors.revenue AS revenue,     -- считаю выручку
        sess.device              -- вывод типа устройства
    FROM bi_practicum.pa_orders ors
    FULL JOIN bi_practicum.pa_sessions sess
        ON ors.user_id = sess.user_id
        AND ors.event_dt >= sess.session_start
        AND ors.event_dt <= sess.session_end
) tempo 
GROUP BY 
    event_dt,
    user_id,
    device,
    order_id,
    channel
HAVING COUNT(*) > 1;

Результат:

Вывод: Вижу два уникальных клиента, у которых задублировались заказы, order id: 23947 и 36617. Чтобы не было дублей, беру среднее по полю с длиной сессии – так получаю примерно то, сколько она длилась для заказа. Поскольку заказчику нужна именно средняя длительность сессии, оптимальным решением будет использовать функцию AVG.

3. Описание данных

После знакомства с данными структурирую взаимосвязи и описываю их.

Таблица costs — данные о рекламных расходах:
• dt — дата, тип данных DATE.
• channel — рекламный канал, тип данных TEXT.
• costs — расходы, тип данных NUMERIC.
• ad_category — тип рекламной кампании, тип данных TEXT.

Таблица sessions — информация о сессиях пользователей:
• user_id — ID пользователя, тип данных NUMERIC.
• device — тип устройства, тип данных TEXT.
• channel — рекламный канал, тип данных TEXT.
• session_start — дата и время начала сессии, тип данных TIMESTAMP.
• session_end — дата и время конца сессии, тип данных TIMESTAMP.
• session_id — ID сессии, тип данных INTEGER.

Таблица orders — данные о заказах:
• user_id — ID пользователя, тип данных NUMERIC.
• event_dt — дата, тип данных TIMESTAMP.
• revenue — выручка, тип данных NUMERIC.
• order_id — ID заказа, тип данных INTEGER.

ER-модель данных:

Для таблицы costs первичный ключ (PK) — поле ad_category. Это означает, что в таблице может быть только одна запись на каждую категорию рекламы. Такая структура необычна, поскольку в один день по одному каналу может быть несколько категорий. Однако оставляю как есть. Возможно, правильнее было бы использовать составной ключ (Date, channel, ad_category).

Для таблицы sessions поле session_id — первичный ключ (PK), идентифицирует каждую сессию. user_id — внешний ключ (FK), т.е. одна сессия принадлежит одному пользователю, один пользователь может иметь много сессий (связь «один ко многим»). Длительность сессии можно вычислить как session_end - session_start. Поле channel позволяет понять, через какой канал пользователь пришёл на сайт в рамках конкретной сессии.

Для таблицы orders поле user_id — первичный ключ (PK) (в диаграмме – неверно, правильнее order_id), идентифицирует каждого пользователя, связан с таблицей sessions. Поле event_dt — внешний ключ (FK), имеет связь «один ко многим», ссылается на первичный ключ таблицы costs (поле dt).

4. Планирование дашборда

На этом этапе представляю макет дашборда: продумываю вкладки и их наполнение, какие данные использую.

Основная (1) – ключевые индикаторы:
• Выручка – сумма доходов за месяц с сравнением MoM. Источник – orders.
• Затраты – расходы на рекламу с динамикой по месяцам. Источник – costs.
• ROI – возврат инвестиций: (Выручка – Затраты) / Затраты. Источники – orders, sessions, costs.
• Количество заказов – сколько заказов совершено в каждом месяце. Источник – orders.
• Конверсия в заказ – доля пользователей, совершивших хотя бы один заказ после перехода из рекламы. Источники – orders, sessions.
• Среднее время сессии – средняя длительность пользовательской сессии. Источник – sessions.
• Количество сессий – количество уникальных пользователей. Источник – sessions.

Дополнительная (2) – типы рекламных кампаний:
• ROI по типу рекламы – Line Chart. Источники – orders, sessions, costs.
• Баланс: доходы и расходы по типу рекламы – Bar Chart (расходы – отрицательные значения).

Дополнительная (3) – каналы рекламы:
• Динамика сессий по каналам привлечения – Bar Chart.
• ROI по каналам привлечения – Line Chart.
• Средняя длительность сессии по каналам – Bar Chart.

Дополнительная (4) – типы устройств:
• Распределение сессий по девайсам – Pie Chart.
• Количество сессий по девайсам по месяцам – Bar Chart.
• Количество сессий по девайсам по дням – Line Chart.

Дополнительная (5) – аннотация: общие сведения о дашборде, информация о метриках и фильтрах.

Фильтры: Период (дефолт 2019-05-01 – 2020-05-01), Гранулярность (месяц), Тип рекламы, Рекламный канал, Тип устройства.

5. Создание дашборда в Superset

5.1 Подготовка кода датасета

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

WITH sess_or AS ( --объединяю заказы и сессии так, чтобы получить как можно больше информации
    --беру дату заказа или дату начала сессии, перевожу в формат даты
    SELECT CAST(COALESCE(ors.event_dt, sess.session_start) AS date) AS event_dt
    --если есть заказ (ors.user_id), беру его; иначе — sess.user_id
        , COALESCE(ors.user_id, sess.user_id) AS user_id
        , ors.order_id AS order_id
        , COALESCE(sess.channel, 'N/A') AS channel   --Канал из сессии или 'N/A', если сессии нет
        , ors.revenue AS revenue
        , device
        , AVG(DATE_PART('minute', session_end - session_start)
        + DATE_PART('hour', session_end - session_start) * 60) AS session_length  --ср. длина сессии в минутах
    FROM bi_practicum.pa_orders ors 
    FULL JOIN bi_practicum.pa_sessions sess --сохраняются все строки из обеих таблиц
        ON ors.event_dt BETWEEN sess.session_start AND sess.session_end
        AND ors.user_id = sess.user_id
   --дата, пользователь, заказ, канал, выручка, устройство) даёт одну строку после группировки
    GROUP BY CAST(COALESCE(ors.event_dt, sess.session_start) AS date)
        , COALESCE(ors.user_id, sess.user_id)
        , ors.order_id
        , COALESCE(sess.channel, 'N/A')
        , ors.revenue
        , device
)  --итоговый запрос с подзапросом во FROM
SELECT raw.event_dt
    , AVG(raw.session_length) AS session_length
    , COUNT(DISTINCT raw.order_id) AS orders  --кол-во уник заказов
    , COUNT(DISTINCT raw.user_id) AS users  --кол-во уник пользователей
    , COUNT(DISTINCT CASE WHEN raw.order_id IS NOT NULL THEN raw.user_id END) AS users_with_order
    , raw.channel
    , raw.ad_category
    , raw.device
    , MAX(pa_costs.costs) AS costs  --максимальная сумма — так как в одной группе (дата+канал) может быть много строк с одинаковыми расходами
    , SUM(raw.revenue) AS revenue  --Суммарная выручка
FROM (  --подзапрос raw – это объединение с расходами
    SELECT COALESCE(sess_or.event_dt, pa_costs.dt) AS event_dt
        , session_length
        , order_id
        , user_id
        , COALESCE(sess_or.channel, pa_costs.channel) AS channel
        , COALESCE(pa_costs.ad_category, 'N/A') AS ad_category
        , device
        , sess_or.revenue
    FROM sess_or
    FULL JOIN bi_practicum.pa_costs   --полное объединение по каналу и дате
        ON sess_or.channel = pa_costs.channel  
        AND sess_or.event_dt = pa_costs.dt 
) raw 
FULL JOIN bi_practicum.pa_costs --в итоговую таблицу попадут даже те строки из pa_costs, которые не соединились с сессиями/заказами
    ON raw.channel = pa_costs.channel
    AND raw.event_dt = pa_costs.dt
GROUP BY
    raw.event_dt
    , raw.channel
    , raw.ad_category
    , raw.device;

Вывод: SQL-запрос объединяет данные о сессиях, заказах и рекламных расходах для последующего анализа – создаю витрину данных и сохраняю её в датасет. Для каждого дня, канала, категории рекламы и устройства посчитаны: средняя длительность сессии, количество заказов, количество уникальных пользователей (всех), количество пользователей с заказами, расходы (MAX – фактически просто значение), выручка.

5.2 Создание метрик в датасете

После создания датасета создаю метрики:

5.3 Скриншоты готового дашборда

Первая вкладка «Общие показатели» – графики основных KPI с сравнением с прошлым периодом (PoP).
Во всех чартах указан период сравнения 1, т.е. в дальнейшем при выборе гранулярности периода, показатель будет сравниваться с предыдущим на том же уровне (день – с прошлым днем, месяц – с прошлым месяцем и т.д.).

Вторая вкладка «Тип рекламы»

Третья вкладка «Канал рекламы»

Четвёртая вкладка «Тип устройства»

Пятая вкладка «Аннотация к дашборду»

6. Выводы

1. Общие затраты на рекламу за год снизились на 61 тыс. и составили чуть более 100 тыс. на май 2020 года. Выручка сократилась на 13% и составила 116 тыс. на май 2020 года (в мае 2019 года было 134 тыс.).

2. Общее количество заказов сократилось на 12%, но увеличилась конверсия в заказ на 40% и составляет на май 2020 года 17,2%.

3. Среднее время сессии держится на протяжении года 29–30 минут.

4. По каналам рекламы видно, что TopAds на втором месте по количеству сессий, но окупаемость канала на протяжении года остаётся в минусе. Также в минусе остаются AdsNon и GlissAds. Следует обратить внимание на канал organic, который лидирует по количеству сессий (165 тыс.), но не отображается на графике окупаемости по причине отсутствия затрат в данных. По этому каналу конверсия в заказ выросла на 39% с мая 2019 по май 2020 года, а общее количество заказов сократилось на 17%.

5. Стабильный рост окупаемости у каналов: GrendelLeap (второе место по окупаемости), RocketAds (самая высокая окупаемость среди каналов), CreativeMediaBee, HovaNetBanner (рост более 90%). По средней продолжительности сессий сильных отличий не наблюдается. Самый быстрый рост окупаемости по каналам: email, cpc, content.

6. GrendelLeap и CreativeMediaBee отстают по количеству сессий, но дают стабильный рост окупаемости. Следует уделить внимание развитию этих каналов.

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

8. Конверсия на уровне года:
– Android: рост 42%, количество заказов снижение на 12%, количество сессий снижается на 38%. Окупаемость вышла в положительную сторону.
– Mac: рост 38%, количество заказов снижение на 12%, количество сессий снижается на 36%. Окупаемость всё ещё в минусе, но достигла -6% (было -30%).
– PC: рост 42%, количество заказов снижение на 9%, количество сессий снижается на 36%. Окупаемость всё ещё в минусе, но достигла -10% (было -37%).
– iPhone: рост 38%, количество заказов снижение на 13%, количество сессий снижается на 37%. Окупаемость увеличилась на 221% и стала 68,47%.

Цель проекта

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

Этапы выполнения

1. Загрузка данных и знакомство с ними
2. Проверка ошибок в данных и их предобработка
2.1 Проверка и корректировка названий столбцов
2.2 Проверка и замена типов данных
2.3 Работа с пропусками
2.4 Проверка на явные и неявные дубликаты в данных
3. Фильтрация данных
4. Категоризация данных
5. Итоговые выводы проекта

Описание данных

Данные /datasets/new_games.csv содержат информацию о продажах игр разных жанров и платформ, а также пользовательские и экспертные оценки игр:

• Name — название игры.
• Platform — название платформы.
• Year of Release — год выпуска игры.
• Genre — жанр игры.
• NA sales — продажи в Северной Америке (в миллионах проданных копий).
• EU sales — продажи в Европе (в миллионах проданных копий).
• JP sales — продажи в Японии (в миллионах проданных копий).
• Other sales — продажи в других странах (в миллионах проданных копий).
• Critic Score — оценка критиков (от 0 до 100).
• User Score — оценка пользователей (от 0 до 10).
• Rating — рейтинг организации ESRB (англ. Entertainment Software Rating Board, ассоциация определяет рейтинг компьютерных игр и присваивает им подходящую возрастную категорию).

1. Загрузка данных и знакомство с ними

Загружаю необходимые библиотеки Python и данные датасета /datasets/new_games.csv.

Ввод [1]:

# Импортируем библиотеку pandas 
import pandas as pd 

# Выгружаем данные /datasets/new_games.csv в датафрейм new_games
new_games = pd.read_csv('путь к файлу/new_games.csv') 

Знакомство с данными: вывод результата метода info(), вывод первых строк.

Ввод [2]:

new_games.info()

Результат:

RangeIndex: 16956 entries, 0 to 16955
Data columns (total 11 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   Name             16954 non-null  object 
 1   Platform         16956 non-null  object 
 2   Year of Release  16681 non-null  float64
 3   Genre            16954 non-null  object 
 4   NA sales         16956 non-null  float64
 5   EU sales         16956 non-null  object 
 6   JP sales         16956 non-null  object 
 7   Other sales      16956 non-null  float64
 8   Critic Score     8242 non-null   float64
 9   User Score       10152 non-null  object 
 10  Rating           10085 non-null  object 
dtypes: float64(4), object(7)
memory usage: 1.4+ MB

Ввод [3]:

# Вывожу первые строки датафрейма
new_games.head() 

Результат:

Вывод:
Выше вижу первые пять строк датафрейма.

Вывод о полученных данных:
1. Данные содержат 11 столбцов и 16956 строк.
2. Есть пропущенные значения в следующих столбцах: Name, Year of Release, Genre, Critic Score, User Score, Rating.
3. Год релиза Year of Release указан в формате числа с плавающей точкой. Необходимо поменять на формат даты.
4. Данные о продажах EU sales приведены в формате строк, необходимо поменять формат на число с плавающей точкой.
5. Данные о продажах JP sales приведены в формате строк, необходимо поменять формат на число с плавающей точкой.
6. Данные об оценке User Score приведены в формате строк, необходимо поменять формат на число с плавающей точкой.

2. Проверка ошибок в данных и их предобработка

2.1. Проверка и корректировка названий столбцов

Вывожу на экран названия всех столбцов датафрейма и проверяю их стиль написания.
Привожу все столбцы к стилю snake case. Слежу, чтобы названия были в нижнем регистре, а вместо пробелов — подчёркивания.

Ввод [4]:

 # Вывожу на экран названия всех столбцов датафрейма и проверяю их стиль написания. print(new_games.columns) 

Результат:

 Index(['Name', 'Platform', 'Year of Release', 'Genre', 'NA sales', 'EU sales', 'JP sales', 'Other sales', 'Critic Score', 'User Score', 'Rating'], dtype='object') 

Ввод [5]:

 
# Привожу все столбцы к стилю snake case. 
# Слежу, чтобы названия были в нижнем регистре, а вместо пробелов — подчёркивания. 
new_games.columns = new_games.columns.str.lower().str.replace(' ','_') 

Ввод [6]:

 print(new_games.columns) 

Результат:

 Index(['name', 'platform', 'year_of_release', 'genre', 'na_sales', 'eu_sales', 'jp_sales', 'other_sales', 'critic_score', 'user_score', 'rating'], dtype='object') 

Вывод 2.1:
Названия всех столбцов приведены к стилю snake case, все в нижнем регистре, а вместо пробелов — подчёркивания.

2.2. Проверка и замена типов данных

В датасете встречаются некорректные типы данных.
Причины возможны следующие:
- Некорректное преобразование при чтении файла.
- В связи с пропусками типы данных преобразовались в неоптимальный формат.
Буду проводить преобразование типов данных. Помню, что столбцы с числовыми данными и пропусками нельзя преобразовать к типу int64. Сначала понадобится обработать пропуски, а затем преобразовать типы данных.

2.2.1. Изменение форматов чисел

Год релиза Year of Release указан в формате числа с плавающей точкой.
Необходимо поменять на формат даты для дальнейшей работы.

Ввод [7]:

 
# Проверяю диапазоны дат в датасете, минимальную и максимальную 
print(new_games['year_of_release'].min())
print(new_games['year_of_release'].max()) 

Результат:

1980.0
2016.0 

Ввод [8]:

 
# Преобразую года в формат даты
new_games['year_of_release'] = pd.to_datetime(new_games['year_of_release'], format='%Y') 

# Проверяю изменения 
print(new_games['year_of_release'].dtype) 
print(new_games['year_of_release'].min()) 
print(new_games['year_of_release'].max()) 
print() print(new_games['year_of_release'].unique()) 

Результат:

 
datetime64[ns] 
1980-01-01 00:00:00 
2016-01-01 00:00:00

 DatetimeArray 
['2006-01-01 00:00:00', '1985-01-01 00:00:00', '2008-01-01 00:00:00',
'2009-01-01 00:00:00', '1996-01-01 00:00:00', '1989-01-01 00:00:00',
'1984-01-01 00:00:00', '2005-01-01 00:00:00', '1999-01-01 00:00:00',
'2007-01-01 00:00:00', '2010-01-01 00:00:00', '2013-01-01 00:00:00',
'2004-01-01 00:00:00', '1990-01-01 00:00:00', '1988-01-01 00:00:00',
'2002-01-01 00:00:00', '2001-01-01 00:00:00', '2011-01-01 00:00:00',
'1998-01-01 00:00:00', '2015-01-01 00:00:00', '2012-01-01 00:00:00',
'2014-01-01 00:00:00', '1992-01-01 00:00:00', '1997-01-01 00:00:00',
'1993-01-01 00:00:00', '1994-01-01 00:00:00', '1982-01-01 00:00:00',
'2016-01-01 00:00:00', '2003-01-01 00:00:00', '1986-01-01 00:00:00',
'2000-01-01 00:00:00', 'NaT', '1995-01-01 00:00:00',
'1991-01-01 00:00:00', '1981-01-01 00:00:00', '1987-01-01 00:00:00',
'1980-01-01 00:00:00', '1983-01-01 00:00:00']

Length: 38, dtype: datetime64[ns]

Вывод:
Преобразование в тип datetime64 выполнено успешно. Это оптимизирует дальнейшую работу.

2.2.2 Просмотр данных о продажах в столбцах EU sales
Ввод [9]:

 
# Смотрю все уникальные значения eu_sales
new_games['eu_sales'].unique() 

Результат:

 
array(['28.96', '3.58', '12.76', '10.93', '8.89', '2.26', '9.14', '9.18', '6.94', '0.63', '10.95', '7.47', '6.18', '8.03', '4.89', '8.49', '9.09', '0.4', '3.75', '9.2', '4.46', '2.71', '3.44', '5.14', '5.49', '3.9', '5.35', '3.17', '5.09', '4.24', '5.04', '5.86', '3.68', '4.19', '5.73', '3.59', '4.51', '2.55', '4.02', '4.37', '6.31', '3.45', '2.81', '2.85', '3.49', '0.01', '3.35', '2.04', '3.07', '3.87', '3.0', '4.82', '3.64', '2.15', '3.69', '2.65', '2.56', '3.11', '3.14', '1.94', '1.95', '2.47', '2.28', '3.42', '3.63', '2.36', '1.71', '1.85', '2.79', '1.24', '6.12', '1.53', '3.47', '2.24', '5.01', '2.01', '1.72', '2.07', '6.42', '3.86', '0.45', '3.48', '1.89', '5.75', '2.17', '1.37', '2.35', '1.18', '2.11', '1.88', '2.83', '2.99', '2.89', '3.27', '2.22', '2.14', '1.45', '1.75', '1.04', '1.77', '3.02', '2.75', '2.16', '1.9', '2.59', '2.2', '4.3', '0.93', '2.53', '2.52', '1.79', '1.3', '2.6', '1.58', '1.2', '1.56', '1.34', '1.26', '0.83', '6.21', '2.8', '1.59', '1.73', '4.33', '1.83', '0.0', '2.18', '1.98', '1.47', '0.67', '1.55', '1.91', '0.69', '0.6', '1.93', '1.64', '0.55', '2.19', '1.11', '2.29', '2.5', '0.96', '1.21', '1.12', '0.77', '1.69', '1.08', '0.79', '2.37', '2.46', '0.26', '0.75', '1.25', '2.43', '0.98', '0.74', '2.23', '0.61', '2.45', '1.41', '1.8', '3.28', '1.16', '1.99', '1.38', '1.36', '1.17', '1.19', '0.99', '1.68', '2.0', '1.33', '1.57', '1.48', '2.1', '1.27', '1.97', '0.91', '1.39', '1.96', '0.24', '1.51', '0.14', '1.29', '2.39', '1.03', '0.5', '0.58', '1.31', '2.02', '1.32', '1.01', '2.27', '2.3', '1.82', '2.78', '0.44', '0.48', '0.27', '0.21', '2.48', '0.51', '1.52', '0.04', '0.28', '1.35', '0.87', '2.13', '1.13', '1.76', '0.76', '2.12', '0.66', '1.6', '1.44', '1.43', '1.7', '0.47', '1.87', '0.86', '0.73', '1.28', '0.81', '1.09', '0.68', '1.22', '1.4', '1.02', '1.49', '1.14', '0.49', '0.9', '0.38', '1.42', '0.95', '1.62', '0.71', '1.05', '0.92', '0.33', '0.3', '1.67', '1.0', '0.89', '0.1', '0.72', '0.59', '0.56', '0.16', '0.97', '0.62', 'unknown', '0.85', '0.94', '0.88', '0.84', '1.06', '0.2', '1.15', '0.8', '1.1', '0.7', '1.92', '0.32', '0.15', '0.53', '0.09', '1.46', '0.29', '0.22', '1.23', '0.07', '0.17', '0.54', '0.36', '0.31', '1.84', '0.52', '0.11', '0.64', '0.12', '2.05', '1.63', '0.82', '0.08', '0.57', '1.65', '0.19', '0.02', '0.43', '0.25', '1.5', '0.18', '0.39', '0.13', '1.07', '0.46', '0.41', '0.06', '0.03', '0.37', '0.05', '0.23', '0.65', '0.42', '0.34', '0.35', '0.78'], dtype=object) 

Вывод:
Вижу строковое значение 'unknown', которое ниже поменяю на индекс 0.
Ввод [10]:

 
# Выполняю замену строкового значения на индекс 0 
new_games['eu_sales'] = new_games['eu_sales'].str.replace('unknown','0')

# Смотрю все уникальные значения eu_sales после замены 
display(new_games['eu_sales'].unique()) 

Результат:

 
array(['28.96', '3.58', '12.76', '10.93', '8.89', '2.26', '9.14', '9.18', '6.94', '0.63', '10.95', '7.47', '6.18', '8.03', '4.89', '8.49', '9.09', '0.4', '3.75', '9.2', '4.46', '2.71', '3.44', '5.14', '5.49', '3.9', '5.35', '3.17', '5.09', '4.24', '5.04', '5.86', '3.68', '4.19', '5.73', '3.59', '4.51', '2.55', '4.02', '4.37', '6.31', '3.45', '2.81', '2.85', '3.49', '0.01', '3.35', '2.04', '3.07', '3.87', '3.0', '4.82', '3.64', '2.15', '3.69', '2.65', '2.56', '3.11', '3.14', '1.94', '1.95', '2.47', '2.28', '3.42', '3.63', '2.36', '1.71', '1.85', '2.79', '1.24', '6.12', '1.53', '3.47', '2.24', '5.01', '2.01', '1.72', '2.07', '6.42', '3.86', '0.45', '3.48', '1.89', '5.75', '2.17', '1.37', '2.35', '1.18', '2.11', '1.88', '2.83', '2.99', '2.89', '3.27', '2.22', '2.14', '1.45', '1.75', '1.04', '1.77', '3.02', '2.75', '2.16', '1.9', '2.59', '2.2', '4.3', '0.93', '2.53', '2.52', '1.79', '1.3', '2.6', '1.58', '1.2', '1.56', '1.34', '1.26', '0.83', '6.21', '2.8', '1.59', '1.73', '4.33', '1.83', '0.0', '2.18', '1.98', '1.47', '0.67', '1.55', '1.91', '0.69', '0.6', '1.93', '1.64', '0.55', '2.19', '1.11', '2.29', '2.5', '0.96', '1.21', '1.12', '0.77', '1.69', '1.08', '0.79', '2.37', '2.46', '0.26', '0.75', '1.25', '2.43', '0.98', '0.74', '2.23', '0.61', '2.45', '1.41', '1.8', '3.28', '1.16', '1.99', '1.38', '1.36', '1.17', '1.19', '0.99', '1.68', '2.0', '1.33', '1.57', '1.48', '2.1', '1.27', '1.97', '0.91', '1.39', '1.96', '0.24', '1.51', '0.14', '1.29', '2.39', '1.03', '0.5', '0.58', '1.31', '2.02', '1.32', '1.01', '2.27', '2.3', '1.82', '2.78', '0.44', '0.48', '0.27', '0.21', '2.48', '0.51', '1.52', '0.04', '0.28', '1.35', '0.87', '2.13', '1.13', '1.76', '0.76', '2.12', '0.66', '1.6', '1.44', '1.43', '1.7', '0.47', '1.87', '0.86', '0.73', '1.28', '0.81', '1.09', '0.68', '1.22', '1.4', '1.02', '1.49', '1.14', '0.49', '0.9', '0.38', '1.42', '0.95', '1.62', '0.71', '1.05', '0.92', '0.33', '0.3', '1.67', '1.0', '0.89', '0.1', '0.72', '0.59', '0.56', '0.16', '0.97', '0.62', '0', '0.85', '0.94', '0.88', '0.84', '1.06', '0.2', '1.15', '0.8', '1.1', '0.7', '1.92', '0.32', '0.15', '0.53', '0.09', '1.46', '0.29', '0.22', '1.23', '0.07', '0.17', '0.54', '0.36', '0.31', '1.84', '0.52', '0.11', '0.64', '0.12', '2.05', '1.63', '0.82', '0.08', '0.57', '1.65', '0.19', '0.02', '0.43', '0.25', '1.5', '0.18', '0.39', '0.13', '1.07', '0.46', '0.41', '0.06', '0.03', '0.37', '0.05', '0.23', '0.65', '0.42', '0.34', '0.35', '0.78'], dtype=object) 

Ввод [11]:

 
# Привожу тип данных eu_sales к типу float64 
new_games['eu_sales']= new_games['eu_sales'].astype('float64') 
display(new_games['eu_sales'].dtype) 

Результат:

 dtype('float64') 

Вывод:
Данные приведены к типу float64 для оптимизации дальнейшей работы.

2.2.3 Смотрю данные о продажах в столбцах JP sales.
Ввод [12]:

 
# Смотрю все уникальные значения jp_sales 
new_games['jp_sales'].unique() 

Результат:

 
array(['3.77', '6.81', '3.79', '3.28', '10.22', '4.22', '6.5', '2.93', '4.7', '0.28', '1.93', '4.13', '7.2', '3.6', '0.24', '2.53', '0.98', '0.41', '3.54', '4.16', '6.04', '4.18', '3.84', '0.06', '0.47', '5.38', '5.32', '5.65', '1.87', '0.13', '3.12', '0.36', '0.11', '4.35', '0.65', '0.07', '0.08', '0.49', '0.3', '2.66', '2.69', '0.48', '0.38', '5.33', '1.91', '3.96', '3.1', '1.1', '1.2', '0.14', '2.54', '2.14', '0.81', '2.12', '0.44', '3.15', '1.25', '0.04', '0.0', '2.47', '2.23', '1.69', '0.01', '3.0', '0.02', '4.39', '1.98', '0.1', '3.81', '0.05', '2.49', '1.58', '3.14', '2.73', '0.66', '0.22', '3.63', '1.45', '1.31', '2.43', '0.7', '0.35', '1.4', '0.6', '2.26', '1.42', '1.28', '1.39', '0.87', '0.17', '0.94', '0.19', '0.21', '1.6', '0.16', '1.03', '0.25', '2.06', '1.49', '1.29', '0.09', '2.87', '0.03', '0.78', '0.83', '2.33', '2.02', '1.36', '1.81', '1.97', '0.91', '0.99', '0.95', '2.0', '1.01', '2.78', '2.11', '1.09', '0.2', '1.9', '1.27', '3.61', '1.57', '2.2', '1.7', '1.08', '0.15', '1.11', '0.29', '1.54', '0.12', '0.89', '4.87', '1.52', '1.32', '1.15', '4.1', '1.46', '0.46', '1.05', '1.61', '0.26', '1.38', '0.62', '0.73', '0.57', '0.31', '0.58', '1.76', '2.1', '0.9', '0.51', '0.64', '2.46', '0.23', '0.37', '0.92', '1.07', '2.62', '1.12', '0.54', '0.27', '0.59', '3.67', '0.55', '1.75', '3.44', '0.33', '2.55', '2.32', '2.79', '0.74', '3.18', '0.82', '0.77', '0.4', '2.35', '3.19', '0.8', '0.76', '3.03', '0.88', 'unknown', '0.45', '1.16', '0.34', '1.19', '1.13', '2.13', '1.96', '0.71', '1.04', '2.68', '0.68', '2.65', '0.96', '2.41', '0.52', '0.18', '1.34', '1.48', '2.34', '1.06', '1.21', '2.29', '1.63', '2.05', '2.17', '1.56', '1.35', '1.33', '0.63', '0.79', '0.75', '0.53', '1.53', '1.3', '0.39', '0.69', '0.42', '0.93', '0.56', '0.84', '0.72', '0.32', '1.71', '1.65', '0.61', '1.51', '1.5', '1.44', '1.24', '1.18', '1.37', '1.0', '1.26', '0.85', '0.43', '0.67', '1.14', '0.86', '1.17', '0.5', '1.02', '0.97'], dtype=object) 

Ввод [13]:

 
# Выполняю замену строковых значений на индекс 0
new_games['jp_sales'] = new_games['jp_sales'].str.replace('unknown','0') 

Ввод [14]:

 
# Привожу тип данных jp_sales к типу float64 
new_games['jp_sales'] = new_games['jp_sales'].astype('float64') 
display(new_games['jp_sales'].dtype) 

Результат:

 dtype('float64') 

Вывод:
Преобразование строковых значений выполнено успешно, данные приведены к типу float64 для оптимизации дальнейшей работы.

2.2.4 Работа с форматами данных User Score

Данные об оценке User Score приведены в формате строк, необходимо поменять формат на число с плавающей точкой. Смотрю уникальные значения.

Ввод [15]:

 
# Смотрю все уникальные значения user_score
display(new_games['user_score'].unique()) 

Результат:

 
array(['8', nan, '8.3', '8.5', '6.6', '8.4', '8.6', '7.7', '6.3', '7.4', '8.2', '9', '7.9', '8.1', '8.7', '7.1', '3.4', '5.3', '4.8', '3.2', '8.9', '6.4', '7.8', '7.5', '2.6', '7.2', '9.2', '7', '7.3', '4.3', '7.6', '5.7', '5', '9.1', '6.5', 'tbd', '8.8', '6.9', '9.4', '6.8', '6.1', '6.7', '5.4', '4', '4.9', '4.5', '9.3', '6.2', '4.2', '6', '3.7', '4.1', '5.8', '5.6', '5.5', '4.4', '4.6', '5.9', '3.9', '3.1', '2.9', '5.2', '3.3', '4.7', '5.1', '3.5', '2.5', '1.9', '3', '2.7', '2.2', '2', '9.5', '2.1', '3.6', '2.8', '1.8', '3.8', '0', '1.6', '9.6', '2.4', '1.7', '1.1', '0.3', '1.5', '0.7', '1.2', '2.3', '0.5', '1.3', '0.2', '0.6', '1.4', '0.9', '1', '9.7'],
dtype=object) 

Вывод:
Вижу строковое значение 'tbd', которое ниже поменяю на индекс -1 для оптимизации дальнейшей работы.
Ввод [16]:

 
# Меняю строковое значение на индекс -1 
new_games['user_score'] = new_games['user_score'].str.replace('tbd','-1') 

Ввод [17]:

 
# Выполняю преобразование типа данных на float64 
new_games['user_score'] = new_games['user_score'].astype('float64') 
display(new_games['user_score'].dtype) 
display(new_games['user_score'].unique()) 

Результат:

 
dtype('float64') 
array([ 8. , nan, 8.3, 8.5, 6.6, 8.4, 8.6, 7.7, 6.3, 7.4, 8.2, 9. , 7.9, 8.1, 8.7, 7.1, 3.4, 5.3, 4.8, 3.2, 8.9, 6.4, 7.8, 7.5, 2.6, 7.2, 9.2, 7. , 7.3, 4.3, 7.6, 5.7, 5. , 9.1, 6.5, -1. , 8.8, 6.9, 9.4, 6.8, 6.1, 6.7, 5.4, 4. , 4.9, 4.5, 9.3, 6.2, 4.2, 6. , 3.7, 4.1, 5.8, 5.6, 5.5, 4.4, 4.6, 5.9, 3.9, 3.1, 2.9, 5.2, 3.3, 4.7, 5.1, 3.5, 2.5, 1.9, 3. , 2.7, 2.2, 2. , 9.5, 2.1, 3.6, 2.8, 1.8, 3.8, 0. , 1.6, 9.6, 2.4, 1.7, 1.1, 0.3, 1.5, 0.7, 1.2, 2.3, 0.5, 1.3, 0.2, 0.6, 1.4, 0.9, 1. , 9.7]) 

Вывод:
Преобразование типа данных user_score на float64 выполнено успешно.

2.3 Работа с пропусками
2.3.1 Изучаю данные с пропущенными значениями.

Ввод [18]:

 
# Вычисляю количество пропущенных строк в датафрейме. Результат вывожу на экран. 
display(new_games.isna().sum())

Результат:

 dtype: int64 

Ввод [19]:

 
# Вычисляю количество пропущенных строк в датафрейме в процентах от общего числа строк. 
# Результат вывожу на экран. 
display (new_games.isna().sum()/ len(new_games)*100) 

Результат:

dtype: float64 

Вывод 2.3.1:
В данных наблюдаются пропуски в следующих столбцах:
• name: в 2 строках (0.01 % данных) отсутствует информация о названии игры. Однако, процент пропусков слишком низкий и ими можно принебречь.
• genre: в 2 строках (0.01 % данных) отсутствует информация о жанре игры. Возможно, пропуски в этих данных связаны с пропусками в названиях. Однако, процент пропусков слишком низкий и ими можно принебречь.
• year_of_release: в 275 строках (1,62 % данных) отсутствует информация года выхода игры. Отсутствие данных может затруднить анализ, связанный с годамы выхода игр.
• user_score: в 6804 строках (40,13 % данных) отсутствует информация об оценке игры пользователями. Отсутствие данных может затруднить анализ, связанный с оценками пользователей.
• rating: в 6871 строке (40,52 % данных) отсутствует информация о рейтинге. Отсутствие данных может затруднить анализ, связанный с рейтингом.
• critic_score: в 8714 строках (51,39 % данных) отсутствует информация об оценке игры критиками. Отсутствие данных может затруднить анализ, связанный с оценками критиков.
Вижу критически большое количество пропусков, более 40%, в столбцах critic_score, user_score, rating.

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

2.3.2 Замена пропусков

Пропуски в столбцах year_of_release, genre, name оставляю без изменений в связи с их небольшим (менее 2%) количеством.
Буду обрабатывать пропуски в critic_score, user_score, rating. В связи с большим количеством (более 40%) средним значением или медианой заменять не буду, чтобы не искажать массив данных. Заменю пропуски в трех столбцах на индекс -1. Буду учитывать этот индекс при анализе данных.

Ввод [20]:

 
# Вывожу на экран все уникальные значения critic_score, смотрю тип данных
display(new_games['critic_score'].unique()) 
# Проверяю тип данных столбца
display(new_games['critic_score'].dtype) 

Результат:

 
array([76., nan, 82., 80., 89., 58., 87., 91., 61., 97., 95., 77., 88., 83., 94., 93., 85., 86., 98., 96., 90., 84., 73., 74., 78., 92., 71., 72., 68., 62., 49., 67., 81., 66., 56., 79., 70., 59., 64., 75., 60., 63., 69., 50., 25., 42., 44., 55., 48., 57., 29., 47., 65., 54., 20., 53., 37., 38., 33., 52., 30., 32., 43., 45., 51., 40., 46., 39., 34., 35., 41., 36., 28., 31., 27., 26., 19., 23., 24., 21., 17., 22., 13.])
dtype('float64')

Ввод [21]:

 
# Выполняю замену пропусков на индекс -1 
new_games['critic_score'] = new_games['critic_score'].fillna(-1) 
# Вывожу на экран все уникальные значения после замены 
display(new_games['critic_score'].unique()) 

Результат:

 
array([76., -1., 82., 80., 89., 58., 87., 91., 61., 97., 95., 77., 88., 83., 94., 93., 85., 86., 98., 96., 90., 84., 73., 74., 78., 92., 71., 72., 68., 62., 49., 67., 81., 66., 56., 79., 70., 59., 64., 75., 60., 63., 69., 50., 25., 42., 44., 55., 48., 57., 29., 47., 65., 54., 20., 53., 37., 38., 33., 52., 30., 32., 43., 45., 51., 40., 46., 39., 34., 35., 41., 36., 28., 31., 27., 26., 19., 23., 24., 21., 17., 22., 13.]) 

Ввод [22]:

 
# Вывожу на экран все уникальные значения user_score, смотрю тип данных 
display(new_games['user_score'].unique())

# Проверяю тип данных столбца 
display(new_games['user_score'].dtype) 

Результат:

 
array([ 8. , nan, 8.3, 8.5, 6.6, 8.4, 8.6, 7.7, 6.3, 7.4, 8.2, 9. , 7.9, 8.1, 8.7, 7.1, 3.4, 5.3, 4.8, 3.2, 8.9, 6.4, 7.8, 7.5, 2.6, 7.2, 9.2, 7. , 7.3, 4.3, 7.6, 5.7, 5. , 9.1, 6.5, -1. , 8.8, 6.9, 9.4, 6.8, 6.1, 6.7, 5.4, 4. , 4.9, 4.5, 9.3, 6.2, 4.2, 6. , 3.7, 4.1, 5.8, 5.6, 5.5, 4.4, 4.6, 5.9, 3.9, 3.1, 2.9, 5.2, 3.3, 4.7, 5.1, 3.5, 2.5, 1.9, 3. , 2.7, 2.2, 2. , 9.5, 2.1, 3.6, 2.8, 1.8, 3.8, 0. , 1.6, 9.6, 2.4, 1.7, 1.1, 0.3, 1.5, 0.7, 1.2, 2.3, 0.5, 1.3, 0.2, 0.6, 1.4, 0.9, 1. , 9.7])
dtype('float64')

Ввод [23]:

 
# Выполняю замену пропусков на индекс -1 
new_games['user_score'] = new_games['user_score'].fillna(-1) 
# Вывожу на экран все уникальные значения после замены 
display(new_games['user_score'].unique()) 

Результат:

 
array([ 8. , -1. , 8.3, 8.5, 6.6, 8.4, 8.6, 7.7, 6.3, 7.4, 8.2, 9. , 7.9, 8.1, 8.7, 7.1, 3.4, 5.3, 4.8, 3.2, 8.9, 6.4, 7.8, 7.5, 2.6, 7.2, 9.2, 7. , 7.3, 4.3, 7.6, 5.7, 5. , 9.1, 6.5, 8.8, 6.9, 9.4, 6.8, 6.1, 6.7, 5.4, 4. , 4.9, 4.5, 9.3, 6.2, 4.2, 6. , 3.7, 4.1, 5.8, 5.6, 5.5, 4.4, 4.6, 5.9, 3.9, 3.1, 2.9, 5.2, 3.3, 4.7, 5.1, 3.5, 2.5, 1.9, 3. , 2.7, 2.2, 2. , 9.5, 2.1, 3.6, 2.8, 1.8, 3.8, 0. , 1.6, 9.6, 2.4, 1.7, 1.1, 0.3, 1.5, 0.7, 1.2, 2.3, 0.5, 1.3, 0.2, 0.6, 1.4, 0.9, 1. , 9.7]) 

Ввод [24]:

 
# Выполняю замену пропусков на индекс -1 
new_games['rating'] = new_games['rating'].fillna(-1) 
# Вывожу на экран все уникальные значения после замены 
display(new_games['rating'].unique()) 

Результат:

 
array(['E', -1, 'M', 'T', 'E10+', 'K-A', 'AO', 'EC', 'RP'], dtype=object) 

Вывод 2.3:
Для оптимизации работы с данными замена пропусков в столбцах critic_score, user_score на индексы -1 выполнена успешно.

2.4 Проверка на явные и неявные дубликаты в данных
2.4.1 Поиск явных дубликатов

Ввод [25]:

 
# Проверяю явные дубликаты строк во всем датафрейме, нахожу их количество методом sum 
duplicated_rows_sum = new_games.duplicated(subset = None, keep = 'first').sum() 
print(duplicated_rows_sum) 

Результат:

 241 

Вывод:
Общее количество явных дубликатов строк : 241

Ввод [26]:

 
# Сортирую датафрейм по всем столбцам 
df_sorted = new_games.sort_values(by=new_games.columns.tolist()) 
# Нахожу дубликаты 
duplicates = df_sorted[df_sorted.duplicated(keep = 'first')] 
# Вывожу на экран строки - явные дубликаты
print(duplicates) 

Результат:

Удаляю все явные дубликаты из датафрейма

Ввод [27]:

 
# Смотрю количество строк до удаления 
display(new_games.shape[0]) 

Результат:

 16956 

Ввод [28]:

 
# Сортирую датафрейм по всем столбцам 
ng_sorted = new_games.sort_values(by = list(new_games.columns)) 
# Удаляю дубликаты 
new_games_no_duplicates = ng_sorted.drop_duplicates() 

Ввод [29]:

 
# Смотрю количество строк после удаления 
display(new_games_no_duplicates.shape[0]) 

Результат:

 16715 

Ввод [30]:

 
# Вычисляю количество оставшихся строк в датафрейме в процентах от общего числа строк после удаления дубликатов. 
new_games_rows = len(new_games_no_duplicates)/ len(new_games)*100 display (round(new_games_rows, 2)) 

Результат:

 98.58 

Вывод:
Количество найденных дубликатов: 241 строка (исключая первое вхождение), все дубликаты удалены из датасета.
Количество строк: было 16956 строк, стало 16715 строк. В относительном значении удалено 1,42 % строк.

2.4.2 Неявные дубликаты

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

Ввод [31]:

 
# Смотрю уникальные значения по датам выпуска, ищу дубликаты 
years = new_games['year_of_release'] 
display(years.sort_values().unique()) 

Результат:

 <DatetimeArray> 
['1980-01-01 00:00:00', '1981-01-01 00:00:00', '1982-01-01 00:00:00', '1983-01-01 00:00:00', '1984-01-01 00:00:00', '1985-01-01 00:00:00', '1986-01-01 00:00:00', '1987-01-01 00:00:00', '1988-01-01 00:00:00', '1989-01-01 00:00:00', '1990-01-01 00:00:00', '1991-01-01 00:00:00', '1992-01-01 00:00:00', '1993-01-01 00:00:00', '1994-01-01 00:00:00', '1995-01-01 00:00:00', '1996-01-01 00:00:00', '1997-01-01 00:00:00', '1998-01-01 00:00:00', '1999-01-01 00:00:00', '2000-01-01 00:00:00', '2001-01-01 00:00:00', '2002-01-01 00:00:00', '2003-01-01 00:00:00', '2004-01-01 00:00:00', '2005-01-01 00:00:00', '2006-01-01 00:00:00', '2007-01-01 00:00:00', '2008-01-01 00:00:00', '2009-01-01 00:00:00', '2010-01-01 00:00:00', '2011-01-01 00:00:00', '2012-01-01 00:00:00', '2013-01-01 00:00:00', '2014-01-01 00:00:00', '2015-01-01 00:00:00', '2016-01-01 00:00:00', 'NaT']
Length: 38, dtype: datetime64[ns] 

Вывод:
В годах неявных дубликатов значений нет.

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

Ввод [32]:

 
# Смотрю уникальные значения по жанрам, ищу дубликаты 
genres = new_games['genre'] 
display(genres.sort_values().unique()) 

Результат:

 
array(['ACTION', 'ADVENTURE', 'Action', 'Adventure', 'FIGHTING', 'Fighting', 'MISC', 'Misc', 'PLATFORM', 'PUZZLE', 'Platform', 'Puzzle', 'RACING', 'ROLE-PLAYING', 'Racing', 'Role-Playing', 'SHOOTER', 'SIMULATION', 'SPORTS', 'STRATEGY', 'Shooter', 'Simulation', 'Sports', 'Strategy', nan], dtype=object) 

Ввод [33]:

 
# Привожу все названия к верхнему регистру 
new_games['genre']=new_games['genre'].str.upper() 
display(new_games['genre'].sort_values().unique()) 

Результат:

 
array(['ACTION', 'ADVENTURE', 'FIGHTING', 'MISC', 'PLATFORM', 'PUZZLE', 'RACING', 'ROLE-PLAYING', 'SHOOTER', 'SIMULATION', 'SPORTS', 'STRATEGY', nan], dtype=object) 

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

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

Ввод [34]:

 
# Смотрю уникальные значения по платформе, ищу дубликаты 
platformes = new_games['platform'] 
display(platformes.sort_values().unique()) 

Результат:

 
array(['2600', '3DO', '3DS', 'DC', 'DS', 'GB', 'GBA', 'GC', 'GEN', 'GG', 'N64', 'NES', 'NG', 'PC', 'PCFX', 'PS', 'PS2', 'PS3', 'PS4', 'PSP', 'PSV', 'SAT', 'SCD', 'SNES', 'TG16', 'WS', 'Wii', 'WiiU', 'X360', 'XB', 'XOne'], 
dtype=object) 

Ввод [35]:

 
# Проверяю кол-во вхождений названий платформ 
sorted_platforms = new_games['platform'] 
print(sorted_platforms.value_counts()) 

Результат:

 dtype: int64 

Вывод:
Значений неявных дубликатов в названии платформ не выявлено.

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

Ввод [36]:

 
# Смотрю уникальные значения рейтингов, ищу неявные дубликаты 
ratings = new_games['rating'] 
display(ratings.unique())

# Проверяю кол-во вхождений рейтингов 
print(ratings.value_counts()) 

Результат:

 
array(['E', -1, 'M', 'T', 'E10+', 'K-A', 'AO', 'EC', 'RP'], dtype=object)

rating
-1      6871
E       4037
T       3005
M       1587
E10+    1441
EC         8
K-A        3
RP         3
AO         1
Name: count, dtype: int64

Вывод:
Неявных дубликатов в рейтингах не обнаружено.

Вывод по пункту 2 после проведения предобработки данных:

1. В ходе предобработки данных были изменены названия столбцов (приведены к нижнему регистру, удалены пробелы).
2. По типам данных проведены изменения: года выпуска приведены к формату даты, строковые данные приведены к типу object, числовые данные приведены к типу вещественных чисел.
3. Найдены пропущенные значения, которые были изменены на индексы -1 в столбцах с оценками и с рейтингом.
Остальные пропуски оставлены без изменений.
4. Выявлены явные и неявные дубликаты, которые были обработаны. В результате предобработки данных удалены 1,42% строк.

3. Фильтрация данных

Для изучения истории продаж игр в начале XXI века, по заданию от заказчика оставляю период с 2000 по 2013 год включительно.
Сохраняю новый срез данных в отдельном датафрейме ng_actual.

Ввод [37]:

 
# Создаю фильтр по столбцу year_of_release
mask = ((new_games_no_duplicates['year_of_release']>= '2000-01-01 00:00:00') &
        (new_games_no_duplicates['year_of_release']<= '2013-01-01 00:00:00'))
         
# Применяю фильтр к новой переменной ng_actual
ng_actual = new_games_no_duplicates[mask]

# Проверю фильтрацию по годам
print(ng_actual['year_of_release'].min())
print(ng_actual['year_of_release'].max())

Результат:

 
2000-01-01 00:00:00 
2013-01-01 00:00:00 

4. Категоризация данных
4.1 Категоризация по оценкам пользователей

Провожу категоризацию данных по оценкам пользователей на категории:
- высокая оценка (от 8 до 10 включительно),
- средняя оценка (от 3 до 8, не включая правую границу интервала),
- низкая оценка (от 0 до 3, не включая правую границу интервала).

Ввод [38]:

 
# Создаю столбец 'us_score_rating' для формирования категорий оценок пользователей 
ng_actual['us_score_rating'] = pd.cut(ng_actual['user_score'], 
                               bins = [0, 2.9, 7.9, 10], 
                               labels = ["Низкая", "Средняя", "Высокая"])
                               
#Вывожу на экран первые 20 строк столбцов с оценками и рейтингом
print(ng_actual[['us_score_rating', 'user_score']].head(20))

Результат:

4.2 Категоризация по оценкам критиков

Провожу категоризацию данных по оценкам критиков на категории:
- высокая оценка (от 80 до 100 включительно),
- средняя оценка (от 30 до 80, не включая правую границу интервала),
- низкая оценка (от 0 до 30, не включая правую границу интервала).

Ввод [39]:

 
# Создаю столбец 'cr_score_rating' для формирования категорий оценок критиков 
ng_actual['cr_score_rating'] = pd.cut(ng_actual['critic_score'], 
                               bins = [0, 29.9, 79.9, 100], 
                               labels = ["Низкая", "Средняя", "Высокая"])
                               
# Вывожу на экран первые 20 строк столбцов с оценками и рейтингом
print(ng_actual[['cr_score_rating', 'critic_score']].head(20))

Результат:

4.3 Проверка результата

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

Ввод [40]:

 
# Группирую данные по категориям оценок пользователей 
score_data_low = ng_actual[ng_actual['us_score_rating']== 'Низкая']
score_data_medium = ng_actual[ng_actual['us_score_rating']== 'Средняя']
score_data_high = ng_actual[ng_actual['us_score_rating']== 'Высокая']

Ввод [41]:

 
# Считаю количество игр в каждой категории 
total_low_count = score_data_low['name'].count() 
total_medium_count =score_data_medium['name'].count() 
total_high_count =score_data_high['name'].count() 

Ввод [42]:

 
print(f'Количество игр с низкой оценкой пользователей: {total_low_count}') 
print(f'Количество игр со средней оценкой пользователей: {total_medium_count}') 
print(f'Количество игр с высокой оценкой пользователей: {total_high_count}') 

Результат:

 
Количество игр с низкой оценкой пользователей: 115 
Количество игр со средней оценкой пользователей: 4081 
Количество игр с высокой оценкой пользователей: 2286 

Ввод [43]:

 
# Группирую данные по категориям оценок критиков 
score_critic_low = ng_actual[ng_actual['cr_score_rating']== 'Низкая'] 
score_critic_medium = ng_actual[ng_actual['cr_score_rating']== 'Средняя'] 
score_critic_high = ng_actual[ng_actual['cr_score_rating']== 'Высокая'] 

Ввод [44]:

 
# Считаю количество игр в каждой категории 
cr_total_low_count = score_critic_low['name'].count() 
cr_total_medium_count =score_critic_medium['name'].count() 
cr_total_high_count =score_critic_high['name'].count() 

Ввод [45]:

 
print(f'Количество игр с низкой оценкой критиков: {cr_total_low_count}') 
print(f'Количество игр со средней оценкой критиков: {cr_total_medium_count}') 
print(f'Количество игр с высокой оценкой критиков: {cr_total_high_count}') 

Результат:

 
Количество игр с низкой оценкой критиков: 55 
Количество игр со средней оценкой критиков: 5422 
Количество игр с высокой оценкой критиков: 1692 

4.4 Выделяю ТОП-7 платформ по количеству игр, выпущенных за весь актуальный период.

Ввод [46]:

 
top_platforms = ng_actual['platform'] 
print(top_platforms.value_counts().head(7)) 

Результат:

Вывод:
Первые два места ТОП-7 платформ для игр разделили PS2 и DS.
Платформы WII, PSP, X360, PS3 уступают почти вдвое по количеству.
Замыкает семерку лидеров платформа GBA.

5. Итоговые выводы проекта

На старте работы были загружены данные из таблицы /datasets/new_games.csv.

Первоначальный объем данных: 11 столбцов и 16956 строк, представлена информация о играх с 1980 по 2016 год.

При первичном знакомстве с данными и их предобработке получила такие результаты:
В шести столбцах (name, genre, year_of_release, rating, user_score, critic_score) были обнаружены пропущенные значения.
Максимальное значение пропущенных данных в столбце critic_score — 51,39 %.

Для оптимизации работы с данными в датафрейме были произведены следующие изменения типов данных:
year_of_release: тип данных изменён с float64 на datetime64.
eu_sales, jp_sales: тип данных изменён с object на float64.
user_score тип данных изменён с object на float64.

Для дополнительной работы с данными были проделаны следующие шаги:
1) Проведена фильтрация по годам, выделен срез с 2000 по 2013 года;
2) Созданы дополнительные столбцы с категориями:
- us_score_rating с оценкам пользователей: "низкая", "средняя", "высокая";
- cr_score_rating с оценкам критиков: "низкая", "средняя", "высокая";

После среза данных по годам: с 2000 по 2013 года, было выявлено, что пользователи и критики в основном ставят среднюю оценку играм, реже - высокую и совсем редко - низкую.
Ниже приведена детальная информация по категориям.

Критики:
Количество игр с низкой оценкой критиков: 55
Количество игр со средней оценкой критиков: 5422
Количество игр с высокой оценкой критиков: 1692

Пользователи:
Количество игр с низкой оценкой пользователей: 115
Количество игр со средней оценкой пользователей: 4081
Количество игр с высокой оценкой пользователей: 2286

Самые популярные игровые платформы:
PS2 (2127 игр),
DS (2120 игр),
Wii (1275 игр),
PSP (1180 игр),
X360 (1121 игр),
PS3 (1087 игр),
GBA (811 игр).

Цель проекта:

Настроить автоматическую обработку данных о выручке и способах оплаты для финансовой и продуктовой команд.

Этапы выполнения:

1. Описание данных
2. Построение логики витрины данных
3. Запись таблицы в ClickHouse
4. Автоматизация выгрузки данных
5. Проверка результата
6. Выводы на основании данных

1. Описание данных

Таблица taxi_data.csv содержит данные об активности пользователей, состоит из полей:

· taxi_id — идентификатор водителя;
· trip_start_timestamp — время начала поездки;
· trip_end_timestamp — время окончания поездки;
· trip_seconds — длительность поездки в секундах;
· trip_miles — дистанция поездки;
· fare — стоимость поездки;
· tips — размер чаевых;
· trip_total — общая стоимость поездки: стоимость поездки + чаевые + комиссия;
· payment_type — способ оплаты.

Путь к файлу: parquet_path = «s3a://путь к файлу/taxi_data.parquet»

2. Построение логики витрины данных

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

• количество поездок — count(*),
• среднюю стоимость поездки — avg(...),
• средние чаевые — avg(...),
• суммарную выручку по каждому типу оплаты — sum(...).

Все результаты собираю в одну итоговую таблицу taxi_payment_summary.

2.1 Создание пустой таблицы для записи данных

Создаю в ClickHouse таблицу taxi_payment_summary с помощью SQL-запроса. Она будет содержать такие же столбцы, которые в дальнейшем сохраняю в датафрейме result_taxi_df:
- trips_count,
- avg_fare,
- avg_tips,
- sum_revenue.

CREATE TABLE taxi_payment_summary
(
    payment_type String PRIMARY KEY,
    trips_count Int64,
    avg_fare Float32,
    avg_tips Float32,
    sum_revenue Float32
)
ENGINE = MergeTree()

Результат: таблица появилась в базе данных.

2.2. Загрузка библиотек

#filename=taxi_spark_job.py
from pyspark.sql import SparkSession
import pyspark.sql.functions as F
import sys

2.3 Создание сессии

# Создаю Spark-сессию и при необходимости добавляю конфигурацию
spark = SparkSession.builder.appName("myAggregateTest").config("fs.s3a.endpoint", "название.net").getOrCreate()

# Указываю порт и параметры кластера ClickHouse 
jdbcPort = 4444
jdbcHostname = "some-name.net"
username = "ekaterina"  # Из параметров подключения ClickHouse
jdbcDatabase = "playground_" + username
jdbcUrl = f"jdbc:clickhouse://{jdbcHostname}:{jdbcPort}/{jdbcDatabase}?ssl=true"

2.4 Считывание данных и агрегация

# Считываю исходные данные используя метод spark.read.parquet(). 
df = spark.read.parquet(f"s3a://путь к файлу/taxi_data.parquet", inferSchema=True, header=True)

# Строю агрегат по методам оплаты
result_taxi_df = df.groupBy("payment_type").agg(
    F.count("trip_seconds").alias("trips_count"),
    F.avg("fare").alias("avg_fare"),
    F.avg("tips").alias("avg_tips"),
    F.sum("trip_total").alias("sum_revenue")
)

Результат: данные считаны и агрегированы в Spark.

3. Запись таблицы в ClickHouse

После того, как данные агрегированы, запишу таблицу в ClickHouse с помощью JDBC-драйвера. Ранее на этапе 2.1 заготовка таблицы была создана с помощью SQL-запроса.

result_taxi_df.write \
    .format("jdbc") \
    .option("url", jdbcUrl) \
    .option("user", username) \
    .option("password", "тут_указываю_пароль") \
    .option("dbtable", "taxi_payment_summary") \
    .option("createTableOptions", "ENGINE=MergeTree() ORDER BY payment_type") \
    .mode("overwrite") \
    .save()

Выводы по шагам 2 и 3:
Создана таблица — витрина для анализа информации по каждому способу оплаты. После этого выполнена настройка записи полученной таблицы в ClickHouse. Файл Spark программы сохранен на облаке под названием my_spark_job.py.

4. Автоматизация выгрузки данных

4.1 Создание DAG

Чтобы процесс был полностью автоматическим и не зависел от ручных запусков, необходимо создать и настроить DAG в Airflow. Этот DAG должен ежедневно:
· проверять наличие новых файлов с данными в S3-хранилище;
· запускать Spark-задачу;
· формировать обновлённую итоговую таблицу.

В DAG использую S3KeySensor, чтобы дождаться появления файла в S3.

# filename=taxi_dag.py

from datetime import datetime
from airflow import DAG
from airflow.sensors.s3_key_sensor import S3KeySensor
from airflow.providers.yandex.operators.dataproc import DataprocCreatePysparkJobOperator

DAG_ID = "taxi_service_analysis"

with DAG(
        DAG_ID,
        description = 'Заполнение таблицы taxi_payment_summary ',
        schedule='@daily', # ежедневно
        start_date=datetime(2025, 1, 1),
        tags=["project_taxi_aggregate"],
        catchup=False  # за прошлые даты не надо заполнять
) as dag:
    
    # Определяем переменную user внутри контекста DAG
    user = 'da_20260202_0f32a9dc74'
    
    # 1) Жду появления входного файла в S3
    wait_for_input = S3KeySensor(
        task_id='data_s3_sensor_taxi',
        poke_interval=300, # каждые 5 минут
        timeout=3600, # в течение часа
        bucket_name='da-plus-dags',
        bucket_key="название проекта/taxi_data.parquet",
        mode='poke',
        aws_conn_id='s3',
        wildcard_match=False
    )

    # 2) Запускаю PySpark-задание на кластере Dataproc
    run_pyspark = DataprocCreatePysparkJobOperator(
        name="daily_aggregate_and_load_to_table_taxi",
        task_id="daily_pyspark_job_taxi",
        cluster_id="c9q4134h5vi546h1e148",
        main_python_file_uri=f"s3a://da-plus-dags/{user}/jobs/taxi_spark_job.py"
    )

    # 3) Зависимости - ВНУТРИ блока with
    wait_for_input >> run_pyspark

4.2 Запуск DAG с помощью Airflow UI

Захожу в веб-интерфейс Airflow и запускаю созданный DAG.

4.3 Работа над ошибками

При выполнении вижу возникающую ошибку в выполнении первого процесса — сенсор не находит данных.

Возможная проблема — настройки подключения. Проверяю подключение и перезапускаю DAG.

Захожу Admin → Connections → +
· Connection Id — s3.
· Connection Type — Amazon Web Services.
· Extra — параметры подключения в формате JSON.
{
«aws_access_key_id»: «some_id_»,
«aws_secret_access_key»: «some_token»,
«region_name»: «ru-central1»,
«endpoint_url»: «https://название сервера»
}

Внизу страницы нажимаю Save, чтобы сохранить подключение. Теперь всё готово к запуску! Перехожу на главную страницу и нажимаю Run.

На этот раз задачи выполнены успешно, вижу статус running (выполнение).

На графе видно, что данные подгрузились в хранилище и сейчас выгружаются в ClickHouse. После ожидания вижу, что оба задания выполнены:

5. Проверка результата

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

--Вывод таблицы после выполнения DAG.
SELECT *
FROM taxi_payment_summary;

Результат:

Вывод:
Таблица заполнена данными, DAG отработал.

6. Выводы на основании данных

В итоге получила ежедневно обновляемую таблицу для финансовых отчётов и аналитических дашбордов.
На основании данных можно сделать выводы:

1. После агрегации данных по методам платежа вижу 8 типов платежей, в том числе Unknown, Pcard и Prcard. Вероятно, Pcard и Prcard являются одним методом, просто неверно занесены названия в базу.

2. Основная масса клиентов расплачивается наличными или картой, наличные лидируют.
Самые популярные способы оплаты по количеству поездок:

• Cash – 1,41 млн поездок (44,4% от всех).
• Credit Card – 1,11 млн поездок (34,9%).
• Остальные (Pcard, Prcard, No Charge, Unknown, Dispute) суммарно дают около 1,7% поездок.
• Way2ride – лишь 3 поездки, практически не используется.

3. Поездки с оплатой по карте приносят больше половины дохода. Это связано с более высоким средним чеком и чаевыми.
Выручка по методам оплаты:

• Credit Card – 23,16 млн (более 57% от общей выручки).
• Cash – 16,97 млн (около 42%).
• Прочие способы дают менее 1% выручки.

4. Клиенты с банковскими картами не только тратят больше на саму поездку, но и оставляют значительные чаевые. Наличные же практически не приносят чаевых. Для повышения дохода стоит стимулировать перевод наличных клиентов на безналичную оплату (например, через бонусы или напоминания о чаевых в приложении).

Средний чек:

• Самый высокий средний чек – у Credit Card (15,87).
• No Charge (13,54).
• Самый низкий – у Way2ride (3,5) и Pcard (9,83).

Чаевые:

• Лидер – Credit Card (3,48) – клиенты щедрые.
• Наличные дают крайне низкие чаевые (0,0033) – практически ноль.
• Unknown – средние чаевые (0,36).
• Pcard/Prcard/No Charge – чаевые низкие (0,12–0,26).
• Dispute – нулевые чаевые, что логично (спорные операции).
• Way2ride – высокий процент чаевых относительно среднего чека (0,79 при чеке 3,5 – это ~22% от чека), но из-за единичных поездок не влияет на общую картину.

5. Доля спорных операции (Dispute) небольшая — 0,06% от числа поездок. Возможно, она связана с качеством услуг или ошибками списания. Всего 1 842 поездки, общая выручка 27 тыс., чаевые – 0.

Цели и задачи проекта:

Восстановить связи между таблицами в БД заказчика.
Оптимизировать время выполнения запросов, применяя индексацию и партицирование.
Создать витрину данных для маркетинговой команды.

Этапы выполнения:

1. Описание базы данных
2. ER-диаграмма модели
3. Подключение к базе, план доработок
4. Внесение доработок в таблицы
5. Срочные правки от заказчика
5.1 Удаление тестового отзыва
5.2 Добавление нового отзыва
5.3 Расчет среднего количества наград
5.4 Редактирование отзыва
6. Оптимизация запросов
6.1 Изучение первоначального запроса
6.2 План оптимизации и корректировка запроса
7. Работа с индексами
7.1 Оптимизация отчета достижений по игре
7.2 Оптимизация отчета с сортировкой
8. Партиционирование по дате
8.1 Создание основной таблицы
8.2 Создание партиций для каждого года
8.3 Перенос старых данных в партиции
9. Создание витрины для аналитики
9.1 Планирование запроса для витрины
9.2 Написание запроса
9.3 Сохранение результата
9.4 Возникновение ошибки и исправление

1. Описание базы данных

База данных моделирует экосистему платформы Steam, включая пользователей, игры, достижения, друзей и отзывы.

Таблица players – игроки
Содержит информацию о зарегистрированных пользователях Steam. В ней есть поля:

· player_id — уникальный идентификатор игрока, тип данных BIGINT;
· country — страна проживания игрока, тип данных VARCHAR(50);
· created — дата регистрации в Steam, тип данных TIMESTAMP.

Таблица games — игры
Содержит сведения об играх, представленных в Steam. В ней есть поля:

· game_id — уникальный идентификатор игры, тип данных INTEGER;
· title — название игры, тип данных TEXT;
· developers — разработчики игры, тип данных TEXT;
· publishers — издатели игры, тип данных TEXT;
· genres — жанры, к которым относится игра, тип данных TEXT;
· supported_languages — поддерживаемые языки, тип данных TEXT;
· release_date — дата релиза, тип данных DATE.

Таблица achievements — достижения
Хранит список достижений, доступных в играх. В ней есть поля:

· achievement_id — уникальный идентификатор достижения, тип данных TEXT;
· game_id — игра, к которой относится достижение, тип данных BIGINT;
· title — название достижения, тип данных TEXT;
· description — описание достижения, тип данных TEXT.

Таблица history — история достижений
Регистрирует факты получения достижений игроками. В ней есть поля:

· player_id — уникальный идентификатор игрока, тип данных BIGINT;
· achievement_id — идентификатор полученного достижения, тип данных TEXT;
· date_acquired — дата и время получения достижения, тип данных TIMESTAMP.

Таблица friends — друзья
Отражает социальные связи между игроками. В ней есть поля:

· player_id — уникальный идентификатор игрока, тип данных BIGINT;
· friend — идентификатор его друга, тоже игрока, тип данных BIGINT.

Таблица private_steamids — приватные профили
Список игроков, скрывших свой профиль. В ней одно поле:

· player_id — уникальный идентификатор игрока с приватным профилем, тип данных BIGINT.

Таблица purchased_games — приобретённые игры
Отражает связь между игроками и купленными ими играми. В ней есть поля:

· player_id — уникальный идентификатор игрока, тип данных BIGINT;
· game_id — идентификатор приобретённой игры, тип данных BIGINT.

Таблица reviews — отзывы
Содержит информацию об отзывах, которые оставляют пользователи. В ней есть поля:

· review_id — уникальный идентификатор отзыва, тип данных BIGINT;
· player_id — уникальный идентификатор автора отзыва, тип данных BIGINT;
· game_id — идентификатор игры, к которой написан отзыв, тип данных BIGINT;
· review — текст отзыва, тип данных TEXT;
· helpful — количество лайков за полезность, тип данных BIGINT;
· funny — количество лайков за юмор, тип данных BIGINT;
· awards — количество наград, тип данных BIGINT;
· posted — дата публикации, тип данных date.

2. ER-диаграмма модели

3. Подключение к базе данных, план доработок

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

Результат:

4. Внесение доработок в таблицы

Теперь, когда стало понятно, какие ключи необходимо добавить, приступаю к выполнению.
Использую конструкцию вида: ADD CONSTRAINT название_первичного_ключа PRIMARY KEY (колонка для первичного ключа).

--Создаю первичные ключи для таблиц (исходя из схемы). Их нужно создать 4 штуки;
ALTER TABLE games.steam.players
ADD CONSTRAINT players_playerid_pk PRIMARY KEY (player_id); 
----
ALTER TABLE games.steam.games
ADD CONSTRAINT games_gameid_pk PRIMARY KEY (game_id); 
---
ALTER TABLE games.steam.reviews 
ADD CONSTRAINT reviews_reviewid_pk PRIMARY KEY (review_id);
---
ALTER TABLE games.steam.achievements 
ADD CONSTRAINT achievements_achievementid_pk PRIMARY KEY (achievement_id);

----Создаю внешние ключи для таблиц (исходя из схемы). Их нужно создать 9 штук;
--ADD CONSTRAINT название_ключа FOREIGN KEY (поле_ключа) REFERENCES внешняя_таблица (поле_внешней_таблицы)

ALTER TABLE games.steam.purchased_games 
ADD CONSTRAINT purchasedgamesplayer_fk FOREIGN KEY (player_id) REFERENCES games.steam.players (player_id);
--
ALTER TABLE games.steam.purchased_games 
ADD CONSTRAINT purchasedgamesgame_fk FOREIGN KEY (game_id) REFERENCES games.steam.games (game_id);
--
ALTER TABLE games.steam.reviews 
ADD CONSTRAINT reviewsplayer_fk FOREIGN KEY (player_id) REFERENCES games.steam.players (player_id),
ADD CONSTRAINT reviewsgame_fk FOREIGN KEY (game_id) REFERENCES games.steam.games (game_id);
----
ALTER TABLE games.steam.achievements 
ADD CONSTRAINT achievementsgame_fk FOREIGN KEY (game_id) REFERENCES games.steam.games (game_id);
---
ALTER TABLE games.steam.history 
ADD CONSTRAINT historyplayer_fk FOREIGN KEY (player_id) REFERENCES games.steam.players (player_id),
ADD CONSTRAINT historyachievement_fk FOREIGN KEY (achievement_id) REFERENCES games.steam.achievements (achievement_id);
---
ALTER TABLE games.steam.friends 
ADD CONSTRAINT friendsplayer_fk FOREIGN KEY (player_id) REFERENCES games.steam.players (player_id);
---
ALTER TABLE games.steam.private_steamids 
ADD CONSTRAINT privatesteamidsplayer_fk FOREIGN KEY (player_id) REFERENCES games.steam.players (player_id);
--Готовы 9 внешних ключей

Вывод:
Созданные ключи помогут избежать ошибок, улучшат производительность запросов, особенно когда базы данных начнут расти.
Структура базы данных восстановлена, данные готовы к работе.

5. Срочные правки от заказчика

5.1 Удаление тестового отзыва

В отдел поддержки заказчика поступают срочные просьбы.
Задание от менеджера:
«В таблице reviews кто-то добавил ТЕСТОВЫЙ отзыв! С ID 659351.
Его уже видят клиенты! Если кто-то заметит, будет скандал! УДАЛИТЕ ЕГО!»

--Сначала посмотрю содержание таблицы
SELECT * 
FROM games.steam.reviews
LIMIT 5;

Результат:

Вывод: вижу 6-значные номера у review_id, т.е. номер указан корректно.

--Теперь посмотрю сам отзыв до его удаления
SELECT * 
FROM games.steam.reviews
WHERE review_id = 659351;

Результат:

Вывод: вижу, что отзыв не содержательный.

--Считаю количество строк до удаления
SELECT COUNT(*)
FROM games.steam.reviews;

Результат: 22246

----Удаляю отзыв
DELETE FROM games.steam.reviews
WHERE review_id = 659351;

--Считаю количество строк после удаления
SELECT COUNT(*)
FROM games.steam.reviews;

Результат: 22245

5.2 Добавление нового отзыва

Задание от маркетолога:
«Игрок с player_id = 76561198012771369 обожает новую игру game_id = 280!
Он написал отзыв 28 апреля 2025 года: „Отличная игра!“ Лайков у него нет, зато есть целых 5 наград.
Нам нужно срочно внести это в базу для рекламы!»

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

INSERT INTO reviews (review_id, player_id, game_id, review, helpful, funny, awards, posted) 
VALUES (1185896, 76561198012771369, 280, 'Отличная игра!', 0, 0, 5, '2025-04-28');

5.3 Расчет среднего количества наград

Задание от маркетолога:
«После добавления новой строки с отзывом, посчитайте среднее количество наград пользователей.
Учитывайте только тех, которые оставили отзыв по игре Half-Life: Source.»

--Пишу запрос
SELECT ROUND(AVG(awards))
FROM games.steam.games g
INNER JOIN games.steam.reviews r ON g.game_id = r.game_id
WHERE g.title = 'Half-Life: Source';

Результат: 4

5.4 Редактирование отзыва

Пришло письмо от маркетолога и новая задача:
«Мы отправили отчёт о лучших отзывах партнёрам, а один из отзывов в базе пустой!
Нас заваливают вопросами — необходимо поправить отзыв с review_id = 647595.
Коллеги из поддержки уже выяснили, какой именно текст должен быть: «Отличная игра! Очень понравилось!»

Мне нужно исправить пустой текст отзыва в таблице reviews, записав в поле review с review_id = 647595 корректный текст.

--Исправляем отзыв
UPDATE games.steam.reviews
SET review = 'Отличная игра! Очень понравилось!'
WHERE review_id = 647595
  AND (review IS NULL OR review = '');

Результат: отзыв добавлен в таблицу.

6. Оптимизация запросов

От заказчика поступил запрос на ускорение/улучшение запроса:
«Мы каждый день выгружаем отчёт по активности игроков, он выводит всех пользователей и их покупки.
Однако запрос, который строит эту витрину, работает очень медленно... Отчёты не успевают к утру!
Можешь проверить его? Если надо, улучшай».

6.1 Изучение первоначального запроса

Вот первоначальный код запроса:

SELECT *
FROM players p
INNER JOIN (SELECT * FROM purchased_games ORDER BY 1, 2) pg ON p.player_id = pg.player_id
FULL JOIN (SELECT * FROM games ORDER BY 1, 2, 3, 4, 5) g ON pg.game_id = g.game_id
WHERE  p.player_id IS NOT NULL AND  g.title != 'NULL'
AND p.created IS NOT NULL
ORDER BY p.country, g.developers;

По техническому заданию мне нужно вывести только активных игроков — тех, кто совершил хотя бы одну покупку.
Для каждого игрока нужно видеть:
· имя игрока;
· название игры, которую он купил;
· её жанр.

Анализирую запрос через EXPLAIN ANALYZE, чтобы увидеть «узкие места» и операции, которые занимают больше всего времени.

EXPLAIN analyze
SELECT *
FROM games.steam.players p
INNER JOIN (SELECT * FROM games.steam.purchased_games ORDER BY 1, 2) pg ON p.player_id = pg.player_id
FULL JOIN (SELECT * FROM games.steam.games ORDER BY 1, 2, 3, 4, 5) g ON pg.game_id = g.game_id
WHERE  p.player_id IS NOT NULL AND  g.title != 'NULL'
AND p.created IS NOT NULL
ORDER BY p.country, g.developers;

Результат:
Sort (cost=466539.72..469078.77 rows=1015623 width=178) (actual time=7707.836..8732.667 rows=1015944.00 loops=1)
Planning Time: 0.562 ms
Execution Time: 8787.514 ms

Вывод: в EXPLAIN ANALYZE видно, что запрос выполняется медленно по трём причинам.
✓ Используется внешняя сортировка (external merge), что означает работу с диском, а не с памятью.
Это сильно замедляет запрос.
✓ Подзапросы для purchased_games и games увеличивают сложность выполнения, так как каждый из них требует дополнительной обработки данных.
✓ В запросе используется FULL JOIN, что само по себе более ресурсоёмко по сравнению с INNER JOIN или LEFT JOIN.
В случае FULL JOIN выполняются дополнительные операции для поиска всех записей из обеих таблиц.
Это увеличивает количество строк в промежуточных результатах, что видно и в плане выполнения: большое количество строк при HASH FULL JOIN и длительное время выполнения на этих этапах.

6.2 План оптимизации и корректировка запроса
Исходя из выводов выше, планирую внести изменения в запрос:
1. Вместо SELECT * выбираю запрашиваемые по ТЗ поля: p.player_id, g.title.
2. Исходный запрос содержит сортировку по полям p.country и g.developers, но по техническому заданию эти поля выводить не нужно.
3. Убираю подзапросы в INNER JOIN и FULL JOIN, т.к. сортировка в них не несет смысла. Заменю их на прямые соединения с таблицами purchased_games и games.
4. Корректирую типы присоединения, уберу FULL JOIN. INNER JOIN оставит только совпадающие строки: игрока и его покупки, лишние строки с NULL исчезнут.
5. В блоке WHERE: p.player_id IS NOT NULL – такого не может быть, т.к. первичный ключ в базе данных обязан быть заполненным (NOT NULL) и уникальным. Это базовое правило СУБД.
6. В блоке WHERE у g.title != 'NULL': тут два момента: во-первых, сравнивают строку 'NULL', а не реальный NULL-тип. Во-вторых, по бизнес-логике, если у игрока нет покупки — игра у него всё равно может быть пустой. А нам нужны только активные игроки (у которых есть покупки), а не все записи игр.
7. p.created IS NOT NULL – дата создания игрока нам не важна. По заданию важно только наличие покупок, а дата создания аккаунта роли не играет. Выходит, p.created IS NOT NULL тоже нам не нужно.
8. Вывод 5,6,7: блок WHERE можно полностью удалить из запроса.

--Корректирую запрос и проанализирую его через EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT p.player_id, g.title AS game_title, g.genres
FROM games.steam.players p
INNER JOIN games.steam.purchased_games pg ON p.player_id = pg.player_id
INNER JOIN games.steam.games g ON pg.game_id = g.game_id;

Результат:
Hash Join (cost=5192.25..37132.69 rows=1020974 width=52) (actual time=39.135..654.379 rows=1020974.00 loops=1)
Planning Time: 0.259 ms
Execution Time: 684.428 ms
Количество строк почти не изменилось, потому что удалённые условия не влияли на набор данных. Они были избыточными и не отсеивали строки.

Вывод по разделу 6:
Оптимизация запроса выполнена успешно, на что указывает результат анализа запроса:
- Время выполнения сокращено с 8787.514 мс до 684.428 мс — ускорение более чем в 12 раз.
- Planning Time уменьшен с 0.562 мс до 0.259 мс — подготовка запроса ускорена более чем вдвое.
- Тяжёлый Sort заменён на эффективный Hash Join — это ключевой фактор прироста производительности.
- Удалены условия, которые не влияли на фильтрацию строк, но замедляли выполнение запроса.
- Количество строк осталось практически неизменным (1015623 → 1020974) — изменения не нарушили логику выборки.
- Execution Time снижен с 7.7 секунд до 684 мс — запрос стал отзывчивым даже при больших объёмах данных.
- Существенно снижена стоимость запроса — с 466539.72 до 5192.25.

7. Работа с индексами

Пришло новое сообщение от руководителя маркетинга:
«Мы всё чаще строим отчёты по достижениям игроков. Но история достижений (history) и сами достижения (achievements) — огромные таблицы!
Запросы тормозят. Без ускорения дальше работать невозможно».

Во вложении вижу список часто используемых запросов:

7.1 Оптимизация отчета достижений по игре

Сейчас строят отчёты по достижениям для каждой конкретной игры:

SELECT *
FROM achievements
WHERE game_id = 12345;

Выполняю план запроса.

EXPLAIN ANALYZE
SELECT *
FROM games.steam.achievements
WHERE game_id = 12345;

Результат плана запроса до оптимизации:
Gather (cost=1000.00..36889.22 rows=63 width=75) (actual time=142.577..146.198 rows=0.00 loops=1)
Planning Time: 5.547 ms
Execution Time: 146.216 ms

Вывод: таблица achievements содержит тысячи достижений для разных игр.
Без индекса поиск по game_id заставляет базу сканировать всю таблицу, и это тормозит выполнение.

Решение: создаю индекс Hash для game_id, который будет оптимален при поиске по точному значению (game_id = 12345).
Поиск по точному значению – стандартный случай для Hash-индекса.

---Создаю индекс
CREATE INDEX idx_achievements_game_id_hash ON achievements USING HASH (game_id);

Результат плана запроса после оптимизации:
Bitmap Heap Scan on achievements (cost=4.49..247.94 rows=63 width=75) (actual time=0.023..0.023 rows=0.00 loops=1)
Planning Time: 0.284 ms
Execution Time: 0.044 ms

Выводы 7.1:
- Время выполнения сокращено с 146 мс до 0.044 мс — ускорение более чем в 3000 раз.
- Planning Time уменьшен с 5.5 мс до 0.28 мс — подготовка запроса ускорена почти в 20 раз.
- Замена тяжёлого Gather (параллельное с полным сканом) на лёгкий Bitmap Heap Scan — устранено ненужное сканирование.
- При нулевом результате старый план тратил 146 мс на сборку, новый — всего 0.044 мс.
- Запрос выполняется практически мгновенно, ресурсы не расходуются впустую.

7.2 Оптимизация отчета с сортировкой

Каждый день команда выгружает отчёт по достижениям. Обязательно — с сортировкой по player_id:

SELECT *
FROM history
order by player_id;

Результат плана запроса до оптимизации:
Sort (cost=739573.20..749681.60 rows=4043360 width=37) (actual time=1541.950..2005.585 rows=4043835.00 loops=1)
Planning Time: 3.253 ms
Execution Time: 2141.549 ms

Решение: применяю индекс B-Tree. Он помогает как в точечном поиске, так и в сортировке. При ORDER BY он особенно эффективен.

---Создаю индекс
CREATE INDEX idx_history_player_id ON games.steam.history(player_id);

Результат плана запроса после оптимизации:
Index Scan using idx_history_player_id on history (cost=0.43..211882.33 rows=4043835 width=37) (actual time=0.109..760.670 rows=4043835.00 loops=1)
Planning Time: 0.060 ms
Execution Time: 901.335 ms

Выводы 7.2:
- Время ответа сократилось с 2,14 секунды до 0,9 секунды — каждый запрос стал быстрее более чем на секунду. При массовых нагрузках это даёт колоссальную экономию.
- Устранена дорогостоящая операция Sort над 4 миллионами строк. Память и CPU больше не тратятся на бессмысленное упорядочивание данных там, где оно не нужно.
- Стоимость упала с 739 573 до 0.43, при этом первая строка возвращается почти мгновенно, без длительного этапа подготовки.
- Index Scan работает в 2,4 раза быстрее полной сортировки массива данных. Индекс idx_history_player_id приносит реальную пользу в ускорении.
- Оптимизатор тратит в 54 раза меньше времени на выбор стратегии (3.25 мс → 0.06 мс) — это особенно важно для часто выполняемых запросов.
- Сделан вклад в масштабируемость системы: при росте таблицы выигрыш во времени станет ещё заметнее.

8. Партиционирование по дате

У заказчика выявлена проблема: когда коллеги начинают выполнять запросы к таблице history, которая хранит информацию о достижениях игроков, система сильно тормозит.
Причина в том, что таблица стала слишком большой, и запросы начинают работать очень медленно.

Решение: для ускорения запросов, которые часто выполняются с фильтром по датам, решаю применить партиционирование по дате получения достижения (date_acquired).
Тип Range Partitioning подходит для данных, которые имеют логическую последовательность (в моем случае — это даты).
Решено с заказчиком, что мы будем делить таблицу по годам.

8.1 Создание основной таблицы

--Создаю таблицу history_partitioned
CREATE TABLE history_partitioned (
       player_id int8 NULL,
       achievement_id text NULL,
       date_acquired timestamp NULL,
       CONSTRAINT history_achievements_fk_p FOREIGN KEY (achievement_id) REFERENCES steam.achievements(achievement_id),
       CONSTRAINT history_players_fk_p FOREIGN KEY (player_id) REFERENCES steam.players(player_id)
)
PARTITION BY RANGE (date_acquired);

Результат: таблица создана

8.2 Создание партиций для каждого года

--Теперь, когда есть основная таблица, создаю партиции для каждого года:
CREATE TABLE history_partitioned_2020 PARTITION OF history_partitioned
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
 
CREATE TABLE history_partitioned_2021 PARTITION OF history_partitioned
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
 
CREATE TABLE history_partitioned_2022 PARTITION OF history_partitioned
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
 
CREATE TABLE history_partitioned_2023 PARTITION OF history_partitioned
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
 
CREATE TABLE history_partitioned_2024 PARTITION OF history_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Результат: таблицы по годам созданы, каждая будет содержать данные только за определённый год.

8.3 Перенос старых данных в партиции

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

--INSERT INTO history_partitioned (player_id, achievement_id, date_acquired)
SELECT player_id, achievement_id, date_acquired
FROM history

Результат: данные успешно перезаписаны в таблицы по годам.

Вывод:
Этап партиционирования значительно улучшает производительность системы и сокращает время выполнения запросов с датами.
Теперь запросы, фильтрующие по date_acquired, сканируют только соответствующую партицию, а не всю таблицу.

9. Создание витрины для аналитики

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

Согласовали с заказчиком наличие следующих полей для витрины:
· player_id — идентификатор игрока;
· country — страна игрока (если нет — указать «Не указана»);
· game_id — идентификатор игры;
· game_title — название игры;
· achievement_id — идентификатор достижения;
· date_acquired — дата получения достижения;
· review_id — идентификатор отзыва;
· review — текст отзыва;
· helpful_reviews_count — количество лайков «полезно»;
· funny_reviews_count — количество лайков «смешно»;
· awards_reviews_count — количество наград за отзыв.

Условия фильтрации:
· Только игроки, которые получили достижения в 2024 году.
· Только игроки, которые купили больше 3 игр.

9.1 Планирование запроса для витрины

Перед созданием запроса определяю план, отмечаю важные моменты, формирую составные части:
1. Основа – таблица players, вся информация будет собрана по каждому отдельному игроку.
2. Связь с покупками через INNER JOIN, чтобы попали только те, у которых есть покупки.
3. Чтобы были только игроки с тремя играми и больше: GROUP BY player_id HAVING COUNT(game_id) > 3
4. Продумываю порядок присоединений: games с achievements (по game_id), затем – с history_partitioned (по achievement_id), и после этого – с players (по player_id).
5. Между игроками и достижениями использую соединение INNER JOIN, т.к. не все игроки могли получить достижения в 2024 году.
6. Нужны только достижения за 2024 год: WHERE date_acquired BETWEEN '2024-01-01' AND '2024-12-31'.
7. Отзывы лучше привязывать по player_id + game_id, т.к. игрок мог оставить несколько отзывов по разным играм.
8. Для страны, если нет – указать «Не указана»: COALESCE(p.country, 'Не указана') AS country.

9.2 Написание запроса

Теперь пишу весь запрос, учитываю детали из предыдущего пункта.

SELECT p.player_id, 
       COALESCE(p.country, 'Не указана') AS country,
       g.game_id, 
       g.title AS game_title, 
       hp.achievement_id, 
       hp.date_acquired, 
       r.review_id, 
       r.review, 
       r.helpful AS helpful_reviews_count, 
       r.funny AS funny_reviews_count, 
       r.awards AS awards_reviews_count
FROM steam.players p
INNER JOIN steam.purchased_games pg ON p.player_id = pg.player_id
INNER JOIN steam.games g ON pg.game_id = g.game_id
INNER JOIN steam.achievements a ON g.game_id = a.game_id
INNER JOIN steam.history_partitioned hp ON hp.achievement_id = a.achievement_id and hp.player_id = p.player_id
LEFT JOIN steam.reviews r ON p.player_id = r.player_id AND g.game_id = r.game_id
WHERE hp.date_acquired BETWEEN '2024-01-01' AND '2024-12-31'
      AND p.player_id IN ( -- Подзапрос на тех игроков, которые купили
     SELECT player_id
     FROM steam.purchased_games
     GROUP BY player_id
     HAVING COUNT(game_id) > 3  -- Условие, что куплено больше 3 игр
 );

Получаю план этого запроса командой EXPLAIN ANALYZE.

Результат:
Hash Left Join (cost=102605.73..121773.72 rows=538 width=392) (actual time=1821.216..2551.383 rows=893014.00 loops=1)
Planning Time: 4.952 ms
Execution Time: 2582.422 ms

Вывод:
Hash Left Join: система соединяет несколько таблиц, используя хеш-таблицу в памяти.
Технически эффективно, но при росте данных может потреблять много ресурсов.
Сейчас это допустимый компромисс между скоростью и объёмом данных.
loops: 1 говорит о том, что запрос выполнен за один проход. Нет лишних повторных сканирований.
actual time: первые строки появились через 1,8 секунды, а полный результат через 2,6 сек. Пользователь видит данные не мгновенно.
Planning Time: система потратила 5 миллисекунд на составление плана — это отлично. «Думает» система быстро.
Execution Time: запрос выполняется за 2,6 секунды и обрабатывает почти миллион записей (893 014 строк).

9.3 Сохранение результата

Витрина готова. Теперь сохраню результат в отдельную таблицу player_activity_vitrine.

CREATE TABLE steam.player_activity_vitrine (
    player_id int,
    country varchar(50),
    game_id int,
    game_title text,
    achievement_id text,
    date_acquired timestamp,
    review_id int,
    review text,
    helpful_reviews_count int,
    funny_reviews_count int,
    awards_reviews_count int
);

Результат: создана таблица

Вставляю данные в новую таблицу.

INSERT INTO steam.player_activity_vitrine (
   player_id,
   country,
   game_id,
   game_title,
   achievement_id,
   date_acquired,
   review_id,
   review,
   helpful_reviews_count,
   funny_reviews_count,
   awards_reviews_count
)
SELECT
    p.player_id,
    coalesce(p.country, 'Не указана') as country,
    g.game_id,
    g.title AS game_title,
    hp.achievement_id,
    hp.date_acquired,
    r.review_id,    
    r.review,
    r.helpful AS helpful_reviews_count,
    r.funny AS funny_reviews_count,
    r.awards AS awards_reviews_count
FROM steam.players p
INNER JOIN steam.purchased_games pg ON p.player_id = pg.player_id
INNER JOIN steam.games g ON pg.game_id = g.game_id
INNER JOIN steam.achievements a ON g.game_id = a.game_id
INNER JOIN steam.history_partitioned hp ON hp.achievement_id = a.achievement_id and hp.player_id = p.player_id
LEFT JOIN steam.reviews r ON p.player_id = r.player_id AND g.game_id = r.game_id
WHERE hp.date_acquired between '2024-01-01' and '2024-12-31'
AND p.player_id IN (
     SELECT player_id
     FROM steam.purchased_games
     GROUP BY player_id
     HAVING COUNT(game_id) > 3  -- Условие, что куплено больше 3 игр
);

9.4 Возникновение ошибки и исправление

При выполнении кода выше возникла ошибка: SQL Error [22003]: ОШИБКА: целое вне диапазона.
Скорее всего, она была в том, что использовался тип данных даты timestamp вместо date или не хватало порядка int для больших чисел.
Исправляю это, но перед исправлением удаляю таблицу с витриной.

-- Удаляю старую таблицу (не подходят типы данных)
DROP TABLE IF EXISTS steam.player_activity_vitrine;

-- Создаю новую таблицу с правильными типами данных, меняю int на bigint и timestamp на date
CREATE TABLE steam.player_activity_vitrine (
    player_id bigint,
    country varchar(50),
    game_id bigint,
    game_title text,
    achievement_id text,
    date_acquired date,
    review_id bigint,
    review text,
    helpful_reviews_count bigint,
    funny_reviews_count bigint,
    awards_reviews_count bigint
);

Результат:
Теперь при заполнении таблицы витрины код сработал, данные добавились.
Созданная витрина будет использоваться для принятия бизнес-решений и дальнейшего анализа поведения игроков.
У маркетологов и продуктовой команды будут точные данные.

Цель проекта:

Проанализировать демографию пользователей и особенности использования самокатов. Определить возможную выгоду от распространения платной подписки на самокаты - проверить гипотезы на доступных данных.

Этапы выполнения

1. Загрузка данных
2. Предобработка данных
3. Исследовательский анализ данных
4. Объединение данных
5. Длительность поездок
6. Подсчёт выручки
7. Проверка гипотез
7.1 Длительность средненго времени поездки
7.2 Длительность среднего расстояния
7.3 Прибыльность тарифов
8. Распределения вероятностей
8.1 Расчёт выборочного среднего и стандартного отклонения
8.2 Вычисление значения функции распределения в точке (CDF)
8.3 Вероятность для интервала (CDF)
8.4 Определение критической дистанции поездок (PPF)
9. Выводы по проекту

Описание данных

Таблица с пользователями users_go.csv

• user_id — уникальный идентификатор пользователя.
• name — имя пользователя.
• age — возраст.
• city — город.
• subscription_type — тип подписки: free, ultra.

Таблица с поездками rides_go.csv

• user_id — уникальный идентификатор пользователя.
• distance — расстояние в метрах, которое пользователь проехал в текущей сессии.
• duration — продолжительность сессии в минутах, то есть время с того момента, как пользователь нажал кнопку «Начать поездку», до того, как он нажал кнопку «Завершить поездку».
• date — дата совершения поездки.

Таблица с подписками subscriptions_go.csv

• subscription_type — тип подписки.
• minute_price — стоимость одной минуты поездки по этой подписке.
• start_ride_price — стоимость начала поездки.
• subscription_fee — стоимость ежемесячного платежа.

1. Загрузка и просмотр данных

# Импортирую библиотеку pandas 

import pandas as pd

# Cчитываю и сохраню в отдельные датафреймы три CSV-файла.

df_users_go = pd.read_csv('https://путь к файлу/users_go.csv')
df_rides_go = pd.read_csv('https://путь к файлу/rides_go.csv')
df_subscriptions_go = pd.read_csv('https://путь к файлу/subscriptions_go.csv')

Вывожу первые пять строк каждого датафрейма - познакомлюсь с содержанием таблиц.

# Смотрю первые строки файлов
df_users_go.head()

Результат:

df_rides_go.head()

Результат:

df_subscriptions_go.head()

Результат:

Оцениваю объём данных.
Определяю количество строк в каждом из трёх датафреймов.

u = len(df_users_go) 
r = len(df_rides_go)
s = len(df_subscriptions_go)
print(f'{u} {r} {s}')

Результат:
1565 18068 2

Вывод:
Самый объемный файл - с поездками. На втором месте - файл с пользователями.
Таблица с подписками состоит из 2х строк - это 2 тарифа.
Названия столбцов в нужном виде, подходят для анализа. Значения столбцов соответствуют описанию.

2. Знакомство с данными и их предварительная подготовка

Углубляюсь в структуру данных, выполняю преобразование для дальнейшего анализа.

2.1 Работа с датафреймом df_rides_go

# Смотрю на пропуски и дубликаты датафрейм df_rides_go
nu1 = df_rides_go.isnull().sum()
dup1 = df_rides_go.duplicated().sum()
# Вывод результата
print(f'{nu1} {dup1}')

Результат: 0 0

print(df_rides_go.dtypes)

Результат:

user_id       int64
distance    float64
duration    float64
date         object
dtype: object

Вывод:
Пропуски и дубликаты в датафрейме не обнаружены.
Планирую делать анализ данных поездок на выявление сезонных трендов.
Формат данных признака date необходимо преобразовать из текста в дату для работы с временными данными.
Также для дальнейшей группировки по месяцам, нужно создать отдельный признак - месяц.

df_rides_go['date'] = pd.to_datetime(df_rides_go['date'])
print(df_rides_go.dtypes)

Результат:

user_id              int64
distance           float64
duration           float64
date        datetime64[ns]
dtype: object

df_rides_go['month'] = df_rides_go['date'].dt.month

df_rides_go['duration'] = round(df_rides_go['duration'],0)
df_rides_go['duration'] = df_rides_go['duration'].astype(int)
display(df_rides_go['duration'].dtypes)

Результат:
dtype('int64')

Вывод 2.1:
Выполнена проверка на пропуски и дубликаты, их нет.
Данные таблицы с поездками подготовлены для дальнейшего анализа:
- дата приведена в формат datetime64, создан новый признак - месяц;
- длительность поездки округлена до целого числа.

2.2 Работа с датафреймом df_users_go
Чтобы понимать полноту и уникальность данных пользователей, определяю количество пропусков и дубликатов.

# Поиск дублей и пропусков
duplicates = df_users_go.duplicated().sum()
nulls = df_users_go.isnull().sum()
print(f'{nulls.sum()} {duplicates}')

Результат:
0 31

Вывод:
Пропусков не обнаружено. Дубликатов пользователей найдена 31 строка.

# Удаляю дубликаты в датафрейме df_users_go
df_users_go = df_users_go.drop_duplicates(keep='first')
duplicates = df_users_go.duplicated().sum()
print(f'{duplicates}')

Результат: 0

Вывод 2.2:
Проверка на пропуски показала, что их нет.
Удалила 31 найденный дубликат из таблицы с пользователями.

3. Исследовательский анализ данных (EDA)

Изучаю и визуализирую информацию о географии и демографии сервиса.
Попробую найти закономерности в дистанциях и длительности поездок.

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

# Импортирую библиотеку matplotlib.pyplot
import matplotlib.pyplot as plt

# Изучаю количество пользователей по городам.
users_by_city_count = df_users_go['city'].value_counts()
display(users_by_city_count)

Результат:

# Посмотрю количеством пользователей для каждого типа подписки.
subscription_type_count = df_users_go['subscription_type'].value_counts()
display(subscription_type_count)

Результат:

# Построю круговую диаграмму с процентами пользователей с подпиской и без. 
subscription_type_count.plot(
    kind= 'pie',
    title='Соотношение пользователей с подпиской и без подписки',
    autopct='%.0f%%' , # для отображения процентных значений на диаграмме.
    ylabel= '' ,
    startangle = 90,
    colors= ['red','green'])
plt.show()

Результат:

# Построю гистограмму возрастов пользователей самокатов. 
n_bins = max(df_users_go['age'])-min(df_users_go['age']) # количество бинов, равное разности
df_users_go['age'].hist(figsize=(6,6), bins=n_bins)
plt.title('Возраст пользователей')
plt.xlabel('Возраст')
plt.show()

Результат:

# Рассчитаю долю несовершеннолетних (возрастом менее 18 лет) пользователей самокатов.
under_18 = len(df_users_go[df_users_go['age'] < 18])
users_under_18_ratio = under_18/len(df_users_go)*100
# Переведем результат в целое число
users_under_18_ratio = int(round(users_under_18_ratio,0))
print(f'Доля несовершеннолетних пользователей самокатов составляет {users_under_18_ratio}%.')

Результат:
Доля несовершеннолетних пользователей самокатов составляет 5%.

3.2 Исследование поездок: длительность
Длительность поездки является важной метрикой в работе сервиса:
-если средняя длительность поездок будет слишком высокой, самокаты будут быстрее выходить из строя,
-если слишком низкой, значит, клиентам что-то не нравится в сервисе.

# Расчитаю среднее значение, 25-й и 75-й процентили и стандартное отклонение длительности поездки. 
# Для расчёта стандартного отклонения воспользуюсь методом std().
duration_mean = int(round(df_rides_go['duration'].mean(),0))
duration_std = int(round(df_rides_go['duration'].std(),0))

duration_pct25 = int(df_rides_go['duration'].quantile(0.25))
duration_pct75 = int(df_rides_go['duration'].quantile(0.75))

print(f'Средняя длительность поездки {duration_mean} минут со стандартным отклонением {duration_std}.
\nОсновная часть поездок занимает от {duration_pct25} до {duration_pct75} минут.')

Результат:
Средняя длительность поездки 18 минут со стандартным отклонением 6.
Основная часть поездок занимает от 14 до 22 минут.

4. Объединение данных

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

# Объединяю данные о пользователях и поездках
df = df_users_go.merge(df_rides_go, on='user_id', how ='left')

# Присоединяю информацию о подписках
df = df.merge(df_subscriptions_go, on='subscription_type', how = 'left')

4.1 Характеристики объединённого датафрейма

# Вывожу первые строки датафрейма
display(df.head())
# Вывожу количество строк и столбцов в объединённом датафрейме
n_rows = len(df)
n_cols = df.shape[1]
print(f'В полученном датафрейме {n_rows} строк и {n_cols} столбцов.')

Результат:

В полученном датафрейме 18068 строк и 12 столбцов.

4.2 Отделение пользователей с подпиской и без

mask1 = df['subscription_type'] =='ultra'
df_ultra = df[mask1]
df_free = df[~mask1]

Вывод 4:
Созданы 3 таблицы: df, df_ultra, df_free. Общая таблица объединяет всю доступную информацию о сервисе.
Две отдельные таблицы - подписчики и пользователи бесплатного тарифа.

5. Длительность поездок

В этом разделе провожу анализ длительности поездок.
Строю гистограмму длительности поездок для обоих групп - использую датафреймы df_ultra и df_free.
Дополнительно рассчитаю среднюю длительность поездки для пользователей с подпиской и без.

# Гистограмма длительности поездки для пользователей с подпиской и без
plt.figure(figsize=(15, 5))
df_free['duration'].hist(label='free', bins = 30)
df_ultra['duration'].hist(label='ultra', bins = 30)
plt.xlabel('Длительность поездки, мин.')
plt.title('Гистограмма распределения длительности поездок')
plt.legend()
plt.show()

Результат:

# Расчет и вывод на экран средней длительности поездки для пользователей с подпиской и без
mean_duration_free = int(round(df_free['duration'].mean(),0))
mean_duration_ultra = int(round(df_ultra['duration'].mean(),0))
print(f'Средняя длительность поездки для пользователей без подписки {mean_duration_free} мин, а для пользователей с подпиской {mean_duration_ultra} мин')

Результат:
Средняя длительность поездки для пользователей без подписки 17 мин, а для пользователей с подпиской 19 мин.
На графике видно, что профили длительности поездок у пользователей с разной подпиской схожи.
У пользователей без платной подписки больше разброс по времени поездок, чем у пользователей с платной подпиской.

6. Подсчёт выручки

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

# Группировка в датафрейме df_gp без использования групповых индексов (опция as_index=False)
df_gp = df.groupby(['user_id', 'name', 'subscription_type', 'month'],as_index=False )

df_agg = df_gp.agg(
    total_distance=('distance', 'sum'),
    total_duration=('duration', 'sum') ,
    rides_count=('duration', 'count'),
    subscription_type= ('subscription_type', 'first'),
    minute_price= ('minute_price', 'first'),
    start_ride_price= ('start_ride_price', 'first'),
    subscription_fee= ('subscription_fee', 'first')
)

6.2 Функция для подсчёта выручки
Создаю функцию calculate_monthly_revenue(row) для расчёта месячной выручки по формуле:
monthly_revenue = start_ride_price * rides_count — выручка от начала каждой поездки.
+ minute_price * total_duration — выручка за время использования.
+ subscription_fee — фиксированная выручка от подписок.

def calculate_monthly_revenue(row):
    ride_revenue = row['start_ride_price'] * row['rides_count']
    time_revenue = row['minute_price'] * row['total_duration']
    subscription_rev = row['subscription_fee']
    
    monthly_revenue = ride_revenue + time_revenue + subscription_rev
    
    return monthly_revenue

6.3 Создание столбца с месячной выручкой на пользователя
Создаю новый столбец с месячной выручкой на пользователя monthly_revenue.
Применяю функцию calculate_monthly_revenue(row) к каждой строке агрегированного датафрейма df_agg.

# Применяю функцию к каждой строке датафрейма и создаю новый столбец
df_agg['monthly_revenue'] = df_agg.apply(calculate_monthly_revenue, axis=1)

6.4 Поиск пользователя с максимальной выручкой
Теперь исследую полученные значения выручки.
Найду пользователя с максимальной суммарной выручкой за весь период наблюдения.

# Найду пользователя с максимальной суммарной выручкой
user_revenue = df_agg.groupby(['user_id', 'name'])['monthly_revenue'].sum().reset_index()
top_user = user_revenue.loc[user_revenue['monthly_revenue'].idxmax()]

# Вывожу помесячную статистику для этого пользователя
top_user_id = top_user['user_id']
monthly_stats = df_agg[df_agg['user_id'] 
                == top_user_id][['user_id', 'name', 'month', 'rides_count', 'monthly_revenue']]

# Вывод результатов
print(monthly_stats)

Результат:

Вывод 6.4:
Пользователь с максимальной суммарной выручкой – user_id = 1236, имя Александр.
Общая выручка за год – 4 926 условных единиц.
Общее количество поездок – 27.
Помесячная динамика неравномерна:
- пик выручки в марте (762 при 5 поездках),
- минимум выручки в сентябре (122 при 1 поездке).
Наблюдается слабая прямая связь между числом поездок и выручкой:
в феврале 3 поездки дали 614,
в октябре 3 поездки дали 430,
Это указывает на разную стоимость или протяжённость поездок.
Средняя стоимость за 1 поездку: от 114 до 290 руб.

7. Проверка гипотез

В этом блоке выполняю проверку трех гипотез на доступных данных и отвечаю на вопросы бизнеса:
1. Тратят ли пользователи с подпиской больше времени на поездки?
2. Расстояние, которое проезжают пользователи с подпиской за одну поездку, меньше 3130 метров?
3. Выручка от пользователей с подпиской выше, чем выручка от пользователей без подписки?

# Импорт библиотеки scipy
import scipy.stats as st

Пишу вспомогательную функцию для интерпретации результатов.
Функция будет интерпретировать результаты статистического теста на основе p-value и заданного уровня значимости.
Функция позволит решать, следует ли принять альтернативную гипотезу или сохранить нулевую гипотезу.

def print_stattest_results(p_value:float, alpha:float = 0.05):
    if (p_value < alpha):
        print(f'Полученное значение {p_value=} меньше критического уровня {alpha=}. Принимаем альтернативную гипотезу.')
    else:
        print(f'Полученное значение {p_value=} больше критического уровня {alpha=}. Опровергнуть нулевую гипотезу нельзя.')

# Для проверки вызываю функцию для p_value = 0.0001 и p_value = 0.1.
print_stattest_results(p_value=0.0001, alpha = 0.05)
print_stattest_results(p_value=0.1, alpha = 0.05)

Результат проверки:
Полученное значение p_value=0.0001 меньше критического уровня alpha=0.05. Принимаем альтернативную гипотезу.
Полученное значение p_value=0.1 больше критического уровня alpha=0.05. Опровергнуть нулевую гипотезу нельзя.

7.1 Длительность для пользователей с подпиской и без
Важно понять, тратят ли пользователи с подпиской больше времени на поездки?
Сформулирую нулевую и альтернативную гипотезы:

Нулевая гипотеза (Н0): Среднее время поездки у пользователей с подпиской и без подписки одинаковое.
Альтернативная гипотеза (Н1): Среднее время поездки у пользователей с подпиской больше, чем у пользователей без подписки.

Чтобы проверить эту гипотезу, использую неагрегированные данные из датафреймов df_ultra и df_free.
Рассчитаю значение p_value для выбранной гипотезы, использую функции модуля scipy.stats и односторонний t-тест.

# Выполнение одностороннего t-теста
results = st.ttest_ind(ultra_duration, free_duration, alternative='greater')

# Извлечение p-value из результатов t-теста
p_value = results.pvalue

# Вызов функции с рассчитанным значением p-value
print_stattest_results(p_value)

Результат теста:
Полученное значение p_value=3.1600689435611813e-35 меньше критического уровня alpha=0.05.
Принимаем альтернативную гипотезу.

# Также дополнительно рассчитаю среднюю длительность поездки для тарифов ultra и free.
ultra_duration = df_ultra['duration']
free_duration = df_free['duration']

# Средняя длительност поездки для каждой группы
ultra_mean_duration = round(ultra_duration.mean(),2)
free_mean_duration = round(free_duration.mean(),2)

print(f'Средняя длительность поездки тарифа Ultra {ultra_mean_duration}')
print(f'Средняя длительность поездки тарифа Free {free_mean_duration}')

Результат:
Средняя длительность поездки тарифа Ultra 18.55
Средняя длительность поездки тарифа Free 17.39

Вывод 7.1:
Оказалось, что пользователи с тарифом Ultra активнее, приносят больше времени использования сервиса. Разница средненго времени на пользователя между тарифами составляет около 1,16 минуты на одну поездку.
Статистически значимое различие (p‑value < 0.05) говорит о том, что связь между наличием подписки и длительностью поездки не случайна.
Бизнесу стоит рассматривать тариф Ultra как инструмент удержания наиболее активных клиентов и делать акцент на преимуществах для длительных поездок. Например, можно предложить пользователям: бесплатную пересадку, увеличенный лимит времени и приоритетную поддержку.
В дальнейшем следует проанализировать, как изменение условий подписки (цена, включённые минуты) влияет на среднюю длительность поездки.
Возможно, есть потенциал для дальнейшего роста этого показателя.
Следует изучить поведение пользователей Free, которые совершают короткие поездки ниже среднего и попробовать предложить им специальные бонусы за переход. Например, временно увеличенный лимит или скидку на первую подписку.

7.2 Длительность поездки: больше или меньше критического значения
Проанализирую вторую важную продуктовую гипотезу.
Расстояние одной поездки в 3130 метров — оптимальное с точки зрения износа самоката.
Можно ли сказать, что расстояние, которое проезжают пользователи с подпиской за одну поездку, меньше 3130 метров?

Сформулирую нулевую и альтернативную гипотезы:
Нулевая гипотеза (Н0): Средняя дистанция поездки у пользователей с подпиской равна 3130 м.
Альтернативная гипотеза (Н1): Средняя дистанция поездки у пользователей с подпиской больше 3130 м.

Чтобы проверить эту гипотезу использую неагрегированные данные из датафрейма df_ultra.
А именно: данные о дистанции каждой поездки distance.

Рассчитаю значение p_value для выбранной гипотезы, использую функции модуля scipy.stats и односторонний t-тест.
В качестве результата вызову написанную функцию print_stattest_results(p_value, alpha), передав ей рассчитанное значение p_value.

null_hypothesis = 3130
ultra_distance = df_ultra['distance']

# Провожу t-тест (односторонний: alternative='greater'- проверяю, что среднее Ultra строго больше Free.)
results = st.ttest_1samp(ultra_distance, null_hypothesis, alternative='greater')

# Извлечение p-value из результатов t-теста
p_value = results.pvalue

print_stattest_results(p_value, alpha=0.05)

Результат теста:
Полученное значение p_value=0.9195368847849785 больше критического уровня alpha=0.05.
Опровергнуть нулевую гипотезу нельзя.
Тест позволил проверить равенство выборочного среднего определенному значению (3130 метров).
Есть основание утверждать, что подписчики проезжают расстояние в пределах заданного.

Вывод 7.2:
Проверка среднего расстояния показала, что износ самокатов от пользователей Ultra остаётся в пределах плановых значений. Бизнес может не опасаться, что подписка провоцирует чрезмерно длинные поездки, которые ускоряют амортизацию парка. Следовательно, нет необходимости вводить ограничения дистанции для подписчиков Ultra.
Для контроля износа достаточно следить за самыми длинными поездками (95-й перцентиль). Среднее значение в норме, но отдельные пользователи могут совершать очень длинные поездки.

Бизнесу имеет смысл анализировать распределение дистанций и при необходимости вводить мягкие ограничения. Например, дополнительную плату за превышение 5 км. Такая мера не затрагивает большинство подписчиков.

7.3 Прибыль от пользователей с подпиской и без
Проверяю третью гипотезу о том, что выручка от пользователей с подпиской выше, чем выручка от пользователей без подписки.

Сформулирую нулевую и альтернативную гипотезы:
Нулевая гипотеза (Н0): Средняя месячная выручка у пользователей с подпиской и без подписки одинаковая.
Альтернативная гипотеза (Н1): Средняя месячная выручка у пользователей с подпиской выше, чем у пользователей без подписки.

Чтобы проверить эту гипотезу использую агрегированные данные из датафрейма df_agg. А именно: данные о месячной выручке от каждого пользователя - monthly_revenue.

Рассчитаю значение p_value для выбранной гипотезы, использую функции модуля scipy.stats и односторонний t-тест.
Дополнительно рассчитаю среднюю выручку для тарифов ultra и free.

# Смотрю первые строки датафрейма
display(df_agg.head())

Результат:

# Делю на 2 группы: нахожу все строки выручки в зависимости от тарифа, использую общий датафрейм df_agg
revenue_ultra = df_agg.loc[df_agg['subscription_type'] == 'ultra', 'monthly_revenue']
revenue_free = df_agg.loc[df_agg['subscription_type'] == 'free', 'monthly_revenue']

# Провожу t-тест (односторонний: alternative='greater'- проверяю, что среднее Ultra строго больше Free.)
results = st.ttest_ind(revenue_ultra, revenue_free, alternative='greater')
p_value = results.pvalue
print_stattest_results(p_value, alpha=0.05)

# Считаю средние значения выручки по тарифам
mean_revenue_ultra = round(revenue_ultra.mean())
mean_revenue_free = round(revenue_free.mean())

print(f'Средняя выручка подписчиков Ultra {mean_revenue_ultra} руб.')
print(f'Средняя выручка подписчиков Free {mean_revenue_free} руб.')

Результат:
Полученное значение p_value=1.7274069878387966e-37 меньше критического уровня alpha=0.05.
Принимаем альтернативную гипотезу.
Средняя выручка подписчиков Ultra 359 руб
Средняя выручка подписчиков Free 322 руб

Вывод 7.3:
Подписчики Ultra приносят больше выручки. Средняя месячная выручка пользователей с подпиской Ultra составляет 359 рублей, а у пользователей Free — 322 рубля. Разница средней месячной выручки составляет 37 рублей, т.е. на 11,5% больше у подписчиков Ultra.
Различие между группами статистически значимо. Это означает, что вероятность случайно получить такое различие при условии равенства средних практически равна нулю. Следовательно, подписка Ultra действительно ассоциируется с более высокой выручкой, несмотря на фиксированную плату 199 руб в месяц.

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

8. Распределения вероятностей

В компании возникла идея предлагать дополнительную скидку подписчикам, совершающим длительные поездки продолжительностью более 30 минут. Мне необходимо оценить долю таких поездок.
Ранее я построила гистограмму распределения длительности поездок для выборки. Однако эти данные охватывают лишь часть пользователей всех самокатов, а теперь интересуют значения для всей генеральной совокупности.
Учитывая, что у меня нет доступа ко всем данным о поездках, решено смоделировать длительность с помощью нормального распределения. Использую в качестве параметров выборочное среднее и стандартное отклонение из доступных данных о поездках.

8.1 Расчёт выборочного среднего и стандартного отклонения
Расчитаю среднюю длительность поездки и сохраню в переменную mu. Вычислю стандартное отклонение длительности duration и сохраню в переменную sigma.
Задаю значение переменной target_time, равное 30. Эта переменная будет использоваться для последующего вычисления вероятности.

# Импорт библиотеки
import numpy as np

# Вычисляю среднее значение
mu = df_ultra['duration'].mean()

# Вычисляю стандартное отклонение
sigma = df_ultra['duration'].std()

# Задаю целевое время
target_time = 30

# Вывод результата
print(f'Средняя длительность поездки {round(mu, 1)}, стандартное отклонение {round(sigma)}.')

Результат:
Средняя длительность поездки 18.5, стандартное отклонение 6.

Вывод 8.1:
Средняя длительность поездки — 18,5 минуты, стандартное отклонение — 6 минут. Это означает, что большинство поездок укладывается в интервал примерно от 12,5 до 24,5 минуты. Целевое время в 30 минут заметно выше среднего и встречается реже.

8.2 Вычисление значения функции распределения в точке (CDF)
Если вычислить значение функции распределения в точке, это позволит узнать вероятность того, что случайная величина примет значение меньше заданного либо равное ему. Использую функцию norm() из библиотеки Scipy для создания нормального распределения с параметрами mu и sigma.
Применю метод cdf() к целевому времени target_time для получения вероятности, что случайная величина будет меньше target_time или равна ему. Полученное значение сохраню в переменную prob.

from scipy.stats import norm

# Вычисляю вероятность того, что случайная величина будет меньше указанного значения или равна ему
duration_norm_dist  = st.norm(mu, sigma)
prob = round(1 - st.norm(mu, sigma).cdf(target_time),3) # Использую CDF для нахождения накопленной вероятности

print(f'Вероятность длительности поездки более 30 минут {prob}')

Результат:
Вероятность длительности поездки более 30 минут 0.02

Вывод 8.2:
Вероятность того, что длительность поездки превысит 30 минут, составляет всего 2%. Абсолютное большинство поездок (98%) укладываются в 30 минут.
Это говорит о том, люди редко нуждаются в длительных поездках. Длительные поездки (свыше 30 минут) создают большую нагрузку на аккумулятор, мотор и механические части самоката.
Дополнительный износ от «долгих» поездок незначителен (2%) и может не требовать специальных мер. Рекомендую ввести плату за каждую минуту сверх 30 минут, что создаст дополнительный доход без ухудшения опыта для большинства.
Можно смело предложить тариф Ultra с увеличенным лимитом времени для перехода с тарифа Free. Это будет востребовано 2% случаев, но может стать решающим фактором при выборе подписки.

8.3 Вероятность для интервала (CDF)
Коллеги посчитали, что процент пользователей, для которых будет показана скидка, недостаточно большой. Она вряд ли поможет в увеличении лояльности клиентов.
Дополнительно меня просят проверить, какой процент пользователей совершает поездки в интервале от 20 до 30 минут. Возможно, именно для них стоит провести промоакцию?

Создам переменные low и high, указывающие на начало (20) и конец (30) интересующего временного интервала. Использую кумулятивную функцию распределения (CDF) для объекта duration_norm_dist.
Это позволит мне вычислить вероятность достижения верхней границы (high) и нижней границы (low).

# Определяю границы интервала
low = 20
high = 30

# Вычисляем вероятность попадания в интервал
prob_high = duration_norm_dist.cdf(high)  # P(X ≤ 30)
prob_low = duration_norm_dist.cdf(low)   # P(X ≤ 20)

# Вероятность попадания в интервал [20, 30]
prob_interval = prob_high - prob_low

prob_interval = round(prob_interval, 3)

# Вывожу результат
print(f'Вероятность того, что пользователь совершит поездку длительностью от {low} до {high} минут: {prob_interval}')

Результат:
Вероятность того, что пользователь совершит поездку длительностью от 20 до 30 минут: 0.377

Вывод 8.3:
Процент пользователей, совершающих поездки в интервале от 20 до 30 минут, составляет 37,7%. Вполне возможно, что промоакция на такой выборке поможет вырастить лояльность к продукту. Промоакция охватит более трети всех поездок. При этом стоимость привлечения одного лояльного пользователя окажется невысокой, так как акция точечная.
Если отдельно проанализировать долю поездок 20–30 минут у пользователей Ultra и Free, можно понять, кому такая акция нужнее. В случае, если у Free эта доля выше, то промоакция может стать мостиком для перевода их на подписку. Например, «получите больше минут без переплаты с тарифом Ultra».
Зная, что более трети поездок длятся 20–30 минут, можно оптимизировать размещение станций замены батарей и график обслуживания. Самокаты, используемые в таких поездках, разряжаются не полностью, что позволяет увеличить количество поездок на одной зарядке.

8.4 Определение критической дистанции поездок (PPF)
Длительные поездки могут негативно сказываться на сроке службы самоката. В связи с этим принято решение установить критическую дистанцию, превышение которой будет сопровождаться дополнительной платой. Для этого необходимо определить расстояние, которое превышается только в 10% поездок (90-й процентиль).

Моя задача — смоделировать распределение длительности поездок, предполагая, что оно подчиняется нормальному закону. Нужно рассчитать критическую дистанцию, ниже которой находится 90% всех поездок.

Рассчитаю среднюю дистанцию поездки для всех пользователей из датафрейма df (с подпиской и без) и сохраню в переменную mu. Вычислю стандартное отклонение дистанции поездки distance и сохраните в переменную sigma.
Задам значение переменной target_prob, равное 0.90. Эта переменная будет использоваться для вычисления критической дистанции. Создам объект нормального распределения distance_norm с заданными значениями mu и sigma. Применю к созданному нормальному распределению distance_norm метод ppf() и в качестве аргумента передам целевую вероятность target_prob. Полученное значение сохранию в переменную critical_distance.

# Вычисляю среднее значение
mu = df['distance'].mean()

# Вычисляю стандартное отклонение
sigma = df['distance'].std()

# Вероятность, для которой хочу найти значение (90% случаев)
target_prob = 0.90

# Создаю объект нормального распределения
distance_norm = st.norm(loc=mu, scale=sigma)

# Рассчитываю критическую дистанцию для заданного процентиля поездок
critical_distance = distance_norm.ppf(target_prob)

print(f'{100 * target_prob} % поездок имеют дистанцию ниже критического значения {critical_distance:.2f} М.')

Результат:
90.0 % поездок имеют дистанцию ниже критического значения 4187.97 М.

Вывод 8.4:
Критическая дистанция 4,2 км — это объективный 90-й процентиль всех поездок. Введение дополнительных сборов за длинные поездки способно снизить эксплуатационные издержки и повысит прибыльность.
Мера дополнительного сбора позволит:
- минимализировать риск для лояльности (затронет лишь 10% поездок),
- защитить парк самокатов от преждевременного износа,
- создать дополнительный доход без значительного ухудшения пользовательского опыта.
Такой подход экономически обоснован и не требует изменения поведения большинства клиентов.

9. Выводы по проекту

1. Демография и география
5% пользователей — несовершеннолетние (до 18 лет). Возможно, требуется проверка соответствия правилам сервиса или родительский контроль. Города с наибольшим числом пользователей: Пятигорск, Екатеринбург, Ростов-на-Дону. Логистику и рекламные кампании стоит адаптировать под региональные особенности.
2. Пользователи с подпиской Ultra - ценная аудитория
Средняя длительность поездки у подписчиков выше (18,6 мин против 17,4 мин у Free). Средняя месячная выручка от подписчика Ultra на 11,5% больше, чем от пользователя Free. Средняя дистанция поездки у Ultra статистически не превышает критическое значение 3130 м (p‑value = 0,92).
Необходимо продолжать активно продвигать тариф Ultra среди пользователей Free (акции, бесплатный период, скидка на подписку).
Нужно отслеживать длинные поездки (95-й перцентиль) и при необходимости ввести доплату за поездки свыше 4,2 км (10%).

Описание проекта 10