[Data Analysis] 코호트 분석 실습

코호트 분석 실습해보기

게시 2025/05/29

By 황병현

15 분읽는 시간

코호트 분석 실습

데이터 : eCommerce Events History in Cosmetics Shop

쿼리를 작성하는 목표: 중형 화장품 온라인 스토어에서 5개월동안의 데이터에서 사용자의 관심 브랜드의 유지율을 확인하고자 한다. 이를 통해 각 브랜드의 관심도, 충성고객 등을 파악하고자 한다.

확인할 지표: 메인지표: 유지율(각 기간 브랜드별 사용자가 본 수 / 브랜드별 처음으로 사용자가 본 수), 가드레일 지표: 이탈률(기간별 AU/ 처음 기간 AU)

데이터의 기간: 2019.10 - 2020.02

사용할 테이블: 2019-Oct.csv, 2019-Nov.csv, 2019-Dec.csv, 2020-Jan.csv, 2020-Feb.csv

데이터 특징:

중형 화장품 온라인 스토어에서 5개월(2019.10 - 2020.02)동안의 행동 데이터로 Open CDP 프로젝트에서 수집한 데이터이다.
2019-Oct.csv: (1782439, 9), 482.54MB
2019-Nov.csv: (1810735, 9), 545.84MB
2019-Dec.csv: (1654771, 9), 415.3MB
2020-Jan.csv: (1811717, 9), 501.79MB
2020-Feb.csv: (1723228, 9), 488.8MB
공통:

컬럼 이름	설명
`event_time`	이벤트가 발생한 시간 (UTC 기준)
`event_type`	이벤트 유형. 이 데이터셋에서는 `view`(보기), `cart`(장바구니 넣기), `remove_from_cart`(장바구니 삭제), `purchase`(구매) 존재
`product_id`	상품의 고유 ID
`category_id`	상품의 카테고리 ID
`category_code`	상품 카테고리 분류 코드. 의미 있는 카테고리일 경우에만 제공되며, 액세서리처럼 다양한 종류에는 생략될 수 있음
`brand`	소문자로 표기된 브랜드 이름. 누락될 수 있음
`price`	상품의 가격 (소수점 포함)
`user_id`	영구적인 사용자 ID
`user_session`	임시 사용자 세션 ID. 동일한 세션 동안 유지되며, 사용자가 오랜 시간 후 다시 접속하면 변경됨

※위 실습은 블로그의 실습을 참고하여 만들었습니다.

일반적으로 코호트는 상호배타적(mutually exclusive)이다. 즉, 코호트끼리 겹치지 않는다. 메인지표에서는 번거로움을 피하고자 그런 과정을 거치지 않았는데, 이 점은 주의하기 바람!

  
import pandas as pd
import random

random.seed(1004)

whole_target_pool = set()
whole_data = pd.DataFrame()

for file in ['2019-Oct.csv', '2019-Nov.csv', '2019-Dec.csv', '2020-Jan.csv', '2020-Feb.csv']:
    data = pd.read_csv(file)
    sample_targets = random.sample(list(data.user_id.unique()), 1000)
    whole_target_pool.update(sample_targets)
    # 추출한 1000명 중 앞서 추출한 사용자가 있는 경우를 합하여 데이터를 추출한다.
    new_target = data[data.user_id.isin(sample_targets) | data.user_id.isin(whole_target_pool)]

    print(f"{file}: ",len(new_target.user_id.unique()), new_target.shape)
    whole_data = pd.concat([whole_data, new_target])

print(f"전체 데이터셋: {whole_data.shape}")
whole_data.head(3)

2019-Oct.csv:  1000 (9432, 9)
2019-Nov.csv:  1113 (17417, 9)
2019-Dec.csv:  1247 (14018, 9)
2020-Jan.csv:  1308 (20059, 9)
2020-Feb.csv:  1392 (25459, 9)
전체 데이터셋: (86385, 9)

	event_time	event_type	product_id	category_id	category_code	brand	price	user_id	user_session
5167	2019-10-01 04:11:01 UTC	view	5649236	1487580008246412266	NaN	concept	8.65	555480375	ede986d2-417f-4c08-a61b-021c9d0007eb
5253	2019-10-01 04:13:15 UTC	view	5649236	1487580008246412266	NaN	concept	8.65	555480375	ede986d2-417f-4c08-a61b-021c9d0007eb
6462	2019-10-01 04:41:46 UTC	view	5649236	1487580008246412266	NaN	concept	8.65	555484401	bf933666-7fbb-4536-9f3a-102ceaee5859

  
import sqlite3

con = sqlite3.connect('ecommerce.db')

cur = con.cursor()

cur.execute(
    """
    CREATE TABLE events
        (
            event_time text,
            event_type text,
            product_id text,
            category_id text,
            category_code text,
            brand text,
            price text,
            user_id text,
            user_session text
        )
    """
)

data_row = []
# 튜플형태로 row 저장
for idx, row in whole_data.iterrows():
    data_row.append(tuple([*row.values]))

# executemany를 사용하여 table에 insert
cur.executemany(
    """
    INSERT INTO events
    VALUES (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
    )
    """,
    data_row
)

con.commit()

메인지표: 브랜드별 리텐션

월별 브랜드 리텐션

  
import sqlite3

con = sqlite3.connect('ecommerce.db')

cur = con.cursor()

  
result = con.execute(
    """
    WITH
    base AS (
    SELECT
        user_id,
        -- '월'만 빼내는데, 연도가 바뀌면 계산이 틀어지기 때문에 현재 연도에서 가장 낮은 연도인 2019년을 뺀 만큼에 12를 곱한 값을 더해준다
        -- 즉, 2020년의 1월은 12 + (2020-2019)*12 = 13이 된다
        STRFTIME('%m', DATE(SUBSTR(event_time, 1, 10))) + (STRFTIME('%Y', DATE(SUBSTR(event_time, 1, 10))) - 2019) * 12 AS event_month,
        event_type,
        brand
    FROM events
    WHERE 
        brand IS NOT NULL
        AND event_type = 'view'
    ),
    first_view AS (
    SELECT
        user_id,
        brand AS cohort,
        MIN(event_month) AS cohort_time
    FROM base
    GROUP BY
        user_id,
        brand
    ),
    joinned AS (
    SELECT
        t1.user_id,
        t2.cohort,
        t1.event_month,
        t1.event_month - t2.cohort_time AS month_diff
    FROM base t1
    LEFT JOIN first_view t2
    ON t1.user_id = t2.user_id
    AND t1.brand = t2.cohort
    )

    SELECT
        cohort,
        month_diff,
        COUNT(DISTINCT user_id)
    FROM joinned
    GROUP BY 
        cohort,
        month_diff
    ORDER BY 
        cohort ASC,
        month_diff ASC
    """
).fetchall()

  
# 데이터프레임으로 만들고
# 컬럼의 이름을 바꿔주고
# 피벗 기능을 이용해 코호트 테이블 형태로 만들어준다
# 빈 값은 0으로 채운다
pivot_table = pd.DataFrame(result)\
    .rename(columns={0: 'cohort', 1: 'duration', 2: 'value'})\
    .pivot(index='cohort', columns='duration', values='value')\
    .fillna(0)\
    .sort_values(by=[0], ascending=False)\
    .iloc[:10, :]

# 상위 10개만 잘랐다
pivot_table

duration	0	1	2	3	4
cohort
runail	678.0	67.0	26.0	15.0	5.0
irisk	547.0	54.0	28.0	12.0	6.0
grattol	419.0	48.0	21.0	11.0	3.0
masura	311.0	27.0	8.0	3.0	0.0
estel	300.0	17.0	2.0	2.0	1.0
kapous	292.0	19.0	7.0	3.0	1.0
jessnail	280.0	19.0	7.0	4.0	0.0
uno	219.0	12.0	5.0	0.0	1.0
ingarden	217.0	28.0	6.0	5.0	3.0
concept	211.0	8.0	2.0	1.0	0.0

  
# 첫 번째 기간으로 나누어 비율로 만들어주고
# %가 나오도록 포맷팅을 해주고
# 색을 입혀준다

round(pivot_table.div(pivot_table[0], axis='index'), 2)\
    .style.format({k: '{:,.0%}'.format for k in pivot_table})\
    .background_gradient(cmap ='Blues', axis=None, vmax=0.2) 

duration	0	1	2	3	4
cohort
runail	100%	10%	4%	2%	1%
irisk	100%	10%	5%	2%	1%
grattol	100%	11%	5%	3%	1%
masura	100%	9%	3%	1%	0%
estel	100%	6%	1%	1%	0%
kapous	100%	7%	2%	1%	0%
jessnail	100%	7%	2%	1%	0%
uno	100%	5%	2%	0%	0%
ingarden	100%	13%	3%	2%	1%
concept	100%	4%	1%	0%	0%

주별 브랜드 리텐션(2019-10)

  
result_2 = con.execute("""
WITH 
base AS (
-- 문자열을 날짜로 바꿔주기 위한 용도
SELECT
    user_id,
    -- '주간'만 빼내는데, 연도가 바뀌면 계산이 틀어지기 때문에 현재 연도에서 가장 낮은 연도인 2019년을 뺀 만큼에 52를 곱한 값을 더해준다
    -- 즉, 2019년 마지막 주는 52가 되고, 2020년의 첫 주는 1 + (2020-2019)*52 = 53이 된다
    STRFTIME('%W', DATE(SUBSTR(event_time, 1, 10))) + (STRFTIME('%Y', DATE(SUBSTR(event_time, 1, 10))) - 2019) * 52 AS event_week,
    event_type,
    brand
FROM events
-- 9개의 주간으로 나누기 위해 기간을 제한해준다
WHERE STRFTIME('%W', DATE(SUBSTR(event_time, 1, 10))) + (STRFTIME('%Y', DATE(SUBSTR(event_time, 1, 10))) - 2019) * 52 <= 47
AND brand IS NOT NULL
AND event_type = 'view'
AND DATE(SUBSTR(event_time, 1, 10)) >= '2019-10-01'
AND DATE(SUBSTR(event_time, 1, 10)) <= '2019-10-31'
)
,first_view AS (
SELECT
    user_id,
    brand AS cohort,
    MIN(event_week) AS cohort_time
FROM base
GROUP BY user_id, brand
)
,joinned AS (
SELECT
    t1.user_id,
    t2.cohort,
    t1.event_week,
    t1.event_week - t2.cohort_time AS week_diff
FROM base t1
LEFT JOIN first_view t2
ON t1.user_id = t2.user_id
AND t1.brand = t2.cohort
)

SELECT
    cohort,
    week_diff,
    COUNT(DISTINCT user_id)
FROM joinned
GROUP BY cohort, week_diff
ORDER BY cohort ASC, week_diff ASC
""").fetchall()

  
# 데이터프레임으로 만들고
# 컬럼의 이름을 바꿔주고
# 피벗 기능을 이용해 코호트 테이블 형태로 만들어준다
# 빈 값은 0으로 채운다
pivot_table_2 = pd.DataFrame(result_2)\
    .rename(columns={0: 'cohort', 1: 'duration', 2: 'value'})\
    .pivot(index='cohort', columns='duration', values='value')\
    .fillna(0)\
    .sort_values(by=[0], ascending=False)\
    .iloc[:10, :]

# 상위 10개만 잘랐다
pivot_table_2

duration	0	1	2	3	4
cohort
runail	100.0	10.0	5.0	1.0	0.0
irisk	85.0	6.0	5.0	2.0	0.0
masura	69.0	4.0	3.0	2.0	1.0
grattol	52.0	6.0	4.0	3.0	0.0
estel	45.0	1.0	0.0	0.0	2.0
jessnail	41.0	4.0	0.0	0.0	0.0
kapous	40.0	3.0	0.0	0.0	0.0
concept	40.0	0.0	0.0	0.0	0.0
ingarden	33.0	3.0	3.0	1.0	0.0
uno	32.0	2.0	1.0	0.0	0.0

  
# 첫 번째 기간으로 나누어 비율로 만들어주고
# %가 나오도록 포맷팅을 해주고
# 색을 입혀준다

round(pivot_table_2.div(pivot_table_2[0], axis='index'), 2)\
    .style.format({k: '{:,.0%}'.format for k in pivot_table_2})\
    .background_gradient(cmap ='Blues', axis=None, vmax=0.2) 

duration	0	1	2	3	4
cohort
runail	100%	10%	5%	1%	0%
irisk	100%	7%	6%	2%	0%
masura	100%	6%	4%	3%	1%
grattol	100%	12%	8%	6%	0%
estel	100%	2%	0%	0%	4%
jessnail	100%	10%	0%	0%	0%
kapous	100%	8%	0%	0%	0%
concept	100%	0%	0%	0%	0%
ingarden	100%	9%	9%	3%	0%
uno	100%	6%	3%	0%	0%

가드레일 지표: 이탈률

  
guardrail = con.execute("""
WITH 
base AS (
-- 문자열을 날짜로 바꿔주기 위한 용도
SELECT
    user_id,
    STRFTIME('%m', DATE(SUBSTR(event_time, 1, 10))) + (STRFTIME('%Y', DATE(SUBSTR(event_time, 1, 10))) - 2019) * 12 AS event_month
FROM events
WHERE event_type = 'view'
)
,first_view AS (
-- 우선 사용자별로 최초 유입 월을 찾는다. 이게 코호트가 된다.
SELECT
    user_id,
    MIN(event_month) AS cohort
FROM base
GROUP BY user_id
)
,joinned AS (
-- 기존 데이터에 위에서 찾은 코호트를 조인해준다. 그리고 기존 이벤트 월과 코호트 월의 차이를 빼준다
SELECT
    t1.user_id,
    t2.cohort,
    t1.event_month,
    t1.event_month - t2.cohort AS month_diff
FROM base t1
LEFT JOIN first_view t2
ON t1.user_id = t2.user_id
)

-- (기준 코호트, 기준 코호트로부터의 경과주) 쌍을 만들어 고유한 사용자 수를 센다
SELECT
    cohort,
    month_diff,
    COUNT(DISTINCT user_id)
FROM joinned
GROUP BY cohort, month_diff
ORDER BY cohort ASC, month_diff ASC
""").fetchall()

  
# 데이터프레임으로 만들고
# 컬럼의 이름을 바꿔주고
# 피벗 기능을 이용해 코호트 테이블 형태로 만들어준다
# 빈 값은 0으로 채운다
pivot_table_3 = pd.DataFrame(guardrail)\
    .rename(columns={0: 'cohort', 1: 'duration', 2: 'value'})\
    .pivot(index='cohort', columns='duration', values='value')\
    .fillna(0)

pivot_table_3

duration	0	1	2	3	4
cohort
10	978.0	102.0	78.0	48.0	38.0
11	971.0	158.0	105.0	99.0	0.0
12	961.0	129.0	86.0	0.0	0.0
13	977.0	150.0	0.0	0.0	0.0
14	972.0	0.0	0.0	0.0	0.0

  
# 첫 번째 기간으로 나누어 비율로 만들어주고
# %가 나오도록 포맷팅을 해주고
# 색을 입혀준다

round(pivot_table_3.div(pivot_table_3[0], axis='index'), 2)\
    .style.format({k: '{:,.0%}'.format for k in pivot_table_3})\
    .background_gradient(cmap ='Blues', axis=None, vmax=0.2) 

duration	0	1	2	3	4
cohort
10	100%	10%	8%	5%	4%
11	100%	16%	11%	10%	0%
12	100%	13%	9%	0%	0%
13	100%	15%	0%	0%	0%
14	100%	0%	0%	0%	0%

  
con.close()

해석하기

메인지표

ingarden가 두번째 달에 15%로 가장 높은 유지율을 가지고 있다. 하지만 세번째 달이 지나면 3%로 유지율이 줄어드는 것을 확인할 수 있다. 타 브랜드에 비하여 10%p로 줄어들었다.(확인 필요)
2기간이 지나면 유지율이 5%이하로 낮아진다.

Action Plan

2기간동안 유지율을 높일 수 있는 방안 탐색. ex) 프로모션, 특정 제품 추가 분석 등
ingarden가 유지율이 높다가 두번째 달에 급격히 낮아지는 이유 탐색

가드레일 지표

다음달 이탈률이 84% ~ 90%까지 나온다. 이탈률이 급격히 낮아짐으로 메인보다 가드레일에 먼저 초점을 맞출 필요가 있다.

회고

데이터에서 상품을 본(View)기준으로 분석을 진행하다보니 정확한 분석과 해석은 힘들지만 코호트 분석을 어떻게 만들고 나타낼 수 있을 지 이해할 수 있게 되었다.

해석에서 어떻게 해석을 해야할 지 고민을 많이 해보았으나, 아직 잘 와닿지 않는 거 같다. 좀더 다양한 해석을 찾아보고 이해해보는 시간을 가져야 겠다.

Data Analysis, Data