새소식

인기 검색어

IT/SQL

[데이터 분석을 위한 SQL 레시피] 5장 12강 시계열에 따른 사용자 전체의 상태 변화 찾기_part2(p.275~p.302)

  • -
728x90
반응형

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

💡 가로 축에 등록일, 세로 축에 해당 월의 서비스 사용자 수를 집계하는 표의 장점
  • 이전과 비교해 n개월 후의 잔존율이 내려갔다면?
    • 신규 등록자가 서비스를 사용하기 위한 장벽이 높아지지는 않았는지 확인하기
  • n개월 후에 잔존율이 갑자기 낮아지는 경향이 있다면?
    • 서비스의 사용 목적을 달성하는 기간이 예상보다 너무 짧지는 않은지 확인하기
  • 오래 사용하던 사용자인데도 특정 월을 기준으로 사용하지 않게 되었다면?
    • 사용자가 서비스 내부에서의 경쟁 등으로 빨리 지친 것은 아닌지 확인하기

 

코드 12-21. 12개월 후까지의 월을 도출하기 위한 보조 테이블을 만드는 쿼리

WITH
mst_intervals(interval_month) AS (

 -- 12개월 동안의 순번 만들기(generate_series등으로 대체 가능) - with문을 지워야만 오류X
 -- SELECT *
 -- FROM generate_series(1, 12) AS interval_month
 -- int : generate_series(start, stop)
 -- int : generate_series(start, stop, step)
 -- timestamp : generate_series(start, stop, step interval)
 -- start 값 부터 stop 값 까지 차례대로 증감하는 일련의 집합을 리턴해준다.
 -- step 파라미터를 적지 않으면 자동으로 1증가, 양수 음수 둘 다 입력 가능하다.

 -- ■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 만들기
 -- 8강 5절 참고하기
 VALUES (1), (2), (3), (4), (5), (6)
      , (7), (8), (9), (10), (11), (12)
)
SELECT *
FROM mst_intervals

 

코드 12-22. 등록 월에서 12개월 후까지의 잔존율을 집계하는 쿼리

WITH
mst_intervals AS (
 --[코드 12-21] 참고하기
WITH
mst_intervals(interval_month) AS (
 -- 12개월 동안의 순번 만들기(generate_series등으로 대체 가능)
 -- ■ PostgreSQL의 경우 VALUES 구문으로 일시 테이블 만들기
 -- 8강 5절 참고하기
 VALUES (1), (2), (3), (4), (5), (6)
      , (7), (8), (9), (10), (11), (12)
)
SELECT *
FROM mst_intervals)
, mst_users_with_index_month AS (
  -- 사용자 마스터에 등록 월부터 12개월 후까지의 월을 추가하기
  SELECT
      u.user_id
    , u.register_date
    -- n개월 후의 날짜, 등록일, 등록 월 n개월 후의 월 계산하기
    -- ■ PostgreSQL의 경우 다음과 같이 사용하기
    , 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(str, pos, len): str에서 pos번째 위치에서 len개의 문자를 읽어들인다.
    , 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 --중복제거 키워드인 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

코드 12-22 실행 결과

 

💡 substring(str, pos, len)
     : str에서 pos번째 위치에서 len개의 문자를 읽어 들인다.
💡 "ON" 키워드는 "JOIN" 절과 함께 사용되어 두 테이블 간의 조인되는 조건을 지정함.
     따라서 여러 열, 함수 또는 식과 관련된 복잡한 조인 조건을 지정할 수 있습니다. 특정 기준에 따라 테이블을
     결합하는 유연성을 제공하여 데이터베이스 쿼리에서 원하는 결과를 검색 가능.

     데이터 필터링을 위해 "WHERE" 절에서 사용할 수 있는 다른 유형의 조건과 조인 조건을 구분함.
💡 매일 확인해야 할 리포트는 아니지만, 장기적인 관점에서 사용자 등록과 지속 사용을 파악할 때는 굉장히
    유용하게 활용할 수 있음.
    참고로 이러한 리포트를 작성할 때는 해당 월에 실시한 대책 또는 캠페인 등의 이벤트를 함께 기록하면, 수치
    변화의 원인 등도 쉽게 파악할 수 있어서 더 효율적으로 활용할 수 있음.

 

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

코드 12-23. 신규 사용자 수, 리피트 사용자 수, 컴백 사용자 수를 집계하기

💡 MAU: 월간 사용자 수(Monthly Active Users)
  • 월 사용자 수
  • 세부 분석 필요: MAU 중 신규 가입자는 누구인지

예시 사진: 유의미한 데이터는 MAU를 더 분석해야 나올 수도 있다!

  • 신규 사용자 : 이번 달에 등록한 신규 사용자
  • 컴백 사용자: 한동안 사용하지 않다가 복귀한 사용자
  • 리피트 사용자: 이전 달에도 사용했던 사용자

 

예시 사진: MAU 유형으로 분류하면 더 효과적인 서비스 개선이 가능!

 

코드 12-23. 신규 사용자 수, 리피트 사용자 수, 컴백 사용자 수를 집계하는 쿼리

코드 12-23 실행 결과

.

WITH

monthly_user_action AS( 
  --월별 사용자 액션 집약하기: MAU
  SELECT DISTINCT 
  u.user_id
    , substring(u.register_date, 1, 7) AS register_month -- 일자 중 연, 월만 받기 위해 substring 사용
    , 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) --ㅣlag(월) ==> 이전 월('월' 컬럼의 윗 행)
                  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

  -- ==========================
  -- new_users             : 특정 달의 신규 사용자 수
  -- repeat_users          : 특정 달의 리피트 사용자 수
  -- come_back_users       : 특정 달의 컴백 사용자 수
  -- ==========================
  , 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
;

주요 문법

substring(str, position, length)

  • str테이블의 position부터(0이 아닌 1부터 시작) length글자까지 가져오기

lag(column)

  • 그 컬럼에서 이전 row의 값을 가져오기

interval 타입 참고자료: https://ysyblog.tistory.com/136

 

코드 12-24. 리피트 사용자를 세분화해서 집계하는 쿼리

지금까지 전체 사용자를 3가지로 분류했다. 그런데 리피트 사용자 역시 3가지로 분류할 수 있다.

  • 신규 리피트 사용자: 이전 달 신규, 이번 달 사용 유지
  • 기존 리피트 사용자: 이전 달 유지, 이번 달 사용 유지
  • 컴백 리피트 사용자: 이전 달 컴백, 이번 달 사용 유지

코드 12-24 실행 결과

 

해석:

  • 월별 MAU의 카테고리 분류가 더 상세. 우측 세 column들의 값의 합이 repeat_users임을 확인.
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  
)
, monthly_users AS ( 
  SELECT 
    m1.action_month

    , count(m1.user_id) AS mau
    , count(CASE WHEN m1.c = 'new_user'      THEN 1 END) AS new_users 
    , count(CASE WHEN m1.c = 'repeat_user'   THEN 1 END) AS repeat_users 
    , count(CASE WHEN m1.c = 'come_back_user'THEN 1 END) AS come_back_users 
-- 여기서부터 리피트 사용자의 세분화
    , count(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'new_user' THEN 1 END) 
AS new_repeat_users  -- 신규 리피트
    , count(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'repeat_user' THEN 1 END) 
AS continuous_repeat_users -- 기존 리피트
    , count(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'come_back_user' THEN 1  end) 
AS come_back_repeat_users -- 컴백 리피트

    FROM 
      -- m1: 해당 월의 사용자 분류 테이블
      monthly_user_with_type AS m1
      LEFT OUTER JOIN
      -- m0: 이전 달의 사용자 분류 테이블
      monthly_user_with_type AS m0
      ON m1.user_id = m0.user_id
      AND m1.action_month_priv = m0.action_month
    GROUP BY 
      m1.action_month
    )
    SELECT 
    *
    FROM 
      monthly_users
    ORDER BY 
      action_month
    ;

 

코드 12-25. MAU 내역과 MAU 속성들의 반복률을 계산하는 쿼리

💡 반복률: 리피트 사용자로 유지 / 전환된 비율

코드 12-25. 반복률이 추가되는 것을 제외하면 코드 12-24와 동일하므로 이전 컬럼들은 생략하였습니다.

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  
)
, monthly_users AS ( 
  SELECT 
    m1.action_month

    , count(m1.user_id) AS mau
    , count(CASE WHEN m1.c = 'new_user'      THEN 1 END) AS new_users -- 신규 리피트
    , count(CASE WHEN m1.c = 'repeat_user'   THEN 1 END) AS repeat_users -- 기존 리피트
    , count(CASE WHEN m1.c = 'come_back_user'THEN 1 END) AS come_back_users -- 컴백 리피트
    , count(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'new_user' THEN 1 END) 
            AS new_repeat_users
    , count(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'repeat_user' THEN 1 END) 
            AS continuous_repeat_users
    , count(CASE WHEN m1.c = 'repeat_user' AND m0.c = 'come_back_user' THEN 1  end) 
            AS come_back_repeat_users

    FROM 
      -- m1: 해당 월의 사용자 분류 테이블
      monthly_user_with_type AS m1
      LEFT OUTER JOIN
      -- m0: 이전 달의 사용자 분류 테이블
      monthly_user_with_type AS m0
      ON m1.user_id = m0.user_id
      AND m1.action_month_priv = m0.action_month
    GROUP BY 
      m1.action_month
    )
SELECT 
    action_month
  , mau
  , new_users
  , repeat_users
  , come_back_users
  , new_repeat_users
  , continuous_repeat_users
  , come_back_repeat_users
  --  신규 --> 리피트 비율
  , 100.0*
  new_repeat_users / NULLIF(LAG(new_users) over(ORDER BY action_month), 0)
  AS priv_new_repeat_ratio

  -- 리피트 --> 리피트 비율
  , 100.0 * 
  continuous_repeat_users / NULLIF(LAG(new_users) over(ORDER BY action_month), 0)
  AS priv_continuous_repeat_users

  -- 컴백 --> 리피트 비율
  , 100.0 * 
  come_back_repeat_users / NULLIF(LAG(new_users) OVER(ORDER BY action_month), 0)
  AS priv_come_back_repeat_ratio

FROM 
  monthly_users
ORDER BY
  action_month
  ;

 

8. 방문 종류를 기반으로 성장지수 집계하기

💡 SQL : CAST 식, LAG 함수, SUM(CASE ~)
     분석 : 성장지수
🔖 성장지수를 집계할 때는 서비스의 특성에 맞게 날짜별, 주차, 월차 등의 적절한 집계 기간을 선택해야한다.

 

성장지수

 

성장지수 집계하기

성장지수 집계하기

  • 신규 등록인가(is_new)
  • 탈퇴 회원인가(is_exit)
  • 특정 날짜에 서비스에 접근했는가(is_access)
  • 전날 서비스에 접근했는가(was_access)

 

코드 12-26. 성장지수 산출을 위해 사용자 상태를 집계하는 쿼리

WITH
unigue_action_log AS (
    -- 같은 날짜 로그를중복해 세지 않도록 중복 배제하기
    SELECT DISTINCT user_id
                    , substring(stamp, 1, 10) AS action_date
    FROM action_log
)
, mst_calendar AS (
-- 집계하고 싶은 기간을 캘린더 테이블로 만들어두기
-- generate_series 등으로 동적 생성도 가능
    SELECT '2016-10-01' AS dt
    UNION ALL SELECT '2016-10-02' AS dt
    UNION ALL SELECT '2016-10-03' AS dt
    -- 생략
    UNION ALL SELECT '2016-10-04' AS dt
)
, target_date_with_user AS (
    -- 사용자 마스터에 캘린더 테이블의 날짜를 target_date로 추가하기
    SELECT    c.dt AS target_date
            , u.user_id
            , u.register_date
            , u.withdraw_date
    FROM mst_users AS u
        CROSS JOIN
         mst_calendar AS c
)
, user_status_log AS (
    SELECT
          u.target_date
        , u.user_id 
        , u.register_date 
        , u.withdraw_date 
        , a.action_date
        , CASE WHEN u.register_date = a.action_date THEN 1 ELSE 0 END AS is_new
        , CASE WHEN u.withdraw_date  = a.action_date THEN 1 ELSE 0 END AS is_exit
        , CASE WHEN u.target_date = a.action_date THEN 1 ELSE 0 END AS is_access
        , LAG(CASE WHEN u.target_date = a.action_date THEN 1 ELSE 0 END)
         OVER(
             PARTITION BY u.user_id
             ORDER BY u.target_date
             ) AS was_access
    FROM target_date_with_user AS u
        LEFT JOIN
         unigue_action_log AS a
         ON u.user_id = a.user_id
         AND u.target_date = a.action_date
    WHERE
        -- 집계 기간을 등록일 이후로만 필터링하기
        u.register_date  <= u.target_date
        -- 탈퇴 날짜가 포함되어 있으면, 집계 기간을 탈퇴 날짜 이전만으로 필터링하기
    AND (
        u.withdraw_date  IS NULL 
        OR u.target_date <= u.withdraw_date 
    )
)
SELECT
    target_date
    , user_id 
    , is_new
    , is_exit
    , is_access
    , was_access
FROM user_status_log
;

 

코드 12-27. 매일의 성장지수를 계산하는 쿼리

  • Signup, Reactivation, Deactivation, Exit, Growth_index 를 날짜별로 계산
WITH
unigue_action_log AS (
    -- [코드 12-26] 참고하기
)
, mst_calendar AS (
    -- [코드 12-26] 참고하기
)
, target_date_with_user AS (
        -- [코드 12-26] 참고하기
)
, user_status_log AS (
        -- [코드 12-26] 참고하기
)
, user_growth_index AS (
    SELECT *
    , CASE 
        -- 어떤 날짜에 신규 등록 또는 탈퇴한 경우 signup 또는 exit으로 판정하기
        WHEN is_new + is_exit = 1 THEN
            CASE
                WHEN is_new = 1 THEN 'Singup'
                WHEN is_exit = 1 THEN 'Exit'
            END
        -- 신규 등록과 탈퇴가 아닌 경우 Reactivation 또는 Deactivation으로 판정하기
        -- 이때 Reactivation, Deactivation의 정의에 맞지 않는 경우는 NULL로 지정
        WHEN is_new + is_exit = 0 THEN
            CASE
                WHEN was_access = 0 AND is_access = 1 THEN 'Reactivation'
                WHEN was_access = 1 AND is_access = 0 THEN 'Deactivation'
            END
            -- 어떤 날짜에 신규 등록과 타퇴를 함께 했다면(is_new + is_exit =2 ) NULL로 지정
            END AS growth_index
    FROM user_status_log
)
SELECT
    target_date
    , SUM(CASE growth_index WHEN 'Signup' THEN 1 ELSE 0 END) AS Signup
    , SUM(CASE growth_index WHEN 'Reactivation' THEN 1 ELSE 0 END) AS Reactivation
    , SUM(CASE growth_index WHEN 'Deactivation' THEN 1 ELSE 0 END) AS Deactivation
    , SUM(CASE growth_index WHEN 'Exit' THEN 1 ELSE 0 END) AS EXIT
    -- 성장지수 정의에 계산하기
    , SUM(CASE growth_index
            WHEN 'Signup' THEN 1
            WHEN 'Reactivation' THEN 1
            WHEN 'Deactivation' THEN -1
            WHEN 'Exit' THEN -1
            ELSE 0
        END
        ) AS growth_index
FROM user_growth_index
GROUP BY target_date
ORDER BY target_date
;

서비스 런칭 때부터의 성장지수 추이

  • 런칭 시점에 맞춰 광고하면 쉽게 사용자를 획득할 수 있지만, 일시적이고 지속하지 못함
  • 🤔그럼 어떻게 사용자를 늘릴 수 있을까? ⇒ 서비스(제품)으로 성장, 미디어XXXXX

 

9. 지표 개선 방법 익히기(2p)

💡 우리의 목표는 매출과 사용자 수를 늘리는 것!

12강 3절 에서 ‘어떤 액션이 영향을 주었는지 확인하는 것’이 포인트

글의 업로드와 댓글 수를 늘리고 싶은 경우

  • 팔로우 수에 따라 차이가 있는가?
  • 팔로워 수에 따라 차이가 있는가?
  • 프로필 사진을 등록한 사람에 따라 차이가 있는가?

신규 사용자의 리피트율(충성고객)을 개선하고 싶은 경우

  • 등록한 달에 올린 글의 수에 따라 차이가 있는가?
  • 등록한 달에 팔로우한 사람 수에 따라 차이가 있는가?
  • 등록 다음날부터 7일 이내의 사용 일수에 따라 차이가 있는가?

CVR(전환율)을 개선하고 싶은 경우

  • 구매 전에 상세 페이지를 본 횟수에 따라 차이가 있는가?
  • 매 전에 관심 상품 기능 사용 여부에 따라 차이가 있는가?
728x90

'IT > SQL' 카테고리의 다른 글

[데이터 분석을 위한 SQL 레시피] 6장 15강 사이트 내의 사용자 행동 파악하기  (0) 2023.06.19
[데이터 분석을 위한 SQL 레시피] 6장 13강,14강  (1) 2023.06.19
[데이터 분석을 위한 SQL 레시피] 5장 12강 시계열에 따른 사용자 전체의 상태 변화 찾기_part1(p.233~p.275)  (0) 2023.06.17
[데이터 분석을 위한 SQL 레시피] 5장 11강. 사용자 전체의 특징과 경향 찾기  (0) 2023.06.17
[데이터 분석을 위한 SQL 레시피] 4장 9강, 10강  (0) 2023.06.17
Contents

당신이 좋아할만한 콘텐츠

댓글 0

포스팅 주소를 복사했습니다

이 글이 도움이 되었다면 공감 부탁드립니다.

SHARE

DARK

MENU

티스토리툴바