この記事は Nikkei Advent Calendar 2021 の 19 日目の記事です。

こんにちは。エンジニアの田中です。 API・バックエンドチームとして主に開発だったり分析周りだったりを担当しています。

今回BigQueryを用いた分析例を一つ紹介しようと思います。 紹介するのは、ユーザーの会員状態を持ったDBのスナップショットしかデータがとれてない時の会員ごとの会員継続期間の計測方法です。

読者レベル的にはOVER句を使う、分析集計関数を使うようになったぐらいを想定しています。 意外とこのレベルのクエリの情報はあまりなさそうだったというのも書く理由になっています。

日経の分析環境について

日経での分析業務では詳細な分析をBigQuery+Redash、ユーザー単位のクイックな探索にElasticSearch＋Kibanaを分析に用いることが多いです。 自分が日経に入社する以前の環境でもBigQueryを使っていましたが、レコード数が数百万オーダー程度であることが多く、また課金体系がオンデマンドでした。 余裕でローカルで動く量、かつトライアンドエラーにそこそこのお金がかかる環境だったので、ローカルマシンにデータを落としてPythonを使って分析をしていましたし、周囲でもそういった人が多かったです。

一方で、日経電子版のログデータは月百万人以上のユニークユーザーからのアクセスを記録したものであり、 数ヶ月分のデータをスキャンすると数TBに及ぶためローカルにレコードを落としてきて分析するのも少し手間です。 また日経の分析環境ではBigQueryのリソース管理に関してBigQuery Reservationsを利用しており、スキャン量に依存した課金体系ではありません。 すなわちスキャン量を気にせずクエリを投げられます。

ローカルで処理するにはダウンロード/保持すら面倒なデータ量でスキャン量には依存しない課金体系のため、分析はできるだけBigQueryで完結させています。とはいえ完全に難しいわけではなくEKSを使ったスケーラブルなJupyter Hub環境を用意してもらっているのでPythonでガンガン処理することもできます。

そういった経緯でPythonメインだった環境からBigQueryに移り、いろんな分析を半年近く続けてきて、（自分の中で）ある程度ナレッジが溜まってきたのでTips的に放出してみようと思います。 ただ色々出そうとしたら長くて心が折れたので、Webサービスだったら結構使いまわしが効きそうだなと思った一個をピックアップします。

会員ごとの会員継続期間

会員ごとに会員継続期間を持っておくと平均継続期間などを多角的に分析する際に役に立ちます。 ユーザーの属性や別の詳細な分析によって得られたユーザーセグメントに関して分けて分析したいといった場合に、 事前に計算されたテーブルがあるといろんなセグメントに分けての分析がスムーズになります。 逆に長期継続するタイプとそうでないタイプをわけて分析をしたいといった場合にも利用できます。 またユーザー属性や登録時の初期行動などから継続期間を予測するような機械学習モデルを作るときにも教師データとして機能します。

このクエリにおいてはデータ構造に会員を管理しているシステムのDBの日次のスナップショットだけがDWHに連携されているような状態を想定します。 つまり、特定の日に会員だったユーザーのIDとその日付だけがあり、退会などのイベントが取れていないものとします。

このパターンの集計ではユーザー状態の変更のイベントログをログ基盤に飛ばし、それをベースに集計するのが理想ですが、 そうはなっていないことがいろんな理由で往々にしてあると思います。

この状態からある日の会員数や、そのうちnヶ月後では何人継続していたかなどのコホート分析的な計算は比較的容易です(ある時点とnヶ月後のuser_idをそれぞれ抽出したものをそれぞれ外部結合して数えるだけ)。 ですが、ユーザーごとに継続期間を出して用意しておこうと考えると、ケアすることが少し増えます。 順を追って説明していきます。

ユーザーの状態とそれに応じた出力の定義

ロジックを説明するためにユーザーの状態を考えておきます。 今回考えておくパターンは次の通りです

1. ある時点で初めて会員になり集計時点でも会員
2. ある時点で初めて会員になり、その後退会したままの会員
3. 一度会員になった後退会して再度会員になり集計時点でも会員

3.のせいでこの分析がやや難しくなります。 何故かというと単純に会員になった一番最初の日と会員ではなくなった最後の日を見比べるだけでは途中でやめて再開している状態を考慮できていない可能性があるためです。 簡単な例を図示します。

このユーザーは2020/09/01に初めて入会して2021/03/01に退会しています。 その後2021/06/01に入会し、2021/09/01以降まで会員を継続しています。 そして2021/09/01の時点で集計を走らせることにします。 するとこのユーザーの会員の期間は2020/09/01から2021/03/01の6ヶ月間になります。 一方で2021/06/01にも入会しているので、このユーザーに対して行を分けてそれぞれの過去g継続した期間と現在も会員であるという情報を出力したいです。 次のように保持するようにします。

register_date	withdraw_date	contract_days	user_id	register_state
2020-06-01	2021-03-01	274	user_A	first_register
2021-06-01	null	null	user_A	resumed

ややこしそうですね。

考え方

少し説明不足になるかもしれませんが、文章で簡潔に説明させていただきます。 まず一つのユーザーだけを抽出し、日付順にソートされた状態を考えます。

初めてレコードが登録された日をみます。これを初回登録日としてフラグを立てておきます。

次に退会日がある場合を調べます。 ある行について考えた時次の行の日付を見ます(lead_snapshot_dateに次の行のsnapshot_date)。

	user_id	snapshot_date	lead_snapshot_date
1	user_A	2020-08-01	2020-08-02
2	user_A	2020-08-02	2020-08-03
3	user_A	2020-08-03	2020-10-01
4	user_A	2020-10-01	2020-10-02

これが1日後だったら翌日も会員であるとします。 1日より長く後ろに飛んでいるとしたらその時点の日に退会したものとします(ここでは3行目)。 あるいはNULLであればその日を最後に戻ってこなかったものとします（悲しい）。

次に再開日がある場合を調べます。 その次にある行について一つ前の行の日付を見ます。

	user_id	snapshot_date	lag_snapshot_date
1	user_A	2020-08-01	null
2	user_A	2020-08-02	2020-08-01
3	user_A	2020-08-03	2020-08-02

これが1日前だったら前日も会員であったとします。 1日より長い日数であればその時点では再開したものとします。 これがNULLであれば初回登録であったということになります（が最初に判定しています）。

そしてこれら初回登録日、退会日、再開日をそれぞれフラグとして与え、そのレコードだけを残します。このときフラグは排他です。 最後はフラグごとに条件づけて差分を取ります。

• 初回登録の次のレコードが退会日であればその二つの日付の差分を取り契約継続期間とします。
• 初回登録の次のレコードがなければnullにしておきます。
• 再開日の次のレコードが退会日であればその二つの日付の差分を取り契約継続期間とします。
• 再開日の次のレコードがなければnullにしておきます。

クエリ

長く見えますが前半は擬似的にスナップショットのテーブルを生成する部分なのでロジックには関係ありません。

WITH
  user_snapshot AS ( -- ユーザー登録情報のスナップショットを生成
  SELECT
    'user_A' AS user_id,
    d AS snapshot_date,
  FROM
    UNNEST(GENERATE_DATE_ARRAY("2020-08-01", "2021-09-01")) d -- ある時点で初めて会員になり集計時点でも会員
  UNION ALL
  SELECT
    'user_B' AS user_id,
    d AS snapshot_date,
  FROM
    UNNEST(GENERATE_DATE_ARRAY("2020-07-01", "2021-02-01")) d -- ある時点で初めて会員になり、その後退会したままの会員
  UNION ALL
  SELECT
    'user_C' AS user_id,
    d AS snapshot_date,
  FROM
    UNNEST(GENERATE_DATE_ARRAY("2020-06-01", "2020-12-31")) d
  UNION ALL
  SELECT
    'user_C' AS user_id,
    d AS snapshot_date,
  FROM
    UNNEST(GENERATE_DATE_ARRAY("2021-02-01", "2021-02-28")) d
  UNION ALL
  SELECT
    'user_C' AS user_id,
    d AS snapshot_date,
  FROM
    UNNEST(GENERATE_DATE_ARRAY("2021-06-01", "2021-09-01")) d -- 一度会員になった後退会して再度会員になり集計時点でも会員その１
  UNION ALL
  SELECT
    'user_D' AS user_id,
    d AS snapshot_date,
  FROM
    UNNEST(GENERATE_DATE_ARRAY("2020-06-01", "2021-03-01")) d
  UNION ALL
  SELECT
    'user_D' AS user_id,
    d AS snapshot_date,
  FROM
    UNNEST(GENERATE_DATE_ARRAY("2021-06-01", "2021-09-01")) d --一度会員になった後退会して再度会員になり集計時点でも会員その２
  ),
  ---------------------------------------以下ロジック----------------------------------------
  flags AS (
  SELECT
    snapshot_date,
    DATE_DIFF(snapshot_date, LAG(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date), day) != 1 AS resume_flag,
    MIN(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date) = snapshot_date AS first_register_flag,
    (
      LEAD(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date) IS NULL OR 
      DATE_DIFF(snapshot_date, LEAD(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date), day) != -1
    ) AND snapshot_date != cdate AS withdraw_flag,,
    MAX(snapshot_date) OVER(PARTITION BY user_id) AS latest_date,
    user_id
  FROM
    user_snapshot
  CROSS JOIN (
    SELECT
      DATE('2021-09-01') AS cdate)
  WHERE
    0=0
  QUALIFY
    resume_flag
    OR first_register_flag
    OR withdraw_flag)
SELECT
  snapshot_date AS resister_date,
  CASE
    WHEN first_register_flag OR resume_flag THEN LEAD(snapshot_date) OVER (PARTITION BY user_id ORDER BY snapshot_date)
  END
  AS withdraw_date,
  CASE
    WHEN first_register_flag OR resume_flag THEN DATE_DIFF(LEAD(snapshot_date) OVER (PARTITION BY user_id ORDER BY snapshot_date), snapshot_date, day) + 1
  END
  AS contract_days,
  user_id,
  CASE
    WHEN first_register_flag THEN 'first_register'
    WHEN resume_flag THEN 'resumed'
  END
  AS register_state
FROM
  flags
WHERE
  0=0
QUALIFY
  withdraw_flag IS FALSE
ORDER BY
  user_id,
  snapshot_date

（OVER句を多用してますが、全部GROUP BYに置き換え可能で、本当はそちらの方が効率が良いです。（OVER句の結果は再利用されないため、OVER句を使うごとに別々に評価される。） とはいえOVER句の方がロジックの説明には見通しがよさそうなので今回はOVER句で書いてます）

順番にみていきます。

フラグ付与

MIN(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date) = snapshot_date AS first_register_flag,

一番若い日付を最初に登録した日のフラグとします。

  (
    LEAD(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date) IS NULL OR 
    DATE_DIFF(snapshot_date, LEAD(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date), day) != -1
  ) AND snapshot_date != cdate AS withdraw_flag,

LEADを使って日付ソートした時の次の行を取得しています。 cdateは例のために仮置きした集計実行日です（ただしcdate以降の日付になっているレコードはないものとします）。 集計当日でないかつ次のレコードが翌日でないことをこれで判定しています。 ただし最後のレコードの場合LEADの結果がNULLになり、計算結果は全部NULLを優先するという仕様上、この評価の結果もNULLになってしまうので、それを止めるためにIS NULLの評価を入れてます。

DATE_DIFF(snapshot_date, LAG(snapshot_date) OVER(PARTITION BY user_id ORDER BY snapshot_date), day) != 1 AS resume_flag,

ほぼ退会と同じですが一つ前をみるLAG関数を使っています。NULLになるのを気にしていないのはNULLになる場合は初回登録日と一致するためです。

  WHERE
    0=0
  QUALIFY
    resume_flag
    OR first_register_flag
    OR withdraw_flag 

QUALIFYを使ってどれかのフラグを持ったレコードだけ抽出します。 QUALIFYはOVER句を使った時のHAVING句のようなものです。 BigQueryの場合WHERE句を使わないとQUALIFYを使ってはいけないというパーサー上のルールがあるのでWHERE 0=0を入れています。 この処理をここで入れる理由として、BigQueryではサブクエリ間でデータの通信が発生するので、サブクエリ内で抽出しておかないと処理が重くなることがあります。 ちなみにQUALIFYを使うには、OVER句がクエリの中にあればいいだけなので、QUALIFYを使いたい場合は列にOVER ()とつけるだけで(おそらく)副作用なく使えます。 GROUP BYせずにHAVING的なことをしたい場合にどうぞ(演算結果でフィルタしたいけどGROUP BYも分析関数も使ってない場合など)。

契約期間算出

  snapshot_date AS resister_date,
  CASE
    WHEN first_register_flag OR resume_flag THEN LEAD(snapshot_date) OVER (PARTITION BY user_id ORDER BY snapshot_date)
  END
  AS withdraw_date,
  CASE
    WHEN first_register_flag OR resume_flag THEN DATE_DIFF(LEAD(snapshot_date) OVER (PARTITION BY user_id ORDER BY snapshot_date), snapshot_date, day) + 1
  END
  AS contract_days,
  user_id,
  CASE
    WHEN first_register_flag THEN 'first_register'
    WHEN resume_flag THEN 'resumed'
  END
  AS register_state

フラグを抽出して日付でソートすれば、次のように並んでいます。

• 初回登録->退会->再開->退会->...

あとは初回登録と再開のフラグを持ったレコードだけに着目して次のレコードが存在すれば差分と日付をとっておきます。 結果として次のようなテーブルになります。

	resister_date	withdraw_date	contract_days	user_id	register_state
1	2020-08-01	null	null	user_A	first_register
2	2020-07-01	2021-02-01	216	user_B	first_register
3	2020-06-01	2020-12-31	214	user_C	first_register
4	2021-02-01	2021-02-28	28	user_C	resumed
5	2021-06-01	null	null	user_C	resumed
6	2020-06-01	2021-03-01	274	user_D	first_register
7	2021-06-01	null	null	user_D	resumed

所望の結果が得られました。 あとはこれを元に煮るなり焼くなりできます。

終わりに

再入会を考慮したユーザーごとの会員継続期間を集計する方法を紹介してみました。 状態というとややこしいですが、集計において直前の状態にしか依存していない上にフォワードで見れば変化後は一つの状態しか取らないのでシンプルに書けました。 ここで各種入会キャンペーンの場合を考えたりすると何かと厳しいので、別テーブルを用意してJOINすべきです。会員状態に休会や支払い停止などの複雑な状態遷移を考える必要がある場合も難しそうです。 他にもスナップショットがDWHに連携され始めた日がサービス開始時より後だった場合に、新しい会員状態を考える必要がありますが、やれることに対して説明が長くなるだけだったので省きました （できることは分析の中で必要な時に分けれるようにするぐらいなので、初回登録とみなした日がスナップショットの最初の連携日だった場合とそうでない場合をわけるだけです。クエリ的には最後のregister_stateを日付見て分けるだけです）。

また、このクエリは割と汎用性があると思っていて、アクセスログを使って機能やページに連続アクセスした日を集計したり、粒度を週にまとめて何週連続でアクセスしたかを基準にした利用継続期間を取るなど、機能やページ別のロイヤリティの分析に応用することができると思います。

他にもネタがあって、集合類似度を測り、入れ子関係を分析する方法や欠測を考慮した効率のよい平均や標準偏差の出し方などを紹介したかったですが、 一個だけでもそれなりの量になったので今後書きたくなった時に紹介させていただこうと思います。