[GA4 퍼널 분석] Day3-1 (view_item → add_to_cart | Device UX 분석)

상품 조회는 했는데 왜 장바구니에는 담지 않았는가?

가설 : Device UX 문제

- 모바일 UX에 문제가 있는가?

 

1. 원본 로그에서 필요한 정보 꺼내오기

이벤트 레벨 데이터 추출

- 누가, 어떤 행동, 어떤 기기, 어떤 세션에서 했는가?

SELECT
      -- 사용자 익명 ID
      user_pseudo_id,

      -- 이벤트명
      event_name,

      -- 디바이스 종류
      -- device.category 원본 컬럼을 보기 쉽게 별칭 처리
      device.category AS device_category,

      -- 세션 ID 추출
      -- GA4에서는 ga_session_id가 event_params 배열 안에 들어있음
      -- 그래서 UNNEST로 event_params를 펼친 뒤
      -- key='ga_session_id'인 값을 가져옴
      (
        SELECT value.int_value
        FROM UNNEST(event_params)
        WHERE key = 'ga_session_id'
      ) AS ga_session_id

    -- GA4 이벤트 원본 테이블
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

    -- 이번 분석에서는 상품 조회와 장바구니 이벤트만 필요
    WHERE event_name IN ('view_item', 'add_to_cart')

 

행	user_pseudo_id	event_name	device_category	ga_session_id
1	1065673.072	view_item	desktop	8521814404
2	1158637.038	view_item	mobile	9370997437
3	1158637.038	view_item	mobile	9370997437
4	1158637.038	view_item	mobile	9370997437
5	1227012.847	view_item	mobile	6274278013
6	1325688.152	view_item	mobile	2448315271
7	1325688.152	view_item	mobile	2448315271
8	1325688.152	view_item	mobile	2448315271
9	1450787.99	view_item	mobile	5969589222
10	1450787.99	view_item	mobile	5969589222

 

2. 세션 단위 행동 요약

세션 레벨 퍼널 테이블 생성

- 한 번의 방문 안에서 어떤 퍼널 단계까지 갔는가?

- 전환율은 행동 횟수보다 세션 기준 이동률으로 봐야함

WITH funnel AS (

  SELECT
    -- 사용자 고유 식별자 (익명 ID)
    -- 같은 사용자인지 구분하기 위한 값
    user_pseudo_id,

    -- 세션 고유 ID 생성
    -- user_pseudo_id + ga_session_id를 합쳐
    -- "한 사용자의 한 방문(세션)" 단위로 구분
    CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS session_id,

    -- 사용자의 디바이스 종류
    -- mobile / desktop / tablet
    device_category,

    -- 해당 세션에서 view_item(상품 조회)을 한 적 있으면 1
    -- 없으면 0
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS viewed,

    -- 해당 세션에서 add_to_cart(장바구니 담기)를 한 적 있으면 1
    -- 없으면 0
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS added_to_cart

  FROM (

    SELECT
      user_pseudo_id,
      event_name,
      device.category AS device_category,
      (
        SELECT value.int_value
        FROM UNNEST(event_params)
        WHERE key = 'ga_session_id'
      ) AS ga_session_id
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE event_name IN ('view_item', 'add_to_cart')

  )

  -- 세션 단위로 묶기
  -- 즉, 한 세션에서 상품 조회/장바구니 여부를 요약
  GROUP BY
    user_pseudo_id,
    session_id,
    device_category
)

SELECT *
FROM funnel
LIMIT 100;

 

행	user_pseudo_id	session_id	device_category	viewed	added_to_cart
1	1097358.959	1097358.9593491100-5232661095	mobile	1	0
2	1594068.455	1594068.4547320568-3799143947	desktop	1	0
3	2438082.734	2438082.7340324546-6237879680	mobile	1	0
4	4363514.201	4363514.2007527928-3724604903	desktop	1	0
5	4792087.788	4792087.7875785224-1021976914	mobile	1	0
6	5279971.607	5279971.6070059308-671585977	desktop	1	1
7	5556391.018	5556391.0179080945-9067770222	desktop	1	1
8	5807519.956	5807519.9559617104-2885067489	desktop	1	0
9	7063403.642	7063403.6424377274-848783642	mobile	1	0
10	9047676.95	9047676.9504398272-4029771579	mobile	1	0

 

3. 디바이스별 집계 및 전환율 계산

세그먼트 레벨 집계 및 KPI 산출

- 디바이스별 성과 차이는?

-- 디바이스별(view_item → add_to_cart) 전환율 분석
-- 목표:
-- "상품을 본 세션 중 장바구니까지 간 비율이 디바이스별로 어떻게 다른가?"

WITH funnel AS (

  SELECT
    -- 사용자 고유 식별자 (익명 ID)
    -- 같은 사용자인지 구분하기 위한 값
    user_pseudo_id,

    -- 세션 고유 ID 생성
    -- user_pseudo_id + ga_session_id를 합쳐
    -- "한 사용자의 한 방문(세션)" 단위로 구분
    CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS session_id,

    -- 사용자의 디바이스 종류
    -- mobile / desktop / tablet
    device_category,

    -- 해당 세션에서 view_item(상품 조회)을 한 적 있으면 1
    -- 없으면 0
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS viewed,

    -- 해당 세션에서 add_to_cart(장바구니 담기)를 한 적 있으면 1
    -- 없으면 0
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS added_to_cart

  FROM (

    SELECT
      -- 사용자 익명 ID
      user_pseudo_id,

      -- 이벤트명
      -- 예: view_item, add_to_cart
      event_name,

      -- 디바이스 종류
      -- device.category 원본 컬럼을 보기 쉽게 별칭 처리
      device.category AS device_category,

      -- 세션 ID 추출
      -- GA4에서는 ga_session_id가 event_params 배열 안에 들어있음
      -- 그래서 UNNEST로 event_params를 펼친 뒤
      -- key='ga_session_id'인 값을 가져옴
      (
        SELECT value.int_value
        FROM UNNEST(event_params)
        WHERE key = 'ga_session_id'
      ) AS ga_session_id

    -- GA4 이벤트 원본 테이블
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

    -- 이번 분석에서는 상품 조회와 장바구니 이벤트만 필요
    WHERE event_name IN ('view_item', 'add_to_cart')

  )

  -- 세션 단위로 묶기
  -- 즉, 한 세션에서 상품 조회/장바구니 여부를 요약
  GROUP BY
    user_pseudo_id,
    session_id,
    device_category
)

SELECT

  -- 디바이스별 결과 보기
  device_category,

  -- 상품 조회(view_item)를 한 세션 수
  COUNTIF(viewed = 1) AS view_sessions,

  -- 장바구니(add_to_cart)를 한 세션 수
  COUNTIF(added_to_cart = 1) AS cart_sessions,

  -- 장바구니 전환율(%)
  -- cart_sessions / view_sessions
  -- SAFE_DIVIDE: 0으로 나누는 오류 방지
  ROUND(
    SAFE_DIVIDE(
      COUNTIF(added_to_cart = 1),
      COUNTIF(viewed = 1)
    ) * 100,
    2
  ) AS add_to_cart_rate

FROM funnel

-- view_item이 있었던 세션만 분석 대상
WHERE viewed = 1

-- 디바이스별 집계
GROUP BY device_category

-- 전환율 낮은 순 정렬
-- 어떤 디바이스가 가장 문제인지 빠르게 확인 가능
ORDER BY add_to_cart_rate ASC;

 

행	device_category	view_sessions	cart_sessions	add_to_cart_rate
1	tablet	1700	321	18.88
2	desktop	44819	8780	19.59
3	mobile	30501	6072	19.91

핵심 : 이벤트 로그를 바로 분석하지 않고, 세션 단위 퍼널 구조로 변환한 뒤 세그먼트별 KPI를 산출

 

결과

전환율 : 모바일 > 데스크탑 > 태블릿

 

해석

모바일 UX가 주요 문제라는 초기 가설은 현재 데이터 기준으로 강하게 지지되지 않음

• 모바일이 오히려 가장 높음
• 데스크탑과 차이도 매우 작음 (0.32%)
• 즉, view_item → add_to_cart 단계에서 모바일만 특별히 문제라고 보기 어려움

즉, 디바이스 자체보다 다른 요인이 더 중요할 가능성 높음

• 신규/재방문
• 유입 채널
• 상품 카테고리
• 가격대

결론

장바구니 전환 저하는 디바이스 문제보다 상품/유입/사용자 특성 문제일 가능성이 높음

 

디바이스는 큰 문제가 아니었음 따라서 문제는 "어디서 접속했는가"보다 누가, 어떤 의도로 들어왔는가에 더 가까울 가능성이 높음