7. BigQueryのクエリのスケジューリングの設定方法や使い方

この記事ではBigQueryのクエリを定期的に実行し、出力結果をテーブルとして自動保存する方法をご紹介します。

Google Analyticsのデータを日毎・週毎・月毎に自動集計することで、手運用の手間を削減することができます。スケジュールの設定を細かくカスタムすることができるため、好きなタイミングでクエリを自動実行することができます。

日毎のクエリのスケジューリング設定方法

1. クエリのスケジューリングの利用設定

初めてスケジューリング機能を利用する場合は、機能自体を有効化する必要があります。

2. クエリの用意

2,3行目の「DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)」で前日の日付を取得しています。ユーザー定義関数を使うことで、前日や前週、前月の日付を対象期間に設定できます。
また、テーブル名は自身のものに書き換えてください。以下のクエリを実行してもNo resultsになります。

-- データを取得する対象期間を設定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY));
CREATE TEMPORARY FUNCTION toDate()  AS (DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY));

​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
 date
 , COUNT(DISTINCT user_pseudo_id) AS active_users
FROM `convert_event_params`
GROUP BY date
)

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​

3. 「Create new scheduled query」をクリック

4. 各項目を設定

以下の画像のように各項目を設定します。
⑤のテーブル名ですが、「{run_time-24h|"%Y%m%d"}」と書くことで前日の日付に変わります。
例えば、実行日が2020/12/15の場合は「daily_calc_firebase_screen_class_20201214」になります。

週毎にクエリのスケジューリングを設定する例

クエリについては以下のようになります。抜粋になるため、その他のクエリは上記の日毎のものを参考にしてください。

-- データを取得する対象期間を設定
-- 7日前を指定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 7 DAY));
-- 1日前を指定
CREATE TEMPORARY FUNCTION toDate()  AS (DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY));

WITH
`convert_event_params` AS(
SELECT
 -- 日付をその週の初めの日曜日に変換
 DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), WEEK) 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'
)

etc....

先週初め（日曜）から先月末（土曜）までのGoogle Analyticsのデータを取得できるように対象期間を設定しつつ、週毎にデータを集計できるように設定しています。
例えば、2021年1月10日（日）に実行された場合は2021年1月3日（日）〜1月9日（土）が対象となります。

-- 7日前を指定
CREATE TEMPORARY FUNCTION fromDate() AS (DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 7 DAY));
-- 1日前を指定
CREATE TEMPORARY FUNCTION toDate()  AS (DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY));


-- 日付をその週の初めの日曜日に変換
DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), WEEK) AS date

また、以下の画像を参考にスケジュールを設定してください。
テーブル名ですが、2020年1月10日に実行された場合は「weekly_calc_firebase_screen_class_20210109」で保存されます。

月毎にクエリのスケジューリングを設定する例

クエリについては以下のようになります。抜粋になるため、その他のクエリは上記の日毎のものを参考にしてください。

-- データを取得する対象期間を設定
-- 先月の初日を指定、○月1日
CREATE TEMPORARY FUNCTION fromDate() AS (DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH));
-- 先月末を指定、○月31日など
CREATE TEMPORARY FUNCTION toDate()  AS (DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY));

WITH
`convert_event_params` AS(
SELECT
 -- 日付をYYYY年MM月1日に変換
 DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), MONTH) 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'
)

etc....

先月初めから先月末までのGoogle Analyticsのデータを取得できるように対象期間を設定しつつ、月毎にデータを集計できるように設定しています。

-- 先月の初日を指定、○月1日
CREATE TEMPORARY FUNCTION fromDate() AS (DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH));
-- 先月末を指定、○月31日など
CREATE TEMPORARY FUNCTION toDate()  AS (DATE_SUB(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 DAY));


-- 日付をYYYY年MM月1日に変換
 DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), MONTH) AS date​

また、以下の画像を参考にスケジュールを設定してください。
テーブル名ですが、2020年1月1日に実行された場合は「monthly_calc_firebase_screen_class_20201231」で保存されます。

最後に

今回は日毎、週毎、月毎にクエリのスケジューリングを設定する方法をご紹介しました。
クエリの内容を書き換えることで、自動かつ定期的にクエリを実行してテーブルとして保存できます。

この保存されたテーブルをGoogle Data Studioで読み込むように設定することで、モニタリング用の自動更新されるダッシュボードが完成します。
次の記事ではBigQueryのテーブルをGoogle Data Studioで表示する方法をご紹介します。

このマガジンの記事

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のクエリのスケジューリングの設定方法や使い方