[블로그프로젝트#14](번외편st.) EDA 하려다가 무한 수정 굴레에 빠져버림 (feat. ppt작성 일주일째)

 이전글:

2026.04.07 - [Project] - [블로그프로젝트#13] 빅쿼리 raw event 데이터 추출하기(.csv)

[블로그프로젝트#13] 빅쿼리 raw event 데이터 추출하기(.csv)

---

부끄럽게도 이전글이 근 한달전이네.. 반 이상은 회사 면접보고 지원하느라 썼구나(다행)

 

목차

---

테이블을 수정하자

지난 글에서 최종 테이블을 뽑았었는데.. 그 최종 테이블이 여러번 바뀌어버림..

 

수정된 사항(데이터)

• batch_page_id 수정
  • 'batch_page_id'를 페이지 고유 id로 가정하고 뽑았었다. 뽑은 이유는 페이지 순서대로 넘버링해서 첫페이지가 어디였는지 알아내기 위함.
  • 그런데 알고보니 '세션 내에서' 페이지의 고유 아이디였음;; 세션이 바뀌면 같은 페이지임에도 batch_page_id가 변경됨.. 그래서 이 변수는 page_location으로 변경했다.
    • 그런데!!! page_location에서 같은 글 뒤에 ?pidx=숫자 가 붙는 경우가 있어, 이 경우도 다르게 체크하더라. (*pidx.. 가 붙는것은 글 내 있는 사진을 클릭했을때 뜨더라)
    • 예, yj-data.tistory.com/485  vs yj-data.tistory.com/485?pidx=0
    • 처리방식: ?pidx=숫자 부분은 무시하고 동일하게 체크하도록 수정했다.

    REGEXP_REPLACE(
      (SELECT ep.value.string_value
       FROM UNNEST(event_params) AS ep
       WHERE ep.key = 'page_location'),
      r'\?.*$',
      ''
    ) AS page_location,

• 페이지 넘버링을 2가지 방식으로 함
  • 한 세션 혹은 uid 당 페이지를 총 몇개 방문했는지 궁금한것과 + 한 세션 혹은 uid 당 페이지를 몇개를 거쳤는지 궁금한것
  • 예를들어, post A - post B - post C - post B - post A를 거쳤을 때, 총 페이지수는 3개, 거친 페이지는 5개가 된다.
  • 목적: 전자는, 중복을 제거하고 총 몇개의 페이지를 거쳤는지, 즉 몇개를 최종적으로 소비했는지 알 수 있게 됨. 후자는, 유입부터 이탈까지 고객 여정의 길이를 페이지 수로 알 수 있음. 진짜 첫페이지는 후자로 확인이 가능함. KPI인 '첫 콘텐츠 소비율'도 후자로 계산 가능.
• 시간대를 한국시간대로 변경
  • 원래 코드대로 하면 UTC로 계산이 되어서, +9시간 해서 한국시간으로 이벤트 시간을 뽑았다.
• page_referrer에 붙은 검색 키워드 추가
  • daum 유입같은 경우는, 리퍼러에 어떤 키워드로 검색 유입이 되었는지 알 수 있더라. 그래서 키워드만 따로 뽑아서 컬럼 만듦.
• nav_type, nav_source 추가
  • internal_navigation(내부이동)의 상세 내용을 확인할 수 있는 태그를 GTM으로 만들어뒀었다. 그 태그를 확인하기 위해 nav_type, nav_source를 추가했다. 원래 nav_source는 source가 원래 element인데, 이미 GA에서 자동으로 생성되는 컬럼에 'source'가 있어서 nav_source로 변경해서 추출.
• 일자 변경
  • 대조군/실험군 일자를 동일하게 맞추고, 가능하면 2주로 딱 맞아 떨어지게 하고 싶어서, 기간을 수정했다.
  • 원래: 3월 27일 ~ 4월 30일
  • 현재: 대조군 3월 28일 ~ 4월 10일 (토~금), 실험군 4월 11일 ~ 4월 24일 (토~금)
    • 실험용 요소 변경을 4월 11일 0시~2시 동안 진행했기 때문에, 이처럼 정했다. 와 진짜 쉽지않았다.. 200개가 넘는 글에 목차 여부를 확인하고 목차를 새로 단다는것은..

-- 최종 코드

WITH base AS (
  SELECT
    user_pseudo_id,

    (SELECT ep.value.int_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'ga_session_id') AS session_id,

    event_date,

    DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_time_kst,

    TIMESTAMP_MICROS(event_timestamp) AS event_timestamp_utc,

    CASE
      WHEN DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')
        BETWEEN '2026-03-28' AND '2026-04-10' THEN 'control'
      WHEN DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')
        BETWEEN '2026-04-11' AND '2026-04-24' THEN 'treatment'
      ELSE NULL
    END AS test_group,

    event_name,

    (SELECT ep.value.int_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'batch_ordering_id') AS batch_ordering_id,

    batch_event_index,

    session_traffic_source_last_click.manual_campaign.source,
    session_traffic_source_last_click.manual_campaign.medium,
    session_traffic_source_last_click.cross_channel_campaign.default_channel_group,

    geo.continent,
    geo.sub_continent,
    geo.country,
    geo.region,
    geo.city,

    device.category,
    device.operating_system,

    -- (SELECT ep.value.string_value
    -- FROM UNNEST(event_params) AS ep
    -- WHERE ep.key = 'page_location') AS page_location,

    REGEXP_REPLACE(
      (SELECT ep.value.string_value
       FROM UNNEST(event_params) AS ep
       WHERE ep.key = 'page_location'),
      r'\?.*$',
      ''
    ) AS page_location,

    (SELECT ep.value.string_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'page_title') AS page_title,

    (SELECT ep.value.string_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'page_referrer') AS page_referrer,

    (SELECT ep.value.string_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'term') AS term_page_referrer,

    (SELECT ep.value.int_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'batch_page_id') AS batch_page_id,

    (SELECT ep.value.string_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'nav_type') AS nav_type,

    (SELECT ep.value.string_value
     FROM UNNEST(event_params) AS ep
     WHERE ep.key = 'source') AS nav_source


  FROM `analytics_526092412.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260328' AND '20260424'
),

page_ordered AS (
  SELECT
    *,
    MIN(event_timestamp_utc) OVER (
      PARTITION BY user_pseudo_id, session_id, batch_page_id
    ) AS page_first_event_time,

    MIN(event_timestamp_utc) OVER (
      PARTITION BY user_pseudo_id, session_id, page_location
    ) AS visited_page_event_time

  FROM base
)

SELECT
  * EXCEPT(page_first_event_time, visited_page_event_time),

  DENSE_RANK() OVER (
    PARTITION BY user_pseudo_id, session_id
    ORDER BY page_first_event_time, batch_page_id
  ) AS page_no_in_session,

  DENSE_RANK() OVER (
  PARTITION BY user_pseudo_id, session_id
  ORDER BY visited_page_event_time, page_location
) AS page_cnt_in_session

FROM page_ordered
ORDER BY user_pseudo_id, session_id, event_timestamp_utc, batch_event_index;

 

PPT를 작성 / 수정하자

4. (개인) 노유정_개인블로그분석(진행중).pdf

1.43MB

Gdrive에 연결하면 언젠간 또 뻑날 수 있으므로 파일박치기!

 

자신있게 PPT는 2~3일이면 끝까지 작성할 수 있을것이라고 장담했던 나레기.. 진짜 감이 없어도 너무 없었다ㅋㅋㅋㅋㅋ 30% 작성하는데 일주일이 넘게 걸렸다^^! 당장 사업기획쪽으로 다른 프로젝트 진행할게 있어서, 일단 EDA 40%까지 기록한 상황에서 잠시 멈추기로함ㅎㅎ

 

적당한 틀과.. 적당한 목차로 시작했던 나.. 처음에는 너무 PPT가 짧을까봐 걱정했지만, 쓰다보니 설명해야할게 생각보다 많다는 것을 깨달음. 내용 하나하나가 다 빌드업 되어 결말까지 달려가야한다는 것을 깨닫고 보충하다보니 시간이 슝슝간다.

 

내 머리속에만 적당히 정리되어있는건 아무 도움이 안되는 거였어!!!

 

목차

그렇게 고민 고민하다가.. 하나씩 정리되어 아래와 같은 목차로 작성되었고,

아니 프로젝트 내용은 시작도 안했는데 이게 맞아..?

 

추후 추가 될 내용

나중에 작성할 예정인데 아직 세세하게 작성할 감당이 안되는 내용들은 이렇게 당장은 마무리해뒀다.

하.. 이거 언제함;;

EDA 하려다 보니

• 트래픽 정제
  • 괴상한 데이터가 보여서 열심히 파보니 해외 봇 등장☆ 그리고 내 IP를 제외해뒀다고 생각했는데 그래도 유유히 뚫고 들어오는 나의 흔적..ㅎ
  • 봇 제외 기준을 고민고민해서 만들면서.. 이벤트 데이터 탐험하는 맛이 쏠쏠하더이다(약간의 눈물이 추가되어 살짝 짠맛ㅠㅠ)
• 데이터 수정/탐구
  • 생각해보니 대조군 기간만 작성해야함; 잔뜩 데이터 수정하고 ㅎㅎ
  • 유입 소스에 아무리 봐도 알 수 없는 category라는 녀석을 탐구하고
• 드디어 방문자 데이터 개요를 작성할 공간에 다다랐는데..!
  • '무슨 데이터를 어떻게 정리해서 보여줘야 보는 사람이 이해할지' 고민하는 것이 너무너무너무 중요해서 머리속이 매우 복잡해졌다..! 이 데이터가 필요한거 같기도하고 아닌거같기도하고.. 특정 데이터 내용이 누락되면 개연성을 해칠것 같기도하고, 너무 쓸데없는 얘긴가 싶기도 하고.. 고민고민고민고민 하는 나날을 보내면서 일단 40%정도만 작성완료. 앞으로 뭘 할지는 대략 파악은 해뒀지만 하다보면 바뀔 수 있음(;;)

일단 여기까지 작성함. 원래 실험군기간까지로 작성했던거에서 실험군을 빼니까 조촐해졌다. 나중에 구성 바꿔야할지도;;

 

 

그래도 아직까지는 꽤 개연성 있게 작성이 되고 있다고 생각은 하는데.. 괜찮겠지..? 아무튼 네.. 사업기획쪽 지식을 보충하고 돌아와서 다시 ㅎrㄲㅔyo..☆ 

 

---

2주 뒤 컴백 예정 ★-★v (제발 2주만에 가능해라 제발)

하 진짜 취준하니까 시간 너무 빨리간다 살려줘