SQL | 프로젝트 #8 문제정의 가설 검증

소리·2024년 7월 18일

실제로 분석을 하면 (문제정의 - 분석 ) X N번 그러니까 계속 반복해서 문제를 찾는다고 한다. 답답한데 내가 그러고 있는 와중인 것 같다.

초기 문제 정의

cart > remove_from_cart 상태가 매출에 해가 되고, purchase로 변화시켜야 매출을 올릴 수 있으므로, remove_from_cart 액션을 문제라고 정의했다.

문제정의에서 놓친 부분

1) remove_from_cart가 정말 매출에 영향을 미쳤는지에 대해서 파악을 해야 문제라고 생각할 수 있다.
2) 이게 실제로는 쇼핑을 하면서 당연하게 벌어지는 액션일 수도, 혹은 어떤 문제에 의해서 발생하는 것일 수도 있는데 후자여야 문제라고 주장할 수 있다.
3) 지금 뭐가 문제인지 모르니까 지표를 골라서 가설을 생성해야한다.

즉, 문제정의를 위한 가설을 다시 정해야한다는 뜻!

풀어나갈 방향

액션이 정상인지, 아닌지 판단하는 것으로 제일 설득력 있는 지표는 매출이라고 생각할 수 있다.

그래서 remove_from_cart를 한 유저와 그렇지 않은 유저의 매출 차이를 확인한다.
혹은 remove_from_cart 유저를 기준으로 remove_from_cart 하기 전 후의 매출을 비교하는 방법도 생각해볼 수 있다.
여기서 고려해야 할 지점은 remove_from_cart 유저와 그렇지 않은 유저 간의 매출과 연관된 지표는 모두 찾아야 실제로 매출 차이가 유효한지에 대해서 이야기할 수 있다. (예를 들어 집단의 크기가 작으면 Acquisition이 좁아 revenue가 당연히 작다고 이야기할 수 있다. 그렇지 않은지를 확인해야한다.)

데이터로 확인

1. `remove_from_cart` 유저가 그렇지 않은 유저에 비해 매출이 낮은지 확인 (총 구매 금액, 구매 횟수 확인)

remove_from_cart 가 하나라도 있는 user 구하기

-- activation이 있는 유저 (2번 이상 event_time이 찍여있는 user만 있는 테이블 생성

CREATE TEMPORARY TABLE two AS (
	SELECT *
	FROM beauty_ecommerce
	WHERE user_id IN (
		SELECT user_id
		FROM beauty_ecommerce
		GROUP BY user_id
		HAVING COUNT(event_time) != 1
	)
);

-- remove_from_cart가 하나라도 있는 유저 id 리스트
CREATE TEMPORARY TABLE remove_user_ids AS (
    SELECT DISTINCT user_id 
    FROM two 
    WHERE event_type = 'remove_from_cart'
);

-- 해당 유저 전체 열 데이터 추출
CREATE TEMPORARY TABLE remove_users AS (
    SELECT * 
    FROM two 
    WHERE user_id IN (SELECT user_id FROM remove_user_ids)
);

-- 최종 데이터 추출
SELECT COUNT(DISTINCT user_id) AS user_counts
	, SUM(CASE WHEN event_type = 'purchase' THEN price ELSE 0 END) AS total_purchase
    , (SELECT AVG(event_count) FROM user_event_counts) AS avg_event_count
FROM remove_users;

remove_from_cart 가 없는 있는 user 구하기

-- remove_from_cart가 없는 유저 아이디
CREATE TEMPORARY TABLE control_user_ids AS (
    SELECT DISTINCT user_id 
    FROM two 
    WHERE event_type != 'remove_from_cart'
);

CREATE TEMPORARY TABLE control_users AS (
    SELECT * 
    FROM two 
    WHERE user_id IN (SELECT user_id FROM control_user_ids)
);


-- control 그룹 확인하기
CREATE TEMPORARY TABLE control_counts AS (
    SELECT user_id, COUNT(event_time) AS event_count
    FROM control_users
    GROUP BY user_id
);

SELECT COUNT(DISTINCT user_id) AS user_counts
	, SUM(CASE WHEN event_type = 'purchase' THEN price ELSE 0 END) AS total_purchase
    , (SELECT AVG(event_count) FROM control_counts) AS avg_event_count
FROM control_users;

▶️ 각 user 수와 총 매출액, 그리고 평균 활동량(event_time)으로 데이터량을 확인했고, 아래 표로 한 눈에 비교할 수 있다.

user수, 평균 이벤트 수, 평균 구매액, 총 구매 금액 확인 *수정

category	user_counts	avg_event_counts	avg_purchase_amount	total_purchas_amount
remove	152,841	82.58	61.97	4,561,284.87
control	561,948	5.8476	30.48	579,894.50

user_counts 값의 차이가 4.6배 차이난다. 매출 차이가 유효한 지 확인한다.

-- 임금차이 평균과 분산 계산
-- remove_from_cart가 있는 사용자들의 평균 및 분산
SELECT 
    AVG(price) AS avg_price_remove_cart,
    VARIANCE(price) AS var_price_remove_cart
FROM remove_users;

-- remove_from_cart가 없는 사용자들의 평균 및 분산
SELECT 
    AVG(price) AS avg_price_non_remove_cart,
    VARIANCE(price) AS var_price_non_remove_cart
FROM control_users;

t검정 통계량으로 확인

(피드백) 좋은 접근이지만, 현재 상황에서는 고객 세분화가 더 적절해보인다.

가설 : remove_from_cart 이벤트가 있는 user과 없는 user의 평균 가격 차이는 통계적으로 유의미하다.

import math
from scipy import stats

# 평균 및 분산
mean1 = 6.577837550673571
mean2 = 8.193504426426388
var1 = 212.56697676049723
var2 = 345.11566258046065

# 표본 크기
n1 = 152841
n2 = 714164

# 표준 편차
std1 = math.sqrt(var1)
std2 = math.sqrt(var2)

# t-검정 통계량 계산
pooled_std = math.sqrt(((std1 ** 2) / n1) + ((std2 ** 2) / n2))
t_stat = (mean1 - mean2) / pooled_std

# 자유도 계산
df = (((std1 ** 2) / n1 + (std2 ** 2) / n2) ** 2) / ((((std1 ** 2) / n1) ** 2) / (n1 - 1) + (((std2 ** 2) / n2) ** 2) / (n2 - 1))

# p-값 계산
p_value = stats.t.sf(abs(t_stat), df) * 2

print(f't-statistic: {t_stat}, p-value: {p_value}')

▶️ 유의수준 (0.05)보다 훨씬 작으므로 유저 간의 평균 가격 차이가 통계적으로 유의미하다고 볼 수 있다.
▶️ remove_from_cart 유저가 그렇지 않은 유저보다 전체 매출액에서 약 12.71% 높다.

또 다른 눈에 띄는 차이로는 remove_from_cart일 경우 평균 사이트를 이용하는 액션이 4배나 많았다. 액션은 많은데 매출이 적다.

구매 횟수

category	purchase_counts
remove	964,102
control	1,044,993

▶️ remove 유저 그룹이 4만회 정도 적다.

평균 구매금액, 재구매율을 확인해보자

평균 구매 금액

category	avg_revenue
remove	6.9738
control	8.0486

재구매율

category	repurchase_rate
remove	94.1540
control	74.186

-- 쿼리 원문
CREATE TEMPORARY TABLE two AS (
	SELECT *
	FROM beauty_ecommerce
	WHERE user_id IN (
		SELECT user_id
		FROM beauty_ecommerce
		GROUP BY user_id
		HAVING COUNT(event_time) != 1
	)
);

-- remove_from_cart가 있는 유저 아이디 데이터 추출
CREATE TEMPORARY TABLE remove_user_ids AS
SELECT DISTINCT user_id
FROM two
WHERE event_type = 'remove_from_cart';

-- remove_from_cart가 없는 유저를 위한 임시 테이블 생성
CREATE TEMPORARY TABLE non_remove_user_ids AS
SELECT DISTINCT user_id
FROM two
WHERE user_id NOT IN (SELECT user_id FROM remove_user_ids);

-- remove_from_cart가 있는 유저의 구매 데이터 추출
CREATE TEMPORARY TABLE user_purchases_remove AS
SELECT user_id, COUNT(*) AS purchase_count
FROM two
WHERE event_type = 'purchase'
AND user_id IN (SELECT user_id FROM remove_user_ids)
GROUP BY user_id;

-- remove_from_cart가 없는 유저의 구매 데이터 추출
CREATE TEMPORARY TABLE user_purchases_non_remove AS
SELECT user_id, COUNT(*) AS purchase_count
FROM two
WHERE event_type = 'purchase'
AND user_id IN (SELECT user_id FROM non_remove_user_ids)
GROUP BY user_id;


-- remove_from_cart가 있는 유저의 재구매율
SELECT 
    'remove_from_cart' AS category,
    COUNT(CASE WHEN purchase_count >= 2 THEN user_id END) * 1.0 / COUNT(user_id) AS repurchase_rate
FROM user_purchases_remove

UNION ALL

-- remove_from_cart가 없는 유저의 재구매율
SELECT 
    'no_remove_from_cart' AS category,
    COUNT(CASE WHEN purchase_count >= 2 THEN user_id END) * 1.0 / COUNT(user_id) AS repurchase_rate
FROM user_purchases_non_remove;

2. remove_from_cart 유저의 remove 액션 전후 매출

▶️ user_id 별로 뽑았을 때, (remove 전)은 0이 많았고, (remove 후)는 확실히 값이 존재했다.
데이터 일부로만 판단하기 어려우니, remove 전후 매출 평균을 확인해본다.

(결과)

SELECT 
    AVG(revenue_before_remove) AS avg_revenue_before_remove,
    AVG(revenue_after_remove) AS avg_revenue_after_remove
FROM (
    SELECT 
        t.user_id,
        SUM(CASE WHEN t.event_sequence < m.min_remove_sequence AND t.event_type = 'purchase' THEN t.price ELSE 0 END) AS revenue_before_remove,
        SUM(CASE WHEN t.event_sequence >= m.min_remove_sequence AND t.event_type = 'purchase' THEN t.price ELSE 0 END) AS revenue_after_remove
    FROM user_event_timeline t
    JOIN min_event_sequence m ON t.user_id = m.user_id
    GROUP BY t.user_id
) AS user_revenue;

🔲 remove_from_cart를 해본 적이 있는지 여부로 고객을 나누기에는 remove_from_cart가 보편적이고, 섞여있어서 해당 가설은 매출을 높이기 위한 활동으로 적절하지 않다. 트렌드를 보고 주목할 부분을 다시 찾아본다.

AARRR 확인

🔸 유저 수 기준

1. Acquisition

SELECT COUNT(*)
FROM (SELECT user_id, min(event_time) frist_visit
	FROM beauty_ecommerce
	GROUP BY user_id) a;

▶️ 1,341,499

2. Activation

SELECT COUNT(*)  
FROM (
    SELECT user_id
    FROM beauty_ecommerce
    GROUP BY user_id
    HAVING COUNT(event_time) != 1
) a

▶️ 714,789

3. Revenue

구매자

SELECT COUNT(DISTINCT user_id)
FROM beauty_ecommerce
WHERE event_type = 'purchase';

▶️ 92,652

매출액
▶️ 5,141,716

4. Retention

SELECT 
    COUNT(DISTINCT CASE WHEN sub.purchase_count > 1 THEN sub.user_id END) AS repurchase_rate
FROM 
    (SELECT user_id, COUNT(event_time) AS purchase_count
     FROM beauty_ecommerce
     WHERE event_type = 'purchase'
     GROUP BY user_id) AS sub;

▶️ 83,242

소리

데이터로 경로를 탐색합니다.

이전 포스트

SQL | 프로젝트 #07 매출 관련 확인

다음 포스트

SQL | 프로젝트 #8 문제정의 가설 검증

초기 문제 정의

문제정의에서 놓친 부분

풀어나갈 방향

데이터로 확인

1. `remove_from_cart` 유저가 그렇지 않은 유저에 비해 매출이 낮은지 확인 (총 구매 금액, 구매 횟수 확인)

user수, 평균 이벤트 수, 평균 구매액, 총 구매 금액 확인 *수정

평균 구매금액, 재구매율을 확인해보자

2. remove_from_cart 유저의 remove 액션 전후 매출

AARRR 확인

1. Acquisition

2. Activation

3. Revenue

4. Retention

SQL | 프로젝트 #07 매출 관련 확인

SQL | 프로젝트 #2-1 지표 해석 (문제 다시 찾기)

0개의 댓글

SQL | 프로젝트 #8 문제정의 가설 검증

초기 문제 정의

문제정의에서 놓친 부분

풀어나갈 방향

데이터로 확인

1. remove_from_cart 유저가 그렇지 않은 유저에 비해 매출이 낮은지 확인 (총 구매 금액, 구매 횟수 확인)

user수, 평균 이벤트 수, 평균 구매액, 총 구매 금액 확인 *수정

평균 구매금액, 재구매율을 확인해보자

2. remove_from_cart 유저의 remove 액션 전후 매출

AARRR 확인

1. Acquisition

2. Activation

3. Revenue

4. Retention

SQL | 프로젝트 #07 매출 관련 확인

SQL | 프로젝트 #2-1 지표 해석 (문제 다시 찾기)

0개의 댓글

1. `remove_from_cart` 유저가 그렇지 않은 유저에 비해 매출이 낮은지 확인 (총 구매 금액, 구매 횟수 확인)