구글 애널리틱스 로우데이터를 이용해 구매고객 예측모델 만들기(1/3) - GA 로우데이터 가공

프로세스 설명

구글 애널리틱스는 웹에 유입된 고객들의 event를 분석할 수 있는 툴이다. GA4로 전환되면서 구글 애널리틱스의 Raw data를 무료로 빅쿼리에 적재할 수 있다.

빅쿼리에 적재된 구글 애널리틱스의 Raw데이터를 이용하여, 고객이 구매를 할지, 하지 않을 지 분류하는 binary classifier를 학습하고자 한다.

해당 모델은 고객들의 이벤트를 보고 구매 고객인지, 비구매 고객인지를 예측할 것이고, 우리는 모델이 구매 고객이라고 예측하였으나, 실제로는 구매하지 않은 고객을 ‘잠재구매고객’으로 정의하여 마케팅을 실행할 수 있다.

 

순서

1. 빅쿼리 로우데이터 추출
2. 코랩으로 학습 하기
3. 예측 수행

 

빅쿼리 로우데이터 추출

• 구글애널리틱스의 로우 데이터는 이벤트 발생 시간을 기준으로 적재된다.

GA 로우데이터

• 고객 기준으로 분류를 수행할 것이기 때문에 고객 기준으로 데이터를 전처리할 필요가 있다.
• GA 로우데이터는 user_id와 user_pseudo_id로 고객을 식별할 수 있다.

고객 식별자인 user_id와 user_pseudo_id

 

 

GA는 $웹에서$ 고객을 아래와 같이 식별한다
1. 쿠키를 기반으로 만들어지는 기기ID$클라이언트 ID$
2. 구글 시그널 데이터
3. 웹사이트의 유저 id

• 1, 2, 3번을 전부 활용하여 고객을 식별하는 것이 좋으나, 본 분석에 활용한 GA 데이터는 user_id를 수집하고 있지 않았다. 따라서 user_pseudo_id를 기준으로 고객을 식별하도록 데이터를 전처리 하였다.

 

1. 사용 컬럼

• EDA를 통해 고객의 구매 확률에 영향을 미치는 변수들을 확인하였다.
• device의 os가 구매 확률에 영향을 미치는 지 살펴 보는 과정이다.

1. 구매 유저들의 user_pseudo_id를 수집한다.
2. user_pseudo_id 별 device_os 정보를 수집하고, 구매한 유저는 1, 구매하지 않은 유저는 0으로 하여 purchase_yn 컬럼을 만든다.
3. device_os로 group by하여 purchase_yn의 평균 값을 취하면, device os별 구매 확률이다.

WITH purchase_user_pseudo_id AS (
  SELECT DISTINCT user_pseudo_id
    FROM `events_*`
    WHERE event_name = 'purchase'
),

device_table AS (
  SELECT
  user_pseudo_id, MAX(device.operating_system) AS device_os, MAX(device.web_info.browser) AS device_browser, 
  MAX(CASE WHEN user_pseudo_id IN (SELECT user_pseudo_id FROM purchase_user_pseudo_id) THEN 1 ELSE 0 END) AS purchase_yn
  FROM `events_*`
GROUP BY user_pseudo_id
)

SELECT device_os, ROUND(AVG(purchase_yn), 6) AS purchase_conversion_rate, COUNT(*) AS user_count
FROM device_table
GROUP BY device_os
ORDER BY purchase_conversion_rate DESC;

 

device os별 구매 전환율과 고객 수

• 위와 같은 과정을 통해 변수들을 탐색하였고, 아래와 같이 9개의 변수를 추출하였고, 변수명을 지정하였다.

고객 고유 번호 : user_pseudo_id

• 고객 고유 식별 번호이다.

소비 시간$초$ : time_second

• 각 이벤트에 존재하는 engagement_time_msec로, event별 소비한 시간을 총 합쳐서 ‘초’ 단위로 변환한 변수

view_item 이벤트 수 : view_item_count

• view_item 이벤트$고객이 item을 볼 때마다 이벤트 생성$의 개수.

utm_파라미터 : utm_source, utm_medium, utm_content

• utm 파라미터 - 캠페인 소스
• utm 파라미터 참고 (https://biggerandstronger.tistory.com/18#:~:text=수-,UTM파라미터,-소스)
  • $utm_content보다 utm_campaign을 사용했어야 했다!$

첫 방문 시간 대 : first_visit_hour

• 고객이 처음 방문했을 때, 유입된 시간대를 의미함

기기 운영체제 : device_os

• 고객이 사용하는 기기의 운영체제 정보

기기 Browser : device_browser

• 고객이 사용하는 기기의 browser 정보

접속 지역 : region

• 고객이 접속한 지역

 

2. Target

구매 여부 : purchase_yn

• 고객의 구매여부, 구매했으면 1 구매하지 않았으면 0
• 고객이 구매했을 때 발생하는 이벤트는 ‘purchase’이다. ‘purchase’ 이벤트가 발생한 고객을 구매고객, 발생하지 않은 고객을 비구매고객으로 구분할 것이다.

 

3. 최종 데이터셋

• user_pseudo_id를 기준으로, 9개의 Feature를 가지고 구매 여부를 target으로 하는 데이터셋을 추출하였다.

최종 데이터셋 형태

 

• 모델 inference 시 최근 방문일 기준으로 최근 2주만 예측하는 로직을 구축하기 위해 최종 데이터셋에는 가장 최근 방문일인 last_visit을 추가하였다.
• 또한, 학습 시 6개월 치 데이터만 가지고 학습하고자 하였기 때문에 6개월 치 데이터만 가지고 왔다.
• 쿼리

WITH purchase_user_pseudo_id AS(
  SELECT DISTINCT user_pseudo_id
    FROM `events_*`
    WHERE event_name = 'purchase'
    AND _TABLE_SUFFIX > FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH))  /*오늘 기준 6개월 데이터만 추출*/ 
  ),

  main AS( /* 다른 테이블들을 join할 기준 임시 테이블 생성 */
    SELECT user_pseudo_id,
        MAX(event_date) AS last_visit,
        SUM(CASE WHEN event_params.key = 'engagement_time_msec' THEN ROUND(event_params.value.int_value/1000, 2) ELSE 0 END) AS time_second, /* microsecend를 초단위로 변경 */
        (CASE WHEN user_pseudo_id IN (SELECT user_pseudo_id FROM purchase_user_pseudo_id) THEN 1 ELSE 0 END) AS purchase_yn 
      FROM `events_*`, UNNEST(event_params) AS event_params
      WHERE _TABLE_SUFFIX > FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH))
      GROUP BY user_pseudo_id
  ),

  view_item_count_table AS ( /* view_item의 이벤트를 담는 테이블 생성 */
    SELECT
      user_pseudo_id,
      SUM(CASE WHEN event_name='view_item' THEN 1 ELSE 0 END) as view_item_count
    FROM `events_*`
    WHERE _TABLE_SUFFIX > FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)) 
    GROUP BY user_pseudo_id
  ),

  view_table AS( /* utm 파라미터를 포함한 view_item의 이벤트 */
    SELECT
      events.user_pseudo_id AS user_pseudo_id,
      MAX(event_name) AS event_name,
      MAX(view_item_count) AS view_item_count,
      MAX(CASE WHEN event_params.key = 'source' THEN event_params.value.string_value ELSE '' END) AS source,
      MAX(CASE WHEN event_params.key = 'medium' THEN event_params.value.string_value ELSE '' END) AS medium,
      MAX(CASE WHEN event_params.key = 'content' THEN event_params.value.string_value ELSE '' END) AS content
    FROM `events_*` AS events, UNNEST(event_params) AS event_params
    LEFT JOIN view_item_count_table ON events.user_pseudo_id  = view_item_count_table.user_pseudo_id
    WHERE event_name = 'view_item'
    AND _TABLE_SUFFIX > FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)) 
    GROUP BY events.user_pseudo_id
  ),

  first_visit_table AS ( /* 고객의 첫 방문시간대 */
    SELECT
      user_pseudo_id,
      MAX(FORMAT_TIME('%H',TIME(TIMESTAMP_MICROS(event_timestamp)))) AS first_visit_hour
    FROM `events_*`, UNNEST(event_params) AS event_params
    WHERE event_name = 'first_visit' 
    AND _TABLE_SUFFIX > FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH))
    GROUP BY user_pseudo_id
  ),

  device_table AS (
    SELECT user_pseudo_id,
      MAX(device.operating_system) AS device_os,
      MAX(device.web_info.browser) AS device_browser
    FROM `events_*`
    WHERE _TABLE_SUFFIX > FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH))
    GROUP BY user_pseudo_id
  ),

  geo_table AS (
    SELECT user_pseudo_id,
      MAX(geo.region) AS region,
     FROM `events_*`
    WHERE _TABLE_SUFFIX > FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)) 
    GROUP BY user_pseudo_id
  )

SELECT main.user_pseudo_id, 
    main.last_visit,
    main.time_second,
    view_table.view_item_count AS view_item_count,
    view_table.source AS view_item_source,
    view_table.medium AS view_item_medium,
    view_table.content AS view_item_content,
    first_visit_table.first_visit_hour,
    device_table.device_os,
    device_table.device_browser,
    geo_table.region,
    main.purchase_yn
  FROM main
  LEFT JOIN first_visit_table
  ON main.user_pseudo_id = first_visit_table.user_pseudo_id
  LEFT JOIN view_table
  ON main.user_pseudo_id = view_table.user_pseudo_id
  LEFT JOIN device_table
  ON main.user_pseudo_id = device_table.user_pseudo_id
  LEFT JOIN geo_table
  ON main.user_pseudo_id = geo_table.user_pseudo_id

---

Reference

GA에서 고객 식별 : https://osoma.kr/blog/ga4-user-id/

GA 로우데이터 스키마 : https://support.google.com/analytics/answer/7029846?sjid=14257891707400598045-AP#zippy=%2Cuser%2Citems