AI 및 머신러닝

BigQuery ML을 사용하여 모바일 게임 유저의 이탈 예측하기

2022년 12월 22일

David (Taebeom) Kim

AI Consultant

Google Cloud 사용해 보기

$300의 무료 크레딧과 20개 이상의 항상 무료인 제품으로 Google Cloud 사용을 시작해보세요.

BigQuery를 Google Analytics 및 Firebase 와 함께 활용함으로써, 서비스 관리자는 고객들의 행동데이터를 비즈니스 의사 결정에 적극 활용할 수 있습니다. 본 블로그에서는 총 2부로 나누어 BigQuery와 BigQuery ML을 사용하여 모바일 게임 유저의 이탈을 사전에 예측하고, 리텐션을 높이는 방법을 알아볼 예정입니다.

[Business Background]

Flood-It! 이라는 모바일 퍼즐 게임을 제공하는 회사가 있다고 한번 가정을 해보겠습니다. (Flood-it!은 구글의 Lab Pixies 에서 만든 모바일 퍼즐 게임입니다.)

https://storage.googleapis.com/gweb-cloudblog-publish/images/Flood-it.max-1500x1500.png

Flood-It! 게임 화면

QuickPlay 를 통해 체험을 해볼 수도 있고,Progressive 모드를 통해 단계를 넘어가며 점수를 쌓을 수도 있습니다. 하나의 Stage를 클리어 하면 다음단계로 갈지, 메인 메뉴로 갈지 등을 선택할 수 있습니다. 게임에 지게되면 Game Over 화면이 뜨면서 메인메뉴로 갈 수 있으며 Progressive 모드를 하게되면 통계 화면에서 자신의 통계를 볼 수도 있습니다. 이때 난이도는 S, M, L 로 갈 수록 어려워 진다고 보시면 됩니다.

회사에서는 약 75%의 유저가 24시간 내에 이탈한다는 것을 발견했습니다. 이것을 해결하기 위해 이탈할 유저들을 사전에 예측해서 그들의 리텐션을 높이는 것을 목표로 하고 있습니다.마침 회사에서는 Firebase를 통해 수집된 Google Analytics 데이터, 즉 유저의 행동 데이터를 보유중입니다.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image3_NEFOsaS.max-1300x1300.png

그러면 우리가 어떻게 사용자의 이탈을 예측하고, 리텐션을 높일 수 있을까요? 아래 절차를 통해서 가능합니다.

[Problem Solving]

사용자의 행동데이터를 수집
수집된 데이터를 활용하여 이탈 여부를 예측하는 머신러닝 모델을 학습
이후 새로운 유저가 방문을 할 경우
사전에 학습시켜둔 모델을 활용하여 이 유저의 이탈 여부를 예측
이탈 가능성이 높은 유저에게는 쿠폰을 지급한다거나 프로모션을 주는 등의 Message를 발송해서 이탈을 방어

이를 도식화하면 아래 그림과 같습니다.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image6_mp4Lxv2.max-1200x1200.png

2번 단계에서 모델을 학습하기 위해서는 데이터를 분석하고, 모델을 생성하고, 평가하고 튜닝하는 등의 작업이 필요한데 이때 빅쿼리 ML과 Looker Studio를 활용하게 됩니다. 또한 4번 단계에서 유저의 이탈을 예측할때는 빅쿼리 ML을 활용하게 됩니다. 이렇게 예측한 결과는 GA에서 Segment를 구분하여 관리를 하실 수도 있고 Firebase의 Cloud Messaging과 연동을 하여 대상자에게 Notification을 발송할 수도 있습니다.

이제 위의 절차들을 하나씩 살펴보도록 하겠습니다.

[데이터 수집]

데이터 수집에는 아래 작업들이 포함됩니다.

1) GA4 또는 Firebase 에서 BigQuery 로 Data Export
2) Export된 데이터 조회
3) 데이터 가공 및 Preprocessing

1. GA4 또는 Firebase 에서 BigQuery 로 Data Export

GA4 에서는 BigQuery로 데이터를 Streaming Export 할 수 있도록 기능을 제공하고 있으며 이는 [GA4] BigQuery Export 가이드를 통해 설정하실 수 있습니다. 또한 과거의 Google Analytics for Firebase 를 사용하실 경우 [GA4] Firebase integration 가이드를 참고하여 GA4 로 통합이 가능합니다. Google Analytics for Firebase 에서 BigQuery로의 Export를 하실 경우 Export project data to BigQuery 가이드를 참고하여 추출이 가능합니다.

본 실습에서는 이미 Data가 Export 되어있는 상황을 가정하도록 하겠습니다.

2. Export된 데이터 조회

이번에는 위의 1단계를 통해 BigQuery에 이미 추출된 Flood-It! 게임 유저들의 행동 데이터를 활용하겠습니다. 실습에서 사용할 데이터는 Public Dataset으로 오픈되어있어 누구나 조회가 가능합니다. 빅쿼리에 접속하여 데이터를 조회하는 방법은 query-public-dataset-console 가이드를 참고하시면 되며 데이터를 조회하는 쿼리는 아래와 같습니다.

lang-sql

로드 중...

아래 그림과 같이 쿼리를 입력하여 조회하면 콘솔 내에서 결과를 확인할 수 있습니다.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image10_2cc2pWK.max-1500x1500.png

데이터 조회 결과 2018.06.12 ~ 2018.10.03 기간 동안의 모바일 유저의 행동데이터가 수집되어있습니다. 이를 Json 형태로 보면 사용자를 나타내는 식별자(user_pseudo_id) 와 이벤트 발생 시점 (event_timestamp) 칼럼으로 구성이 되어있음을 알 수 있습니다. 이벤트란 사용자가 App 안에서 있었던 상호작용(Interaction) 을 의미합니다. 예를들어 화면을 로드한다거나, 클릭을 한다거나 하는 각각의 행위를 수집한것을 이벤트 라고 합니다.

lang-py

로드 중...

[
  {
    "event_date": "20180723",
    "event_timestamp": "1532389933625001",
    "event_name": "level_retry",
    "event_params": [
      {
        "key": "firebase_event_origin",
        "value": {
          "string_value": "app+gtm",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      },
      ……
    ],
    ……
    "user_pseudo_id": "F2D0B7E8667E4855BEFD39ECA3185BF7",
    "user_properties": [
      {
        "key": "ad_frequency",
        "value": {
          "string_value": "3",
          "int_value": null,
          "float_value": null,
          "double_value": null,
          "set_timestamp_micros": "1483823843814000"
        }
      },
      ……
    ],
    ……
    "device": {
      "category": "tablet",
      	……
      "operating_system": "ANDROID",
	……
      "browser": null,
      "browser_version": null,
      "web_info": null
    },
    "geo": {
      "continent": "Americas",
      "country": "United States",
      ……
  },
……
]

3. 데이터 가공 및 Preprocessing

데이터 가공에서는 다음의 절차들을 거칠 예정입니다.

1) 유저의 이탈여부 칼럼 생성
2) 유저의 Demographic 칼럼 추출
3) 행동 데이터를 유저 기준으로 병합 (Aggregation)하여 각 Event의 횟수 추출
4) 데이터 탐색을 통한 추가 Feature 생성
5) 추출된 칼럼들을 조합하여 학습 데이터 생성

1) 유저의 이탈여부 칼럼 생성

유저의 이탈 여부를 예측하기 위해서 user_pseudo_id를 기준으로 하는, 유저의 이탈 여부 칼럼을 View로 만들어보겠습니다. 유저의 최초 상호작용(App 설치) 후 24시간 이후에도 아무런 상호작용이 없을 경우 이탈로 간주합니다. 이를 확인하기 위해서 우선 유저별로 최초 상호작용 시간과 마지막 상호작용 시간을 조회하여 firstlasttouch 라는 임시 테이블로 조회합니다.

lang-sql

로드 중...

유저의 마지막 상호작용이 최초 상호작용 보다 24시간 이내일때 (즉 24시간 이후에는 어떠한 상호작용도 없었음) 이를 이탈(churned) 로 간주합니다.

lang-sql

로드 중...

유저의 마지막 상호작용이 최초 상호작용 보다 10분 이내일때 (즉 10분 이후에는 어떠한 상호작용도 없을 경우) 이를 bounced 로 정의합니다. 즉 앱 설치 후 10분 이내에 흥미를 잃고 떠나는 유저를 의미합니다.

다음으로 View를 생성하기 위해서는 View를 저장할 위치 (Dataset) 을 만들어주어야 됩니다. 아래 쿼리를 통해 demo Dataset을 쉽게 만들 수 있습니다.

로드 중...

Dataset까지 만들었다면 View를 생성하는 쿼리는 아래와 같습니다.

lang-sql

로드 중...

View로 생성된 결과는 BigQuery Console 의 좌측 네비게이션 바에서 보실 수 있습니다.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image9_JlW8E9q.max-1300x1300.png

2) 유저의 Demographic 칼럼 추출

Demographic 칼럼에는 유저의 접속 국가, 사용한 기종, OS, 언어 등이 포함되어있습니다. 유저의 마지막 접속 Demographic 정보를 추출하여 Feature 로 만드는 쿼리는 아래와 같습니다.

lang-sql

로드 중...

동일한 방법으로 유저의 최초 접속 Demographic 정보도 추출이 가능합니다. row_num 칼럼의 ORDER BY 구문만 ASC 로 바꿔주면 됩니다.

lang-sql

로드 중...

3) 행동 데이터를 유저 기준으로 병합 (Aggregation)하여 각 Event의 횟수 추출

유저별로 Event 데이터가 Row로 구성이 되어있는데 이를 하나로 병합하기 위해서 Event 의 횟수를 추출하겠습니다.

lang-sql

로드 중...

CREATE OR REPLACE VIEW demo.user_aggregate_behavior AS (
WITH
 events_first24hr AS (
   #select user data only from first 24 hr of using the app
   SELECT
     e.*
   FROM
     `firebase-public-project.analytics_153293282.events_*` e
   JOIN
     demo.returningusers r
   ON
     e.user_pseudo_id = r.user_pseudo_id
   WHERE
     e.event_timestamp <= r.ts_24hr_after_first_engagement
   )
SELECT
 user_pseudo_id,
 SUM(IF(event_name = 'user_engagement', 1, 0)) AS cnt_user_engagement,
 SUM(IF(event_name = 'level_start_quickplay', 1, 0)) AS cnt_level_start_quickplay,
 SUM(IF(event_name = 'level_end_quickplay', 1, 0)) AS cnt_level_end_quickplay,
 SUM(IF(event_name = 'level_complete_quickplay', 1, 0)) AS cnt_level_complete_quickplay,
 SUM(IF(event_name = 'level_reset_quickplay', 1, 0)) AS cnt_level_reset_quickplay,
 SUM(IF(event_name = 'post_score', 1, 0)) AS cnt_post_score,
 SUM(IF(event_name = 'spend_virtual_currency', 1, 0)) AS cnt_spend_virtual_currency,
 SUM(IF(event_name = 'ad_reward', 1, 0)) AS cnt_ad_reward,
 SUM(IF(event_name = 'challenge_a_friend', 1, 0)) AS cnt_challenge_a_friend,
 SUM(IF(event_name = 'completed_5_levels', 1, 0)) AS cnt_completed_5_levels,
 SUM(IF(event_name = 'use_extra_steps', 1, 0)) AS cnt_use_extra_steps,
FROM
 events_first24hr
GROUP BY
 1
 );

하지만 이정도의 가공으로 충분할까요?

머신러닝의 정확도를 높이기 위해 데이터의 특성을 파악하여 Feature들을 더 추가해보겠습니다.

4) 데이터 탐색을 통한 추가 Feature 생성

데이터 탐색을 위해서

1) 가설을 세우고
2) Looker Studio 를 통하여 데이터를 시각화 하여 가설을 확인해보고
3) 의미있는 Feature를 추가하도록 하겠습니다.

4.1) 가설

사용자의 가장 마지막 Action(Event) 가 이탈률 예측에 있어서 중요한 역할을 할 것

4.2) 시각화

Event 발생 비율을 시각화 해서 보면, screen_view 이벤트와 user_engagement 이벤트, level_start_quick_play, level_end_quick_play, post_score, level_start, level_end 이벤트 순으로 비중이 줄어드는것을 볼 수 있습니다.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image14_ArSh1Uc.max-400x400.png

Event의 발생 비중

https://storage.googleapis.com/gweb-cloudblog-publish/images/image11_2nl6s4T.max-900x900.png

우선 사용자가 마지막으로 본 화면 (last_screen_view) 를 추출해보겠습니다. 유저별로 screen_view event 의 마지막 timestamp 를 조회한 결과를 임시 테이블로 만듭니다.

lang-sql

로드 중...

이후 해당 시점에 조회한 화면값을 p.value.string_value 를 통해 추출합니다. 이때 bounced 유저는 제외(즉 10분 내 흥미를 잃고 떠난 유저는 제외) 하여 살펴보겠습니다.

lang-sql

로드 중...

이제 조회한 결과를 활용하여 Looker Studio에서 last screen view 와 이탈률의 관계를 시각화하여 살펴보겠습니다.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image8_DuzYttO.max-1400x1400.png

Looker Studio를 통해 확인해보니 화면별로 이탈률(이탈한 유저 수 / 총 유저 수) 이 다르게 분포하므로 해당 Feature를 활용하도록 하겠습니다.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image15_QqsefQt.max-1200x1200.png

4.3) Feature 추가

마찬가지 방법으로 이에따라 아래 Feature 들을 추가해보겠습니다.

last_screen_view : 사용자가 가장 마지막으로 본 화면
last_level_start_quickplay : 퀵플레이에서 마지막으로 시작한 레벨 (S : Easy, M : Normal, L : Hard)
last_level_end_quickplay : 퀵플레이에서 마지막으로 종료한 레벨(S : Easy, M : Normal, L : Hard)
last_post_score : 마지막으로 남긴 게임 평가 점수 (post_score)
last_user_engagement : 앱 종료 직전에 발생했던 액션(Event)
last_level_start : 본게임에서 마지막으로 시작한 레벨
last_level_end : 본게임에서 마지막으로 종료한 레벨

last_level_start_quickplay, last_level_end_quickplay 등의 Feature 들을 추가하여 last_events_detail View로 만드는 쿼리는 아래와 같습니다.

lang-sql

로드 중...

CREATE OR REPLACE VIEW demo.last_events_detail AS (
WITH screen_view as (
  SELECT  e.user_pseudo_id,
          max(e.event_timestamp) as last_event_timestamp
  FROM
    `firebase-public-project.analytics_153293282.events_*` e,
    demo.returningusers s
  WHERE e.event_name="screen_view"
  AND   e.event_timestamp <= s.ts_24hr_after_first_engagement
  AND   e.user_pseudo_id = s.user_pseudo_id
  GROUP by 1
)
,level_start_quickplay as (
  SELECT  e.user_pseudo_id,
          max(e.event_timestamp) as last_event_timestamp
  FROM
    `firebase-public-project.analytics_153293282.events_*` e,
    demo.returningusers s
  WHERE e.event_name="level_start_quickplay"
  AND   e.event_timestamp <= s.ts_24hr_after_first_engagement
  AND   e.user_pseudo_id = s.user_pseudo_id
  GROUP by 1
)
,level_end_quickplay as (
  SELECT  e.user_pseudo_id,
          max(e.event_timestamp) as last_event_timestamp
  FROM
    `firebase-public-project.analytics_153293282.events_*` e,
    demo.returningusers s
WHERE e.event_name="level_end_quickplay"
  AND   e.event_timestamp <= s.ts_24hr_after_first_engagement
  AND   e.user_pseudo_id = s.user_pseudo_id
  GROUP by 1
)
,post_score as (
  SELECT  e.user_pseudo_id,
          max(e.event_timestamp) as last_event_timestamp
  FROM
    `firebase-public-project.analytics_153293282.events_*` e,
    demo.returningusers s
  WHERE e.event_name="post_score"
  AND   e.event_timestamp <= s.ts_24hr_after_first_engagement
  AND   e.user_pseudo_id = s.user_pseudo_id
  GROUP by 1
)
,user_engagement as (
  SELECT  e.user_pseudo_id,
          max(e.event_timestamp) as last_event_timestamp
  FROM
    `firebase-public-project.analytics_153293282.events_*` e,
    demo.returningusers s
  WHERE e.event_name="user_engagement"
  AND   e.event_timestamp <= s.ts_24hr_after_first_engagement
  AND   e.user_pseudo_id = s.user_pseudo_id
  GROUP by 1
)
,level_start as (
  SELECT  e.user_pseudo_id,
          max(e.event_timestamp) as last_event_timestamp
  FROM
    `firebase-public-project.analytics_153293282.events_*` e,
    demo.returningusers s
  WHERE e.event_name="level_start"
  AND   e.event_timestamp <= s.ts_24hr_after_first_engagement
  AND   e.user_pseudo_id = s.user_pseudo_id
  GROUP by 1
)
,level_end as (
  SELECT  e.user_pseudo_id,
max(e.event_timestamp) as last_event_timestamp
  FROM
    `firebase-public-project.analytics_153293282.events_*` e,
    demo.returningusers s
  WHERE e.event_name="level_end"
  AND   e.event_timestamp <= s.ts_24hr_after_first_engagement
  AND   e.user_pseudo_id = s.user_pseudo_id
  GROUP by 1
)
select *,
     (SELECT  p.value.string_value
      FROM
        `firebase-public-project.analytics_153293282.events_*` e,
        screen_view t
        LEFT JOIN UNNEST(event_params) p
      WHERE e.event_name="screen_view"
      AND   p.key = 'firebase_screen_class'
      AND   e.event_timestamp = t.last_event_timestamp
      AND   e.user_pseudo_id = tr.user_pseudo_id ) as last_screen_view,
    (SELECT  p.value.string_value
      FROM
        `firebase-public-project.analytics_153293282.events_*` e,
        level_start_quickplay t
        LEFT JOIN UNNEST(event_params) p
      WHERE e.event_name="level_start_quickplay"
      AND   p.key = 'board'
      AND   e.event_timestamp = t.last_event_timestamp
      AND   e.user_pseudo_id = tr.user_pseudo_id ) as last_level_start_quickplay,
    (SELECT  distinct p.value.string_value
      FROM
        `firebase-public-project.analytics_153293282.events_*` e,
        level_end_quickplay t
        LEFT JOIN UNNEST(event_params) p
      WHERE e.event_name="level_end_quickplay"
      AND   p.key = 'board'
      AND   e.event_timestamp = t.last_event_timestamp
      AND   e.user_pseudo_id = tr.user_pseudo_id ) as last_level_end_quickplay,
    (SELECT  p.value.string_value
      FROM
        `firebase-public-project.analytics_153293282.events_*` e,
 post_score t
        LEFT JOIN UNNEST(event_params) p
      WHERE e.event_name="post_score"
      AND   p.key = 'level_name'
      AND   e.event_timestamp = t.last_event_timestamp
      AND   e.user_pseudo_id = tr.user_pseudo_id ) as last_post_score,
    (SELECT  p.value.string_value
      FROM
        `firebase-public-project.analytics_153293282.events_*` e,
        user_engagement t
        LEFT JOIN UNNEST(event_params) p
      WHERE e.event_name="user_engagement"
      AND   p.key = 'firebase_screen_class'
      AND   e.event_timestamp = t.last_event_timestamp
      AND   e.user_pseudo_id = tr.user_pseudo_id ) as last_user_engagement,
    (SELECT  p.value.string_value
      FROM
        `firebase-public-project.analytics_153293282.events_*` e,
        level_start t
        LEFT JOIN UNNEST(event_params) p
      WHERE e.event_name="level_start"
      AND   p.key = 'level_name'
      AND   e.event_timestamp = t.last_event_timestamp
      AND   e.user_pseudo_id = tr.user_pseudo_id ) as last_level_start,
    (SELECT  p.value.string_value
      FROM
        `firebase-public-project.analytics_153293282.events_*` e,
        level_end t
        LEFT JOIN UNNEST(event_params) p
      WHERE e.event_name="level_end"
      AND   p.key = 'level_name'
      AND   e.event_timestamp = t.last_event_timestamp
      AND   e.user_pseudo_id = tr.user_pseudo_id ) as last_level_end
from demo.returningusers tr
)

5) 추출된 칼럼들을 조합하여 학습 데이터 생성

앞에서 추출한 칼럼들을 하나로 병합하여 새로운 View를 생성하겠습니다.

lang-sql

로드 중...

SELECT
  dem.*,
  IFNULL(beh.cnt_user_engagement, 0) AS cnt_user_engagement,
  IFNULL(beh.cnt_level_start_quickplay, 0) AS cnt_level_start_quickplay,
  IFNULL(beh.cnt_level_end_quickplay, 0) AS cnt_level_end_quickplay,
  IFNULL(beh.cnt_level_complete_quickplay, 0) AS cnt_level_complete_quickplay,
  IFNULL(beh.cnt_level_reset_quickplay, 0) AS cnt_level_reset_quickplay,
  IFNULL(beh.cnt_post_score, 0) AS cnt_post_score,
  IFNULL(beh.cnt_spend_virtual_currency, 0) AS cnt_spend_virtual_currency,
  IFNULL(beh.cnt_ad_reward, 0) AS cnt_ad_reward,
  IFNULL(beh.cnt_challenge_a_friend, 0) AS cnt_challenge_a_friend,
  IFNULL(beh.cnt_completed_5_levels, 0) AS cnt_completed_5_levels,
  IFNULL(beh.cnt_use_extra_steps, 0) AS cnt_use_extra_steps,
  ret.user_first_engagement,
  EXTRACT(MONTH from TIMESTAMP_MICROS(ret.user_first_engagement)) as month,
  EXTRACT(DAYOFYEAR from TIMESTAMP_MICROS(ret.user_first_engagement)) as julianday,
  EXTRACT(DAYOFWEEK from TIMESTAMP_MICROS(ret.user_first_engagement)) as dayofweek,
  led.last_screen_view,
  led.last_level_start_quickplay,
  led.last_level_end_quickplay,
  led.last_post_score,
  led.last_user_engagement,
  led.last_level_start,
  led.last_level_end,
  ret.churned,
FROM
  demo.returningusers ret
LEFT OUTER JOIN
  demo.user_demographics dem
ON
  ret.user_pseudo_id = dem.user_pseudo_id
LEFT OUTER JOIN
  demo.user_aggregate_behavior beh
ON
  ret.user_pseudo_id = beh.user_pseudo_id
LEFT OUTER JOIN
  demo.last_events_detail led
ON
  ret.user_pseudo_id = led.user_pseudo_id
WHERE ret.bounced = 0

만들어진 Train View 를 통해 데이터를 조회해보겠습니다. 총 8031 건의 데이터가 조회되며 데이터가 user_pseudo_id를 기준으로 훨씬 단순해진것을 볼 수 있습니다.

lang-sql

로드 중...