[TO SQL] RFM 전처리 정리

이어서! python lifetimes 을 이용해서 구했던 일부 RFM 지표들을 SQL를 활용해서 구해보려고 합니다!

우선, ep 테이블의 경우, 각 에피소드별 정보가 들어가 있는 테이블입니다

• 웹툰명(title), 에피소드명(episode), 업로드 날짜(upload_at) 외 댓글 수, 좋아요, 별점 등
• 테이블에서 볼 수 있듯이, 당시 스크래핑했던 웹툰 업로드 날짜의 값이 23.06.06 형태이며 string 값으로 수집이 되어있었기 때문에,
• 문자열 형태의 날짜를 날짜 데이터 타입으로 변환하는 함수인 PARSE_DATE() 를 이용해서 DATE 타입으로 바꿔줘야 했습니다.

-- episode 정보가 들어있는 table 
-- upload_date 데이터 값 (20.06.01)을 %y-%m-%d 형태(20-06-01) date 타입으로 전처리  
with ep as (
SELECT title
      , episode
      , upload_at
      , PARSE_DATE('%y.%m.%d', upload_at) AS upload_date    
FROM WEBTOON.ep
)

WEBTOON.user 테이블은 episode 별로 유저들의 댓글 데이터가 남겨져있는 테이블입니다.

• 예전의 포스팅에서 유저들의 유료결제 여부를 확인하기 위해
  "업로드된 날짜 > 댓글을 작성한 날짜" 즉, 업로드 되기전에 댓글을 작성한 유저의 경우'미리보기'(쿠키구매)를 한 것으로 볼 수 있었죠.
• 또한 각 episode에 유저들은 여러개의 댓글을 남길 수 있기 때문에, 에피소드별 가장 처음 댓글을 남긴 날짜를 기준으로 결제금액을 측정해야합니다.(한 에피소드당 하나의 댓글만 인정)
• 즉, 한 유저가 한 에피소드에 여러개의 댓글을 남겨도 가장 먼저 남겼던 댓글을 기준으로 결제여부를 판단해야하니 아래와 같은 전처리가 필요했습니다.

윈도우 함수를 이용해서 각 에피소드, 유저별로 댓글을 작성한 날짜 오름차순 기준으로 번호를 매기면, 가장 처음 작성한 날짜가 1에 해당하겠죠.

, comment as (
  SELECT *
  FROM (SELECT  episode
        	, user_id
        	, DATE(comment_date) as comment_date
        	, ROW_NUMBER() OVER(PARTITION BY episode,user_id ORDER BY comment_date) as comment_num
        	, comment_like
  FROM WEBTOON.user) a
  WHERE comment_num = 1
) -- episode, user_id 별로 가장 먼저 남겼던 댓글만 가져옵니다. 

이후, comment 댓글 데이터에 ep 테이블에 있는 각 에피소드별 upload_date 정보를 결합해준 뒤, 댓글작성일과 업로드 규칙에 따라 유저당 유료결제 (1200)금액을 계산하여 price 컬럼을 만들어주고, user_id 별 RFM 을 구했습니다.

# user_id 별 RFM 지표
SELECT user_id
      , MIN(comment_date) as first_comment_date
      , MAX(comment_date) as last_comment_date
      , DATE_DIFF(MAX(comment_date),MIN(comment_date),DAY) AS recency
      , COUNT(user_id) as frequncy
      , SUM(price) as monetary
FROM (SELECT C.episode
            , C.user_id
            , C.comment_date
            , C.comment_like
            , E.upload_date
            , CASE WHEN C.comment_date < E.upload_date THEN 1200 ELSE 0 END AS price --
      FROM comment C
      JOIN ep E ON C.episode = E.episode ) a 
GROUP BY user_id