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

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

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

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

sessions_project_test_part — таблица с данными по сессиям пользователей в процессе проведения теста.

Таблица sessions_project_test_part:

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

Содержание¶

Распределение участников теста
Пересечение групп теста

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

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

WITH

-- Сформируем профиль пользователя
profiles AS (
    SELECT
           DISTINCT (user_id) AS user_id,
           FIRST_VALUE(install_date) OVER (PARTITION BY user_id ORDER BY session_start_ts) 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_part
    WHERE install_date BETWEEN '14-10-2020' AND '21-10-2020'
)

-- Распределение участников по датам, регионам, устройствам, тестовым группам
SELECT install_date,
       first_region,
       first_device,
       test_name,
       test_group,
       COUNT(DISTINCT user_id) AS new_dau
FROM profiles
GROUP BY install_date, first_region, first_device, test_name, test_group;

nstall_date	first_region	first_device	test_name	test_group	new_dau
2020-10-14	CIS	Android	gaming_laptops_test	current_groups	309
2020-10-14	CIS	Android	gaming_laptops_test	new_groups	297
2020-10-14	CIS	iPhone	gaming_laptops_test	current_groups	110
2020-10-14	CIS	iPhone	gaming_laptops_test	new_groups	120
2020-10-14	CIS	Mac	gaming_laptops_test	current_groups	66
2020-10-14	CIS	Mac	gaming_laptops_test	new_groups	61
2020-10-14	CIS	PC	gaming_laptops_test	current_groups	160
2020-10-14	CIS	PC	gaming_laptops_test	new_groups	163
2020-10-14	EU	Android	gaming_laptops_test	current_groups	97
2020-10-14	EU	Android	gaming_laptops_test	new_groups	86
2020-10-14	EU	iPhone	gaming_laptops_test	current_groups	32
2020-10-14	EU	iPhone	gaming_laptops_test	new_groups	49
2020-10-14	EU	Mac	gaming_laptops_test	current_groups	17
2020-10-14	EU	Mac	gaming_laptops_test	new_groups	26
2020-10-14	EU	PC	gaming_laptops_test	current_groups	70
2020-10-14	EU	PC	gaming_laptops_test	new_groups	61
2020-10-14	MENA	Android	gaming_laptops_test	current_groups	245
2020-10-14	MENA	Android	gaming_laptops_test	new_groups	273
2020-10-14	MENA	iPhone	gaming_laptops_test	current_groups	116
2020-10-14	MENA	iPhone	gaming_laptops_test	new_groups	124
2020-10-14	MENA	Mac	gaming_laptops_test	current_groups	51
2020-10-14	MENA	Mac	gaming_laptops_test	new_groups	68
2020-10-14	MENA	PC	gaming_laptops_test	current_groups	145
2020-10-14	MENA	PC	gaming_laptops_test	new_groups	142

2. Пересечение групп теста ¶

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

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,
           test_group
    FROM sessions_project_test_part
    WHERE install_date BETWEEN '2020-10-14' AND '2020-10-14'
)

-- Расчет количества вхождения уникальных пользователей в тестовые группы
SELECT user_id,
       COUNT(DISTINCT test_group) AS in_groups
FROM profiles
WHERE test_name = 'gaming_laptops_test'
GROUP BY user_id
HAVING COUNT(DISTINCT test_group) > 1;

Результат

В базе данных не существует записей, удовлетворяющих вашему запросу