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

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

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

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

holidays_events_project — таблица с праздничными датами;
sessions_project_history — таблица с историческими данными по сессиям пользователей;
purchases_project_history — таблица с историческими данными по покупкам пользователей;

Таблица holidays_events_project:

name — Название праздника;
regions — Регионы праздника;
start_dt — Дата начала праздника;
finish_dt — Дата окончания праздника.

Таблица sessions_project_history:

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_history:

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

Содержание¶

Список маркетинговых активностей
Получение DAU новичков
Распределение новичков по устройствам и регионам
Конверсия и выручка

1. Список маркетинговых активностей ¶

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

SELECT *
FROM holidays_events_project
WHERE start_dt >= '01-01-2020';

name	region	start_dt	finish_dt
New year	CIS, MENA, EU	01.01.2020	01.01.2020
May Day	CIS, EU	05.01.2020	05.01.2020
Chinese New Year	APAC	25.01.2020	25.01.2020
Valentines day	CIS, MENA, EU	14.02.2020	14.02.2020
St. Patrics Day	EU	17.03.2020	17.03.2020
Victory Day (May 9th)	CIS	09.05.2020	09.05.2020
Orthodox Сhristmas	CIS	01.07.2020	01.07.2020
International Womens Day	CIS, EU	03.08.2020	03.08.2020
Chinese Moon Festival	APAC	01.10.2020	07.10.2020
Catholic Christmas	MENA, EU	25.12.2020	25.12.2020

2. Получение DAU новичков ¶

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

WITH

-- Ппрофиль пользователя
profiles AS (
    SELECT DISTINCT user_id,
           FIRST_VALUE(install_date) OVER (PARTITION BY user_id ORDER BY install_date) AS install_date,
           MAX(registration_flag) OVER (PARTITION BY user_id ORDER BY install_date) AS registration_flag,
           FIRST_VALUE(region) OVER (PARTITION BY user_id ORDER BY install_date) AS first_region,
           FIRST_VALUE(device) OVER (PARTITION BY user_id ORDER BY install_date) AS first_device
    FROM sessions_project_history
    WHERE install_date BETWEEN '2020-08-11' AND '2020-09-10'
)

-- DAU новых пользователей
SELECT install_date,
       COUNT(user_id) AS new_dau
FROM profiles
GROUP BY install_date;

nstall_date	new_dau
29.08.2020	6660
02.09.2020	3240
27.08.2020	5440
20.08.2020	5215
19.08.2020	4412
…	…

3. Распределение новичков по устройствам и регионам ¶

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

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
     FROM sessions_project_history
     WHERE install_date BETWEEN '2020-08-11' AND '2020-09-10'
)

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

first_region	first_device	new_users
MENA	iPhone	10684
EU	Mac	1958
CIS	Android	26831
CIS	Mac	6187
EU	Android	8931
CIS	iPhone	12155
EU	iPhone	3925
EU	PC	5023
CIS	PC	15150
MENA	Mac	5436
MENA	PC	13394
MENA	Android	24365

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

Получение данных о числе транзакций и суммарных тратах пользоваетелей в категории «Комьютерная техника» (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
    FROM sessions_project_history
    WHERE install_date BETWEEN '2020-08-11' AND '2020-09-10'
),

-- Данные о покупках
orders AS (
    SELECT user_id,
           COUNT(session_id) AS transactions,
           SUM(price) AS revenue
    FROM purchases_project_history
    WHERE category = 'computer_equipments'
    GROUP BY user_id
)

-- Единый набор данных
SELECT p.user_id,
       p.install_date,
       o.transactions,
       o.revenue
FROM profiles p
LEFT JOIN orders o ON o.user_id = p.user_id;

user_id	install_date	transactions	revenue
0EDCD03B5927E3AE	07.09.2020	nan
257E110D1E29DFEB	05.09.2020	nan
173812A81C491DD5	15.08.2020	nan
2A047102DCE7ABD8	12.08.2020	2	1506
…	…	…	…