FirebaseAnalytics x BigQuery でSQLからクローズドファネルを作る

##3行まとめ

• FirebaseAnalyticsでは、現状オープンファネルでのレポーティングしかできず、ニーズの高いクローズドファネルはコンソールの機能としては提供されていない
• BigQueryExportしていれば、SQLでシンプルに作れるので楽チン
• RedashやmetabaseなどのBIツールと連携しておくと幸せになれるかも

##FirebaseAnalyticsでは何故かオープンファネルしか見れない
FirebaseAnalyticsには、ファネルレポート機能がありますが、こちらオープンファネルでのレポーティングになっていて、正直かなり使いづらいです笑

要は単純に各イベントの発生回数をバーチャートで表示しているだけなので、初めて使う方は戸惑ってしまうのではないでしょうか?

クローズドファネル機能の追加は改善ニーズとしては上がっているようで、GoogleDevelopersブログでも紹介されていたりはするのですが、こちらの記事では2ステップまでの分析手法までしか書かれていなかったので、3ステップ以上も分析できるように拡張したものを紹介します。

クエリの中身についてはこちらのMITライセンスのソースコード Copyright (c) 2018 Suyeol Jeon (xoul.kr)をかなり参考にさせてもらっています。というよりかは、殆どそのままですので、アレンジされたい方はこちらも是非ご覧になってください。

ファネルを用いた分析は、UX全体の中で離脱ポイントを把握するという意味で重要かと思いますので、ぜひ自分の環境に合わせてSQLを改変してみて、サービス改善に役立ててもらえればと思います。

##前提

• 今回の記事では、FirebaseAnalytisを利用中かつBigQueryのローデータエクスポートを行なっている環境を想定しています。
• 記事内ではGoogleが提供しているサンプル用のデータセット"firebase-public-project.analytics_153293282"を使用しています。
• 題材はFirebaseAnalyticsにしていますが、①ユーザーID②イベント名③タイムスタンプの3行を持つテーブルがあれば同様の分析を実施可能かと思います。

##SQL本体(時間が無い人はこちらをコピペして使ってください)

closed_funnel.sql

-- standardSQLを使用します
-- standardSQLを使用します
#standardSQL

-- FIrebaseAnalyticsのサンプルデータセットで、①初回起動 --> ②no_more_extra_steps --> ③spend_virtual_currencyのクローズドファネルを計測

WITH
data AS (
  SELECT
    user_pseudo_id,
    event_timestamp AS timestamp,
    -- ファネルステップ１のイベント名を入れる
    (CASE event_name WHEN "first_open" THEN event_timestamp END) AS step_0_timestamp,
    -- ファネルステップ2のイベント名を入れる
    (CASE event_name WHEN "no_more_extra_steps" THEN event_timestamp END) AS step_1_timestamp,
    -- ファネルステップ3のイベント名を入れる
    (CASE event_name WHEN "spend_virtual_currency" THEN event_timestamp END) AS step_2_timestamp
  FROM
    -- 例では、Firebaseのサンプルデータセットを使用していますが、お使いの環境に合わせて変更します。
    -- 年次のファネルを作っていますが、読み込む日付範囲を変更する事で、ファネルのタイムウィンドウを絞り込めます
    `firebase-public-project.analytics_153293282.events_2018*`,
    UNNEST(event_params) as params
),

funnel AS (
  SELECT
    user_pseudo_id,
    timestamp,
    LAST_VALUE(step_0_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_0_funnel,
    LAST_VALUE(step_1_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_1_funnel,
    LAST_VALUE(step_2_timestamp IGNORE NULLS) OVER(PARTITION BY user_pseudo_id ORDER BY timestamp) AS step_2_funnel
  FROM data
)

SELECT
  1 as step_num,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
    THEN step_0_funnel END
  ) AS count
  FROM funnel
UNION ALL SELECT
  2 as step_num,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
      AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
    THEN step_0_funnel END
  ) AS count
  FROM funnel
UNION ALL SELECT
  3 as step_num,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
      AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
      AND step_2_funnel IS NOT NULL AND step_1_funnel < step_2_funnel
    THEN step_0_funnel END
  ) AS count
  FROM funnel
ORDER BY step_num
;

##クエリ解説

• 読解が難しくなるサブクエリの使用は避け、with句を用いて作っています。個別にロジックを説明します。

View1 : data

○実行SQL

closed_funnel_part1_with.sql

SELECT
    user_pseudo_id,
    event_timestamp AS timestamp,
    (CASE event_name WHEN "first_open" THEN event_timestamp END) AS step_0_timestamp,
    (CASE event_name WHEN "no_more_extra_steps" THEN event_timestamp END) AS step_1_timestamp,
    (CASE event_name WHEN "spend_virtual_currency" THEN event_timestamp END) AS step_2_timestamp
    FROM `firebase-public-project.analytics_153293282.events_*`,
    UNNEST(event_params) as params

○実行結果と解説

- まずは①ユーザーID / ②ログのタイムスタンプ / ③設定したイベントのタイムスタンプ を抽出します。 - 今回は、"初回起動"->"no_more_extra_steps"->spend_virtual_currency"の3ステップのファネルを作りますが、必要であればステップ数を追加します。

with句 ②funnel

○実行SQL

closed_funnel_part2_with.sql

  SELECT
    user_pseudo_id,
    timestamp,
    LAST_VALUE(step_0_timestamp IGNORE NULLS) OVER(PARTITION BY session ORDER BY timestamp) AS step_0_funnel,
    LAST_VALUE(step_1_timestamp IGNORE NULLS) OVER(PARTITION BY session ORDER BY timestamp) AS step_1_funnel,
    LAST_VALUE(step_2_timestamp IGNORE NULLS) OVER(PARTITION BY session ORDER BY timestamp) AS step_2_funnel
  FROM data

○実行結果と解説

• Window関数のLAST_VALUEを使って、【各ユーザー毎にそれぞれの対象イベントの最終発生タイムスタンプ】を集計します。

last_value.sql

--　ユーザー単位で各ログ発生地点における、各STEPイベントの最終タイムスタンプを集計します。
LAST_VALUE(step_0_timestamp IGNORE NULLS) OVER(PARTITION BY session ORDER BY timestamp) AS step_0_funnel,

• __[イベントA->イベントB->イベントCを順番に実行したユーザー]__はデータ上の表現で言い換えると、__条件[イベントA->イベントB->イベントCのログを持つ]かつ[イベントAの最新>イベントB>イベントCのタイムスタンプログを持つ]__を満たすユーザーとなります
• 続いての、集計クエリに備えて各アクセスログにフラグを立てていくようなイメージです

例1)初回起動は行なったが、その後のイベントを実行していないユーザー
-->この時点では、step0は実行しているが、step_1_funnel/step_2_funnelはnullになっています

例2)2018年度の初回起動は無しだが、その後のイベントを実行したユーザー
-->この時点では、step0は実行していないが、step_1とstep_2が存在するのでイベント2/3自体は実行されています

集計

○実行SQL

closed_funnel_part3_with.sql

SELECT
  1 as step_num,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
    THEN step_0_funnel END
  ) AS count
  FROM funnel
UNION ALL SELECT
  2 as step_num,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
      AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
    THEN step_0_funnel END
  ) AS count
  FROM funnel
UNION ALL SELECT
  3 as step_num,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
      AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
      AND step_2_funnel IS NOT NULL AND step_1_funnel < step_2_funnel
    THEN step_0_funnel END
  ) AS count
  FROM funnel
ORDER BY step_num
;

○実行結果と解説

• 最終的な集計結果がこちら

closed_funnel_part3_finish.sql

SELECT
  2 as step_num,
  COUNT(
    DISTINCT CASE
    WHEN step_0_funnel IS NOT NULL
      AND step_1_funnel IS NOT NULL AND step_0_funnel < step_1_funnel
    THEN step_0_funnel END
  ) AS count
  FROM funnel

• 各ステップ毎の結果をUNION ALLで縦に連結していきます。
• case whenでフラグを立てて同一セッションの発生回数をカウントしています。ユーザー数ではなく、あくまでセッション数をカウントしている事に注意してください。 ユーザー単位でカウントする場合には、集計クエリの then step_XX_funnelの部分を、user_pseudo_idに変更するとOKだと思います。(未検証です!)

##おまけ

• イベント名やテーブルのスキャン範囲を動的パラメータにして、RedashやmetabaseなどのBIツールで閲覧できるようにしておくと、応用可能性が広がって楽しいです。
• 良い分析ライフを!