SkillForge — дослідження та очищення даних

Автор: Дмитро Купріянов
Період експерименту: 01–20 серпня 2025 року

Мета цього ноутбука — дослідити якість змодельованих даних A/B-тесту нового онбордингу «Миттєве занурення», виконати очищення та підготувати CSV для PostgreSQL.

Результат підготовки: збережено 10 000 користувачів і 59 746 щоденних записів за період тесту. Повні дублікати видалено, негативні значення days_on_product замінено на пропуски. Невідомі значення конверсії збережено без заміни на нуль.

Опис проєкту

↑ До змістуДо панелі навігації

Мета проєкту — проаналізувати результати A/B тесту нової функції "Миттєве занурення" у SkillForge та перевірити, чи підвищує вона конверсію в преміум-підписку.

1. Завантаження даних

↑ До змістуДо панелі навігації

Завантажимо таблиці з Excel-файлу та перевіримо їхню структуру.

import pandas as pd

file_path = 'SkillForge.xlsx'
xls = pd.ExcelFile(file_path)
xls.sheet_names

['device', 'country', 'group', 'user', 'daily']
device = pd.read_excel(file_path, sheet_name ='device')
country = pd.read_excel(file_path, sheet_name ='country')
group = pd.read_excel(file_path, sheet_name ='group')
users = pd.read_excel(file_path, sheet_name ='user')
daily = pd.read_excel(file_path, sheet_name ='daily')

2. Первинний огляд даних

↑ До змістуДо панелі навігації

Проведемо первинний огляд таблиць: кількість рядків і колонок, приклади записів та типи даних.

device
device_id device_name
0 1 Desktop
1 2 Mobile
2 3 Smartwatch
3 4 TV
4 5 Tablet 
device.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 2 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   device_id    5 non-null      int64 
 1   device_name  5 non-null      object
dtypes: int64(1), object(1)
memory usage: 212.0+ bytes

country
country_id country_code
0 1 BR
1 2 DE
2 3 FR
3 4 IN
4 5 US
5 6 XX 
country.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 6 entries, 0 to 5
Data columns (total 2 columns):
 #   Column        Non-Null Count  Dtype 
---  ------        --------------  ----- 
 0   country_id    6 non-null      int64 
 1   country_code  6 non-null      object
dtypes: int64(1), object(1)
memory usage: 228.0+ bytes

group
group_id group_label
0 1 A
1 2 B 
group.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 2 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   group_id     2 non-null      int64 
 1   group_label  2 non-null      object
dtypes: int64(1), object(1)
memory usage: 164.0+ bytes

users.head()

users.info()

Таблиця users містить 10 000 користувачів і 5 полів. У device_id, country_id, group_id та days_on_product присутні пропущені значення. Через наявність пропусків частина числових ідентифікаторів має тип float64. Детальну обробку пропусків і типів даних буде виконано на етапі перевірки якості та очищення даних.

daily.head()

daily.info()

Таблиця daily містить 61 002 записи щоденної активності користувачів. Поле date коректно розпізнано як дату. У converted присутні пропущені значення, тому поле має тип float64. Коректність значень converted та періоду дат буде перевірена на етапі перевірки якості та очищення даних.

3. Перевірка якості та очищення даних

↑ До змістуДо панелі навігації

Перевіримо дані на пропуски, дублікати, некоректні типи,аномальні значення та порушення цілісності, після чого виконаємо необхідне очищення.

3.1. Перевірка довідників

↑ До змістуДо панелі навігації

print(device.isna().sum())
print(device.duplicated().sum())
print(device['device_id'].duplicated().sum())

device_id      0
device_name    0
dtype: int64
0
0

print(country.isna().sum())
print(country.duplicated().sum())
print(country['country_id'].duplicated().sum())

country_id      0
country_code    0
dtype: int64
0
0

print(group.isna().sum())
print(group.duplicated().sum())
print(group['group_id'].duplicated().sum())

group_id       0
group_label    0
dtype: int64
0
0

print(device['device_name'].duplicated().sum())
print(country['country_code'].duplicated().sum())
print(group['group_label'].duplicated().sum())

0
0
0

Висновок: довідники device, country та group не містять пропусків, повних дублікатів або повторень ключів і категорій. Очищення для цих таблиць не потрібне.

3.2. Якість даних користувачів

↑ До змістуДо панелі навігації

users.isna().sum()

print(users.duplicated().sum())
print(users['user_id'].duplicated().sum())

0
0

users['device_id'].value_counts(dropna=False)

device_id
2.0    5958
1.0    2867
5.0     940
NaN     205
4.0      18
3.0      12
Name: count, dtype: int64
((users['device_id'].value_counts(dropna=False) / users.shape[0]).round(4))*100

device_id
2.0    59.58
1.0    28.67
5.0     9.40
NaN     2.05
4.0     0.18
3.0     0.12
Name: count, dtype: float64

Невалідних device_id не видно. У довіднику в нас допустимі ID 1–5, і тут зустрічаються саме вони + NaN, виявлено 205 (2.05%) пропущених значень

users['country_id'].value_counts(dropna=False)

country_id
2.0    1943
5.0    1881
1.0    1863
4.0    1808
3.0    1804
NaN     671
6.0      30
Name: count, dtype: int64
((users['country_id'].value_counts(dropna=False) / users.shape[0]).round(4))*100

country_id
2.0    19.43
5.0    18.81
1.0    18.63
4.0    18.08
3.0    18.04
NaN     6.71
6.0     0.30
Name: count, dtype: float64
users['country_id'].value_counts(dropna=False)

У country_id виявлено 671 пропущене значення (6.71%). Значення ID відповідають довіднику.

users['group_id'].value_counts(dropna=False)

group_id
1.0    5006
2.0    4940
NaN      54
Name: count, dtype: int64
((users['group_id'].value_counts(dropna=False) / users.shape[0]).round(4))*100

group_id
1.0    50.06
2.0    49.40
NaN     0.54
Name: count, dtype: float64

Розподіл користувачів між групами A і B майже рівномірний (50.06% проти 49.40%), що є позитивним для A/B тесту. У 54 користувачів (0.54%) відсутнє значення group_id - ці записи потребують окремого рішення перед проведенням статистичного аналізу.

users['days_on_product'].describe().reset_index()
index days_on_product
0 count 9840.000000
1 mean 29.947663
2 std 17.952773
3 min -29.000000
4 25% 15.000000
5 50% 30.000000
6 75% 44.000000
7 max 78.000000 
users[users['days_on_product'] < 0].shape

(20, 5)
users[users['days_on_product'] < 0]

Виявлено 20 користувачів (0.2%) із негативним days_on_product від -29 до -2 днів. Такі значення є логічно некоректними та потребують обробки під час очищення даних.

users[users['days_on_product'] == 0]

3.3. Якість щоденних записів

↑ До змістуДо панелі навігації

daily.isna().sum()

daily.duplicated().sum()

np.int64(1196)
daily[['user_id','date']].duplicated().sum()

np.int64(1196)
daily[daily[['user_id','date']].duplicated(keep = False)].sort_values(by = 'user_id').head(20)

У таблиці daily виявлено 1196 повних дублікатів. Повторні записи мають однакові user_id, date та converted, тому їх можна безпечно видалити під час очищення даних.

daily['converted'].value_counts(dropna = False)

converted
0.0    52726
1.0     7760
NaN      516
Name: count, dtype: int64
(daily['converted'].value_counts(dropna = False) / daily.shape[0])*100

converted
0.0    86.433232
1.0    12.720894
NaN     0.845874
Name: count, dtype: float64

У converted виявлено 516 пропущених значень (0.85%). Оскільки "0" означає відсутність конверсії, пропуски не будуть автоматично замінені на 0.

print(daily['date'].min())
print(daily['date'].max())

2025-06-15 00:00:00
2026-01-01 00:00:00

(daily['date'] < '2025-08-01').sum()

np.int64(35)
(daily['date'] > '2025-08-20').sum()

np.int64(27)
((daily['date'] <= '2025-08-20') & (daily['date'] >= '2025-08-01')).sum()

np.int64(60940)

У таблиці daily виявлено 62 записи поза періодом експерименту: 35 до 01.08.2025 та 27 після 20.08.2025. Для подальшого A/B аналізу будуть використані лише записи за 01–20 серпня 2025 року

3.4. Перевірка зв’язків між таблицями

↑ До змістуДо панелі навігації

(~daily['user_id'].isin(users['user_id'])).sum()

np.int64(0)

усі користувачі з daily присутні в users. Зв’язок між таблицями коректний.

(~users['device_id'].dropna().isin(device['device_id'])).sum()

np.int64(0)
(~users['country_id'].dropna().isin(country['country_id'])).sum()

np.int64(0)
(~users['group_id'].dropna().isin(group['group_id'])).sum()

np.int64(0)

Усі заповнені ID у таблиці users коректно відповідають довідникам. Помилок у зв’язках між таблицями не виявлено.

3.5. Очищення даних

↑ До змістуДо панелі навігації

users_clean = users.copy()

users_clean.loc[users_clean['days_on_product'] < 0,'days_on_product'] = pd.NA

users_clean[users_clean['days_on_product'] < 0]

daily_clean = daily.copy()

daily_clean = daily_clean.drop_duplicates()

print(daily_clean.duplicated().sum())
print(daily_clean.shape[0])

0
59806

daily_clean = daily_clean[(daily_clean['date'] <= '2025-08-20') & (daily_clean['date'] >= '2025-08-01')]

print(daily_clean['date'].min())
print(daily_clean['date'].max())
print(daily_clean.shape[0])

2025-08-01 00:00:00
2025-08-20 00:00:00
59746

Після видалення дублікатів залишено лише записи за період A/B тесту — з 1 по 20 серпня 2025 року.

3.6. Невідомі статуси конверсії

↑ До змістуДо панелі навігації

daily_clean['converted'].value_counts(dropna = False)

converted
0.0    51656
1.0     7589
NaN      501
Name: count, dtype: int64
(daily_clean['converted'].value_counts(dropna = False) / daily_clean.shape[0])*100

converted
0.0    86.459345
1.0    12.702106
NaN     0.838550
Name: count, dtype: float64

Після очищення в converted залишилось 501 пропущене значення. Щоб оцінити їх вплив, перевіряємо, скільки унікальних користувачів мають хоча б один пропуск у цій колонці.

daily_clean[daily_clean['converted'].isna()]['user_id'].nunique()

485

501 пропущене значення converted належать 485 унікальним користувачам. Тобто в частини користувачів є кілька днів із невідомим статусом конверсії.

cnt_unuq_corist = daily_clean[daily_clean['converted'].isna()]['user_id'].unique()

daily_clean[daily_clean['user_id'].isin(cnt_unuq_corist) & (daily_clean['converted'] == 1)]['user_id'].nunique()

256

Із 485 користувачів із пропусками в converted 256 мають підтверджену конверсію в інші дні. Для решти 229 користувачів факт конверсії неможливо визначити однозначно через пропущені значення.

Пропуски в converted не замінюємо на 0, оскільки це могло б штучно занизити конверсію. Для 256 користувачів конверсію підтверджено в інші дні, а для 229 користувачів статус залишається невідомим. Далі перевіримо їх розподіл між групами A і B.

users_with_conversion = daily_clean[daily_clean['user_id'].isin(cnt_unuq_corist) & (daily_clean['converted'] == 1)]['user_id'].unique()

users_unknown_conversion = set(cnt_unuq_corist) - set(users_with_conversion)

len(users_unknown_conversion)

229

Для 229 користувачів статус конверсії залишається невідомим. Далі перевіримо, чи рівномірно ці користувачі розподілені між групами A і B.

users_clean[users_clean['user_id'].isin(users_unknown_conversion)]['group_id'].value_counts(dropna = False)

group_id
1.0    142
2.0     82
NaN      5
Name: count, dtype: int64

Користувачі з невідомим статусом конверсії розподілені між групами нерівномірно: 142 у групі A та 82 у групі B, ще у 5 користувачів група не визначена. Тому просте виключення таких користувачів може вплинути на результат A/B тесту.

Висновок: дані очищено від повних дублікатів, записів поза періодом тесту та некоректних негативних значень days_on_product. Пропуски, які неможливо коректно відновити, залишено як невідомі значення для подальшого врахування в аналізі.

3.7. Приведення типів даних

↑ До змістуДо панелі навігації

users_clean['device_id'] = users_clean['device_id'].astype('Int64')
users_clean['country_id'] = users_clean['country_id'].astype('Int64')
users_clean['group_id'] = users_clean['group_id'].astype('Int64')
users_clean['days_on_product'] = users_clean['days_on_product'].astype('Int64')

users_clean.info()

daily_clean['converted'] = daily_clean['converted'].astype('Int64')

daily_clean.info()

Висновок: типи даних приведено до коректного формату. ID-поля, days_on_product та converted зберігаються як цілі числа з підтримкою пропущених значень, а date — у форматі дати.

4. Підсумкова перевірка та експорт

↑ До змістуДо панелі навігації

print(users_clean.shape)
print(daily_clean.shape)

print(users_clean.duplicated().sum())
print(daily_clean.duplicated().sum())

print((users_clean['days_on_product'] < 0).sum())

print(daily_clean['date'].min())
print(daily_clean['date'].max())

(10000, 5)
(59746, 3)
0
0
0
2025-08-01 00:00:00
2025-08-20 00:00:00

Очищення завершено. Повні дублікати та некоректні негативні значення відсутні, а daily_clean містить лише записи за період A/B тесту — 1–20 серпня 2025 року. Пропуски, які неможливо достовірно відновити, залишено без штучного заповнення.

Підсумок очищення

↑ До змістуДо панелі навігації

Дані / проблема Виконана дія Результат
daily: 61 002 початкові записи Видалено 1 196 повних дублікатів 59 806 записів
Записи поза періодом тесту Після дедуплікації видалено 60 записів поза 01–20.08.2025 59 746 записів
days_on_product: 20 негативних значень Замінено на пропуски, користувачів збережено Негативних значень немає; 10 000 користувачів
Пропуски converted Збережено як невідомі значення 501 пропуск у щоденних записах
Пропуски ідентифікаторів і категорій Збережено без штучного заповнення 54 користувачі без групи не входять до A/B-порівняння

У початкових даних було 62 записи поза періодом; після видалення повних дублікатів залишилося 60 таких записів. Це різні етапи очищення, тому кількості відрізняються.

4.1. Експорт очищених таблиць

↑ До змістуДо панелі навігації

output_dir = 'SkillForge_clean'

device.to_csv(f'{output_dir}/device.csv', index=False)
country.to_csv(f'{output_dir}/country.csv', index=False)
group.to_csv(f'{output_dir}/experiment_group.csv', index=False)

users_clean.to_csv(f'{output_dir}/users.csv', index=False)
daily_clean.to_csv(f'{output_dir}/daily.csv',index=False,date_format='%Y-%m-%d')

Результат: п’ять очищених CSV збережено в SkillForge_clean.

5. SQL: таблиці та аналітичні view

↑ До змістуДо панелі навігації

Після очищення даних створюємо таблиці та аналітичні view у PostgreSQL для A/B-аналізу.

Авторський код із файлу SkillForge_sql(1).sql: створення таблиць, ключів, обмежень та двох view. Переглянути SQL-файл на GitHub ↗

5.1. Таблиці та обмеження

↑ До змістуДо панелі навігації

--Ствоення таблиць та структури таблиць 



-- Довідник типів пристроїв користувачів
CREATE TABLE  device (

device_id INTEGER PRIMARY KEY,
device_name TEXT UNIQUE NOT NULL

);

-- Довідник країн користувачів
CREATE TABLE country (

country_id INTEGER PRIMARY KEY,
country_code TEXT UNIQUE NOT NULL

);

-- Довідник груп A/B тесту:
-- A — контрольна група, B — тестова група
CREATE TABLE  experiment_group ( 

group_id INTEGER PRIMARY KEY,
group_label TEXT UNIQUE NOT NULL CHECK (group_label IN ('A', 'B'))

);


-- Основна таблиця користувачів.
-- Зберігає пристрій, країну, групу A/B тесту та кількість днів користування продуктом.
CREATE TABLE users (

user_id INTEGER PRIMARY KEY,
device_id INTEGER,
country_id INTEGER,
group_id INTEGER,
days_on_product INTEGER CHECK (days_on_product >= 0),

FOREIGN KEY (device_id) REFERENCES device(device_id),
FOREIGN KEY (country_id) REFERENCES country(country_id),
FOREIGN KEY (group_id) REFERENCES experiment_group(group_id)

);

CREATE TABLE daily (

user_id INTEGER NOT NULL,
date DATE NOT NULL,
converted INTEGER CHECK (converted IN (0, 1)),

  PRIMARY KEY (user_id, date),
  FOREIGN KEY (user_id) REFERENCES users(user_id)

);


-- Щоденні записи активності користувачів у межах A/B тесту.
-- converted: 1 — конверсія відбулася, 0 — не відбулася, NULL — статус конверсії невідомий.

5.2. Щоденна агрегація — v_daily_agg

↑ До змістуДо панелі навігації

CREATE OR REPLACE VIEW v_daily_agg AS 
SELECT 
    d.date,
    eg.group_label,
    COUNT(*) AS cnt_users,
    SUM(d.converted) AS cnt_conversions,
    SUM(CASE WHEN d.converted IS NULL THEN 1 ELSE 0 END) AS cnt_unknown_convert,
    COUNT(d.converted) AS cnt_known,
    ROUND((SUM(d.converted) / COUNT(d.converted)::NUMERIC) * 100,2) AS conver_rate
FROM users AS u 
   JOIN daily AS d 
      ON u.user_id = d.user_id 
   JOIN experiment_group AS eg 
      ON u.group_id = eg.group_id 
GROUP BY d.date,eg.group_label;


SELECT *
FROM v_daily_agg
ORDER BY date, group_label;

--Денна агрегація показує кількість користувачів, підтверджених конверсій, невідомих статусів та conversion rate окремо для груп A і B за кожен день тесту.

5.3. Підсумковий статус користувача — v_user_conversion

↑ До змістуДо панелі навігації

CREATE OR REPLACE VIEW v_user_conversion AS
SELECT 
    u.user_id,
    eg.group_label,
    d.device_name,
    c.country_code,
    u.days_on_product,
    CASE
        WHEN SUM(CASE WHEN da.converted = 1 THEN 1 ELSE 0 END) > 0
            THEN 1
        WHEN SUM(CASE WHEN da.converted IS NULL THEN 1 ELSE 0 END) > 0
            THEN NULL
        ELSE 0
    END AS conversion_status
FROM users u
   JOIN experiment_group eg
      ON u.group_id = eg.group_id
   LEFT JOIN device d
      ON u.device_id = d.device_id
   LEFT JOIN country c
       ON u.country_id = c.country_id
   LEFT JOIN daily da
       ON u.user_id = da.user_id
GROUP BY
    u.user_id,
    eg.group_label,
    d.device_name,
    c.country_code,
    u.days_on_product;


SELECT *
FROM v_user_conversion
ORDER BY user_id;

--v_user_conversion формує один підсумковий запис на кожного користувача та визначає його статус конверсії за весь період тесту. 
--View буде використана для основного A/B аналізу та сегментації користувачів.

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