1. アプリの各画面を閲覧したユーザー数をBigQueryで集計する方法

本記事の対象者は以下のGoogle Analyticsをお使いの方
・「Google Analytics for Firebase（Firebase 向け Google Analytics）」
・「Google Analytics 4（旧：Google Analytics App + Webプロパティ）」

BigQueryでGoogle Analyticsのデータを確認すると、様々な要素が記録されていることが確認できます。それぞれの違いを理解するのには時間が掛かります。まずは手を動かしながら、データの内容理解をすることをおすすめします。

今回はアプリの各画面を閲覧したユーザー数や閲覧回数などを集計するクエリをご紹介します。

アプリの各画面を閲覧したユーザー数や閲覧回数取得用のクエリ

「FROM `analytics_XXXXXXXX.events_*`」のXXXXXXXの部分はご自身のGoogle AnalyticsのプロパティIDに置き換えてください。
今回はFirebaseデモプロジェクト（firebase-public-project、analytics_153293282.events_*）のデータを利用しています。

firebase-public-projectの追加方法は以下の画像を参考にしてください。

-- データを取得する対象期間を設定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE('2018-10-03'));
CREATE TEMPORARY FUNCTION toDate()  AS (DATE('2018-10-03'));

WITH
-- 縦持ちのデータを扱いやいように横持ちに変換
`convert_event_params` AS(
SELECT
 PARSE_DATE("%Y%m%d", event_date) AS date
 , user_pseudo_id
 , (SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'firebase_screen_class') AS firebase_screen_class
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE
 _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', fromDate()) AND FORMAT_DATE('%Y%m%d', toDate())
 AND event_name = 'screen_view'
 AND platform != 'WEB'
)
,
-- Firebaseの各画面ごとのユーザー数や閲覧回数を計算
`calc_event_users` AS (
SELECT
 date
 , firebase_screen_class
 , COUNT(1) AS views
 , COUNT(DISTINCT user_pseudo_id) AS view_users
FROM `convert_event_params`
GROUP BY date, firebase_screen_class
)
,
--  screen_viewのイベントが発生したアクティブユーザー数を計算
`calc_active_users` AS (
SELECT
 PARSE_DATE("%Y%m%d", event_date) AS date
 , COUNT(DISTINCT user_pseudo_id) AS active_users
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', fromDate()) AND FORMAT_DATE('%Y%m%d', toDate())
 AND event_name = 'screen_view'
 AND platform != 'WEB'
GROUP BY date, event_name
)

SELECT
date
, firebase_screen_class
, views
, views / view_users AS views_per_user
, view_users
, view_users / active_users AS view_users_per_AU
, active_users
FROM `calc_event_users`
INNER JOIN `calc_active_users` USING(date)
ORDER BY date, firebase_screen_class

クエリの実行結果

BigQueryで実行した結果が上記になります。
各screen_classごとに閲覧回数や閲覧ユーザー数などを出すことができました。

クエリの解説

-- データを取得する対象期間を設定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE('2018-10-03'));
CREATE TEMPORARY FUNCTION toDate()  AS (DATE('2018-10-03'));

日付をユーザー定義関数として定めています。この日付を調節するだけで、何月何日から何月何日までのGoogle Analyticsのデータを読み込むかを指定できるようにしてあります。
無駄にデータを読み込んでお金を消費しないように、お試しでクエリを実行するときは1日分のデータのみを読み込むのがおすすめです。

-- 縦持ちのデータを扱いやいように横持ちに変換
`convert_event_params` AS(
SELECT
 -- データが記録された日の日付
 PARSE_DATE("%Y%m%d", event_date) AS date
 
 -- インストールごとに割り当てられる擬似的なユーザーID、仮のIDが自動で設定される
 , user_pseudo_id
 
 -- Field: event_paramsの中にあるスクリーンクラスを抽出
 , (SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'firebase_screen_class') AS firebase_screen_class
 
-- Google AnalyticsのプロパティIDのデータの読み込み
FROM `analytics_XXXXXXXX.events_*`
WHERE
 -- 何月何日から何月何日までのデータを読み込むか絞り込み
 _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', fromDate()) AND FORMAT_DATE('%Y%m%d', toDate())
 
 --- screen_viewイベントのみに絞り込み
 AND event_name = 'screen_view'
 
 --- iOSやAndroidのデータのみに絞り込み
 AND platform != 'WEB'
)​

スクリーンが移動したときに記録されるscreen_viewイベントのデータのみを抽出しています。
screen_viewイベントのfirebase_screen_classはスクリーンクラス名になります。
screen_viewについての詳細な説明は以下のページから確認できます。

自動的に収集されるイベント - Firebase ヘルプ

-- Firebaseの各画面ごとのユーザー数や閲覧回数を計算
`calc_event_users` AS (
SELECT
  date
  , firebase_screen_class
  
  -- 合計閲覧回数を計算
  , COUNT(1) AS views
  
  -- 合計閲覧ユーザー数を計算
  , COUNT(DISTINCT user_pseudo_id) AS view_users
FROM `convert_event_params`
GROUP BY date, firebase_screen_class
)

合計閲覧回数はユーザーがscreen_viewイベントを各スクリーンごとに集計したものです。

ただし、スクリーン名が一緒の場合などはscreen_viewイベントが発生しません。以下の条件のどれかにあてはまる場合、screen_viewは記録されます。

以前にスクリーンの設定が行われていない
新しいスクリーン名が以前のスクリーン名と異なる
新しいスクリーン クラス名が以前のスクリーン クラス名と異なる
新しいスクリーン ID が以前のスクリーン ID と異なる
引用：https://support.google.com/firebase/answer/6317485?hl=ja

--  screen_viewのイベントが発生したアクティブユーザー数を計算
`calc_active_users` AS (
SELECT
  PARSE_DATE("%Y%m%d", event_date) AS date
  
  -- screen_viewイベントが発生したユーザー数を計算
  , COUNT(DISTINCT user_pseudo_id) AS active_users
FROM `analytics_XXXXXXXX.events_*`
WHERE
   _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', fromDate()) AND FORMAT_DATE('%Y%m%d', toDate())
  
  -- screen_viewイベントのみに絞り込み
  AND event_name = 'screen_view'
  
  -- アプリのみ絞り込み
  AND platform != 'WEB'
GROUP BY date, event_name
)

screen_viewイベントが発生したユーザー数を母数（アクティブユーザー数）として計算しています。
アクティブユーザー数を計算することで、どれくらいの割合のユーザーが各画面を利用しているか分かります。

SELECT
 date
 , firebase_screen_class
 
 -- 合計閲覧回数
 , views
 
 -- 1ユーザーあたりの各画面の閲覧回数
 , views / view_users AS views_per_user
 
 -- 合計閲覧ユーザー数
 , view_users
 
 -- アクティブユーザーあたりの利用率
 , view_users / active_users AS view_users_per_AU
FROM `calc_event_users`
INNER JOIN `calc_active_users` USING(date)
ORDER BY date, firebase_screen_class

最後にJOINしつつ、1ユーザーあたりの閲覧回数や、利用率を計算しています。
利用率を計算することで、どの画面が利用されているか・いないかが分かります。今後の開発の参考になると思います。

最後に

この記事ではfirebase_screen_class（各スクリーン）ごとに閲覧ユーザー数などを集計する方法を紹介しました。
今回のクエリをもとに改善していくことで様々な数値を取得できます。
例えば、トップ（firebase_previous_class）→ 検索画面（firebase_screen_class）などのように遷移数なども取得することができます。

このマガジンの記事

1. アプリの各画面を閲覧したユーザー数をBigQueryで集計する方法
2. Google Analytics 4の各イベントについての説明(page_view, screen_viewなど)
3. event_paramsについて（BigQueryのGoogle Analytics 4プロパティ）
4. GAをBigQueryで分析する際の注意点（WebとAppの違いやPlatformの絞り込み）
5. BigQueryでGoogle Analyticsを分析するときに参照したいドキュメント一覧
6. BigQueryのUDFの使い方や使用例の紹介（SQL編）
7. BigQueryのクエリのスケジューリングの設定方法や使い方