오늘 할 일
- 대시보드 완성
- a/b테스트 설계
TIL
빅쿼리에 저장된 데이터 sql로 전처리하기
하루에 한번 이벤트와 유저에 대한 데이터 쌓임.
이벤트 데이터의 기본 스키마
하지만 null 값이 많고, 한 행에 여러 값들이 들어있는 json이기에 sql이나 파이썬으로 전처리 필요.
새탭에서 열고 필요한 데이터 불러오기.
INSERT INTO travel-project-448705.analytics_processed.events_flat
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일부터 자동화
AND e.event_date NOT IN (SELECT DISTINCT event_date FROM travel-project-448705.analytics_processed.events_flat); -- 중복 데이터 방지
이런 형태의 데이터 셋이 만들어짐.
테이블로 저장되면 문제가 스케쥴링을 걸어둘경우 문제가 발생 매번 새로 만들기 때문에 insert로 추가 하는 쿼리로 작성.
예약된 쿼리로 설정. 그리고 이걸 looker로 연결.
여기서 바로 연결 가능!
looker 자동 업데이트 기간 설정. 설정안하면 기본값 12시간 마다 업데이트로 설정됨.
대시보드 만들기
기본 숫자 카드 설정
총방문자수
user_pseudo_id 집계 구분
신규방문자수
COUNT(DISTINCT CASE WHEN event_name = 'first_visit' THEN user_pseudo_id END)
재방문자수
COUNT(DISTINCT user_pseudo_id) - COUNT(DISTINCT CASE WHEN event_name = 'first_visit' THEN user_pseudo_id END)
설문완료자수
COUNT(DISTINCT CASE WHEN REGEXP_CONTAINS(page_location, '^https://james40517.softr.app/result') THEN user_pseudo_id END)
소프터이탈률
1 - (COUNT(DISTINCT CASE WHEN REGEXP_CONTAINS(page_location, '^https://james40517.softr.app/result') THEN user_pseudo_id END)
/ COUNT(DISTINCT CASE WHEN REGEXP_CONTAINS(page_location, '^https://james40517.softr.app') THEN user_pseudo_id END))
탈리이탈률
1 - (COUNT(DISTINCT CASE WHEN event_name = 'Tally.FormSubmitted' THEN user_pseudo_id END)
/ COUNT(DISTINCT CASE WHEN event_name = 'Tally.FormPageView' THEN user_pseudo_id END))
결과페이지체류시간
AVG(
CASE
WHEN REGEXP_CONTAINS(page_title, 'result')
THEN engagement_time_sec
ELSE NULL
END
)
AVG(engagement_time_sec)
소프터 퍼널 분석
CASE
WHEN REGEXP_CONTAINS(page_title, '대전여행 맞춤경로 추천') THEN '1단계: 맞춤경로 추천'
WHEN REGEXP_CONTAINS(page_title, 'result') THEN '2단계: 결과 페이지 이동'
WHEN REGEXP_CONTAINS(page_title, 'feedback_ending') AND NOT REGEXP_CONTAINS(page_path, 'map.naver.com') THEN '3단계: 트렌드 보러가기'
WHEN REGEXP_CONTAINS(page_path, 'map.naver.com') THEN '4단계: 네이버 길찾기'
ELSE '기타'
END
필터에서 기타 제외, 측정항목은 유저별로 설정!
utm별 그래프
소스와, 미디움으로 나눠 유입률과 이탈(결과 도달 못한 경우) 그리고 피드백 체출확인!
utm_source:
CASE
WHEN utm_source= 'friend' THEN 'friend'
WHEN utm_source= 'dpforum' THEN 'dpforum'
WHEN utm_source= 'kakaobang2' THEN 'kakaobang2'
WHEN utm_source= 'blind' THEN 'blind'
WHEN utm_source= 'kakaobang3' THEN 'kakaobang3'
WHEN utm_source IN ('bloglatte', 'community4') THEN 'bloglatte'
ELSE '기타'
END
utm_medium:
CASE
WHEN utm_medium = 'direct' THEN 'direct'
WHEN utm_medium = 'social' THEN 'social'
WHEN utm_medium = 'referral' THEN 'referral'
ELSE '기타'
END
향후 일정
일단 대시보드는 기존의 방식말고 다른방식으로 수정.
그리고 빨리 a/b테스트 설계하고 실험후 데이터가 어떻게 쌓이는지 확인한 뒤 대시보드 및 쿼리 수정필요.
