[TIL]2025-02-04(화)

오늘 할 일

• 대시보드 만들기
• A/B테스트 배포 방법 생각하기

TIL

• 빅쿼리로 데이터 만들기

-- 전처리된 데이터를 새로운 테이블로 저장
CREATE OR REPLACE TABLE `travel-project-448705.analytics_processed.events_flat` AS
SELECT
  -- 기본 컬럼
  e.event_date,
  TIMESTAMP_MICROS(e.event_timestamp) + INTERVAL 9 HOUR AS event_datetime_kst,  -- 한국시간 변환
  e.event_name,
  e.user_pseudo_id,

  -- event_params에서 값 추출 (모든 주요 키 포함)
  ep_page_path.value.string_value AS page_path,
  ep_non_interaction.value.string_value AS non_interaction,
  ep_batch_page_id.value.int_value AS batch_page_id,
  ep_ga_session_number.value.int_value AS ga_session_number,
  ep_page_location.value.string_value AS page_location,
  ep_engaged_session_event.value.int_value AS engaged_session_event,
  ep_page_referrer.value.string_value AS page_referrer,
  ep_batch_ordering_id.value.int_value AS batch_ordering_id,
  ep_event_category.value.string_value AS event_category,
  ep_ga_session_id.value.int_value AS ga_session_id,
  ep_event_label.value.string_value AS event_label,
  ep_session_engaged.value.int_value AS session_engaged,
  ep_page_title.value.string_value AS page_title,
  ep_engagement_time.value.int_value / 1000 AS engagement_time_sec,  -- 밀리초를 초로 변환

  -- device 정보
  e.device.category AS device_category,
  e.device.web_info.browser AS browser,
  e.device.web_info.browser_version AS browser_version,

  -- geo 정보
  e.geo.city AS city,
  e.geo.country AS country,

  -- UTM 정보
  e.traffic_source.name AS utm_campaign,
  e.traffic_source.medium AS utm_medium,
  e.traffic_source.source AS utm_source,

  -- 세션 마지막 클릭 정보
  e.session_traffic_source_last_click.manual_campaign.campaign_name AS last_click_campaign_name,
  e.session_traffic_source_last_click.manual_campaign.source AS last_click_source,
  e.session_traffic_source_last_click.manual_campaign.medium AS last_click_medium

FROM
  `travel-project-448705.analytics_474540276.events_*` AS e,  -- 모든 날짜 테이블에서 가져오기

  -- 필요한 event_params 키값을 UNNEST
  UNNEST(e.event_params) AS ep_page_path
  LEFT JOIN UNNEST(e.event_params) AS ep_non_interaction ON ep_non_interaction.key = 'non_interaction'
  LEFT JOIN UNNEST(e.event_params) AS ep_batch_page_id ON ep_batch_page_id.key = 'batch_page_id'
  LEFT JOIN UNNEST(e.event_params) AS ep_ga_session_number ON ep_ga_session_number.key = 'ga_session_number'
  LEFT JOIN UNNEST(e.event_params) AS ep_page_location ON ep_page_location.key = 'page_location'
  LEFT JOIN UNNEST(e.event_params) AS ep_engaged_session_event ON ep_engaged_session_event.key = 'engaged_session_event'
  LEFT JOIN UNNEST(e.event_params) AS ep_page_referrer ON ep_page_referrer.key = 'page_referrer'
  LEFT JOIN UNNEST(e.event_params) AS ep_batch_ordering_id ON ep_batch_ordering_id.key = 'batch_ordering_id'
  LEFT JOIN UNNEST(e.event_params) AS ep_event_category ON ep_event_category.key = 'event_category'
  LEFT JOIN UNNEST(e.event_params) AS ep_ga_session_id ON ep_ga_session_id.key = 'ga_session_id'
  LEFT JOIN UNNEST(e.event_params) AS ep_event_label ON ep_event_label.key = 'event_label'
  LEFT JOIN UNNEST(e.event_params) AS ep_session_engaged ON ep_session_engaged.key = 'session_engaged'
  LEFT JOIN UNNEST(e.event_params) AS ep_page_title ON ep_page_title.key = 'page_title'
  LEFT JOIN UNNEST(e.event_params) AS ep_engagement_time ON ep_engagement_time.key = 'engagement_time_msec'
WHERE
  e.event_date >= '20250201'; -- 2월 1일부터 자동화

looker studio로 연결

향후계획

자동화된 대시보드 + 피드백 데이터 연결