[GA4] Bigquery scripting을 통해 예약된 쿼리로 퍼널 데이터 적재하기

google에서 알려주는 절차적 언어(scripting)에 대한 도움말

Google Bigquery의 Scripting을 통해 예약된 쿼리로 데이터 적재하기

평범하게 SQL로 쿼리를 이것저것 쓰고 있다 보면 가끔 이런 생각을 하게된다. 

- 쿨하게 반복문 지정해서 select도 우르르 되었으면 좋겠다. 리스트 안에서 특정한 무언가를 꺼낼수도 있었으면 좋겠다.
- 반복문과 조건문 섞어서 빠르게 select 하고 싶다.
- 변수처리도 시원하게 해줬음 좋겠다... 포맷팅 캐스팅 속시원~하게 진행하고 싶다..

빅쿼리에서 사용하는 SQL은 절차적 언어를 지원해서, DECLARE, SET에 의한 사전 정의와 함께 LOOP와 같은 반복문이나 EXECUTE IMMEDIATE와 같은 동적 SQL도 충분히 활용할 수 있다. 이러한 스크립팅이 가능하다 보니 빅쿼리 웹 콘솔 내에서 이런 저런 함수를 활용해서 예약된 쿼리 기능을 활용하면 다른 언어를 탈 것도 없이 편하게 테이블 적재가 가능하다. (모든게 해결되는 GCP는 마치 하나의 유니버스같다. ;;) 그래서 이번엔 빅쿼리 언어 만으로 퍼널 데이터 적재를 해보고자 한다.

 

이때, 퍼널 데이터를 만들 때 어떤 쿼리를 활용했는가에 대해서는 이전 글에서 쓴 closed funnel로 구성한 이벤트 데이터 테이블이다. 

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

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

GA4가 정식적으로 자사 쇼핑몰에 적용된 것이 올해 8월 1일부터이기에, 그 이후의 데이터를 위와 같은 형태로 쌓아서 보고있다. 이제 이걸 데일리로 뽑기 위해서 테이블 적재를 위한 쿼리를 예약을 해놔야 한다. 

퍼널 데이터 적재를 위해 필요한 빅쿼리 스크립팅 순서 

내가 작성한 쿼리에서 변수처리가 필요한 부분은 _table_suffix의 date 부분인데, 이부분은 사실 원래 date_range라는 별도의 테이블에 날짜 함수를 넣어서 이 테이블을 불러오는 형태로 만들었었다.

with date_range as (
     select format_date('%Y%m%d',date_sub(current_date('Asia/Seoul'), interval 1 day)) as date_yesterday
)

이런 식으로 말이다. 다만 뒤에 이어지는 backfill 과정이나, 특정일의 데이터를 추출할 경우 data_range 를 쓰는건 정말 비효율적이다. 

이 때, 기존의 closed funnel 쿼리에서 내가 원하는 기간 만큼의 쿼리를 얻으려면 기간을 정하는 부분을 변수로 선언하는 과정과 반복문이 필요하다. 

-- 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

WHERE _TABLE_SUFFIX BETWEEN format_date('%Y%m%d',event_day) AND format_date('%Y%m%d',event_day)
AND event_name = "begin_checkout" )

따라서, 필요한 함수는 DECLARE 와 SET으로 데이터 타입과 변수에 대한 정의를 진행하고 LOOP로 반복문을 돌리는 정도일 것이다.  

이전 기간 데이터 Backfill이 필요할 때는 배열 함수를 활용해보자

구글 도움말 문서 내의 ARRAY 생성 함수 예제

빅쿼리가 지원하는 다양한 함수 중에서 일정한 ARRAY를 가져올 수 있는 함수가 있는데, 이 함수가 오늘의 메인 디쉬 쿼리이다. 내가 지정한 기간까지의 데이터를 ARRAY 형태로 넣어준다. 이 ARRAY에서 전체 요소를 하나씩 가져오는 offset 혹은 ordinal을 활용해서 하나씩 변수를 세팅하고 SQL의 _table_suffix 부분에 넣어주면 된다. 쿼리 순서는 다음과 같다.

 

-- DECLARE
-- i : ARRAY에서 특정 행 추출할 때 쓸 요소
-- DATES : date array를 만들어서 넣을 곳
-- event_day : _table_suffix에서 실질적으로 넣을 값
DECLARE i INT64 DEFAULT 0;
DECLARE DATES ARRAY<DATE>;
DECLARE event_day DATE;

-- SET : 기존에 생성한 DATES에 1일 간격의 ARRAY 만들어주기
SET DATES = GENERATE_DATE_ARRAY(DATE(끝내는 날짜), DATE(종료 날짜), INTERVAL 1 DAY);

-- LOOP 반복문
LOOP
	SET i = i + 1;
    SET event_day = DATES[ORDINAL(i)];
    
    INSERT INTO `TABLE` 
    SELECT

END LOOP;

위 형태에서 INSERT를 진행할 때 미리 만들어 둔 테이블의 경로를 기입하고, INSERT 하는 데이터의 쿼리에 위에서 만들었던 event_day라는 변수를 세팅해주면 backfill용 쿼리는 완성이다. 

 

빅쿼리 스크립팅 관련 참고 자료 : t아카데미 bigquery 시작하기

빅쿼리 중에서도 실제로 sql 쿼리를 두들기면서 한글로 이야기하는 강의가 많지 않았는데, 최근에 t아카데미에서 본 자료가 너무 좋았다.