웹로그 데이터 분석 프로젝트 경과 보고서 (4)

웹로그 데이터 분석 프로젝트 경과 보고서 (3)

 

회사와 프로젝트를 병행하려고 하니 아주 죽을 것 같습니다. 퇴근하고 공부, 운동, 육아등등... 너무 대단들 하십니다 진짜루.

 

STEP 0 - 분석대상 테이블 만들기

1. 사람 찾기

일단 분석을 하기 이전에 일단 사람들을 걸러내야했다. 티스토리 운영을 하면서 들어왔던 수많은 댓글봇들... 좋아요 구걸 서로 구독 구걸, 잘봤어요 어쩌구 저쩌구... 전혀 글과 관련 없는 댓글이 보이면 그냥 거의 대부분 봇이다. 그럼 어찌 됐든 그 봇들도 내 웹페이지에 들어온 이상 로그를 남겼을 것이고 그것들을 걸러내는 작업을 필수적으로 해줘야했다. 쭈루룩 데이터를 살펴보다가 뭔가 이상한 부분을 눈치챘다. 

음...

이 테이블을 설명하기 앞서 duration_total이란 날짜 불문, user_pseudo_id별 event 소요시간을 총합한 것. 예를들면, 서로 다른 날짜에 들어와서 1초씩 소요했다면 2초가 찍힐 것이다. 근데 무려 166개의 user_pseudo_id가 0으로 찍혔다. 😲😲

 

그렇다고 무작정 지울 수는 없다. 그 사이에 정말 한명이라도 사람이 있다면 너무 아깝지 않느냐? 하여 다음과 같은 로직을 세웠다. 들어왔다가 잘못 눌러서 나갈 수 있으나, 만약 관심이 있다면 다시한번 들어오지 않았을까? 이 경우, 해당 user_pseudo_id가 사람인지 로봇인지 중요하지 않다. 과연 내가 분석하기에 합당한 고객인지가 중요한 쟁점이 될 것이다. 사이트에 들어왔다가 0초만에 나가는게 인간이 가능한 행동인지는 모르겠으나, 설령 사람이라고 하더라도, 흥미가 유발됐으면 들어왔을 것이고, 아니면 나갈 것이다. 들어왔다 0초만에 나간 사람을 분석범위에 포함시키고 싶진 않다. 하여 과감하게 제거.

 

사실 몇 가지 로직을 더 만들고 거르고 걸러 사람임을 골라냈으나, 똑같은 milisecond가 중복으로 찍힌 user_pseudo_id들은 예의 주시하고 있다. 우연이라고 할지라도 1/1000초 단위로 오차없이, 똑같은 이벤트를, 같은 이벤트 수를 발생시키는 것이 가능할까? 앞으로도 계속 지켜봐야할 것이다. 너넨 의심스러운 정황이 포착되면 바로 나가리야..!

 

2. 테이블 물리적 순서 맞추기

테이블의 물리적 순서가 도대체 뭐가 중요하냐?라고 묻는 이에게... 나도 동의한다. 이는 BigQuery의 독특한 특성을 알아야하는데, 바로 Struct와 Arrary의 데이터형식이다.

 

https://zzsza.github.io/gcp/2020/04/12/bigquery-unnest-array-struct/

BigQuery UNNEST, ARRAY, STRUCT 사용 방법

 

BigQuery에서 Array 형식의 데이터를 UNNEST하는 과정에서 해당 순서는 온전히 유지된다. 하지만 그 UNNEST된 테이블을 모종의 WHERE, ORDER BY를 거쳐주는 순간, 물리적 순서가 어그러져버린다. 이러면 살짝 곤란한 것이, A라는 이벤트에서 파생되는 매개변수들(page_title, scoll_depth, page_referrer)와 같은 것들이 난장판으로 섞여져버린다. 물론 저 매개변수들이 B에 가서 붙는다거나 하지 않지만, 그냥 보기가 불편하다는 것.

 

이는 사실 A라는 이벤트에서 어떤 매개변수들이 들어가있는지 알고있으면 전혀 문제될 것이 없는 문제이다. 하지만 나는 아직 초짜잖아 ㅠ... 옆에서 이벤트보고 해당 이벤트 매개변수 보고 오옹... 이래야하는 단계라서 굳이굳이 이걸 맞춰주었다.

 

맞추는 방법 찾기가 정말 골치아팠는데, 완벽하게 ORDER BY를 해줘도 결국 해당 테이블을 FROM절에 넣어버리면 다시 순서가 바뀌는 경우가 허다했다. 정말 똑같은 쿼리를 고대로 복붙해서 두 번 실행해줬을 때 순서가 달라지는 걸 보고 뒷목을 몇 번이나 잡았다... 가장 간단한 방법은 그냥 순서가 맞춰져있는 쿼리를 저장해버리는 것. 굳이 굳이 서브쿼리나 CTE로 하려다가 몇 시간이나 날렸는지... 물리적 순서를 보존하고 싶다면 고대로 그냥 저장하면 된다... 

 

이때난 이 방법을 몰랐지

 

 

3. first_date, event_date 활용하여 weekly retention 구해줌

└ null값 처리를 해줘야한다(처리완료)

 

마지막으로 Weekly Retention을 구해주었다. 사실 문제를 정의를 고도화 하는 것이 더 상책이지만, 눈앞에 데이터가 있는데 쿼리치는 걸 어떻게 참아..!!! 무조건 해야죠 ㅇㅇ..

 

그래서 쓸만한 데이터셋을 구축하고 처음으로 나의 타겟이된 건 바로 weekly retention. 사실 그렇게 막 엄청 유명한 그런 사이트는 아니라서 리텐션이 의미가 없을 수 있지만, 그래도 몇 명이라도 있으면, 그들을 그룹화하여 유의미한 인사이트를 뽑아낼 수 있지 않을까? 라는 생각이 들어서 실시해줬다.

 

쿼리를 짜는 일은 간단(3시간)했ㄷ..다. Retention을 안본지 너무 오래되서 어떻게 짜는지도 까먹었는데, 최초날짜 방문한 사람이 그 다음에도 들어오면 +1 이것에 기대어 쿼리를 천천히 짜나갔다. 그런데 최초날짜 방문이 NULL인 사람들도 있어서 event_date의 MIN값을 최초날짜로 업데이트 해줬다. UPDATE 문법을 처음 사용해보는데, 나름 간편해서 좋았구용...
해서 나온 weekly retention의 테이블은 이쪽!

null은 뭐에요..?

 해당 null은 본격적인 블로그를 시작하기 전 09/28이전에 찍힌 user_pseudo_id이다. 그 때는 내부 ip도 정해주지 않아서, 정황상 나일 수 밖에 없어서 그냥 안봐줘도 된다.

 

뭐... 예상해줬던 대로, 그리 많은 사람들이 최초 접속날짜 이후 다른 날짜에 들어와주진 않았습니다. 하지만 1주차 부터 꾸준히 들어와주는 한명, 11/09에는 무려 25명 중 4명이나 다음 주에도 들어와줬네요. 해당 주차에 어떤 게시글을 보고 어떤 기대감을 품었기에 그 다음 주에도 들어와주셨을까요? 그리고 총합적으로, week1에 기록된 분들을 조금 더 조사해봐야겠습니다.

---

-- 아직 null값 처리를 못해줬다
WITH t1 AS (
SELECT *
FROM(
  SELECT DISTINCT LEFT(CAST(first_time AS STRING), 10) AS first_date, user_pseudo_id, CONCAT(LEFT(event_date, 4), '-', SUBSTR(event_date, 5, 2), '-', RIGHT(event_date, 2)) AS event_date
  FROM analytics_452246970.true_human_table
) AS tempp)
,
t2 AS (
      SELECT first_date, 
            CASE -- 이번트 날짜의 weekly 범주
                  WHEN CAST(first_date AS DATE) BETWEEN CAST('2024-09-28' AS DATE) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 6 DAY) THEN '2024-09-28' 
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 7 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 13 DAY) THEN '2024-10-05' 
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 14 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 20 DAY) THEN '2024-10-12'
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 21 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 27 DAY) THEN '2024-10-19'
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 28 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 34 DAY) THEN '2024-10-26'
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 35 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 41 DAY) THEN '2024-11-02'
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 42 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 48 DAY) THEN '2024-11-09'
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 49 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 55 DAY) THEN '2024-11-16'
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 56 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 62 DAY) THEN '2024-11-23'
                  WHEN CAST(first_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 63 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 69 DAY) THEN '2024-11-30'
                  ELSE 'Null'
            END AS first_week,
            user_pseudo_id,
            CASE 
                  WHEN CAST(event_date AS DATE) BETWEEN CAST('2024-09-28' AS DATE) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 6 DAY) THEN '2024-09-28' 
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 7 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 13 DAY) THEN '2024-10-05' 
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 14 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 20 DAY) THEN '2024-10-12'
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 21 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 27 DAY) THEN '2024-10-19'
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 28 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 34 DAY) THEN '2024-10-26'
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 35 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 41 DAY) THEN '2024-11-02'
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 42 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 48 DAY) THEN '2024-11-09'
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 49 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 55 DAY) THEN '2024-11-16'
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 56 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 62 DAY) THEN '2024-11-23'
                  WHEN CAST(event_date AS DATE) BETWEEN DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 63 DAY) AND DATE_ADD(CAST('2024-09-28' AS DATE), INTERVAL 69 DAY) THEN '2024-11-30'
                  ELSE '2024-12-07'
            END AS event_week
      FROM t1
)


SELECT first_week,
       COUNT(DISTINCT user_pseudo_id) AS week0,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 7 DAY) THEN user_pseudo_id END) AS week1,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 14 DAY) THEN user_pseudo_id END) AS week2,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 21 DAY) THEN user_pseudo_id END) AS week3,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 28 DAY) THEN user_pseudo_id END) AS week4,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 35 DAY) THEN user_pseudo_id END) AS week5,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 42 DAY) THEN user_pseudo_id END) AS week6,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 49 DAY) THEN user_pseudo_id END) AS week7,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 56 DAY) THEN user_pseudo_id END) AS week8,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 63 DAY) THEN user_pseudo_id END) AS week9,
       COUNT(DISTINCT CASE WHEN CAST(event_week AS DATE) = DATE_ADD(CAST(first_week AS DATE), INTERVAL 63 DAY) THEN user_pseudo_id END) AS week10
FROM t2
WHERE first_week != 'Null'
GROUP BY 1