사용자를 파악하기 위한 데이터 추출

데이터분석/PostgreSQL

사용자를 파악하기 위한 데이터 추출 - 2

환성 2023. 7. 5. 17:07

728x90

목차

1. RFM 분석에서 통합 랭크를 계산하는 쿼리

2. 날짜별 등록 수 추이

3. 지속률과 정착률 산출하기

4. 액션 수에 따른 정착률 집계하기

5. 사용 일수에 따른 정착률 집계하기

6. 사용자 잔존율 집계하기

7. 방문 빈도를 기반으로 사용자 속성을 정의하고 집계하기

실습에 필요한 테이블

RFM 분석에서 통합 랭크를 계산하는 쿼리

다음 사진과 같이 RFM 분석을 3차원으로 표현하면 125개의 그룹이 생겨 관리하기가 어려워진다.
따라서, 사용자를 1차원으로 구분하여 R+F+M값을 통합 랭크로 계산을 하는 방법이 있다.
이렇게 관리하게 되면 13개의 그룹으로 나누어 관리하게 되므로 보다 관리하기 수월해진다.

WITH
user_rfm AS (
	SELECT
        user_id
        , MAX(dt) AS recent_date
        , CURRENT_DATE - MAX(dt::date) AS recency
        , COUNT(dt) AS frequency
        , SUM(amount) AS monetary
    FROM
		purchase_log
	GROUP BY
		user_id
)
, user_rfm_rank AS (
	SELECT
            user_id
            , recent_date
            , recency
            , frequency
            , monetary
            , CASE
                WHEN recency < 14 THEN 5
                WHEN recency < 28 THEN 4
                WHEN recency < 60 THEN 3
                WHEN recency < 90 THEN 2
                ELSE 1
            END AS r
			, CASE
                WHEN 20 <= frequency THEN 5
                WHEN 10 <= frequency THEN 4
                WHEN 5 <= frequency THEN 3
                WHEN 2 <= frequency THEN 2
                WHEN 1 <= frequency THEN 1
            END AS f
			, CASE
                WHEN 300000 <= monetary THEN 5
                WHEN 100000 <= monetary THEN 4
                WHEN 30000 <= monetary THEN 3
                WHEN 5000 <= monetary THEN 2
                ELSE 1
            END AS m
    FROM 
        user_rfm
)
SELECT
    r + f + m AS total_rank
    , r, f, m
    , COUNT(user_id)
FROM
    user_rfm_rank
GROUP BY
    r, f, m
ORDER BY
    total_rank DESC, r DESC, f DESC, m DESC;

날짜별 등록 수 추이

사용자 수를 집계할 떄는 사용자을 유일하게 식별할 수 있는 ID(사용자 ID)로 중복을 제거해서 센다.

SELECT
	register_date
	, COUNT(DISTINCT user_id) AS register_count
FROM
	mst_users
GROUP BY
	register_date
ORDER BY
	register_date;

지속률과 정착률 산출하기

사용자가 등록하더라도 서비스를 지속해서 사용하지 않는 다면, 활용으로 이어지지 않는다.
소셜 게임을 예시로, 경쟁 상대가 없어져 지속 의욕이 감소하게 된다.
지속률 : 등록일 기준으로 이후 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표 (사용자 수 / 등록 수)
지속률 테이블

등록일	등록 수	6월 13일 사용자 수	6월 14일 사용자 수	6월 15일 사용자 수	다음날 지속률	2일 지속률	3일 지속률
6월 12일	150	80	60	40	80%	60%	40%
6월 13일	200	-	150	100	75%	50%	-
6월 14일	150	-	-	75	50%	-	-

정착률 : 등록일 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표(사용자 수 / 등록 수)
정착률 테이블

등록일	등록 수
6월 12일	100	6/13~6/19 사용자 수	6/20~6/26 사용자 수	7일 정착률	14일 정착률
6월 12일	100	80	60	80%	60%
6월 13일	200	6/14~6/20 사용자 수	6/20~6/26 사용자 수	7일 정착률	14일 정착률
6월 13일	200	150	100	75%	50%

지속률 : 사용자가 매일 사용했으면 하는 서비스(뉴스 사이트, 소셜 게임, SNS)
정착률 : 사용자가 어떤 목적이 생겼을 때 사용했으면 하는 서비스(EC 사이트, 리뷰 사이트, Q&A 사이트)
다음날 지속률을 계산하는 쿼리

WITH
action_log_with_mst_users AS (
    SELECT
        u.user_id
        , u.register_date
        -- 액션 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기
        , CAST(a.stamp AS date) AS action_date
        , MAX(CAST(a.stamp AS date)) OVER() AS latest_date

        -- 등록일 다음날의 날짜 계산하기
        , CAST(u.register_date::date + '1 day':: interval AS date)
        AS next_day_1
    FROM
        mst_users AS u
        LEFT OUTER JOIN
            action_log AS a
        ON u.user_id = a.user_id
)
, user_action_flag AS (
	SELECT
            user_id
            , register_date
            -- 4. 등록일 다음날에 액션을 했는지 안 했는지를 플래그로 나타내기
            , SIGN(
            -- 3. 사용자별로 등록일 다음날에 한 액션의 합계 구하기
                SUM(
                -- 1. 등록일 다음날이 로그의 최신 날짜 이전인지 확인하기
                    CASE WHEN next_day_1 <= latest_date THEN
				--2. 등록일 다음날의 날짜에 액션을 했다면 1, 안 했다면 0 지정하기
                    CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END
                END
                )
            ) AS next_1_day_action
)
SELECT
	register_date
	, AVG(100.0 * next_1_day_action) AS repeat_rate_1_day
FROM
	user_action_flag
GROUP BY
	register_date
ORDER BY
	register_date;

지속률 지표 관리하는 마스터 테이블 작성 쿼리

WITH
repeat_interval(index_name, interval_date) AS (
    VALUES
        ('01 day repeat', 1)
        , ('02 day repeat', 2)
        , ('03 day repeat', 3)
        , ('04 day repeat', 4)
        , ('05 day repeat', 5)
        , ('06 day repeat', 6)
        , ('07 day repeat', 7)
)
SELECT *
FROM repeat_interval
ORDER BY index_name;

정착률 지표를 관리하는 마스터 테이블 작성 쿼리

WITH
repeat_inverval(index_name, interval_begin_date, interval_end_date) AS (
    VALUES
        ('07 day retention', 1, 7)
        , ('14 day retention', 8, 14)
        , ('21 day retention', 15, 21)
        , ('28 day retention', 22, 28)
)
SELECT *
FROM repeat_interval
ORDER BY index_name;

정착률 계산하는 쿼리

WITH
repeat_inverval(index_name, interval_begin_date, interval_end_date) AS (
	VALUES
        ('07 day retention', 1, 7)
        , ('14 day retention', 8, 14)
        , ('21 day retention', 15, 21)
        , ('28 day retention', 22, 28)
), action_log_with_index_date AS (
	SELECT
        u.user_id
        , u.register_date
	-- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환
        , CAST(a.stamp AS date) AS action_date
        , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
        , r.index_name

	-- 지표와 대상 기간 시작일과 종료일 계산
        , CAST(u.register_date::date + '1 day'::interval * r.interval_begin_date AS date)
            AS index_begin_date
        , CAST(u.register_date::date + '1 day'::interval * r.interval_end_date AS date)
            AS index_end_date
    FROM
        mst_users AS u
    LEFT OUTER JOIN
        action_log AS a
        ON u.user_id = a.user_id
            CROSS JOIN
            repeat_interval AS r
)
, user_action_flag AS (
    SELECT
        user_id
        , register_date
        , index_name
        , SIGN
            SUM(
                CASE WHEN index_end_date <= latest_date THEN
                    CASE WHEN action_date BETWEEN index_begin_date AND index_end_date
                        THEN 1 ELSE 0
                    END
                END
                )
            ) AS index_date_action
    FROM
        action_log_with_index_date
    GROUP BY
        user_id, register_date, index_name, index_begin_date, index_end_date
)
    SELECT
        register_date

액션 수에 따른 정착률 집계하기

알 수도 있는 사람(페이스 북, 인스타그램), xxx님을 함께 알고 있습니다(페이스북) 화면 출력, 인기 사용자 팔로우 튜토리얼과 같은 것이다.
보다 사용자에게 서비스 유도 활성화하기 위해서이다.
액션의 계급 마스터와 사용자 액션 플래그의 조합을 산출하는 쿼리

WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
    VALUES ('14 day retention', 8, 14)
)
, action_log_with_index_date AS (
    SELECT
        u.user_id
        , u.register_date
    -- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환
        , CAST(a.stamp AS date) AS action_date
        , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
        , r.index_name

    -- 지표와 대상 기간 시작일과 종료일 계산
        , CAST(u.register_date::date + '1 day'::interval * r.interval_begin_date AS date)
            AS index_begin_date
        , CAST(u.register_date::date + '1 day'::interval * r.interval_end_date AS date)
            AS index_end_date
    FROM
        mst_users AS u
    LEFT OUTER JOIN
        action_log AS a
        ON u.user_id = a.user_id
            CROSS JOIN
            repeat_interval AS r
)
, user_action_flag AS (
SELECT
	user_id
    , register_date
    , index_name
    , SIGN(
        SUM(
            CASE WHEN index_end_date <= latest_date THEN
            CASE WHEN action_date BETWEEN index_begin_date AND index_end_date
            THEN 1 ELSE 0
            END
        END
		)
	)
)
, mst_action_bucket(action, min_count, max_count) AS ()
    VALUES
        ('comment', 0, 0)
        , ('comment', 1, 5)
        , ('comment', 6, 10)
        , ('comment', 11, 9999) -- 최댓값 9999로 설정
        , ('follow', 0, 0)
        , ('follow', 1, 5)
        , ('follow', 6, 10)
        , ('follow', 11, 9999) -- 최댓값 9999로 설정
)
, mst_user_action_bucket AS (
	-- 사용자 마스터와 액션 단계 마스터 조합하기
    SELECT
        u.user_id
        , u.register_date
        , a.action
        , a.min_count
        , a.max_count
    FROM
        mst_users AS u
    CROSS JOIN
        mst_action_bucket AS a
)
SELECT *
FROM
	mst_user_action_bucket
ORDER BY
	user_id, action, min_count;

사용 일수에 따른 정착률 집계하기

예시로 소셜 게임일 시 등록 직후 사용자에게 1일~5일 동안 연속 접속 로그인 보상 제공, 6일 쨰 굉장히 큰 보너스 지급하는 것이다.
등록일 다음날부터 7일 동안의 사용 일수와 28일 정착 플래그를 생성하는 쿼리

WITH
repeat_interval(index_name, interval_begin_date, interval_end_date) AS (
	VALUES ('28 day retention', 22, 28)
)
, action_log_with_index_date AS (
	SELECT
        u.user_id
        , u.register_date
        -- 액션의 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환
        , CAST(a.stamp AS date) AS action_date
        , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
        , r.index_name

    -- 지표와 대상 기간 시작일과 종료일 계산
    , CAST(u.register_date::date + '1 day'::interval * r.interval_begin_date AS date)
        AS index_begin_date
    , CAST(u.register_date::date + '1 day'::interval * r.interval_end_date AS date)
        AS index_end_date
    FROM
    	mst_users AS u
    LEFT OUTER JOIN
    	action_log AS a
        ON u.user_id = a.user_id
            CROSS JOIN
                repeat_interval AS r
)
, user_action_flag AS (
	SELECT
            user_id
            , register_date
            , index_name
            , SIGN(
                SUM(
                    CASE WHEN index_end_date <= latest_date THEN
                        CASE WHEN action_date BETWEEN index_begin_date AND index_end_date
                            THEN 1 ELSE 0
                        END
                    END
                    )
                )
            ) 
, register_flag AS (
	SELECT
		m.user_id
		, COUNT(DISTINCT CAST(a.stamp AS date)) AS dt_count
		, index_name
		, index_date_action
	FROM
		mst_users AS m
		LEFT JOIN
			action_log AS a
			ON m.user_id = a.user_id
			-- 등록 다음날부터 7일 이내의 액션 로그 결합하기
			AND CAST(a.stamp AS date)
				BETWEEN CAST(m.register_date AS date) + interval '1 day'
					AND CAST(m.register_date AS date) + interval '8 days'
		LEFT JOIN
			user_action_flag AS f
			ON m.user_id = f.user_id
		WHERE
			f.index_date_action IS NOT NULL
		GROUP BY
			m.user_id
		, f.index_name
		, f.index_date_action
)
SELECT *
FROM
	register_action_flag;

사용자 잔존율 집계하기

과거와 현재 비교하고 미래에 대한 목표 전망 검토할 수 있다.
가로 축에 등록일, 세로 축에 해당 월의 서비스 사용자 수
표를 만들어 문제점 확인
- 이전과 비교해 n개월 후의 잔존율이 내려간 경우
  - 신규 등록자가 서비스 서비스 장벽 확인한다.
- n개월 후에 잔존율이 갑자기 낮아지는 경향이 있는 경우
  - 서비스 사용 목적을 달성하는 기간이 예상보다 짧은지 확인한다
- 오래 사용하던 사용자인데도 특정 월을 기준으로 사용 하지 않게 된 경우
  - 사용자가 서비스 내부에서의 경쟁 등으로 빨리 지친것 확인한다

WITH
mst_intervals(interval_month) AS (
		-- 12개월 동안의 순번 만들기(generate_series로 대체)
	VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
)
, mst_users_with_index_month AS (
	SELECT
		u.user_id
	, u.register_date
	-- n개월 후의 날짜, 등록일, 등록 월 n개월 후의 월 계산
		, CAST(u.register_date::date + i.interval_month * '1 month'::interval AS date)
			AS index_date
		, substring(u.register_date, 1, 7) AS register_month
		, substring(CAST(u.register_date::date + i.interval_month * '1 month'::interval AS text)
								,1, 7) AS index_month
	FROM
		mst_users AS u
		CROSS JOIN
			mst_intervals AS i
)
, action_log_in_month AS (
	-- 액션 로그의 날짜에서 월 부분만 추출
	SELECT DISTINCT
		user_id
	, substring(stamp, 1, 7) AS action_month
	FROM
		action_log
)
SELECT
    -- 사용자 마스터과 액션 로그를 결합한 뒤, 월별로 잔존율 집계하기
    u.register_month
    , u.index_month
    -- action_month가 NULL이 아니라면 사용자 수 집계
    , SUM(CASE WHEN a.action_month IS NOT NULL THEN 1 ELSE 0 END) AS users
    , AVG(CASE WHEN a.action_month IS NOT NULL THEN 100.0 ELSE 0.0 END) AS retension_rate
FROM
	mst_users_with_index_month AS u
	LEFT JOIN
		action_log_in_month AS a
		ON u.user_id = a.user_id
		AND u.index_month = a.action_month
GROUP BY
	u.register_month, u.index_month
ORDER BY
	u.register_month, u.index_month;

방문 빈도를 기반으로 사용자 속성을 정의하고 집계하기

MAU : 특정 월에 서비스를 사용한 사용자 수를 말한다.
하지만 MAU만으로는 서비스 가치를 알 수 없다. 몇 명이 신규 사용자이고 기존 사용자인지 알 수 없기 떄문이다.
MAU 구성
- 신규 사용자 ; 이번에 등록한 사용자
- 리피트 사용자 : 이전에도 사용했던 사용자
- 컴백 사용자 : 한동안 사용하지 않았다가 돌아온 사용자

WITH
monthly_user_action AS (
	-- 월별 사용자 액션 집약하기
	SELECT DISTINCT
		u.user_id
		, substring(u.register_date, 1, 7) AS register_month
		, substring(l.stamp, 1, 7) AS action_month
		, substring(CAST(l.stamp::date - interval '1 month' AS text), 1, 7) AS action_month_priv
	FROM
		mst_users AS u
		JOIN
			action_log AS l
			ON u.user_id = l.user_id
)
, monthly_user_with_type AS (
-- 월별 사용자 분류 테이블
    SELECT
        action_month
        , user_id
        , CASE
        -- 등록 월과 액션월이 일치하면 신규 사용자
        WHEN register_month = action_month THEN 'new_user'
        -- 이전 월에 액션이 있으면 리피트 사용자
        WHEN action_month_priv = LAG(action_month)
        	OVER(PARTITION BY user_id ORDER BY action_month)
        	THEN 'repeat_user'
        -- 이외의 경우는 컴백 사용자
        	ELSE 'come_back_user'
        END AS c
, action_month_priv
	FROM
        monthly_user_action
)
SELECT
	action_month
	-- 특정 달의 MAU
	, COUNT(user_id) AS mau
	, COUNT(CASE WHEN c = 'new_user' THEN 1 END) AS new_users
	, COUNT(CASE WHEN c = 'repeat_user' THEN 1 END) AS repeat_users
	, COUNT(CASE WHEN c = 'come_back_user' THEN 1 END) AS come_back_users
FROM 
	monthly_user_with_type
GROUP BY
	action_month
ORDER BY
	action_month;

저작자표시 변경금지

'데이터분석 > PostgreSQL' 카테고리의 다른 글

데이터 활용의 정밀도를 높이는 분석 기술 (0)	2023.07.30
웹사이트에서의 행동을 파악하는 데이터 추출하기 (0)	2023.07.17
사용자를 파악하기 위한 데이터 추출 - 1 (0)	2023.07.05
시계열 기반으로 데이터 집계하기 (0)	2023.07.04
여러 개의 테이블에 대한 조작하기 (0)	2023.07.04

현재글사용자를 파악하기 위한 데이터 추출 - 2

컴공생의 개인공부일지

데이터 분석가가 되기위해

sql #postgresql, ADSP #DB, SQL #데이터리안 #데이터 분석 캠프, 머신러닝 #크롤링, postgresql #sql #데이터분석, postgresql #sql, UST, 알고리즘, tableau, R #통계학 #ML, wsl2 #docker, 벅스 #Bugs #워드클라우드, 데이터 #postgresql #sql, R #통계학 #컴퓨터공학 #ML, 파이썬, 소프트웨어공학, pyspark #Jupyter Lab #Docker, UST #인턴, SQLD #DB, 영어 #숙어 #idiom,

Today :
Yesterday :

컴공생의 개인공부일지

사용자를 파악하기 위한 데이터 추출 - 2

실습에 필요한 테이블

RFM 분석에서 통합 랭크를 계산하는 쿼리

날짜별 등록 수 추이

지속률과 정착률 산출하기

액션 수에 따른 정착률 집계하기

사용 일수에 따른 정착률 집계하기

사용자 잔존율 집계하기

방문 빈도를 기반으로 사용자 속성을 정의하고 집계하기

'데이터분석 > PostgreSQL' 카테고리의 다른 글

'데이터분석/PostgreSQL'의 다른글

티스토리툴바

« 2024/09 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

사용자를 파악하기 위한 데이터 추출 - 2

실습에 필요한 테이블

RFM 분석에서 통합 랭크를 계산하는 쿼리

날짜별 등록 수 추이

지속률과 정착률 산출하기

액션 수에 따른 정착률 집계하기

사용 일수에 따른 정착률 집계하기

사용자 잔존율 집계하기

방문 빈도를 기반으로 사용자 속성을 정의하고 집계하기

'데이터분석 > PostgreSQL' 카테고리의 다른 글

'데이터분석/PostgreSQL'의 다른글

관련글

티스토리툴바