Firebase Analyticsイベントのファネル分析

Posted on Mar 21, 2021 | 456 words | ~3 mins

これはなに

BigQueryでFirebase Analyticsイベントをファネル分析する上で、これをwalkthroughしたメモ。

デモアプリ

分析事、実務プロジェクトでやってもよいが、リアルに課金が発生するのを気にしてしまうのと、個人プロジェクトでは分析対象のデータがそもそも揃えるのが難しい。
sandboxな環境で分析事を試せる十分なデータセットがあるプロジェクト、Google先生が公開してくれているFirebaseデモプロジェクトがある。親切。
- Firebase デモプロジェクト - Firebase ヘルプ
デモプロジェクトのiOSアプリでは、Flood-It!という実在するパズルゲームアプリのアナリティクスデータをつかって分析を行う

指定された手数以内で盤面を一色に塗りつぶすパズルゲームです

ゴール

no_more_extra_stepsを受けたユーザの内、どれだけspend_virtual_currencyにつながったか、を知りたい
2stepファネルをつくる

カダイ

はFirebase ConsoleでつくるとOpen Funnelになること(以下画像は上記ブログ記事から拝借したもの)
- 実際にデモプロジェクトのFirebase Consoleでファネルをつくろうとしたが、現在は権限が制限されているのか、新規の目標到達プロセスの作成ができなかった..

ファネルをみると、step1よりstep2の方が大きい値になってしまっている。step1に関係なくstep2イベントがカウントされている。
いわゆるオープンファネルになってしまっている。いまつくりたいのはクローズドファネル。
ちなみに、GA4ではクローズドな目標到達プロセスも作成できる(後述のイベント間の時間区間指定もできるし、イベントパラメータ指定などもできる。GUIポチポチで完結するので便利。)
- GA4 目標到達プロセスの分析 - アナリティクスヘルプ
今回はFirebase EventsログをBigQueryで弄ぶ主旨なのでBQでやっていく

BigQueryでどうやる

以降はFlood-It!アプリイベントのBigQueryデータセットのクエリエディタで叩いて進めていく

1.) まずは1日分のデータでユーザIDベースで発生イベントを時系列に出してみると

SELECT 
    event_name,
    user_pseudo_id,
    event_timestamp
FROM 
    `firebase-public-project.analytics_153293282.events_20180720`
ORDER BY
    user_pseudo_id, event_timestamp
LIMIT 
    1000

こんなふうに

2.) 次に、no_more_extra_stepsとspend_virtual_currencyイベントに絞ってみると、

SELECT 
     event_name, user_pseudo_id , event_timestamp
FROM 
     `firebase-public-project.analytics_153293282.events_20180720`
WHERE
     (event_name = "no_more_extra_steps" OR event_name = "spend_virtual_currency")
ORDER BY
     user_pseudo_id, event_timestamp

こんなふうに
- クエリ結果の4,5行目をみると、たしかに同一ユーザによってno_more_extra_steps -> spend_virtual_currencyの順でイベント発生してることがわかる
- ので、こういうかんじのをいい感じに抽出していく
- 「次のアレがアレ」という条件がほしい

3.) LEADウィンドウ関数をつかって、「次の結果レコード」についての条件を追加する

SELECT
    event_name,
    user_pseudo_id,
    event_timestamp,
    -- PARTITION BYで擬似ユーザID毎のグループに分ける(でないと同一ユーザによる「次」が担保できない)
    -- ORDER BYで時系列的な並びを担保する
    LEAD(event_name, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_event
FROM
    `firebase-public-project.analytics_153293282.events_20180720`
WHERE
    (event_name = "no_more_extra_steps" OR event_name = "spend_virtual_currency")
ORDER BY
 user_pseudo_id, event_timestamp

こんなふうに
- 「次のイベント」をnext_eventとして抽出できるようになったので
- これに対してファネル条件追加してやればよい

4.) `no_more_extra_steps`イベントの直後に`spend_virtual_currency`イベントが来るケースをカウントするクエリ

SELECT
    COUNTIF (event_name = "no_more_extra_steps") AS funnel_1_total,
    COUNTIF (event_name = "no_more_extra_steps" AND next_event = "spend_virtual_currency") AS funnel_2_total
FROM (
    SELECT
        event_name,
        user_pseudo_id,
        event_timestamp,
        LEAD(event_name, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_event
FROM
    `firebase-public-project.analytics_153293282.events_20180720`
WHERE
    (event_name = "no_more_extra_steps" OR event_name = "spend_virtual_currency")
ORDER BY
    user_pseudo_id, event_timestamp
)

こんなふうに
- 25回のno_more_extra_stepsの内22回がspend_virtual_currencyっぽい

5.) ユーザ数ベースでみると、

SELECT
    COUNT(DISTINCT funnel_1_total) AS f1_users,
    COUNT(DISTINCT funnel_2_total) AS f2_users
FROM (
    SELECT
        IF (event_name = "no_more_extra_steps", user_pseudo_id, NULL) AS funnel_1_total,
        IF (event_name = "no_more_extra_steps" AND next_event = "spend_virtual_currency", user_pseudo_id, NULL) AS funnel_2_total
    FROM (
        SELECT
            event_name,
            user_pseudo_id,
            event_timestamp,
            LEAD(event_name, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_event
        FROM
            `firebase-public-project.analytics_153293282.events_20180720`
        WHERE
            (event_name = "no_more_extra_steps" OR event_name = "spend_virtual_currency")
        ORDER BY
            user_pseudo_id, event_timestamp
    )
)

こんなふうに

6.) この抽出には問題がある。

たとえばno_more_extra_steps後何時間か経過した後にspend_virtual_currencyイベントが発生していた場合
この場合、no_more_extra_stepsが直接spend_virtual_currencyに寄与したかというと考えにくい。
ので、こうしたイベント間の時間経過も考慮したフィルタ条件を書く必要がある(かもしれない。実際は要件による。)
ここでは、1分以内に後続のファネルイベントが起きた場合、を条件に追加したいとする

7.) 「次のイベントのtimestamp」カラムをSevent_name同様追加した上で、timestampの差の範囲指定条件を追加する

SELECT
    COUNT(DISTINCT funnel_1) AS f1_users,
    COUNT(DISTINCT funnel_2) AS f2_users
FROM (
    SELECT
        IF (event_name = "no_more_extra_steps", user_pseudo_id, NULL) AS funnel_1,
        IF (
            event_name = "no_more_extra_steps" AND
            next_event = "spend_virtual_currency" AND
            -- `no_more_extra_steps`の後1分以内に`spend_virtual_currency`イベントが発生したかどうか
            -- timestampはmicrosecond単位なので1000 * 1000
            next_timestamp - event_timestamp < 60 * 1000 * 1000,
            user_pseudo_id, NULL) AS funnel_2
    FROM (
        SELECT
            event_name,
            user_pseudo_id,
            event_timestamp,
            LEAD(event_name, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_event,
            -- next_timestampを追加
            LEAD(event_timestamp, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_timestamp
        FROM
            `firebase-public-project.analytics_153293282.events_20180720`
        WHERE
            (event_name = "no_more_extra_steps" OR event_name = "spend_virtual_currency")
        ORDER BY
            user_pseudo_id, event_timestamp
    )
)

こんなふうに
- イベント時間区間指定なしの場合より減ってるが、こっちがリアルな数値。

8.) 1ヶ月分のデータを日別で取得してデータポータルで可視化してみる

SELECT
    day,
    COUNT(DISTINCT funnel_1) AS f1_users,
    COUNT(DISTINCT funnel_2) AS f2_users
FROM (
    SELECT
        -- 日別をとる
        FORMAT_TIMESTAMP('%F', TIMESTAMP_MICROS(event_timestamp)) as day,
        IF (event_name = "no_more_extra_steps", user_pseudo_id, NULL) AS funnel_1,
        IF (
            event_name = "no_more_extra_steps" AND
            next_event = "spend_virtual_currency" AND
            next_timestamp - event_timestamp < 60 * 1000 * 1000,
            user_pseudo_id, NULL) AS funnel_2
    FROM (
        SELECT
            event_name,
            user_pseudo_id,
            event_timestamp,
            LEAD(event_name, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_event,
            LEAD(event_timestamp, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_timestamp
        FROM
            `firebase-public-project.analytics_153293282.events_*`
        WHERE
            (event_name = "no_more_extra_steps" OR event_name = "spend_virtual_currency")
            AND _TABLE_SUFFIX BETWEEN '20180629' AND '20180728'
        ORDER BY
            user_pseudo_id, event_timestamp
    )
)
GROUP BY
    day
ORDER BY
    day

こんなふうに
せっかくなのでデータポータルに展開して
可視化

以上、BigQueryでFirebase Analyticsイベントの2stepなクローズドファネルやっていきでした。