[토스 | SLASH 24 리뷰] 활성 사용자 데이터 분석을 위한 DW 설계하기

DISCLAIMER

본 아티클은 아래의 발표 영상을 개인적으로 공부하며 정리한 내용입니다. 외부인으로서 내부 D/W와 데이터 분석 현황을 전혀 모르기 때문에, 정확성을 담보할 수 없으며 개인 해석이 가미될 수 있습니다. 영상을 직접 시청하시는 것을 추천 드립니다.

Table of Contents

0. 서론
1. 토스증권에서 자주 인입되는 데이터 요청 유형
2. 기존 Active Users (AU) Pipeline
  2.1. Silver Layer: AU 테이블 세트
  2.2. au_list, au_list_week, au_list_month 테이블
  2.3. au_first, au_last 테이블
  2.4. s_au 테이블
3. 문제점
  3.1. 테이블과 칼럼의 네이밍 컨벤션이 불친절했다.
  3.2. 각 act_type 별 특수한 지표를 담아내지 못했다.
  3.3. 복귀, 이탈 사용자 수를 집계할 만한 정보를 담지 못했다.
4. Active Users (AU) Pipeline 2.0
  4.1. [문제 1] 테이블과 칼럼의 네이밍 컨벤션이 불친절했다.
  4.2. [문제 2] 각 act_type 별 특수한 지표를 담아내지 못했다.
  4.3. [문제 3] 복귀, 이탈 사용자 수를 집계할 만한 정보를 담지 못했다.
5. AU Pipeline 2.0 테이블 사용 방법 사례
  5.1. 2024년 1월 동안 주식 매매한 기록
  5.2. 2024년 1월 동안 커뮤니티 글을 쓴 기록
  5.3. 2024년 1월 동안 주식 매매 일간 사용자 수
  5.4. 2024년 1월 동안 커뮤니티 글 작성 일간 사용자 수
  5.5. 2024년 1월 동안 주식 매매 월간 사용자 수 및 거래금액
  5.6. 2024년 1월 동안 일간 신규 주식 매매 사용자 수 (seq 활용)
  5.7. 2024년 1월 동안 "30일 이상 이탈했다가 돌아온" 복귀 주식 매매 일간 사용자 수 (retain_period 활용)
  5.8. 2024년 1월 동안 활성화했지만 "30일 이상 이탈 중인" 주식 매매 일간 사용자 수 (churn_period 활용)
  5.9. Main Funnel(증권 앱 방문 → 종목 상세 페이지 방문 → 매매)
6. 앞으로 남은 과제
  6.1. 신규로 act_type을 쉽게 추가할 수 있는 Admin 화면 구축하기
  6.2. AU 테이블들을 통해 SQL LLM 봇 만들기

0. 서론

데이터 모델링과 D/W 개발과 관련된 이론 습득은 개인적인 공부로 어느 정도 실력을 키울 수 있었지만, 실제로 회사에서 “편하게 지표를 추출할 수 있는 환경”을 만들기 위한 케이스 스터디를 하기가 어려웠는데요. D/W 관련된 세미나 중 가장 흥미롭고 실질적으로 도움이 된 영상이었습니다.

개인적으로도 앱 방문 사용자 수 하나의 지표만 보는 것이 아니라, 앱의 건전성과 지속 가능성을 평가하기 위해 다음으로 분류해야 한다는 중요성을 체감하고 있습니다.

(1) 방문 사용자 수를 신규/기존/복귀/이탈로 분류하기

• 기존/복귀/이탈로 분류하기 위한 Threshold Days (N) 기준 세우기

(2) 단순히 방문이 아니라, Key Events를 기준으로 “활성” 사용자 수를 확인하기

• 인앱 매출을 향하는 퍼널 내의 이벤트들

이런 분석을 하기 위해서는 꽤 복잡한 쿼리를 작성해야 합니다. 제가 예전에 적었던 아래 아티클을 보시면 이 쿼리가 얼마나 복잡하고 DQ(Data Quality) 관리가 어려운지 느끼실 수 있을 것입니다.

User Movement 지표 쿼리 작성하기

이번 영상을 통해, 사전에 “활성” 사용자에 대해 딥다이브를 편하게 하기 위한 D/W를 어떤 구조로 만들어야 하는지 명확하게 이해할 수 있었는데요. 복습하는 마음으로 자세히 정리하며, 중간 중간 개인적인 생각들도 짧게 정리해봤습니다.

1. 토스증권에서 자주 인입되는 데이터 요청 유형

토스증권의 데이터 사용자들은 “활성”과 관련해서 아래와 같은 요청이 많았습니다.

• “사용자가 언제 방문했나요?”
• “Daily, Weekly, Monthly, Trailing 7 Days, Trailing 30 Days 단위로는 사용자 수가 얼마나 되나요?”
• “각 사용자가 이탈/복귀 중 어떤 카테고리에 속하나요?”
• 복귀: “며칠 만에 복귀했다고 가정한 건가요?” (Threshold Days: N)
• 이탈: “며칠 동안 이탈 중이라고 가정한 건가요?” (Threshold Days: N)

2. 기존 Active Users (AU) Pipeline

필자 작성

2.1. Silver Layer: AU 테이블 세트

먼저 토스증권에서 규정한 Act Type의 종류는 다음과 같습니다.

필자 작성

AU 테이블 세트는 각 act_type 별로 Fact Table들로 구성됩니다. 즉, 특정 이벤트마다 활성화된 raw 테이블들인 것입니다.

필자 작성

2.2. au_list, au_list_week, au_list_month 테이블

AU 테이블 세트가 각 사용자의 모든 Logs를 레코드로 적재하고 있음에 반해, au_list(_*) 테이블은 daily, weekly, monthly 간격마다 사용자 수, 이벤트 수 등을 집계하여 적재합니다.

au_list는 daily 간격, au_list_week은 weekly 간격, au_list_month는 monthly 간격으로 사용자 활성 현황을 보여줍니다.

• (1) AU 데이트 세트로부터 증분 전략을 통해 매 배치마다 au_list_(*) 테이블 insert를 하고,
• (2) au_list_(*) 테이블로부터 DATE_TRUNC() 등을 적용하여 au_list_week, au_list_month 테이블 insert/update를 합니다.

필자 작성

개인적으로 이 테이블은 각 사용자의 Dimension이나 act_type의 상세 파라미터 정보를 활용할 수 없겠다는 생각을 했습니다.

• (1) 집계 과정에서 ukey 식별자가 사라졌으므로, 각 사용자 코호트 별로 쪼개기 어렵기 때문입니다.
• (2) 집계 과정에서 act_type의 상세 파라미터 정보 역시 사라졌으므로, 상세 행동 분석을 하기 어렵기 때문입니다

2.3. au_first, au_last 테이블

au_first 테이블은 act_type 별로 각 사용자가 최초로 활성화된 레코드만 적재한 테이블이며, au_last 테이블은 최근에 활성화된 레코드만 적재한 테이블입니다. 정확한 테이블 명세서는 알 수 없지만, 가령 다음과 같은 질의에 대해 응답하기 위한 목적을 지닐 것으로 추측해봅니다.

• “2025년 1월 한 달 동안 증권 앱 방문한 신규 사용자가 몇 명인가요?”
• “2025년 1월 각 일자 별로 증권 앱에 마지막으로 활성화된 사용자가 몇 명인가요?”

2.4. s_au 테이블

au_list_week, au_list_month 테이블이 각 week과 month를 Static하게 잘라 활성 현황을 보여줬다면, s_au 테이블은 rolling하게 활성 현황을 보여줍니다. 즉, trailing 7 days, trailing 30 days로 집계한 것으로 보입니다. au_list 테이블에만 의존하여 집계하면 될 것 같은데, au_first 테이블에도 의존하신 배경은 정확히 잘 모르겠네요.

3. 문제점

3.1. 테이블과 칼럼의 네이밍 컨벤션이 불친절했다.

데이터를 활용하는 사용자들이 au_list 테이블은 잘 활용하고 있었으나, au_list_week, au_list_month 테이블도 존재한다는 사실을 잘 모르는 경우가 많았다고 합니다.

3.2. 각 act_type 별 특수한 지표를 담아내지 못했다.

가령, 매매 활성화라면 거래액이나 수수료 관련 지표도 분석해야 할텐데, 기존의 구조로는 Silver 테이블에 의존해야 하므로 쿼리 실행 시간 측면에서 상당한 리소스 소요로 이어지고 있었을 것으로 보입니다.

3.3. 복귀, 이탈 사용자 수를 집계할 만한 정보를 담지 못했다.

PO나 데이터 분석가가 복귀와 이탈의 기준인 “N일”을 규정한 후, 이를 Silver Raw 테이블에서 가공해야 했었을 것으로 보입니다. 이 과정에서 쿼리 실행 시간이 소요되거나, 각 사용자마다 다른 결과를 낳아 데이터 신뢰성 문제가 생기기 쉬웠을 것 같습니다.

4. Active Users (AU) Pipeline 2.0

4.1. [문제 1] 테이블과 칼럼의 네이밍 컨벤션이 불친절했다.

네이밍 규칙을 다음과 같이 만들고 적용함으로써 해결했습니다.

필자 작성

• m_: 여러 가지 주제를 담고 있다는 의미를 Prefix로 달아줌 (마트성 테이블)
• 액트타입_모음: 테이블이 어떤 영역의 데이터를 담고 있는지 명시함 (모든 act_type을 담았음)
• __ukey__: 어떤 Dimension 레벨로 집계가 되었는지 명시함 (GROUP BY 절에 들어가는 칼럼을 Underscore 2개로 표시)
• daily: 집계 기간 기준을 명시함

4.2. [문제 2] 각 act_type 별 특수한 지표를 담아내지 못했다.

앞서 살펴봤던 것처럼, au_list_(*) 테이블에는 사용자 수(user_cnt), 이벤트 수(act_cnt) 등의 집계 결과만 확인할 수 있습니다. 즉, 아래와 같은 심층 분석을 할 수 없어 확장성이 떨어진 상황이었습니다. (각 act_type 별로 특수하게 집계해야 하는 칼럼 정보를 반영한 분석이 어려웠죠.)

• AAPL 종목을 매수한 사용자 수는 몇 명인가요?
• 국내 종목 상세 페이지에 방문한 사용자 수는 몇 명인가요?
• 등등

이를 개선하기 위해, 이미 집계가 끝난 au_list_(*) 테이블이 아닌, 각 act_type 별로 각각 마트성 Fact Tables를 구축했습니다.

필자 작성

(Case 1) f_stk_visit__ukey__daily

필자 작성

• 각 사용자가 방문한 종목의 Unique 개수, KR/US 비중 등을 조회할 수 있어요.

(Case 2) f_stk_log__ukey__daily

필자 작성

• 각 사용자가 매매한 종목의 Unique 개수, 거래금액, 수수료 등을 조회할 수 있어요.

(Case 3) f_comm_record__ukey__daily

필자 작성

• 각 사용자가 활성화된 Board 개수, 작성한 게시글 개수, 댓글 개수 등을 조회할 수 있어요.

4.3. [문제 3] 복귀, 이탈 사용자 수를 집계할 만한 정보를 담지 못했다.

개인적으로 가장 감탄했던 해결 방향이었는데요. 다음과 같이, 각 act_type 별로 여러 가지 measures를 담은 Dimension 정보를 담은 테이블들을 신규 생성했습니다.

• f_stk_log__ukey__detail_daily
• f_stk_log__ukey__detail_weekly
• f_stk_log__ukey__detail_monthly

필자 작성

• seq: 각 사용자의 방문 Sequence를 의미합니다. (activation_date 기준의 순서)
• prev_activation_date: 직전 activation_date를 의미합니다.
• retain_period: activation_date - prev_activation_date -1을 의미합니다. (활성화된 시점에 그동안 유지되고 있었던 기간)
• next_activation_date: 직후 activation_date를 의미합니다.
• churn_period: next_activation_date - activation_date - 1을 의미합니다. (활성화된 시점 이후부터 비활성화 중인 기간)
• ls_last: 최근 활성화 시점 여부를 의미합니다.

retain_period, churn_period, seq를 통해 PO나 데이터 분석가가 자유롭게 신규/이탈/복귀 기준을 세워 동적으로 활성 사용자를 분류할 수 있을 것입니다.

5. AU Pipeline 2.0 테이블 사용 방법 사례

아래 쿼리 사례와 같이, 데이터를 조회할 때 쿼리 작성의 일관성이 크게 향상된 것을 확인할 수 있습니다. 즉, 쿼리 재사용성을 높인 것입니다.

5.1. 2024년 1월 동안 주식 매매한 기록

SELECT
  ukey, activation_ts, act_date
FROM
  f_stk_log
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'

5.2. 2024년 1월 동안 커뮤니티 글을 쓴 기록

SELECT
  ukey, activation_ts, act_date
FROM
  f_comm_record
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'

5.3. 2024년 1월 동안 주식 매매 일간 사용자 수

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  f_stk_log__ukey__daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
GROUP BY
  1

-- 또는

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  m_액트타입_모음__ukey__daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
  AND act_type = 'stk_log'
GROUP BY
  1

5.4. 2024년 1월 동안 커뮤니티 글 작성 일간 사용자 수

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  f_comm_record__ukey__daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
GROUP BY
  1

-- 또는

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  m_액트타입_모음__ukey__daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
  AND act_type = 'comm_record'
GROUP BY
  1

5.5. 2024년 1월 동안 주식 매매 월간 사용자 수 및 거래금액

SELECT
  act_date,
  COUNT(*) AS mau,
  SUM(amount) AS amount
FROM
  f_stk_log__ukey__monthly
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
GROUP BY
  1

5.6. 2024년 1월 동안 일간 신규 주식 매매 사용자 수 (seq 활용)

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  f_stk_log__ukey__detailed_daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'
  AND seq = 1  
GROUP BY
  1

-- 또는

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  m_액트타입_모음__ukey__detailed_daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
  AND act_type = 'stk_log'
  AND seq = 1
GROUP BY
  1

5.7. 2024년 1월 동안 “30일 이상 이탈했다가 돌아온” 복귀 주식 매매 일간 사용자 수 (retain_period 활용)

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  f_stk_log__ukey__detailed_daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'
  AND retain_period >= 30
GROUP BY
  1

-- 또는

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  m_액트타입_모음__ukey__detailed_daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
  AND act_type = 'stk_log'
  AND retain_period >= 30
GROUP BY
  1

5.8. 2024년 1월 동안 활성화했지만 “30일 이상 이탈 중인” 주식 매매 일간 사용자 수 (churn_period 활용)

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  f_stk_log__ukey__detailed_daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'
  AND churn_period >= 30
GROUP BY
  1

-- 또는

SELECT
  act_date,
  COUNT(*) AS dau
FROM
  m_액트타입_모음__ukey__detailed_daily
WHERE
  '2024-01-01' <= act_date
  AND act_date < '2024-02-01'  
  AND act_type = 'stk_log'
  AND churn_period >= 30
GROUP BY
  1

5.9. Main Funnel (증권 앱 방문 → 종목 상세 페이지 방문 → 매매)

-- 일간 Funnel
SELECT
  act_date,
  SUM(CASE WHEN act_type = 'se_visit' THEN 1 ELSE 0 END) AS securities_visit_user_cnt,
  SUM(CASE WHEN act_type = 'stk_visit' THEN 1 ELSE 0 END) AS stock_visit_user_cnt,
  SUM(CASE WHEN act_type = 'stk_log' THEN 1 ELSE 0 END) AS stock_transaction_user_cnt
FROM
  m_액트타입_모음__ukey__daily
WHERE
  act_type IN (
    'se_visit',
    'stk_visit',
    'stk_log'
  )
GROUP BY
  1

-- 월간 Funnel
SELECT
  act_date,
  SUM(CASE WHEN act_type = 'se_visit' THEN 1 ELSE 0 END) AS securities_visit_user_cnt,
  SUM(CASE WHEN act_type = 'stk_visit' THEN 1 ELSE 0 END) AS stock_visit_user_cnt,
  SUM(CASE WHEN act_type = 'stk_log' THEN 1 ELSE 0 END) AS stock_transaction_user_cnt
FROM
  m_액트타입_모음__ukey__monthly
WHERE
  act_type IN (
    'se_visit',
    'stk_visit',
    'stk_log'
  )
GROUP BY
  1

-- Trailing 30 Days Funnel
SELECT
  act_date,
  SUM(CASE WHEN act_type = 'se_visit' THEN 1 ELSE 0 END) AS securities_visit_user_cnt,
  SUM(CASE WHEN act_type = 'stk_visit' THEN 1 ELSE 0 END) AS stock_visit_user_cnt,
  SUM(CASE WHEN act_type = 'stk_log' THEN 1 ELSE 0 END) AS stock_transaction_user_cnt
FROM
  m_액트타입_모음__ukey__trailing30
WHERE
  act_type IN (
    'se_visit',
    'stk_visit',
    'stk_log'
  )
GROUP BY
  1

6. 앞으로 남은 과제

6.1. 신규로 act_type을 쉽게 추가할 수 있는 Admin 화면 구축하기

• act_type Dimension 테이블에 레코드 삽입
• 커스텀 집계 칼럼 설정
• Activation 기록을 집계하는 최초 테이블 생성 쿼리 등록

6.2. AU 테이블들을 통해 SQL LLM 봇 만들기

• 개인적으로 AU 테이블 세트가 상당히 획일화된 스키마를 가지고 있기 때문에, LLM 봇을 활용하기에 굉장히 좋은 환경을 가지고 있다고 느꼈습니다.
• PoC 단계에서 어느 정도 성공을 보고 있다고 들었는데, 2025년 SLASH에서 후기를 들을 수 있을지 상당히 기대됩니다.