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

Получение из базы данных маркетплейса Всё.техника данных о результатах теста gaming_laptops_test, полученных 21 октября 2020 года на следующий день после завершения A/B теста по проверке гипотезы о том, что выделение игровых ноутбуков приведет к увеличению конверсии на 100%.

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

Используемые таблицы из БД проекта:

sessions_project_test — таблица с данными о сессиях пользователей по результатам теста;
purchases_project_test — таблица с данными о покупках пользователей по результатам теста;

Таблица sessions_project_test:

user_id — Уникальный идентификатор пользователя;
session_id — Уникальный идентификатор сессии в приложении;
session_date — Дата сессии;
session_start_ts — Дата и время начала сессии;
install_date — Дата установки приложения;
session_number — Порядковый номер сессии для конкретного пользователя;
registration_flag — Флаг зарегистрированного пользователя;
page_counter — Количество просмотренных страниц во время сессии;
region — Регион пользователя;
device — Тип устройства пользователя;
test_name — Название теста;
test_group — Тестовая группа.

Таблица purchases_project_test:

user_id — Уникальный идентификатор пользователя;
session_id — Уникальный идентификатор сессии в приложении;
purchases_date — Дата покупки;
purchases_ts — Дата и время покупки;
purchases_number — Порядковый номер покупки для конкретного пользователя;
product_id — Уникальный идентификатор купленного товара;
category — Категория товара;
price — Цена товара;
test_name — Название теста;
test_group — Тестовая группа.

Содержание¶

Распределение участников теста
Конверсия и выручка

1. Распределение участников теста ¶

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

WITH

-- Формирование профиля пользователя
profiles AS (
    SELECT DISTINCT user_id,
           MIN(install_date) OVER (PARTITION BY user_id) AS install_date,
           MAX(registration_flag) OVER (PARTITION BY user_id) AS registration_flag,
           FIRST_VALUE(region) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS first_region,
           FIRST_VALUE(device) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS first_device,
           FIRST_VALUE(test_name) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS test_name,
           FIRST_VALUE(test_group) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS test_group
    FROM sessions_project_test
    WHERE install_date BETWEEN '2020-10-14' AND '2020-10-20'
)

-- Подсчет количества пользоваетелей в группах
SELECT first_region, 
       first_device, 
       test_name, 
       test_group, 
       COUNT(user_id) AS new_dau
FROM profiles
GROUP BY first_region, first_device, test_name, test_group;

first_region	first_device	test_name	test_group	new_dau
MENA	Android	gaming_laptops_test	current_groups	2770
EU	PC	gaming_laptops_test	new_groups	606
MENA	Mac	gaming_laptops_test	new_groups	590
CIS	iPhone	gaming_laptops_test	new_groups	1307
EU	iPhone	gaming_laptops_test	current_groups	477
EU	iPhone	gaming_laptops_test	new_groups	480
EU	Mac	gaming_laptops_test	new_groups	224
MENA	Android	gaming_laptops_test	new_groups	2650
MENA	PC	gaming_laptops_test	current_groups	1522
CIS	Mac	gaming_laptops_test	new_groups	667
CIS	PC	gaming_laptops_test	new_groups	1687
CIS	iPhone	gaming_laptops_test	current_groups	1321
MENA	Mac	gaming_laptops_test	current_groups	600
CIS	PC	gaming_laptops_test	current_groups	1715
EU	PC	gaming_laptops_test	current_groups	571
CIS	Mac	gaming_laptops_test	current_groups	676
CIS	Android	gaming_laptops_test	current_groups	3109
MENA	iPhone	gaming_laptops_test	new_groups	1226
EU	Android	gaming_laptops_test	current_groups	1059
MENA	PC	gaming_laptops_test	new_groups	1508
EU	Android	gaming_laptops_test	new_groups	1026
EU	Mac	gaming_laptops_test	current_groups	224
CIS	Android	gaming_laptops_test	new_groups	3107
MENA	iPhone	gaming_laptops_test	current_groups	1212

2. Конверсия и выручка ¶

Формирование таблицы результатов теста. Поиск для каждого пользователя его тестовой группы, число транзакций в категории компьютерной техники (computer_equipments) и суммарные траты в этой категории (выручку магазина за покупки, совершённые пользователем в категории компьютерной техники).

WITH

-- Сформируем профиль пользователя
profiles AS (
    SELECT DISTINCT user_id,
           MIN(install_date) OVER (PARTITION BY user_id) AS install_date,
           MAX(registration_flag) OVER (PARTITION BY user_id) AS registration_flag,
           FIRST_VALUE(region) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS first_region,
           FIRST_VALUE(device) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS first_device,
           FIRST_VALUE(test_name) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS test_name,
           FIRST_VALUE(test_group) OVER (PARTITION BY user_id ORDER BY session_start_ts) AS test_group
    FROM sessions_project_test
    WHERE install_date BETWEEN '2020-10-14' AND '2020-10-20'
),

-- Получим данные о покупках
orders AS (
    SELECT user_id,
           COUNT(purchases_number) AS transactions,
           SUM(price) AS revenue
    FROM purchases_project_test
    WHERE category = 'computer_equipments'
    GROUP BY user_id
)

-- Объединим всё в единый набор данных
SELECT p.user_id,
       p.install_date,
       p.test_group,
       o.transactions,
       o.revenue
FROM profiles p
LEFT JOIN orders o ON o.user_id = p.user_id
WHERE p.test_name = 'gaming_laptops_test';

user_id	install_date	test_group	transactions	revenue
477F6CB9D864ECCF	2020-10-18	new_groups	nan
897B3144E7739807	2020-10-15	new_groups	nan
5640981964AC97E6	2020-10-18	current_groups	nan
5C4943395904AD3E	2020-10-17	new_groups	3	2385
…	…	…	…	…