Google Analytics -> BigQuery -> Looker Studio のゴールデンルートを数時間で設定する

この記事は羅針盤アドベントカレンダー 2024の13日目の記事です。
qiita.com

すみません、タイトルの「数時間で設定する」は釣りタイトルです。
（GA4→BQの設定で一日置かないとデータでてこない）

こんにちは、羅針盤の森川です。
最近は水出しコーヒーにハマってます。

今回はアクセス解析系の話です。 Google Analyticsを使っている方は多いというか現在人（IT人間）の必須ツールであり人権かなと思います。

ただGAのレポートって慣れてないと使いづらいですよね。
探索レポートも共有できるけどコピーして編集したり、なんかもどかしいです。
エンジニア的にはデータベースに対してSQLを実行できることが基本的人権であり、心理的安全性です。（クリックカチカチ〜）

そんな私に朗報！
GAデータはBQにデータ送れるし、Looker Studioで可視化すれば、微課金ユーザーとして生きていくことができます。失われた権利と尊厳を取り戻す。

今回は既にGAの設定が済んでいる前提で記載しています。

Google Analytics → Google BigQuery

まずはGAデータをBQに転送保存する設定をします。左下の設定（歯車アイコン）から「サービス間のリンク設定」内にある「BigQueryのリンク」を選択します。

そして右の「リンク」からBQへの転送設定をします。

BQプロジェクトは各自設定してもろて、イベントデータのエクスポートタイプの「毎日」だけチェックを入れておけばOKです。 Streamingやユーザーデータは必要に応じてチェックして送ってください。

設定が終わったらご飯を食べて、お風呂に入って、ゲームしてぐっすり寝て起きると、最初の日次データ転送が終わっているかと思います。
https://console.cloud.google.com/bigquery

（event_... が日次イベントデータ）

Google BigQuery : 集計テーブルの作成

BQにデータが転送されたのでこのままでも使えますが、
ローデータを毎回クエリするのは効率が悪いので、日次の集計データのテーブルを作ります。

幸いにして、BQ単体でスケジュールクエリ機能があるのでそれを使います。
たぶんAPIをEnabeしないと使えないのでEnablingしましょう。これであなたもEnabler。 https://console.cloud.google.com/apis/library/bigquerydatatransfer.googleapis.com

目的に分けて複数のテーブルを作りますが、いくつか依存関係があるのでステップで以下の処理を実行します

1. 事前準備
2. [1-a] ローデータのネストされた型（配列型）をUNNESTしたり、補助カラムを追加したテーブルを作る
1. 集計テーブル作成
2. [2-b] 1-a のデータから日別でデータ集計したテーブルを作る（1行 = 1日の総計）
3. [2-c] 1-a のデータから日別・ページパス別のユニークユーザー数を集計したテーブルを作る（1行 = 1URLの総計）
4. [2-d] 1-a のデータを日別・ユーザー別でユニークにしたテーブルを作る（セッション単位からユーザー単位にする, 1行 = 1ユーザー）
5. [2-e] 1-a のデータから日別・ユーザー別でのファネル通過率のテーブルを作る（1行 = 1ユーザー）
1. ファネル通過率の日別集計テーブル作成
2. [3-f] 2-e のデータを日別の平均・X日移動平均を集計したテーブルを作る（1行 = 1日の総計）

以降は具体的なSQLを用いて解説していきます。 analytics というデータセットをBQ上に作っていてそこにデータを貯めていく前提で話を進めます。

データセットの作成

まずは analytics というデータセットを作成します。
名前は適当に変えてください。

asia-northeast1 リージョンだと20%くらい料金が高いと思いますが、気になるならUSとかにしてください。

事前準備テーブルの作成 [1-a]

この事前テーブルが全ての起点になります。元のGAのテーブルを以降のSQLで使いやすいように整形します。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );
CREATE TEMPORARY FUNCTION date_YYYYMMDD(i INT64) AS ( FORMAT_DATE("%Y%m%d", DATE_SUB(cur_date(), INTERVAL i DAY)) );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT event_date FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY event_date
),
*/
/* [_raw1] GAのローデータを日時指定してます */
_raw1 AS (
  SELECT * FROM `analytics_123456789.events_*`  /* <- change here */
  WHERE _table_suffix BETWEEN date_YYYYMMDD(date_from()) AND date_YYYYMMDD(date_to())
  -- AND _table_suffix NOT IN (SELECT event_date FROM _dest)  /* 重複防止条件 */
),
/* [_raw2] _raw1に日付を追加したりUNNESTしたりしたデータ */
_raw2 AS (
SELECT 
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') as date_jst,
  TIMESTAMP_TRUNC(DATETIME(timestamp_micros(event_timestamp), 'Asia/Tokyo'), SECOND) as datetime_jst,
  FARM_FINGERPRINT(CONCAT(event_timestamp, event_name, user_pseudo_id)) as row_id,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "ga_session_id") as ga_session_id,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "ga_session_number") as ga_session_number,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "engaged_session_event") as engaged_session_event,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "engagement_time_msec") as engagement_time_msec,
  (SELECT CASE WHEN p.value.int_value IS NOT NULL THEN SAFE_CAST(p.value.int_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "entrances") as entrances,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "page_referrer") as page_referrer,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "page_location") as page_location,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as string) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "page_title") as page_title,
  (SELECT CASE WHEN p.value.string_value IS NOT NULL THEN SAFE_CAST(p.value.string_value as INT64) ELSE null END FROM UNNEST(event_params) as p WHERE p.key = "session_engaged") as session_engaged,
  *,
  FROM _raw1
),
/* [raw] _raw2に補助カラムを追加したもの */
raw AS (
SELECT
  row_id,
  date_jst,
  datetime_jst,
  CONCAT(user_pseudo_id, ga_session_id) as user_session_id,
  event_date,
  event_timestamp,
  event_name,
  event_previous_timestamp,
  event_value_in_usd,
  event_bundle_sequence_id,
  event_server_timestamp_offset,
  session_engaged,
  ga_session_id,
  ga_session_number,
  page_referrer,
  page_location,
  page_title,
  /* (1) */
  `change-your-common-project.get_path_from_url`(page_location) as page_path,
  `change-your-common-project.get_query_from_url`(page_location) as page_query,
  engaged_session_event,
  engagement_time_msec,
  engagement_time_msec / 1000 as engagement_time_sec,
  user_id,
  user_pseudo_id,
  privacy_info,
  user_first_touch_timestamp,
  user_ltv,
  device,
  geo,
  app_info,
  traffic_source,
  stream_id,
  platform,
  event_dimensions,
  ecommerce,
  items,
  collected_traffic_source,
  is_active_user,
  /* (1) */
  `change-your-common-project.common.channel_grouping`(LOWER(traffic_source.source), LOWER(traffic_source.medium), LOWER(traffic_source.name)) as channel_group,
  CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END as is_pv,
  CASE WHEN event_name = 'page_view' AND entrances = 1 THEN 1 ELSE 0 END as is_landing,
  CASE WHEN event_name = 'user_engagement' AND session_engaged = 1 THEN 1 ELSE 0 END as is_engaged,
  CASE WHEN event_name IN ('purchase', '決済完了') THEN 1 ELSE 0 END as is_cv, /* <- change here */
FROM _raw2
), 
/* [_stats1] 日別・ユーザー別のセッション数を集計した一時テーブル */
_stats1 AS (
SELECT 
  date_jst,
  COUNT(DISTINCT user_session_id) as stats_sessions,
  COUNT(DISTINCT CASE WHEN is_pv = 1 THEN user_session_id END) as stats_pv_sessions,
  COUNT(DISTINCT CASE WHEN session_engaged = 1 THEN user_session_id END) as stats_engaged_sessions,
FROM raw
GROUP BY date_jst 
),
/* [stats] 日別のセッション数とセッション種別割合を集計したテーブル（同一日であれば全て同じデータが入る） */
stats AS (
SELECT
  date_jst,
  stats_sessions,
  stats_engaged_sessions,
  stats_sessions - stats_engaged_sessions as stats_bounced_sessions,
  SAFE_DIVIDE(stats_engaged_sessions, stats_sessions) as stats_engagement_rate,
  SAFE_DIVIDE(stats_sessions - stats_engaged_sessions, stats_sessions) as stats_bounce_rate,
  SAFE_DIVIDE(stats_pv_sessions, stats_sessions) as stats_event_count_per_session,
FROM _stats1
),
/* [_engage1] 日別・ユーザー別のエンゲージメントを集計した一時テーブル */
_engage1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN session_engaged = 1 THEN engagement_time_sec END) as session_engagement_time_sec,
FROM raw
GROUP BY date_jst, user_session_id
),
/* [engage] 日別のエンゲージメントと割合を集計したテーブル（同一日であれば全て同じデータが入る） */
engage AS (
SELECT 
  t1.date_jst,
  SAFE_DIVIDE(SUM(session_engagement_time_sec), MAX(stats_engaged_sessions)) as avg_engagement_time_sec,
FROM _engage1 t1
JOIN stats t2 ON t1.date_jst = t2.date_jst
GROUP BY t1.date_jst
),
/* [_sess1] 日別・ユーザー別のセッション時間を集計した一時テーブル */
_sess1 AS (
SELECT
  date_jst,
  user_session_id,
  (MAX(event_timestamp) - MIN(event_timestamp)) / 1000000 as session_length_in_sec,
FROM raw
GROUP BY date_jst, user_session_id
), 
/* [sess] 日別の平均セッション時間を集計したテーブル（同一日であれば全て同じデータが入る） */
sess AS (
SELECT
  date_jst,
  SAFE_DIVIDE(SUM(session_length_in_sec), COUNT(DISTINCT user_session_id)) as avg_session_duration_sec,
FROM _sess1
GROUP BY date_jst
),
/* [_views1] 日別・ユーザー別のPV数を集計した一時テーブル */
_views1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN is_pv = 1 THEN 1 ELSE 0 END) as session_pv,
FROM raw
GROUP BY date_jst, user_session_id
), 
/* [viewscount] 日別のセッション辺りのPV数を集計したテーブル（同一日であれば全て同じデータが入る） */
viewscount AS (
SELECT
  date_jst,
  SAFE_DIVIDE(SUM(session_pv), COUNT(DISTINCT user_session_id)) as avg_views_per_session,
FROM _views1
GROUP BY date_jst
) 


/* 上記のテーブルを結合して必要なデータを取得 */
SELECT 
  raw.*,
  st.stats_sessions,
  st.stats_engaged_sessions,
  st.stats_bounced_sessions,
  st.stats_engagement_rate,
  st.stats_bounce_rate,
  st.stats_event_count_per_session,
  e.avg_engagement_time_sec,
  se.avg_session_duration_sec,
  v.avg_views_per_session,
FROM raw 
JOIN stats st ON st.date_jst = raw.date_jst 
JOIN engage e ON e.date_jst = raw.date_jst 
JOIN sess se ON se.date_jst = raw.date_jst 
JOIN viewscount v ON v.date_jst = raw.date_jst 
;

SQLに少しコメント入れているので内容はそこで確認してください。
(1) の箇所は永続関数を使っています。複数のGAプロジェクトで使い回せるように別の共通プロジェクトで定義しています。

channel_grouping はチャネル名を整形する関数です。

-- channel_grouping

CREATE OR REPLACE FUNCTION `change-your-common-project.channel_grouping`(tsource STRING, medium STRING, campaign STRING) AS (
CASE
        WHEN (tsource = 'direct' OR tsource = '(direct)' OR tsource IS NULL) 
            AND (regexp_contains(medium, r'^(\(not set\)|\(none\))$') OR medium IS NULL) 
            THEN 'direct'
        WHEN regexp_contains(campaign, r'^(.*(([^a-df-z]|^)shop|shopping).*)$') 
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_shopping'
        WHEN regexp_contains(tsource, r'^(google|yahoo|bing)$') 
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_search'
        WHEN regexp_contains(tsource, r'^(twitter|facebook|fb|instagram|ig|linkedin|pinterest)$')
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*|social_paid)$') 
            THEN 'paid_social'
        WHEN regexp_contains(tsource, r'^(youtube)$')
            AND regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_video'
        WHEN regexp_contains(medium, r'^(display|banner|expandable|interstitial|cpm)$') 
            THEN 'paid_display'
        WHEN regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            THEN 'paid_other'
        WHEN regexp_contains(medium, r'^(.*(([^a-df-z]|^)shop|shopping).*)$') 
            THEN 'organic_shopping'
        WHEN regexp_contains(tsource, r'^.*(twitter|t\.co|facebook|instagram|linkedin|lnkd\.in|pinterest|tiktok).*') 
            or regexp_contains(medium, r'^(social|social_advertising|social-advertising|social_network|social-network|social network|social_media|social-media|social media|sm|social-unpaid|social_unpaid)$') 
            THEN 'organic_social'
        WHEN regexp_contains(medium, r'^(.*video.*)$') 
            THEN 'organic_video'
        WHEN regexp_contains(tsource, r'^(google|bing|yahoo|baidu|duckduckgo|yandex|ask)$') 
            or medium = 'organic'
            THEN 'organic_search'
        WHEN regexp_contains(tsource, r'^(email|mail|e-mail|e_mail|e mail|mail\.google\.com)$') 
            or regexp_contains(medium, r'^(email|mail|e-mail|e_mail|e mail)$') 
            THEN 'email'
        WHEN regexp_contains(medium, r'^(affiliate|affiliates)$') 
            THEN 'affiliate'
        WHEN medium = 'referral'
            THEN 'referral'
        WHEN medium = 'audio' 
            THEN 'audio'
        WHEN medium = 'sms'
            THEN 'sms'
        WHEN ends_with(medium, 'push')
            or regexp_contains(medium, r'.*(mobile|notification).*') 
            THEN 'mobile_push'
        ELSE '(other)'
    END
);

get_path_from_url はURLからページパスを抜き出す関数です。

CREATE OR REPLACE FUNCTION `change-your-common-project.common.get_path_from_url`(url STRING) RETURNS STRING LANGUAGE js AS R"""
var path = '/' + url.split('/').splice(3).join("/")
  return path.split('?')[0];
""";

get_query_from_url はURLからクエリパラメータを抜き出す関数です。 utm_... 等のパラメータは無視するようにしています。

CREATE OR REPLACE FUNCTION `change-your-common-project.common.get_query_from_url`(url STRING) RETURNS STRING LANGUAGE js AS R"""
parts = url.split('?');
  if (parts.length < 2) return '';
  var ignore_keys = {
    'utm_source': true,
    'utm_medium': true,
    'utm_campaign': true,
    'utm_content': true,
    'utm_term': true,
    'email': true,
    'hash': true,
    'gclid': true,
    'fbclid': true,
    'mc_cid': true,
    'mc_eid': true,
    'msclkid': true,  
  }
  var results = []
  var list = parts[1].split('&')
  for (i = 0; i < list.length; i++)  {
    var item = list[i]
    if (!ignore_keys[item.split('=')[0]]) {
      results.push(item)
    }
  }

  return results.join("&");
""";

スケジュールクエリの設定

上記のSQLのテスト実行とスケジュールクエリ設定を行います。（BQ Data Transfer APIをEnableしてください）

左サイドバーから「スケジュールされたクエリ」を選択し、そのまま作成に進みます。 SQL入力フィールドで先ほどの 1-a のSQLを入力し、一度「実行」してみてエラーが発生しないか確かめます。

問題なく結果が出力されたら「スケジュール」を選択して設定を保存します。以下は設定の例です。

繰り返しの頻度: 日
時刻: 03:00 (UTC)
「設定した時刻に開始」
直近の日付の 12:00 (JST)
「終了しない」
データセット: analytics
Table Id: ga4_raw
宛先テーブルのパーティショニングフィールド: date_jst
「テーブルに追加する」

保存後に、テスト実行してデータが新しいテーブルに保存されるか試します。
作成されたスケジュールクエリを選択し、「バックフィルのスケジュール構成」を選びます。
「1回限りのスケジュールされたクエリ」を選べばテスト実行できます。

実行して数分待つと完了していると思います。成功していれば新しいテーブルが作成されているはずなので、データを確認してみてください。また、SQL中でコメントアウトしてある重複排除の条件を追加すると再実行してもデータが重複しないのでアンコメントして再保存しておいてください。

以降の 2-b ~ 3-f のSQLでも同様にスケジュールクエリを設定してください。実行時間だけは変える必要があり、 1-a 以降の時間（5分後の12:05とか）にしてください。 2-b ~ 2-e は 1-a以外に依存関係が無く別のテーブルなので全て同じ時間に設定しても問題ありません。

日次サマリーテーブルの作成 [2-b]

[1-a] のテーブルを使って日次サマリーデータを保存するテーブルを作ります。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );


WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_stats`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw` -- [a] のテーブルを指定
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
_stats1 AS (
SELECT 
  date_jst,
  COUNT(DISTINCT user_session_id) as sum_sessions,
  COUNT(DISTINCT user_pseudo_id) as sum_user,
  COUNT(DISTINCT CASE WHEN ga_session_number = 1 THEN user_pseudo_id END) as sum_user_new,
  COUNT(DISTINCT CASE WHEN is_pv = 1 THEN user_session_id END) as sum_pv_sessions,
  COUNT(DISTINCT CASE WHEN session_engaged = 1 THEN user_session_id END) as sum_engaged_sessions,
  COUNT(DISTINCT CASE WHEN is_cv = 1 THEN user_session_id END) as sum_cv_sessions,
  COUNT(DISTINCT CASE WHEN is_cv = 1 THEN user_pseudo_id END) as sum_cv_users,
FROM raw
GROUP BY date_jst 
),
stats AS (
SELECT
  date_jst,
  sum_sessions,
  sum_engaged_sessions,
  sum_sessions - sum_engaged_sessions as sum_bounced_sessions,
  sum_user,
  sum_user_new,
  sum_user - sum_user_new as sum_user_old,
  sum_cv_sessions,
  sum_cv_users,
  SAFE_DIVIDE(sum_engaged_sessions, sum_sessions) as avg_engagement_rate,
  SAFE_DIVIDE(sum_sessions - sum_engaged_sessions, sum_sessions) as avg_bounce_rate,
  SAFE_DIVIDE(sum_pv_sessions, sum_sessions) as avg_event_count_per_session,
  SAFE_DIVIDE(sum_user_new, sum_user) as rate_new_user,
  SAFE_DIVIDE(sum_cv_sessions, sum_sessions) as rate_cvr_per_session,
  SAFE_DIVIDE(sum_cv_users, sum_user) as rate_cvr_per_user,
  SAFE_DIVIDE(sum_sessions, sum_user) as avg_session_per_user,
FROM _stats1
),
_engage1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN session_engaged = 1 THEN engagement_time_sec END) as session_engagement_time_sec,
FROM raw
GROUP BY date_jst, user_session_id
),
engage AS (
SELECT 
  t1.date_jst,
  SAFE_DIVIDE(SUM(session_engagement_time_sec), MAX(sum_engaged_sessions)) as avg_engagement_time_sec,
FROM _engage1 t1
JOIN stats t2 ON t1.date_jst = t2.date_jst
GROUP BY t1.date_jst
),
_sess1 AS (
SELECT
  date_jst,
  user_session_id,
  (MAX(event_timestamp) - MIN(event_timestamp)) / 1000000 as session_length_in_sec,
FROM raw
GROUP BY date_jst, user_session_id
), 
sess AS (
SELECT
  date_jst,
  SAFE_DIVIDE(SUM(session_length_in_sec), COUNT(DISTINCT user_session_id)) as avg_session_duration_sec,
FROM _sess1
GROUP BY date_jst
),
_views1 AS (
SELECT
  date_jst,
  user_session_id,
  SUM(CASE WHEN is_pv = 1 THEN 1 ELSE 0 END) as sum_pv_sessions,
FROM raw
GROUP BY date_jst, user_session_id
), 
viewscount AS (
SELECT
  date_jst,
  SUM(sum_pv_sessions) as sum_pv_sessions,
  SAFE_DIVIDE(SUM(sum_pv_sessions), COUNT(DISTINCT user_session_id)) as avg_pv_per_session,
FROM _views1
GROUP BY date_jst
) 


SELECT
  st.date_jst,
  st.sum_user,
  st.sum_user_new,
  st.sum_user_old,
  st.rate_new_user,
  st.sum_cv_sessions,
  st.sum_cv_users,
  st.rate_cvr_per_session,
  st.rate_cvr_per_user,
  st.sum_sessions,
  st.sum_engaged_sessions,
  st.sum_bounced_sessions,
  st.avg_engagement_rate,
  st.avg_bounce_rate,
  st.avg_event_count_per_session,
  st.avg_session_per_user,
  e.avg_engagement_time_sec,
  se.avg_session_duration_sec,
  v.sum_pv_sessions,
  v.avg_pv_per_session,
FROM stats st 
JOIN engage e ON e.date_jst = st.date_jst 
JOIN sess se ON se.date_jst = st.date_jst 
JOIN viewscount v ON v.date_jst = st.date_jst 
;

このSQLでは以下のようなデータが取得できます。
カラム名で察してください。

date_jst	sum_user	sum_user_new	sum_user_old	rate_new_user	sum_cv_sessions	sum_cv_users	rate_cvr_per_session	rate_cvr_per_user	sum_sessions	sum_engaged_sessions	sum_bounced_sessions	avg_engagement_rate	avg_bounce_rate	avg_event_count_per_session	avg_session_per_user	avg_engagement_time_sec	avg_session_duration_sec	sum_pv_sessions	avg_pv_per_session
2024-12-01	1000	500	100	0.5025249169	5	5	0.0150195346	0.01790697674	1100	900	300	0.5048315983	0.2025168402	0.9557072872	1.105049834	100.1146624	150.9513281	5000	5.077770974

日別ページパステーブルの作成 [2-c]

[1-a] のテーブルを使って日別ページパスデータを保存するテーブルを作ります。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_page`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
), 
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
page1 AS (
SELECT 
  date_jst,
  page_path,
  COUNT(DISTINCT user_session_id) as pv_session,
  COUNT(DISTINCT user_pseudo_id) as pv_user,
  COUNT(DISTINCT CASE WHEN is_landing = 1 THEN user_session_id END) as pv_first_page_session,
  COUNT(DISTINCT CASE WHEN is_landing = 1 THEN user_pseudo_id END) as pv_first_page_user,
FROM raw
GROUP BY date_jst, page_path
),
_page2 AS (
SELECT 
  date_jst,
  page_path,
  CASE WHEN is_landing = 1 THEN page_path ELSE null END as first_page,
  CASE WHEN is_landing = 1 THEN LEAD(page_path, 1) OVER (partition by user_session_id ORDER BY datetime_jst ASC) ELSE null END as second_page,
  CASE WHEN page_path = first_value(page_path) OVER (partition by user_session_id ORDER BY datetime_jst DESC) THEN page_path ELSE null END as last_page,
FROM raw
),
page2 AS (
SELECT 
  date_jst,
  page_path,
  SUM(CASE WHEN page_path = second_page THEN 1 ELSE 0 END) as pv_second_page,
  SUM(CASE WHEN page_path = last_page THEN 1 ELSE 0 END) as pv_last_page,
FROM _page2
GROUP BY date_jst, page_path
)

SELECT
  p1.date_jst,
  p1.page_path,
  pv_session,
  pv_user,
  pv_first_page_session,
  pv_first_page_user,
  pv_second_page,
  pv_last_page,
FROM page1 p1
JOIN page2 p2 ON p1.date_jst = p2.date_jst AND p1.page_path = p2.page_path
;

このSQLでは以下のようなデータが取得できます。

date_jst	page_path	pv_session	pv_user	pv_first_page_session	pv_first_page_user	pv_second_page	pv_last_page
2024-12-01	/terms_of_use	6	4	1	1	1	1

日別ユーザーイベントテーブルの作成 [2-d]

[1-a] のテーブルを使って日別ユーザーイベントデータを保存するテーブルを作ります。

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_etl`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
_etl1 AS (
SELECT 
  date_jst,
  user_session_id,
  MIN(datetime_jst) as min_datetime_jst,
  MAX(datetime_jst) as max_datetime_jst,
  MAX(user_pseudo_id) as user_pseudo_id,
  MIN(ga_session_number) as ga_session_number, 
  AVG(engagement_time_sec) as engagement_time_sec,
  MAX(is_engaged) as is_engaged,
  MAX(is_cv) as is_cv,
  SUM(is_pv) as count_pv,
  MAX(device.language) as device_language,
  MAX(device.category) as device_category,
  MAX(device.operating_system) as device_os,
  MAX(device.operating_system_version) as device_os_version,
  MAX(geo.country) as geo_country,
  MAX(geo.region) as geo_region,
  MAX(traffic_source.source) as user_traffic_source,
  MAX(traffic_source.medium) as user_traffic_medium,
  MAX(traffic_source.name) as user_traffic_name,
  MAX(collected_traffic_source.manual_source) as session_traffic_source,
  MAX(collected_traffic_source.manual_medium) as session_traffic_medium,
  MAX(collected_traffic_source.manual_campaign_name) as session_traffic_name,
  MAX(channel_group) as channel_group,
FROM raw
GROUP BY date_jst, user_session_id 
)

SELECT
  *
FROM _etl1
;

このSQLでは以下のようなデータが取得できます。

date_jst	user_session_id	min_datetime_jst	max_datetime_jst	user_pseudo_id	ga_session_number	engagement_time_sec	is_engaged	is_cv	count_pv	device_language	device_category	device_os	device_os_version	geo_country	geo_region	user_traffic_source	user_traffic_medium	user_traffic_name	session_traffic_source	session_traffic_medium	session_traffic_name	channel_group
2024-12-01	100000000	2024-12-01 0:04:41	2024-12-01 0:05:10	100000000	1	3.181	0	0	1	en-us	desktop	Linux	Linux 6.9.0	Japan	Tokyo	facebook.com	referral	(referral)	facebook.com	referral	(referral)	organic_social

日別ユーザー別ファネル通過率テーブルの作成 [2-e]

/* config: 失敗していても取り込めるように4~1日前を期間指定しています */
CREATE TEMPORARY FUNCTION date_from() AS ( 4 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_funnel_cv`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
raw AS (
SELECT 
  *,
  FROM `analytics.ga4_raw`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  -- AND date_jst NOT IN (SELECT date_jst FROM _dest)  /* 重複防止条件 */
),
_data AS (
SELECT 
  date_jst,
  user_pseudo_id,
  MIN(datetime_jst) as min_datetime_jst,
  MAX(datetime_jst) as max_datetime_jst,
  MIN(ga_session_number) as ga_session_number, 
  SUM(is_pv) as count_pv,
  MAX(device.language) as device_language,
  MAX(device.category) as device_category,
  MAX(device.operating_system) as device_os,
  MAX(device.operating_system_version) as device_os_version,
  MAX(geo.country) as geo_country,
  MAX(geo.region) as geo_region,
  MAX(traffic_source.source) as user_traffic_source,
  MAX(traffic_source.medium) as user_traffic_medium,
  MAX(traffic_source.name) as user_traffic_name,
  MAX(collected_traffic_source.manual_source) as session_traffic_source,
  MAX(collected_traffic_source.manual_medium) as session_traffic_medium,
  MAX(collected_traffic_source.manual_campaign_name) as session_traffic_name,
  MAX(channel_group) as channel_group,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=1%' THEN 1 ELSE NULL END) as is_cart1,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=2%' THEN 1 ELSE NULL END) as is_cart2,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=3%' THEN 1 ELSE NULL END) as is_cart3,
  COUNT(DISTINCT CASE WHEN page_path = '/cart' AND page_query LIKE '%page=4%' THEN 1 ELSE NULL END) as is_cart4,
  COUNT(DISTINCT CASE WHEN page_path = '/cart/thanks' THEN 1 ELSE NULL END) as is_thanks,
FROM raw
GROUP BY date_jst, user_pseudo_id 
)

SELECT
  *
FROM _data
;

このSQLでは以下のようなデータが取得できます。

date_jst	user_pseudo_id	min_datetime_jst	max_datetime_jst	ga_session_number	count_pv	device_language	device_category	device_os	device_os_version	geo_country	geo_region	user_traffic_source	user_traffic_medium	user_traffic_name	session_traffic_source	session_traffic_medium	session_traffic_name	channel_group	is_cart1	is_cart2	is_cart3	is_cart4	is_thanks
2024-12-14	100000000	2024-12-01 17:24:39	2024-12-01 17:24:43	1	1	ja	mobile	iOS	iOS 17.6.1	Japan	Tokyo	mail.google.com	referral	(referral)	mail.google.com	referral	(referral)	email	1	0	0	0	0

ファネル通過率の集計テーブル作成 [3-f]

最後に 2-e を使ってファネル通過率の集計テーブルを作ります。ファネル通過率やCVRの日別平均、3日間移動平均、7日間移動平均を保存します。

/* config: 過去8日分の7日間移動平均を取得 */
CREATE TEMPORARY FUNCTION date_from() AS ( 15 );
CREATE TEMPORARY FUNCTION date_to() AS ( 1 );

/* tmp vars */
CREATE TEMPORARY FUNCTION cur_date() AS ( CURRENT_DATE("Asia/Tokyo") );

WITH 
/* [_dest] 初回はこのテーブルは存在しないためコメントアウトしていますが、スケジュールクエリでは重複を防ぐためにアンコメントして使います */
/*
_dest AS (
  SELECT date_jst FROM `analytics.ga4_funnel_avg`
  WHERE date_jst BETWEEN DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
*/
avg_data AS (
  SELECT
    date_jst,
    COUNT(user_pseudo_id) as user_count,
    SUM(is_thanks) as cv_count,
    SUM(is_thanks) / COUNT(user_pseudo_id) as cvr,
    AVG(is_cart1) as is_cart1,
    AVG(is_cart2) as is_cart2,
    AVG(is_cart3) as is_cart3,
    AVG(is_cart4) as is_cart4,
    AVG(is_thanks) as is_thanks,
  FROM
    `analytics.ga4_funnel_cv`
  WHERE date_jst BETWEEN  DATE_SUB(cur_date(), INTERVAL date_from() DAY) AND DATE_SUB(cur_date(), INTERVAL date_to() DAY)
  GROUP BY date_jst
),
cvr_data AS (
  SELECT
    date_jst,
    AVG(is_cart1) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart1_avg7,
    AVG(is_cart2) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart2_avg7,
    AVG(is_cart3) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart3_avg7,
    AVG(is_cart4) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_cart4_avg7,
    AVG(is_thanks) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as is_thanks_avg7,
    AVG(cvr) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) as cvr_avg7,
    AVG(is_cart1) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart1_avg3,
    AVG(is_cart2) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart2_avg3,
    AVG(is_cart3) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart3_avg3,
    AVG(is_cart4) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_cart4_avg3,
    AVG(is_thanks) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as is_thanks_avg3,
    AVG(cvr) OVER(ORDER BY UNIX_DATE(date_jst) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) as cvr_avg3,
  FROM
    avg_data
)


SELECT
  avg_data.date_jst,
  user_count,
  cv_count,
  cvr,
  is_cart1,
  is_cart2,
  is_cart3,
  is_cart4,
  is_thanks,
  is_cart1_avg7,
  is_cart2_avg7,
  is_cart3_avg7,
  is_cart4_avg7,
  is_thanks_avg7,
  cvr_avg7,
  is_cart1_avg3,
  is_cart2_avg3,
  is_cart3_avg3,
  is_cart4_avg3,
  is_thanks_avg3,
  cvr_avg3,
FROM
  avg_data
JOIN cvr_data ON avg_data.date_jst = cvr_data.date_jst 
-- WHERE avg_data.date_jst NOT IN (SELECT date_jst FROM _dest)
;

このSQLでは以下のようなデータが取得できます。

date_jst	user_count	cv_count	cvr	is_cart1	is_cart2	is_cart3	is_cart4	is_thanks	is_cart1_avg7	is_cart2_avg7	is_cart3_avg7	is_cart4_avg7	is_thanks_avg7	cvr_avg7	is_cart1_avg3	is_cart2_avg3	is_cart3_avg3	is_cart4_avg3	is_thanks_avg3	cvr_avg3
2024-12-01	1000	20	0.0162601626	0.112195122	0.06097560976	0.04471544715	0.02195121951	0.0162601626	0.1180583458	0.07179818466	0.04718445738	0.02393212923	0.01794440009	0.01794440009	0.1063511487	0.06086483479	0.04442387541	0.02290562374	0.01832657378	0.01832657378

これで準備は整いました。

Looker Studio でグラフ作成

あとはLooker Studioで可視化するだけです。説明上、イチから作成しますが他の方々が公開しているGA用のテンプレをベースに作ったほうが楽だと思います。他のテンプレを使う場合はコピーして、データソースを直GAからBQに変更して、ポチポチとグラフと指標を設定していくイメージです。

lookerstudio.google.com

一応1グラフ分だけ軽く説明します。

例としてファネルの7日間移動平均の推移を見るためのグラフを作成していきます。まずはLooker Studioにアクセスし、「空のレポート」を作成します。

データ接続はBigQueryを選択し、テーブルは先程作ったBQのテーブルのいずれかを選択してます。（後で他のテーブルも追加できます） date_jst は期間ディメンションとして使うようにします。

そして「グラフ」の部分を選択して表形式から期間グラフに変更します。

その後、指標を適当に選択するとそれっぽいグラフが出来上がります。

あとは見やすく整形したりしていくと最終的にはこんな感じになります。

ステップ1からの到達率は SUM(is_cart3) / SUM(is_cart1)、
次ステップ通過率は SUM(is_cart3) / SUM(is_cart2) みたいな感じで計算式を設定しています。

数字の傾向が変わっている部分で過去にリリースした履歴を見たりして、その影響を調べたりできるようになりました。
これでGAで見ていたデータをBQ+Lookerに移すことができ、エンジニアもマーケもビジネスもオペレーションも同じダッシュボードを参照して議論しやすくなるかと思います。
なおGAの完全な代替ではないので、アドホックな分析は普通に探索レポート使った方が早い場合もあるので、 GAもSQLも両方使えると良いかと思います。

羅針盤技術航海日誌

株式会社羅針盤の技術ブログです

Google Analytics -> BigQuery -> Looker Studio のゴールデンルートを数時間で設定する

Google Analytics → Google BigQuery

Google BigQuery : 集計テーブルの作成

データセットの作成

事前準備テーブルの作成 [1-a]

スケジュールクエリの設定

日次サマリーテーブルの作成 [2-b]

日別ページパステーブルの作成 [2-c]

日別ユーザーイベントテーブルの作成 [2-d]

日別ユーザー別ファネル通過率テーブルの作成 [2-e]

ファネル通過率の集計テーブル作成 [3-f]

Looker Studio でグラフ作成