FQ5の集計をする - BigQuery - iyuichiの私的開発ログ

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

ある日のDAUを連続ログイン５日、４日、３日、２日、１日と各ユーザ数にわけて数字を集計するのですがこいつをFQ5と呼びます。
ちなみにFQの定義を連続ログインではなくて、直近５日間のログイン回数と定義してたとえば以下のように１日おきとか間をおいて遊んでくれているユーザも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