---
title: GA4ページ別エンゲージメント分析_SQLアーキテクチャ解剖
tags: 
author: [渋谷TAIZI](https://docswell.com/user/4514287294)
site: [Docswell](https://www.docswell.com/)
thumbnail: https://bcdn.docswell.com/page/LE1YY62V7G.jpg?width=480
description: GA4ページ別エンゲージメント分析_SQLアーキテクチャ解剖 by 渋谷TAIZI
published: May 10, 26
canonical: https://docswell.com/s/4514287294/5Y8EQ3-2026-05-10-133536
---
# Page. 1

![Page Image](https://bcdn.docswell.com/page/LE1YY62V7G.jpg)

// TECHNICAL_CODE_WALKTHROUGH
GA4 ページ別エンゲージメント分析: SQL アーキテクチャ解剖
段階的クエリ処理構造とデータ変換パイプラインの解説

# Page. 2

![Page Image](https://bcdn.docswell.com/page/GEWGGWR3J2.jpg)

Query Architecture / 全体処理パイプライン
[Phase 0: UDF] | 対象期間の定義 (date_from, date_to)
[Phase 1: CTE 1] | ランディングページの特定 (Session_Landing_Page)
[Phase 2: CTE 2] | エンゲージメント時間の算出 (Session_Engagement)
[Phase 3: CTE 3] | ディメンションと指標の結合 (Joined_Data)
[Phase 4: CTE 4] | レポート用文字列フォーマット (Format_Clac)
[Phase 5: Output] | 最終結果の抽出

# Page. 3

![Page Image](https://bcdn.docswell.com/page/47ZLL5RGJ3.jpg)

Phase 0: 処理対象期間の固定 (UDF定義)
-- ランディングページ別のセッションあたり平均エンゲージメント時間
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
Memory Block
date_from() | date_to()
'20260301' | '20260331'
Global Query Scope
役割: クエリ全体で使用する開始日・終了日を一時関数 (TEMP FUNCTION) として定義。
解説: テーブルサフィックスの指定で何度も記述する日付をハードコードして一元管理。期間変更時の修正漏れを防ぎ、クエリの保守性と可読性を向上させています。

# Page. 4

![Page Image](https://bcdn.docswell.com/page/YJ6WW9RQJV.jpg)

Phase 1-A: LP特定 - 生データの抽出 (FROM句の内側)
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
user_pseudo_id,
(SELECT value.int_value FROM
UNNEST(event_params) WHERE key = 'ga_session_
id') AS ga_session_id,
REGEXP_REPLACE((SELECT value.string_value FROM
UNNEST(event_params) WHERE key =
'page_location'), r'\?.', '') AS
landing_page,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'entrances') AS entrances
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
AND event_name = 'page_view'
UNNEST Flattening Process
{ "params": [ { "key": "canmo", "key": 1000, "value": "1/20750" }, { "key": "canmo", "key": 1000, "value": "1/20750" } ] } event_params (Nested Array)
UNNEST() -> ymd, ga_session_id, landing_page, entrances
・役割: ベースとなるログデータから必要なカラムを抽出。
・解説: FROM句の内側のクエリ (データの源泉) です。指定期間のpage_viewイベントを抽出し、UNNEST関数でネストされたパラメータを展開。REGEXP_REPLACEを用いてURLのクエリパラメータを除外し、正規化されたページパスを取得しています。

# Page. 5

![Page Image](https://bcdn.docswell.com/page/GJ5MMN82J4.jpg)

Phase 1-B: LP特定 - ランディング判定 (外側のクエリ)
"WITH Session_Landing_Page AS (
-- 【1】各セッションのランディングページを特定
SELECT ymd, user_pseudo_id, ga_session_id, landing_page
FROM (
[前スライドのInner Query]
)
WHERE entrances = 1
),"
All Page Views (Inner Query Output)
|
|
|
|
|
WHERE entrances = 1
|
|
|
True Landing Pages (Session_Landing_Page)
・役割: 抽出したデータから「真のランディングページ」のみを絞り込み。
・解説: 内側のクエリで抽出した仮想テーブルに対して外側からフィルタリングを実行します。entrances = 1 (流入元となったページビュー) を満たすレコードのみを残すことで、セッションごとのランディングページを確定させます。

# Page. 6

![Page Image](https://bcdn.docswell.com/page/LE3WW281E5.jpg)

Phase 2: エンゲージメント時間の集計
Session_Engagement AS (
-- 【2】各セッションの合計エンゲージメント時間を算出
SELECT
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_id') AS ga_session_id,
SUM(FLOOR(SAFE_DIVIDE(COALESCE(
(SELECT value.int_value FROM
UNNEST(event_params)
WHERE key = 'engagement_time_msec')
, 0), 1000))) AS session_seconds
)
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
GROUP BY user_pseudo_id, ga_session_id
),
engagement_time_msec
engagement_time_msec
engagement_time_msec
engagement_time_msec
GROUP BY: user_pseudo_id + ga_session_id
/ 1000 (SAFE_DIVIDE) -> FLOOR() -> SUM() -> session_seconds
・役割: 各セッションが消費したエンゲージメント時間を秒単位で計算。
・解説: ユーザーIDとセッションIDでGROUP BYを実行。ミリ秒単位の時間を抽出し、COALESCEでNULLを0に変換。その後SAFE_DIVIDEで1000で割り、安全に秒単位の合計値を算出しています。

# Page. 7

![Page Image](https://bcdn.docswell.com/page/8EDKKZMK7G.jpg)

Phase 3: ディメンションと指標の結合 (INNER JOIN)
Joined_Data AS (
-- 【3】LPと時間を結合
SELECT
slp.ymd,
slp.landing_page,
COUNT(*) AS _ss,
SUM(se.session_seconds) AS
_total_engagement_time_seconds
FROM Session_Landing_Page slp
INNER JOIN Session_Engagement se
ON slp.user_pseudo_id =
se.user_pseudo_id AND slp.ga_session_id
= se.ga_session_id
GROUP BY ymd, landing_page
),
Session_Landing_Page (Phase 1)
ymd
user_pseudo_id
ga_session_id
landing_page
Session_Engagement (Phase 2)
user_pseudo_id
ga_session_id
session_seconds
INNER JOIN
Joined_Data
ymd | landing_page | _ss | _total_engagement_time_seconds
役割: 特定したLPと計算した時間を統合。
解説: 複合キーを用いて両データを結合。日付とLPでグループ化し、セッション数 (COUNT(*)) と合計エンゲージメント時間 (SUM) を集計して最終集計のベースを作成します。

# Page. 8

![Page Image](https://bcdn.docswell.com/page/V7PKKDR3J8.jpg)

Phase 4: 出力用フォーマットへの変換
Format_Clac AS (
-- 【4】最終出力: 数値列とフォーマット列を併記
SELECT ymd, landing_page, _ss,
_total_engagement_time_seconds AS
_engagement_time_seconds,
CONCAT(
FORMAT('%02d時間 ', CAST(FLOOR(
(_total_engagement_time_seconds / 3600) AS
INT64)),
FORMAT('%02d分 ', CAST(FLOOR(MOD(CAST(
(_total_engagement_time_seconds AS INT64),
3600) / 60) AS INT64)),
FORMAT('%02d秒', CAST(MOD(CAST(
(_total_engagement_time_seconds AS INT64),
60) AS INT64))
) AS format_total_time
FROM Joined_Data
ORDER BY ymd ASC, _ss DESC
)
Total Seconds (e.g., 3665s)
/ 3600 -> 01時間
MOD 3600 / 60 -> 01分
MOD 60 -> 05秒
CONCAT -> 01時間 01分 05秒
・役割: 数値の秒数を人間が読みやすい形式に変換。
・解説: FLOOR (切り捨て) とMOD (剰余算) を組み合わせ、秒数から時間、分、秒を抽出。合計時間だけでなく、セッション数で割った平均時間も同様に文字列化しています。

# Page. 9

![Page Image](https://bcdn.docswell.com/page/2JVVV1RNJQ.jpg)

Phase 5: クエリ結果の最終出力
SELECT *
FROM Session_Engagement
ymd | landing_page | _ss | _engagement_time_seconds
2023-10-27 | /product/A | 120 | 3600
2023-10-27 | /blog/B | 50 | 1800
2023-10-27 | /home | 200 | 7200
System Note (Important)
提供された元SQLでは最終出力として Session_Engagement が指定されています。全体のフォーマット結果 (Phase 4) を確認する場合は、ここを SELECT * FROM Format_Clac に変更して実行することが想定されるアーキテクチャとなっています。元SQLの完全性を維持するため、現状のコードを明示しています。
・役割: 構築したテーブル構造からデータを呼び出し。
・解説: 全ての処理が完了した後、最終的なSELECT文で結果を出力します。