프로젝트/GA4 분석

[GA4 퍼널 분석] Day6-1 심층 분석 구조 설계 (User Type 핵심 가설 분해하기)

조성호 2026. 5. 12. 20:05

User Type × Device 분석

신규 유저의 결제 이탈이 모바일/데스크탑 중 어디서 더 심한지 확인

 

1. 세션 단위 기본 데이터 만들기

session_id가 잘 만들어졌는지
device_category가 desktop/mobile/tablet으로 나오는지
event_name에 begin_checkout, purchase가 있는지

-- STEP 1
-- GA4 이벤트 데이터에서 session_id, device, event_name을 가져온다.
-- GA4는 세션 ID가 event_params 안에 들어있기 때문에 UNNEST로 꺼내야 한다.

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

  device.category AS device_category,
  event_name

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
LIMIT 100;
행 session_id device_category event_name
1 1006764.2069391887-4206279779 desktop first_visit
2 1006764.2069391887-4206279779 desktop page_view
3 1006764.2069391887-4206279779 desktop session_start
4 1013119.9128860959-4727731986 desktop session_start
5 1013119.9128860959-4727731986 desktop page_view
6 1013119.9128860959-4727731986 desktop scroll
7 1013119.9128860959-4727731986 desktop user_engagement
8 1013119.9128860959-4727731986 desktop page_view
9 1013119.9128860959-4727731986 desktop scroll
10 1015990.9348172888-1996654482 desktop user_engagement

 

2. 세션별 상태 만들기

has_checkout = true인 세션이 있는지
has_purchase = true인 세션이 있는지
user_type이 New / Returning으로 나뉘는지

-- STEP 2
-- 한 세션 안에서 begin_checkout을 했는지,
-- purchase까지 했는지 표시한다.
-- first_visit이 있으면 신규 유저(New), 없으면 Returning으로 분류한다.

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,
    device.category AS device_category,
    event_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
)

SELECT
  session_id,
  device_category,

  IF(COUNTIF(event_name = 'first_visit') > 0, 'New', 'Returning') AS user_type,

  COUNTIF(event_name = 'begin_checkout') > 0 AS has_checkout,
  COUNTIF(event_name = 'purchase') > 0 AS has_purchase

FROM base
WHERE session_id IS NOT NULL
GROUP BY session_id, device_category
LIMIT 100;
행 session_id device_category user_type has_checkout has_purchase
1 1081184.0817596046-4082583885 desktop New FALSE FALSE
2 1193793.6876966454-146010869 desktop New FALSE FALSE
3 1205497.4641684708-4389586556 mobile New FALSE FALSE
4 1231705.3319226901-6763131327 tablet New FALSE FALSE
5 1530475.7760415555-1127144837 desktop New FALSE FALSE
6 1544842.2565145441-711145347 desktop New FALSE FALSE
7 1552776.5708384380-5681992942 desktop New FALSE FALSE
8 1655781.0131717336-203114948 mobile Returning FALSE FALSE
9 1667167.2733768204-2027989439 mobile New FALSE FALSE
10 1691272.7474873562-9304843115 desktop New FALSE FALSE

 

3. 결제 시작 세션만 필터링

-- STEP 3
-- Day6의 모수는 begin_checkout을 한 세션이다.
-- 따라서 has_checkout = true인 세션만 남긴다.

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,
    device.category AS device_category,
    event_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
),

session_level AS (
  SELECT
    session_id,
    device_category,
    IF(COUNTIF(event_name = 'first_visit') > 0, 'New', 'Returning') AS user_type,
    COUNTIF(event_name = 'begin_checkout') > 0 AS has_checkout,
    COUNTIF(event_name = 'purchase') > 0 AS has_purchase
  FROM base
  WHERE session_id IS NOT NULL
  GROUP BY session_id, device_category
)

SELECT *
FROM session_level
WHERE has_checkout = TRUE
LIMIT 100;
행 session_id device_category user_type has_checkout has_purchase
1 4059472.8389313570-7298208560 mobile New TRUE TRUE
2 4244063.5746229815-4900810928 mobile New TRUE TRUE
3 8626279.3419238923-8875376761 desktop New TRUE TRUE
4 50131723.8552639593-5180005061 mobile Returning TRUE TRUE
5 55604982.6851109541-6658537903 mobile New TRUE FALSE
6 4458995.9765334478-7541641210 desktop Returning TRUE FALSE
7 7915474.0547592083-5461520031 desktop New TRUE FALSE
8 30124887.6196611575-8742759486 mobile New TRUE FALSE
9 52748769.1167940891-6658792661 desktop New TRUE FALSE
10 75939083.3310904019-4178590139 desktop New TRUE TRUE

 

4. 최종 집계

-- STEP 4
-- user_type과 device_category 조합별로
-- checkout_sessions, purchase_sessions, 전환율을 계산한다.

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,
    device.category AS device_category,
    event_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
),

session_level AS (
  SELECT
    session_id,
    device_category,
    IF(COUNTIF(event_name = 'first_visit') > 0, 'New', 'Returning') AS user_type,
    COUNTIF(event_name = 'begin_checkout') > 0 AS has_checkout,
    COUNTIF(event_name = 'purchase') > 0 AS has_purchase
  FROM base
  WHERE session_id IS NOT NULL
  GROUP BY session_id, device_category
)

SELECT
  user_type,
  device_category,

  COUNT(*) AS checkout_sessions,
  COUNTIF(has_purchase) AS purchase_sessions,

  ROUND(SAFE_DIVIDE(COUNTIF(has_purchase), COUNT(*)) * 100, 2) AS checkout_to_purchase_rate,
  ROUND(100 - SAFE_DIVIDE(COUNTIF(has_purchase), COUNT(*)) * 100, 2) AS checkout_dropoff_rate

FROM session_level
WHERE has_checkout = TRUE
GROUP BY user_type, device_category
ORDER BY user_type, checkout_sessions DESC;
행 user_type device_category checkout_sessions purchase_sessions checkout_to_purchase_rate checkout_dropoff_rate
1 New desktop 3360 983 29.26 70.74
2 New mobile 2349 717 30.52 69.48
3 New tablet 132 36 27.27 72.73
4 Returning desktop 3032 1765 58.21 41.79
5 Returning mobile 2125 1276 60.05 39.95
6 Returning tablet 108 68 62.96 37.04

User Type × Source 분석

신규 유저 결제 이탈이 모든 채널에서 발생하는지, 아니면 특정 유입 채널에서 더 심한지 확인

 

1. 기본 이벤트 데이터 확인

session_id가 잘 생성되는지
source에 google, direct, shop.googlemerchandisestore.com 등이 나오는지
event_name에 first_visit, begin_checkout, purchase가 있는지

-- STEP 1
-- GA4 이벤트에서 session_id, source, event_name을 가져온다.
-- session_id는 user_pseudo_id + ga_session_id로 만든다.

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

  IFNULL(traffic_source.source, 'unknown') AS source,
  event_name

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
LIMIT 100;
행 session_id source event_name
1 1005484.1092567297-2718913892 google page_view
2 1005484.1092567297-2718913892 google user_engagement
3 1005484.1092567297-2718913892 google first_visit
4 1005484.1092567297-2718913892 google page_view
5 1005484.1092567297-2718913892 google session_start
6 1019468.5334749980-7900311379 <Other> page_view
7 1019468.5334749980-2306134442 (data deleted) session_start
8 1019468.5334749980-7900311379 <Other> page_view
9 1019468.5334749980-7900311379 <Other> session_start
10 1019468.5334749980-7900311379 <Other> first_visit

 

2. 세션별 User Type 만들기

user_type이 New / Returning으로 나뉘는지
source와 user_type이 같이 붙는지

-- STEP 2
-- 한 세션 안에 first_visit 이벤트가 있으면 New,
-- 없으면 Returning으로 분류한다.

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,

    IFNULL(traffic_source.source, 'unknown') AS source,
    event_name

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
)

SELECT
  session_id,
  source,

  IF(COUNTIF(event_name = 'first_visit') > 0, 'New', 'Returning') AS user_type

FROM base
WHERE session_id IS NOT NULL
GROUP BY session_id, source
LIMIT 100;
행 session_id source user_type
1 1414938.8438770269-7759055264 <Other> New
2 1501902.9797413231-6739338684 <Other> New
3 1619955.1831633439-7020393497 (data deleted) Returning
4 1630230.2677991350-9368282458 <Other> Returning
5 1764099.3784817576-3163935133 (direct) New
6 1834354.8308826699-8159334297 (direct) New
7 1918757.1161978913-1699656519 (direct) New
8 1933490.1851112366-3552151488 (data deleted) Returning
9 2167362.6731522613-5380243175 <Other> Returning
10 2189960.6916096038-5851351375 google New

 

3. 세션별 Checkout / Purchase 여부 만들기

has_checkout = TRUE인 세션이 있는지
has_purchase = TRUE인 세션이 있는지
New / Returning별로 값이 잘 나오는지

-- STEP 3
-- 세션별로 begin_checkout을 했는지,
-- purchase까지 했는지 TRUE/FALSE로 표시한다.

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,

    IFNULL(traffic_source.source, 'unknown') AS source,
    event_name

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
)

SELECT
  session_id,
  source,

  IF(COUNTIF(event_name = 'first_visit') > 0, 'New', 'Returning') AS user_type,

  COUNTIF(event_name = 'begin_checkout') > 0 AS has_checkout,
  COUNTIF(event_name = 'purchase') > 0 AS has_purchase

FROM base
WHERE session_id IS NOT NULL
GROUP BY session_id, source
LIMIT 100;
행 session_id source user_type has_checkout has_purchase
1 1006637.5892076864-7936037416 google New FALSE FALSE
2 1033552.6644233006-6507433582 google Returning FALSE FALSE
3 1048865.3083168916-3936930723 (direct) New FALSE FALSE
4 1163922.9618943717-6432833705 shop.googlemerchandisestore.com Returning FALSE FALSE
5 1194193.0107069199-2277774777 shop.googlemerchandisestore.com New FALSE FALSE
6 1226623.2418352493-7628770474 <Other> New FALSE FALSE
7 1247380.6227482238-1634966350 <Other> New FALSE FALSE
8 1344349.6326547356-5952984580 google New FALSE FALSE
9 1401960.6472629788-8032978085 (data deleted) Returning FALSE FALSE
10 1406369.3899848685-8888399755 google Returning FALSE FALSE

 

4. 결제 시작 세션만 필터링

이제 남은 데이터는 begin_checkout을 한 세션만 해당
이 안에서 purchase 여부를 비교하면 됨

-- STEP 4
-- Day6 분석의 모수는 begin_checkout 세션이다.
-- 따라서 has_checkout = TRUE인 세션만 남긴다.

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,

    IFNULL(traffic_source.source, 'unknown') AS source,
    event_name

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
),

session_level AS (
  SELECT
    session_id,
    source,

    IF(COUNTIF(event_name = 'first_visit') > 0, 'New', 'Returning') AS user_type,

    COUNTIF(event_name = 'begin_checkout') > 0 AS has_checkout,
    COUNTIF(event_name = 'purchase') > 0 AS has_purchase

  FROM base
  WHERE session_id IS NOT NULL
  GROUP BY session_id, source
)

SELECT *
FROM session_level
WHERE has_checkout = TRUE
LIMIT 100;
행 session_id source user_type has_checkout has_purchase
1 33280841.1628915296-6098838085 <Other> New TRUE TRUE
2 39040153.0068804009-9939994790 <Other> Returning TRUE FALSE
3 52442763.3102020102-2867548969 (direct) New TRUE TRUE
4 66787047.1916957378-8606399219 (data deleted) Returning TRUE TRUE
5 3481692.1528741899-4797735061 shop.googlemerchandisestore.com Returning TRUE FALSE
6 8949227.4843059716-2199768083 <Other> Returning TRUE FALSE
7 73317479.4306868663-4637522552 <Other> Returning TRUE TRUE
8 87116489.5307133653-7472317785 google Returning TRUE TRUE
9 4734903.8935246657-6998932990 (data deleted) Returning TRUE FALSE
10 4793087.3513596462-5904345399 shop.googlemerchandisestore.com Returning TRUE TRUE

 

5. User Type × Source별 전환율 계산

New 유저 중 어떤 source의 결제 완료율이 낮은가?
Returning 유저는 source별 차이가 줄어드는가?
google 유입 신규 유저가 특히 낮은가?
direct / referral 계열은 더 높은가?

-- STEP 5
-- user_type과 source 조합별로
-- checkout_sessions, purchase_sessions, 전환율, 이탈률을 계산한다.

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,

    IFNULL(traffic_source.source, 'unknown') AS source,
    event_name

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
),

session_level AS (
  SELECT
    session_id,
    source,

    IF(COUNTIF(event_name = 'first_visit') > 0, 'New', 'Returning') AS user_type,

    COUNTIF(event_name = 'begin_checkout') > 0 AS has_checkout,
    COUNTIF(event_name = 'purchase') > 0 AS has_purchase

  FROM base
  WHERE session_id IS NOT NULL
  GROUP BY session_id, source
)

SELECT
  user_type,
  source,

  COUNT(*) AS checkout_sessions,
  COUNTIF(has_purchase) AS purchase_sessions,

  ROUND(
    SAFE_DIVIDE(COUNTIF(has_purchase), COUNT(*)) * 100,
    2
  ) AS checkout_to_purchase_rate,

  ROUND(
    100 - SAFE_DIVIDE(COUNTIF(has_purchase), COUNT(*)) * 100,
    2
  ) AS checkout_dropoff_rate

FROM session_level
WHERE has_checkout = TRUE
GROUP BY user_type, source
ORDER BY user_type, checkout_sessions DESC;
행 user_type source checkout_sessions purchase_sessions checkout_to_purchase_rate checkout_dropoff_rate
1 New google 2339 687 29.37 70.63
2 New <Other> 1769 527 29.79 70.21
3 New (direct) 1417 438 30.91 69.09
4 New shop.googlemerchandisestore.com 315 84 26.67 73.33
5 New (data deleted) 1 0 0 100
6 Returning google 1218 715 58.7 41.3
7 Returning (data deleted) 1184 697 58.87 41.13
8 Returning (direct) 1098 640 58.29 41.71
9 Returning <Other> 945 556 58.84 41.16
10 Returning shop.googlemerchandisestore.com 820 501 61.1 38.9

Category Checkout 분석

Day5에서 Bags가 장바구니 전환이 낮았다.
그렇다면 Bags는 결제 단계에서도 약한가?

1. begin_checkout 이벤트의 상품 카테고리 확인

-- begin_checkout 이벤트에서 어떤 item_category가 들어있는지 확인한다.

SELECT
  item.item_category,
  COUNT(*) AS rows_count

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(items) AS item

WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'begin_checkout'

GROUP BY item.item_category
ORDER BY rows_count DESC
LIMIT 50;
행 item_category rows_count
1 Apparel 21026
2 New 7524
3 Campus Collection 6638
4 Accessories 6265
5 Shop by Brand 4675
6 Bags 4487
7 Office 4122
8 Clearance 3478
9   3297
10 Drinkware 3055

 

2. checkout 세션과 category 연결

-- begin_checkout을 한 세션별로 item_category를 붙인다.
-- 한 세션에 여러 상품이 있을 수 있으므로 DISTINCT 처리한다.

SELECT DISTINCT
  CONCAT(user_pseudo_id, '-', (
    SELECT value.int_value
    FROM UNNEST(event_params)
    WHERE key = 'ga_session_id'
  )) AS session_id,

  IFNULL(item.item_category, 'Unknown') AS item_category

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(items) AS item

WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'begin_checkout'
LIMIT 100;
행 session_id item_category
1 82379049.4680589452-2185402217 Small Goods
2 6101467.4740745406-1934665090 Clearance
3 53104811.8176614311-1913163986 Apparel
4 17007406.8391181695-6225971848 New
5 17007406.8391181695-6225971848 Office
6 1160488.2375923167-2309154775 Writing Instruments
7 1160488.2375923167-2309154775 Small Goods
8 1617434.1535145542-3795921985 (not set)
9 4696219.9403845023-494995482 (not set)
10 7053762.4921201809-8520777654 (not set)

 

3. purchase 세션 만들기

-- purchase가 발생한 세션 목록만 따로 만든다.

SELECT DISTINCT
  CONCAT(user_pseudo_id, '-', (
    SELECT value.int_value
    FROM UNNEST(event_params)
    WHERE key = 'ga_session_id'
  )) AS session_id

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'purchase'
LIMIT 100;
행 session_id
1 3630058.7076110762-2952382013
2 21818790.8215652903-802082297
3 1720255.4510701421-2427952913
4 6670122091.4622753239-1914010603
5 68546038.8820428933-5425867143
6 1160488.2375923167-2309154775
7 3947718.3318468480-1307500392
8 69662510.3193519179-6872602433
9 49793755.7550891425-7448861652
10 6997954.0657135279-5673725556

 

4. 최종 Category 집계

-- Category Checkout → Purchase 분석
-- 목적:
-- Day5에서 확인한 Category 이슈가
-- begin_checkout → purchase 단계에서도 이어지는지 확인
-- 특히 공백 / (not set) / Uncategorized Items를 Unknown으로 통합

WITH checkout_items AS (
  SELECT DISTINCT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id,

    CASE
      WHEN item.item_category IS NULL
        OR TRIM(item.item_category) = ''
        OR item.item_category = '(not set)'
        OR item.item_category = 'Uncategorized Items'
      THEN 'Unknown'
      ELSE item.item_category
    END AS item_category

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(items) AS item

  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'begin_checkout'
),

purchase_sessions AS (
  SELECT DISTINCT
    CONCAT(user_pseudo_id, '-', (
      SELECT value.int_value
      FROM UNNEST(event_params)
      WHERE key = 'ga_session_id'
    )) AS session_id

  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name = 'purchase'
)

SELECT
  c.item_category,

  COUNT(DISTINCT c.session_id) AS checkout_sessions,
  COUNT(DISTINCT p.session_id) AS purchase_sessions,

  ROUND(
    SAFE_DIVIDE(
      COUNT(DISTINCT p.session_id),
      COUNT(DISTINCT c.session_id)
    ) * 100,
    2
  ) AS checkout_to_purchase_rate,

  ROUND(
    100 - SAFE_DIVIDE(
      COUNT(DISTINCT p.session_id),
      COUNT(DISTINCT c.session_id)
    ) * 100,
    2
  ) AS checkout_dropoff_rate

FROM checkout_items c
LEFT JOIN purchase_sessions p
  ON c.session_id = p.session_id

WHERE c.session_id IS NOT NULL

GROUP BY c.item_category
ORDER BY checkout_sessions DESC;
행 item_category checkout_sessions purchase_sessions checkout_to_purchase_rate checkout_dropoff_rate
1 Apparel 3480 2036 58.51 41.49
2 Unknown 2065 875 42.37 57.63
3 New 1265 789 62.37 37.63
4 Accessories 1010 644 63.76 36.24
5 Shop by Brand 952 568 59.66 40.34
6 Campus Collection 891 622 69.81 30.19
7 Bags 786 448 57 43
8 Office 737 451 61.19 38.81
9 Clearance 681 419 61.53 38.47
10 Drinkware 611 432 70.7 29.3

 

 

 

 

 

 

 

'프로젝트 > GA4 분석' 카테고리의 다른 글

[GA4 퍼널 분석] Day7-1 신규 유저 결제 신뢰 개선 시뮬레이션  (1) 2026.05.13
[GA4 퍼널 분석] Day6-2 심층 분석 검증 (User Type·Unknown 구조 재정의)  (0) 2026.05.12
[GA4 퍼널 분석] Day5-2 결제 전환율 가설 검증 (Day4 결과 통합)  (0) 2026.05.12
[GA4 퍼널 분석] Day5-1 장바구니 전환율 가설 검증 (Day3 결과 통합)  (0) 2026.05.11
[GA4 퍼널 분석] Day4-4 (begin_checkout → purchase | Price Barrier 분석)  (0) 2026.05.09

'프로젝트/GA4 분석'의 다른글

  • 현재글[GA4 퍼널 분석] Day6-1 심층 분석 구조 설계 (User Type 핵심 가설 분해하기)

관련글

티스토리툴바