[TO SQL] 성과지표 및 전처리

이전에 Python으로 진행했었던 웹툰 성과지표들을 SQL 를 활용하여 하나씩 다시 리뉴얼 해보려고 합니다!

DAU,WAU,Stickiness

저는 활성화의 기준을 댓글을 작성한 행위로 보았기 때문에 유저별로 댓글을 작성한 날짜에 해당하는 아래의 데이터를 통해 활성화 지표들을 구할 수 있습니다! DAU , WAU 의 기준을 아래로 정의해보면

• DAU : 일별 고유한 활성화 독자수
• WAU : 주간 고유한 활성화 독자수 (기준 날짜 6일 전부터 해당 날짜까지 상품을 주문한 고객 수)
• Stickiness : 기준 날짜의 고객 고착도

data

첫번째 방법

# 위 user 테이블의 comment_date 의 형태를 date 형태로 바꿔줍니다.
with cte as (
  SELECT user_id
        , DATE(comment_date) as comment_date
  FROM WEBTOON.user 
)


SELECT A.comment_date 
    , COUNT(DISTINCT A.user_id) as DAU
    , COUNT(DISTINCT W.user_id) as WAU
    , (COUNT(DISTINCT A.user_id)/COUNT(DISTINCT W.user_id))*100 AS Stickiness
FROM cte A
LEFT JOIN cte W ON W.comment_date BETWEEN DATE_SUB(A.comment_date, INTERVAL 6 DAY) AND A.comment_date 
GROUP BY A.comment_date

FROM cte A
LEFT JOIN cte W ON W.date BETWEEN DATE_SUB(A.date, INTERVAL 6 DAY)

첫번째 방법은 W.date를 A.date 기준 - 6일 ~ 당일까지 슬라이딩해서 WAU 를 한테이블로 집계해봤습니다.
즉, 기준일(A.date) 부터 6일전 까지 댓글을 작성한 고유 유저ID(W.user_id)가 해당 기준일에 전부 결합이되고, 이를 기준으로 집계하면 WAU가 되는 것 이죠.

하지만 위와 같은 방법으로 구했을때, 자기자신을 self join 하기 때문에 날짜수가 많을 수 록 그만큼 계산량이 급격히 증가해서 cpu가 초과되는 것을 볼 수 있었습니다.😢

두번째 방법

그래서 두번째 방법으로는 DAU와 WAU 를 아예 따로 구해서 comment_date 를 key로 결합하는 방법 입니다.

• DISTINCT comment_date 날짜 축을 먼저 만들고 → 그 축을 기준으로 WAU를 슬라이딩 윈도우처럼 계산하는 형태
• 집계된 결과끼리 join하기 때문에 아무래도 계산량이 더 낮아지는 것 같습니다!

-- DAU
WITH daily AS (
  SELECT comment_date 
        , COUNT(DISTINCT user_id) AS DAU
  FROM cte
  GROUP BY date
),

--WAU 
weekday AS (
  SELECT a.comment_date 
        , COUNT(DISTINCT b.user_id) AS WAU
  FROM (
    SELECT DISTINCT comment_date AS date
    FROM cte 
  ) a -- WAU 계산 시 기준이 되는 날짜 목록
  JOIN cte b ON b.comment_date BETWEEN DATE_SUB(a.comment_date, INTERVAL 6 DAY) AND a.comment_date
  GROUP BY a.comment_date
)

-- 결합
SELECT d.comment_date
  , d.DAU
  , w.WAU
  , d.DAU / w.WAU AS Stickiness
FROM daily d
JOIN weekday w ON d.comment_date = w.comment_date
ORDER BY d.comment_date

유저별 활성화 시간대 비율

이전에 유저들이 어떤 시간대에 주로 활성화 되는지, 오전,점심,저녁 각각 시간대를 나눠서 활동 시간대 비율을 계산했었습니다! 해당 과제를 쿼리로 작성해보면

쿼리

WITH CTE as (
SELECT user_id 
    , comment_date
    , DATE(comment_date) as date
    , EXTRACT(HOUR FROM comment_date) as hour
    , CASE WHEN EXTRACT(HOUR FROM comment_date) BETWEEN 6 AND 12 THEN 1 ELSE 0 END AS morning 
    , CASE WHEN EXTRACT(HOUR FROM comment_date) BETWEEN 12 AND 18 THEN 1 ELSE 0 END AS day 
    , CASE WHEN EXTRACT(HOUR FROM comment_date) BETWEEN 0 AND 6 
                OR EXTRACT(HOUR FROM comment_date) BETWEEN 18 AND 24  THEN 1 ELSE 0 END AS night       
FROM WEBTOON.user)

SELECT user_id
    , SUM(morning) as morning_cnt
    , SUM(day) as day_cnt
    , SUM(night) as night_cnt
    , COUNT(user_id) AS total_cnt
    , ROUND(SUM(morning)*100/ COUNT(user_id),2) as morning_ratio
    , ROUND(SUM(day)*100/ COUNT(user_id),2) as day_ratio
    , ROUND(SUM(night)*100/ COUNT(user_id),2) as night_ratio
FROM CTE
GROUP BY user_id 

• DATE() : %Y-%m-%d date 형태로 바꾸는 함수
• EXTRACT(추출할 날짜 FROM 데이터) : 날짜값에서 특정 날짜를 분리
  mysql 에선 HOUR() 같이 직관적으로 분리가 가능했는데 빅쿼리에서는 좀 더 구체적으로 작성해줘야 합니다.

결과 테이블