GameWith Developer Blog

GameWith のエンジニア、デザイナーが技術について日々発信していきます。

非エンジニアがBigQueryとDataPortalを使って初日のイベント別継続率を出してみた話 #GameWith #TechWith

こんにちは！

ディレクターのケンシロウです。

今回は非エンジニアの自分がGoogle Big QueryとData Portalを使ってのアプリデータ分析に取り組んだので、勉強方法や実際のクエリなどを紹介したいと思います。

目次

Google BigQueryとは

BigQueryはグーグルが提供しているビッグデータ解析サービスです。SQLクエリで大量のログデータから、欲しい情報を取り出すことが簡単にできます。

データのクエリは課金されます。最初の1TBは無料です。

cloud.google.com

Google Data Portalとは

Data Portalはグーグルが提供しているBI（ビジネスインテリジェンス）ツールです。様々なデータベースに接続して、簡単にデータをまとめたり、グラフで表示したりできます。 datastudio.google.com

BigQueryとDataPortalを使うことになった背景

もともと弊社ではBigQueryを使っていて、今回可視化のツールとして相性が良いDataPortalを選びました。

非エンジニアの自分がやるきっかけ

自分が所属している開発部内で、データドリブンなプロダクトの改善をしていきたいというニーズがありました。

しかし、社内には専属でデータ分析を行うエンジニアがいませんでした。

自分は5年前に少しだけSQLを触ったことはあり、この機会に知見を深めたいと思い立候補しました。

しかし実際はほぼ覚えていない状態からスタートだったので苦戦しました。

学習方法

社内で書かれている既存のクエリをとにかく読み込み、実際に叩きながら自分でコメントもつけていき、一行一行理解していきました。

次に、BigQueryのデータテーブルのプレビューを見てデータ構造の理解を深めました。 プレビューはスプレッドシートとそんなに変わらない印象を受けました。

わからないところはQiitaで使えそうな記事がないか探して、まずはそこに載っているクエリをコピペして叩いてみて理解を深めました。

qiita.com

ぐぐってもわからないものは社内のエンジニアに聞いてました。

さらに、社内のエンジニアとペアプロ的な事をして勉強をしました。

実際のクエリの紹介

アプリの継続率を高めるにあたってマジックナンバーを見つけるために、どういう事をしている人が継続率が高いのかをまず知る必要がありました。

いろいろ調べたましたが、ちょうど使えるクエリがなかったのでクエリを自作しました。

qiita.com

クエリ1

初日に行ったアクション別のユーザー群の継続率を表示するクエリ

#standardSQL

#日付を文字列にするファンクションを生成
create temp function date_to_string(date date) returns string as (
  concat(format_date("%Y", date), format_date("%m", date), format_date("%d", date))
);

#集計する最終日を現在の日付の差で指定
create temp function end_date() returns date as (
  date_add(current_date(), interval - EXTRACT(DAYOFWEEK FROM current_date())-1 day)
);

#集計する開始日を現在の日付の差で指定
create temp function begin_date() returns date as (
  date_add(end_date(), interval -14 day));

#必要な仮想テーブルを記載していくので定義
WITH 

#──────────────────────
#まずは初回起動の日のユーザIDの仮想テーブルを作成する。
start AS (
SELECT 
#ユーザID
  user_pseudo_id,
#初回希望日 
 EXTRACT(DATE FROM TIMESTAMP_MICROS( user_first_touch_timestamp) AT TIME ZONE "Asia/Tokyo") AS　start_date
#データテーブルを選択
FROM
`＜参照するデータテーブル＞`

#条件式を記載していく。 

WHERE
#まずは集計期間の設定、ファンクションで定義したものを使う。（データセットで指定しているので文字列）
_table_suffix between date_to_string(begin_date()) and date_to_string(end_date())

 #初日に行なっているアクションの条件
 AND event_name = "＜イベント名＞"
#初日にイベントを行なっているかどうかの条件
and date(timestamp_micros(user_first_touch_timestamp), "Asia/Tokyo")  = PARSE_DATE("%Y%m%d", event_date)

#以下でグルーピングする。
GROUP BY
  user_pseudo_id,start_date
),

#──────────────────────
#ここからは初回起動の日に関わらず使用日ごとのユーザIDをみていく。
usedate AS (
#以下でユーザIDと日付を取得
SELECT 
#ユーザID
user_pseudo_id,
#イベントを行なった日付（この場合アプリ起動日）、文字列から日付データに変換している。
PARSE_DATE("%Y%m%d", event_date) AS use_date

#データテーブルを選択
FROM
`＜参照するデータテーブル＞*`

#条件式を書いていく 
WHERE
#まずは集計期間の設定、ファンクションで定義したものを使う。（データセットで指定しているので文字列）
_table_suffix between date_to_string(begin_date()) and date_to_string(end_date())

#継続して同じイベントをしている継続率が欲しい場合は下記のイベントを変更する。現状は起動時に送るセッションスタートを使用
AND event_name = "session_start"
#以下でまとめる。
GROUP BY
  user_pseudo_id,
  use_date
),
#──────────────────────
#ここからはリテンションしているユーザのIDを出す。
retention AS(
SELECT
  start.user_pseudo_id,
  start.start_date,
  IFNULL(DATE_DIFF(usedate.use_date, start.start_date,  day), 0) AS retention_day

FROM
# 上記２つの仮想テーブルからINNER JOINでIDが一致するデータのみに絞り込んでいる。（一応初回起動日以降もいれている。）
  start
  INNER JOIN usedate ON start.user_pseudo_id = usedate.user_pseudo_id AND usedate.use_date >= start.start_date

GROUP BY
  start.user_pseudo_id,
  start.start_date,
  retention_day),
  
#──────────────────────
#ここからリテンションDAYのユーザIDをカウントしてUU数にしていく。用途によっては不要
retention_sum AS(
SELECT
  start_date,
  retention_day,
  count(user_pseudo_id) AS uu
FROM
  retention
GROUP BY
  start_date,
  retention_day)
#──────────────────────
#最後に表示する項目をまとめる。
SELECT
  start_date,
#初日のUU数を出しておく。これが分母になるのでグラフ表示にも使いやすい。 
 SUM(CASE WHEN retention_day = 0 THEN uu ELSE NULL END) AS startuu,
#初日からの差分（経過日数）別に継続率を出していく。
 SUM(CASE WHEN retention_day = 1 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_day = 0 THEN uu ELSE NULL END) AS oneday,
  SUM(CASE WHEN retention_day = 3 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_day = 0 THEN uu ELSE NULL END) AS threeday,
  SUM(CASE WHEN retention_day = 7 THEN uu ELSE NULL END) / SUM(CASE WHEN retention_day = 0 THEN uu ELSE NULL END) AS week
FROM
  retention_sum
GROUP BY
  start_date
ORDER BY
  start_date ASC

実際にデータを出してみて

苦労した事

仮想テーブルを理解するまで時間がかかりました。

2つのテーブルのJOINの結果のイメージが最初はできず、実際に手でとにかくノートに書いてみたりしました。

最初はクエリを叩いてもデータが見つからなくて苦労しました。

特にイベントネームとカラムの名前の区別がついておらず、全部変数を日本語にしてほしいなと感じました。

（エンジニアが定義した変数をググっていて、全然出てこなくて徒労に終わりましたw）

firebaseの純正のものはsession_startだったり基本英語で用意されているが社内のイベントは日本語のものも多く、混乱しました。

無限ループになるとエラーが表示されないので、デバッグに苦労しました（時間で課金ではないはずなのでヒヤヒヤしました）

仮想テーブルを一つずつ実行してどこで無限ループになっているか、地道にデバッグをしました。

→デバッグはできるだけ少量のデータで実行して節約しました。

学習中のため表示できない理由が自分の知識不足なのか、そもそもログが存在しないのか判断が難しかったです。

頑張ってみてだめだったら、別のアプローチで似たようなデータが出せないか模索をしていました。

DAUでUUを定義してしまうと、MAUを出すときにDAU * 30みたいな数値になり正確な数値が取れないのでUUの定義は気をつけないといけませんでした。

（自分はGAのデータと比較して差異があったので気づけました）

自分はBigQueryでUUを集計するのではなく、DataPortalで集計することで正確なMAUを算出しました。

（DataPortalでは参照する日付を簡単に変更することができるため、DataPortalでの算出がおすすめです）

良かった点

周りのエンジニアに綺麗なクエリを書くと褒められた！

基本を覚えるとその組み合わせで割と何でも出せるようになるので、もう出せないものはなくなりました。

クエリをわかってくるとデータを算出するのが面白くなってきました！

今後半年の目標がデータアナリストとして活躍することになった（キャリアめっちゃ変わった）

終わりに

現場でデータが身近になっているところは少ない印象です。

なので、ここを整備すると全体のアイデアの質も数字に対する意識も上がっていくと思います。

是非みなさんの現場でもデータ分析を活用してみてください！

今後やりたい事

  • ユーザーが行ったイベントの回数に応じてユーザー群を切り分けて継続率を算出
  • 常に見えるところに大きなモニターなどでデータを表示

最後に

GameWithのDeveloper向けTwitterアカウントを開設しました。

もくもく会の告知やブログの更新情報などを発信するので良かったらフォロー宜しくお願いします！

twitter.com