🔭

Firebase x BigQueryで時間軸を伴うイベントを分析する(1) リテンションの計測

2023/02/20に公開

最近、仕事でも個人開発でも、BigQueryで分析ばかりしている🧐田畑(@nerd0geek1)です。

会社でも個人開発でもそうなんですが、サービスが成長したり、手元の情報が増えるに従って、分析が高度化していくのはよくあることですよね？

最初はDAU / WAU / MAUや、特定のイベントの発生数といった、ある一時点におけるデータを知るための点での分析だけで十分だったのが、
上記の分析に加えて、継続ユーザー(前月以前から利用していたユーザー)数の算出であったり、ユーザーの各画面への滞在時間といった、前後関係を含む線での分析のニーズが増えてきたり、と。

で、こういったクエリは数学の解法に似たところがあり、普通にやっていても自然には思いつかないが、考え方を知れば理解はできる、といったタイプのものなので、自分の備忘のため + 誰かの参考に、と思って解説してみます。

自分のBigQuery力もまだまだなので、より良い記法等あれば教えていただけると🙏

前月以前からの継続ユーザー

では、まずは前月以前からの継続ユーザー数を計測する方法について解説していきます。

各月にアプリを使ってくれたユーザーが、

その月にアプリを使い始めたユーザーか
その月より前に利用履歴があるユーザーか

判別し、サービスへの定着率を計測したい場合に使うようなクエリですね。

イメージとしては、👇のような元データから

以下のような結果をまとめるような感じです。

考え方としては、

元テーブルに対して、同じテーブル(比較テーブル)をINNER JOINで連結し
比較テーブルに対して、絞り込み条件を適用することで、必要なレコードのみを残し、
最終的に元テーブルの値でGROUP BYを行うことで、月ごとに集計する

という感じでしょうか。

実際にやってみる

それでは、実際にやってみましょう。

1. イベント全てを取得するサブクエリを定義する

このタイミングでは、以下のイベントデータの集合を手軽に呼び出せるようにするために、

以下のようなサブクエリを定義します。
また、WHERE句で日付の制限をかけているように、データ全体に対してかけたい制約があれば、この時点でかけておきます。

WITH events AS (
  SELECT
    event_date,
    event_timestamp,
    user_name,
    user_id
  FROM
    `your-project-id.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20221101' AND '20230228'
)

2. INNER JOINで同一ユーザーの過去イベントを紐付ける

次に、イベント全て(t1と呼ぶ)に対して、イベント全て(t2と呼ぶ)をuser_idベースで結合します。
t1は集計の対象としたいイベント、t2はフィルター用のイベント、といったところでしょうか。

まず、以下のようなクエリ(continuousUseEventsサブクエリを追加)で

WITH events AS (
  SELECT
    event_date,
    event_timestamp,
    user_name,
    user_id
  FROM
    `your-project-id.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20221101' AND '20230228'
),
-- ここ以下を追加
continuousUseEvents AS (
  SELECT
    *
  FROM
    events AS t1
  INNER JOIN
    events AS t2 USING(user_id)
)

このような結果を得て、

そこに日付比較の条件を追加することで

WITH events AS (
  SELECT
    event_date,
    event_timestamp,
    user_name,
    user_id
  FROM
    `your-project-id.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20221101' AND '20230228'
),
continuousUseEvents AS (
  SELECT
    *
  FROM
    events AS t1
  INNER JOIN
    events AS t2 USING(user_id)
  -- 以下の条件を追加
  WHERE
    -- 集計対象の日時より過去のイベントのみを紐付ける
    t1.event_timestamp > t2.event_timestamp
)

灰色の列が取り除かれ、

以下のような結果を得ることができました。
これは、言うなればあるイベント(左側)と、それより前に発生したイベント(右側)の集合、でしょうか。

3. 同一ユーザーの前月より前のイベントのみを抽出する

2.で作成した集合に更に条件を追加して、前月以前のイベントと紐付いてるレコードのみを残してみます。

WITH events AS (
  SELECT
    event_date,
    event_timestamp,
    user_name,
    user_id
  FROM
    `your-project-id.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20221101' AND '20230228'
),
continuousUseEvents AS (
  SELECT
    *
  FROM
    events AS t1
  INNER JOIN
    events AS t2 USING(user_id)
  WHERE
    -- 集計対象の日時より過去のイベントのみを紐付ける
    t1.event_timestamp > t2.event_timestamp
  -- 以下の条件を追加
  AND
    -- event_date(YYYYMMDD)から先頭の6文字を比較し、前月以前のデータを取得する
    SUBSTR(t1.event_date, 1, 6) != SUBSTR(t2.event_date, 1, 6)
)

上記のクエリから、以下のようなあるイベント(左側)と、その前月以前に発生したイベント(右側)の集合を得ることができました。

4. 重複レコードの削除

3.の時点で、あるイベント(左側)と、その前月以前に発生したイベント(右側)の集合を得ることができましたが、集計にはまだ不十分です。
2022/12/22のAさんのイベントが２件存在していたり、と集計の邪魔になる重複レコードが存在しているからです。

なので、重複についてはGROUP BYを追加することで1レコードにまとめてしまいます。
また、比較用のテーブルであったt2については、既に不要で、かつ存在していると元テーブルの値だけで集計できないのでこれも取得対象から外してしまいます。
そうして作成されたのが以下のクエリです。

WITH events AS (
  SELECT
    event_date,
    event_timestamp,
    user_name,
    user_id
  FROM
    `your-project-id.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20221101' AND '20230228'
),
continuousUseEvents AS (
  SELECT
    -- ここから
      -- 単純にt1.event_dateでまとめると、「同一ユーザー、同一月、別日のイベント」を集約できないので、
      -- t1.event_timestampから文字列を生成し、それで集約している
    DATE_TRUNC(CAST(TIMESTAMP_MICROS(t1.event_timestamp) AS DATE), MONTH) AS month,
    t1.user_name,
    t1.user_id
    -- ここまでを変更
  FROM
    events AS t1
  INNER JOIN
    events AS t2 USING(user_id)
  WHERE
    -- 集計対象の日時より過去のイベントのみを紐付ける
    t1.event_timestamp > t2.event_timestamp
  AND
    -- event_date(YYYYMMDD)から先頭の6文字を比較し、前月以前のデータを取得する
    SUBSTR(t1.event_date, 1, 6) != SUBSTR(t2.event_date, 1, 6)
  -- 以下を追加
  -- GROUP BYに数値を渡すと、選択列でGROUP BYを行ってくれる。
  GROUP BY
    1, 2, 3
  -- 上記2行は以下と等価
  -- GROUP BY
  -- DATE_TRUNC(CAST(TIMESTAMP_MICROS(t1.event_timestamp) AS DATE), MONTH) AS month,
  -- t1.user_name,
  -- t1.user_id
)

これにより、以下のユーザーごと、月ごとのイベントの集合を得ることができました。

全体をまとめるクエリを書く

ここまでで、少し処理が複雑な集計部分のクエリは書き終わったので、単純な集計で済む部分とクエリを連結します。

WITH events AS (
  SELECT
    event_date,
    event_timestamp,
    user_name,
    user_id
  FROM
    `your-project-id.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20221101' AND '20230228'
),
continuousUseEvents AS (
  SELECT
    -- 単純にt1.event_dateでまとめると、「同一ユーザー、同一月、別日のイベント」を集約できないので、
    -- t1.event_timestampから文字列を生成し、それで集約している
    DATE_TRUNC(CAST(TIMESTAMP_MICROS(t1.event_timestamp) AS DATE), MONTH) AS month,
    t1.user_name,
    t1.user_id
  FROM
    events AS t1
  INNER JOIN
    events AS t2 USING(user_id)
  WHERE
    -- 集計対象の日時より過去のイベントのみを紐付ける
    t1.event_timestamp > t2.event_timestamp
  AND
    -- event_date(YYYYMMDD)から先頭の6文字を比較し、前月以前のデータを取得する
    SUBSTR(t1.event_date, 1, 6) != SUBSTR(t2.event_date, 1, 6)
  -- GROUP BYに数値を渡すと、選択列でGROUP BYを行ってくれる。
  GROUP BY
    1, 2, 3
)

-- ここ以下を追加
SELECT
  DATE_TRUNC(CAST(TIMESTAMP_MICROS(event_timestamp) AS DATE), MONTH) AS month,
  -- クエリでは日本語をカラム名にすることはできませんがここでは説明のため
  COUNT(DISTINCT events.user_id) AS ユニークユーザー数,
  COUNT(*) AS 延べ回数,
  COUNT(DISTINCT continuousUseEvents.user_id) AS 継続ユーザー数
FROM
  events
-- 継続ユーザー以外のデータも当然存在し、それらを排除しないようにする必要があるため、LEFT JOIN
LEFT JOIN
  continuousUseEvents
ON
  events.user_id = continuousUseEvents.user_id AND DATE_TRUNC(CAST(TIMESTAMP_MICROS(events.event_timestamp) AS DATE), MONTH) = continuousUseEvents.month

これで冒頭の集計結果を得ることができました。

まとめ

今回は、リテンションレートの算出に必要となる、継続ユーザー数の取得方法をステップ・バイ・ステップで説明してみました。

次回はより複雑なクエリとして、ユーザーの特定機能・画面への滞在時間を算出するクエリをステップ・バイ・ステップで解説していければと思います。

上記クエリについて、別のアプローチや改善等あれば、コメントいただけるとありがたいです🙏