iyuichiの私的開発ログ

渋谷で働くWebエンジニアのログ. Java, Android, iOS, Docker, GCP, AWS, ゲーム開発

FQ5の集計をする - BigQuery

FQ5というKPIをご存知でしょうか?
ソーシャルゲーム界隈ではおなじみの連続ログイン5日のユーザIDやその数を集計して、キャンペーンやイベントの成果を計測したりするために参照します。

ある日のDAUを連続ログイン5日、4日、3日、2日、1日と各ユーザ数にわけて数字を集計するのですがこいつをFQ5と呼びます。
ちなみにFQの定義を連続ログインではなくて、直近5日間のログイン回数と定義してたとえば以下のように1日おきとか間をおいて遊んでくれているユーザもFQ3とカウントしてよい場合にはもう少し簡単なクエリにできます。

今日 昨日 2日前 3日前 4日前
× ×
× ×
× ×

今回紹介しているクエリは"連続"のみをカウントするものです。
FQ3であれば以下のパターンのみが該当するように集計します。

今日 昨日 2日前 3日前 4日前
× ×
×


それでどうやって集計するかというのを考えるのに私の頭では丸一日くらいかかってしまったので、誰かの参考になれば良いなと思って記事書いてます。

さて、まずはDAUの集計ですがこちらはあまり難しいことはないですが前提となるデータなので一応説明しておきます。
ユーザIDが含まれたログをfluentdとかでMongoDBとかHiveとかに流し込んで、ユーザIDでdistinctしてやって、アクセス日とかと一緒にリスト化します。

例えばこんな感じのリスト。

userid dt
0001 20150501
0011 20150501
0023 20150501
0055 20150501

こいつがDAUの元ネタになります。
これを日ごとにBigQueryに取り込みます。

たとえば、dauというデータセットをつくってdau_20150501, dau_20150502, dau_20150503みたいな感じで取り込むとよいです。

$ bq ls dau
    tableId      Type
 -------------- -------
  dau_20150501   TABLE
  dau_20150502   TABLE
  dau_20150503   TABLE
bq show dau.dau_20150501
Table xxxxxx:dau.dau_20150501

   Last modified                Schema                Total Rows   Total Bytes   Expiration
 ----------------- --------------------------------- ------------ ------------- ------------
  02 May 05:05:00  |- id: string (required)
                              |- dt: string

なんかこんな雰囲気で。
これで日毎のアクセスユーザとその数はわかるようになった訳です。

で、連続ログインを考えます。
FQ1-5の関係ですが以下のようにFQ1(DAU)をベースとした中にFQ2-5のユーザがそれぞれ含まれた集合になります。

f:id:iyuichi:20150528161419p:plain

なので単純に集計するとたとえばFQ5とFQ4の中に重複してカウントしてしまうので、FQ4を集計するときにはFQ5の人は除きたい。
FQ1~FQ5の数字を全て足すとその日のDAUの数字と一致するような値をとりたい訳です。

FQ5のデータを集計して、そこに含まれているユーザはFQ4から除外して、、、とかいうのを1発のクエリでとれないかなと色々試行錯誤してやっと思いついたのが以下です。


FQ5 Query

見てもらえばだいたいわかるかと思いますが、アクセス日ごとにそれぞれビット演算できるように1,2,4,8,16とフラグをつけてユーザIDごとに集計してしまって、そいつに論理積で連続ログインを判定するようにしています。
ここにたどり着くまで丸一日くらい試行錯誤してしまいました。。。。。

合わせて読む
実務目線で見るソーシャルゲームのデータ解析 #at_tokuben - by shigemk2



Google BigQuery

新品価格
¥4,968から
(2015/5/28 16:23時点)

Google BigQueryではじめる自前ビッグデータ処理入門

新品価格
¥2,376から
(2015/5/28 16:23時点)

Google BigQuery Analytics

新品価格
¥4,340から
(2015/5/28 16:23時点)