[GA4] 빅쿼리로 내가 원하는 이벤트의 퍼널(closed funnel) 구현하기

(데이터 추정 불가하도록 블러처리) 필요한 이벤트 수, 전환율에 따른 대시보드로 퍼널 개선/악화의 의미를 찾기 위해 이슈 캘린더를 만들었다

빅쿼리로 내가 원하는 이벤트의 퍼널(closed funnel) 구현하기

2023년, 구글의 강제적인 UA -> GA4 변화에 앞서 올해 하반기 시작 전에 GA4 전자상거래 이벤트까지 세팅을 완료하였다. 빠른 세팅과 이에 따른 시행착오의 장점은 타 브랜드보다 선제적으로 변화에 적응할 수 있는 기간을 갖게 된다는 점일 것이다. 이 과정에서 GA4를 어디까지 활용할 수 있는지 궁금했고, 그 결과 의사결정 수단으로 활용할 수 있는 퍼널 대시보드를 하나 생성해서 전사에 전환율이라는 개념을 전파했다.

퍼널 데이터, 어떻게 확인하는게 제일 편할 것인가

어떻게 해야 팀과 타 부서가 데이터를 빠르게 확인해볼 수 있을까? 이 고민은 내내 계속 이어지는 고민이었다. 

01 : 스프레드시트에 데일리로 전사 지표를 늘어놓고 분석과 함께 전달
02 : 슬랙봇을 만들어서 데일리로 레포팅하기(slack api, ga4 module, airflow, webhook 활용) 
03 : 별도의 대시보드를 생성해서 필요한 지표만 확인 (ga4)

3가지 방법을 모두 시도해봤는데, 1번의 경우에는 데이터팀의 리소스가 너무 많이 든다. 사실.. 백엔드도 데이터 엔지니어도 없어서(ㅜㅜ) 흩어진 데이터들을 모으는 것도 어렵고 그만한 파이프라인을 짜는것도 어렵다고 생각했다. 그 다음에 생각한게 2번째인데, 수치로 받아보면 이 지표가 좋은건지 나쁜건지 한눈에 보기가 어려웠다. 모두가 납득하긴 어려울 것 같았다. 
이 고민들을 완전히 해결은 못하지만 가장 잘 보완해줄 수 있는게 3번의 방법이었다. 쉽게 눈으로 훑을 수 있고 타 기간 대비 데이터의 의미를 빠르게 대조해서 볼 수 있기 때문이었다. 

어떤 퍼널로 구현할까에 대한 고민 ㅡ open vs. closed funnel 

GA4 탐색에서 보이는 개방형 유입경로

GA4 탐색에서 유입경로 탐색 분석 아래의 '개방형 유입경로 만들기'를 ON 하게 되면, 퍼널의 모양이 점점 좁아지는게 아니라 내가 정해놓은 이벤트가 무엇인지에 따라서 들쭉날쭉한 그래프를 볼 수 있을 것이다. 이게 바로 open funnel인데, open과 closed 방식의 퍼널 의미에 대해서는 analytics mania 에서 기깔나게 설명하고 있다. 심지어 영어도 쉽게 쓰여져 있고 표로 유저 케이스를 나눠서 알려준다.

 

내가 담당하는 언더웨어 쇼핑몰에서는 대체로 제품 조회 > 장바구니 > 주문서 조회 > 결제 라는 행동 양식을 따르게 된다. 또한 고객의 구매 주기는 언더웨어다 보니 정말 다양했는데, 대다수가 24시간, 길게는 48시간 이내에 가입 후 구매를 일으킨다.
고객의 구매 여정을 일반화 하기는 참 어렵다. GA4 데이터를 id별로 하나하나 뜯어봤을 때, 브라 검색해서 들어왔다가 팬티를 보셨다가 프로모션을 보셨다가를 반복하다가 한참 뒤에 리타게팅 배너로 유입된 고객들도 있고, 반대로 프로모션 페이지만 딱 가서 필요한거만 조지는(?) 타입도 있다.
다만 이커머스라는 형태 안에서는 위 4개의 이벤트 진행이 고객 가치(LTV)가 높은 고객들의 행동을 대변할 수 있을 것이라 가정했다.

 

빅쿼리로 closed funnel 테이블을 만들어서 구현해보기

우선 2가지의 전환율로 그래프를 그리고자 (상품조회 대비 주문서 조회, 주문서 조회 대비 구매) 딱 필요한 세가지 이벤트 테이블을 만들었다. 상품 조회(view_item) > 주문서 조회(begin_checkout) > 결제(purchase) 순서의 데이터 이다. google bigquery web console에서 해당되는 GA4 데이터 세트에 아래와 같은 standard SQL 형태의 쿼리문을 작성했다.

-- closed funnel 작성 쿼리
-- step1, step2, step3에 필요한 이벤트를 일별로 유저 매칭하여 LEFT JOIN -> COUNT 진행
-- stream_id는 웹 스트림, 앱 스트림의 구별

SELECT step1_event_date as event_date
 , stream_id
 , COUNT(DISTINCT step1_id) AS view_item_users
 , COUNT(DISTINCT step2_id) AS begin_checkout_users
 , COUNT(DISTINCT step3_id) AS purchase_users
FROM ( 
-- STEP 1. view_item
    SELECT
      (CASE WHEN stream_id = '웹 스트림 id' THEN 'WEB' ELSE 'APP' END) as stream_id
      , event_date as step1_event_date
      , user_pseudo_id AS step1_id
      , event_timestamp AS step1_timestamp
      , step2_id
      , step2_timestamp
      , step3_id
      , step3_timestamp
    FROM
      `TABLE`
    LEFT JOIN 
      (
-- STEP 2. view_item -> begin_checkout
      SELECT
        event_date as step2_event_date
        , user_pseudo_id AS step2_id
        , event_timestamp AS step2_timestamp
      FROM
        `TABLE`
      WHERE
        _TABLE_SUFFIX BETWEEN format_date('%Y%m%d',event_day) AND format_date('%Y%m%d',event_day)
        AND event_name = "begin_checkout" )
    ON
      user_pseudo_id = step2_id
    AND event_timestamp < step2_timestamp
      
    LEFT JOIN (
      -- STEP 3. view_item -> begin_checkout -> purchase
      SELECT
        event_date as step3_event_date,
        user_pseudo_id AS step3_id,
        event_timestamp AS step3_timestamp
      FROM
        `TABLE`
      WHERE
        _TABLE_SUFFIX BETWEEN format_date('%Y%m%d',event_day) AND format_date('%Y%m%d',event_day)
        AND event_name = "purchase" )
    ON
      step3_id  = step2_id
      AND step2_timestamp < step3_timestamp
    -- STEP 1. view_item의 where
    WHERE
        _TABLE_SUFFIX BETWEEN format_date('%Y%m%d',event_day) AND format_date('%Y%m%d',event_day)
        AND event_name = "view_item" 
  )
  group by
    stream_id
    , event_date

 closed funnel 쿼리를 짤 때의 고민은 "데이터 로드를 가능한 한 적게" 해주는 작업이었는데, 이 medium이 정말 큰 도움을 주었다. 일별 혹은 특정 기여기간 동안의 발생 이벤트를 셀 때, 우선 첫번째 이벤트의 id만을 가져오고, 그 id의 매칭자에 두가지 조건을 주면 된다.

ON user_pseudo_id = step2_id
AND event_timestamp < step2_timestamp

상품 조회 이벤트(view_item) 데이터를 먼저 가져오고, LEFT JOIN을 거는데 두번째 단계인 주문서 조회 (begin_checkout) 이벤트를 먼저 조건으로 때려버리고 on에 위와 같은 조건을 주면 된다. 이렇게 되면, view_item 다음으로 오게 되는 이벤트(event_timestamp)만 확보할 수 있게 된다. 

 

다만 count(distinct) 는 필수적으로 진행되어야 하는게, 지정한 기간 내(나는 하루였다) 특정 id가 이벤트를 중복 발생시키는 경우가 많기에 distinct로 중복을 방지해줘야 할 것이다. 

테이블로 제작해서 함수화 -> 매일 쿼리 예약을 진행하는 것은 다음 편으로 넘기려고 한다!