---
title: GA4エンゲージメント時間抽出パイプラインの構造解剖
tags: 
author: [渋谷TAIZI](https://docswell.com/user/4514287294)
site: [Docswell](https://www.docswell.com/)
thumbnail: https://bcdn.docswell.com/page/GEWGGV2MJ2.jpg?width=480
description: GA4エンゲージメント時間抽出パイプラインの構造解剖 by 渋谷TAIZI
published: May 06, 26
canonical: https://docswell.com/s/4514287294/K8N1N7-2026-05-06-133334
---
# Page. 1

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

GA4 エンゲージメント時間抽出パイプラインの構造解剖
BigQuery SQLの内部実行プロセスとデータ変換のステップ・バイ・ステップ解説
For Data Analysts & Analytics Engineers

# Page. 2

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

クエリの全体構造と「インサイド・アウト」実行モデル
SQLは外側から記述されますが、論理的な実行は最も内側のFROM句から開始されます。本資料では、データが辿る実際のプロセスに合わせて、最深部から外側へとクエリを解読します。
ミニマップ: 全スライドで現在地をハイライトします
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
WITH Engagement_Time AS (
SELECT ymd, _engagement_time_seconds, FORMAT_TIMESTAMP(
'%H時間 %M分 %S秒', TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64)
) AS format_second
FROM(
SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds
FROM(
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
)
GROUP BY ymd
)
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC
)
SELECT * FROM Engagement_Time
Layer 0: 期間指定
Layer 3: 表示形式の成形
Layer 2: 集計と単位変換
Layer 1: 生データの抽出と展開
Layer 2: 集計と単位変換
Layer 3: 表示形式の成形
実行の方向

# Page. 3

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

Phase 0: 抽出対象期間の定義 (Global Variables)
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
Variable Scope
date_from() -> '20260301'
・BigQueryにおける一時関数(TEMP FUNCTION)を変数として代用するテクニック。
・クエリ内で日付をハードコードせず、先頭で一元管理することで、再利用性と保守性(DRY原則)を担保。
ミニマップ: 全スライドで現在地をハイライトします
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
WITH Engagement_Time AS (
SELECT ymd, _engagement_time_seconds, FORMAT_TIMESTAMP(
'%H時間 %M分 %S秒', TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64)
) AS format_second
FROM(
SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds
FROM(
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
)
GROUP BY ymd
)
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC
)
SELECT * FROM Engagement_Time
Data State Matrix
State: Global Memory -> date_from: '20260301', date_to: '20260331'

# Page. 4

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

Phase 1-A: 最深部のデータ抽出 (Partition Filtering & Date Parsing)
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()
Partition Filter
events_20260228
events_20260301
events_20260331
_TABLE_SUFFIX: ワイルドカードテーブルに対するパーティションプルーニング。スキャン量を最小化しコストを削減。
PARSE_DATE: 文字列 (String) のevent_dateを、後続の処理で扱いやすい日付 (Date) 型へ変換。
March 2026 Tables Only
Data State Matrix
Before: event_date (String: '20260301')
After: ymd (Date: 2026-03-01)
ミニマップ: 全スライドで現在地をハイライトします
SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd,
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to()

# Page. 5

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

Phase 1-B: 配列の展開と特定キーの抽出 (UNNEST)
(SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec
Array Flattening Diagram
event_params Array
[{key: 'page_location', ...}, {key: 'engagement_time_msec', value.int_value: 1500}, {key: 'session_id', ...}]
UNNEST
{key: 'page_location', ...}
{key: 'engagement_time_msec', value.int_value: 1500} -> _engagement_time_msec = 1500
{key: 'session_id', ...}
・GA4特有のネストされたevent_paramsレコードをUNNESTでフラット化。
・スカラサブクエリを使用し、各イベント行に対して特定のキーの値をピンポイントで抽出する処理。
Code Minimap
(SELECT value.int_value
FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec') AS _engagement_time_msec

# Page. 6

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

Phase 2-A: データクレンジングと単位変換 (Null Handling & Math)
FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))
Nested Box Model
COALESCE(..., 0) -> SAFE_DIVIDE(..., 1000) -> FLOOR(...)
欠損値(Null)を0に置換(安全な計算の担保)
ミリ秒(msec)を秒(sec)に変換。ゼロ除算エラーも防止
小数点以下を切り捨て、整数秒として丸め処理
Data State Matrix
Raw Input: Null, 1500
COALESCE: 0, 1500
SAFE_DIVIDE: 0.0, 1.5
FLOOR: 0, 1
Final Output: 0, 1
Code Minimap
SELECT ymd,
SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds
FROM Unnested_Events
GROUP BY 1;

# Page. 7

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

Phase 2-B: 日次集計 (Aggregation)
SELECT ymd, SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds
GROUP BY ymd
2026-03-01 (Event level)
5s, 12s, 0s, 0s
Aggregation Funnel
GROUP BY ymd & SUM
2026-03-01 (Daily Summary)
17s
・変換・丸め処理が完了した秒数をベースに、ymd (日) 単位で総和 (SUM) を算出。
・この段階で、データはイベント単位から日次サマリー単位へと粒度 (Granularity) が変化する。
Code Minimap
SELECT ymd,
SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000))) AS _engagement_time_seconds
GROUP BY ymd

# Page. 8

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

Phase 3-A: ヒューマンリーダブルな形式への成形 (Timestamp Formatting)
Code
FORMAT_TIMESTAMP('%H時間 %M分 %S秒',
TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64))) AS format_second
Type Conversion Pipeline
INT64 -> Timestamp -> String
CAST(.. AS INT64) (厳密な型定義) -> TIMESTAMP_SECONDS(..) (エポック秒からのタイムスタンプ化) -> FORMAT_TIMESTAMP(..) (指定フォーマット出力)
タイムスタンプ関数をハックして時間をフォーマットする、BigQueryにおける洗練されたテクニック。
Data State Matrix
Input: 3665 (seconds)
Output: '01時間 01分 05秒' (string)
Code Minimap
SELECT ymd, _engagement_time_seconds, FORMAT_TIMESTAMP(
'%H時間 %M分 %S秒',
TIMESTAMP_SECONDS(CAST(_engagement_time_seconds AS INT64))
) AS format_second

# Page. 9

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

Phase 3-B: ノイズの除去と整列 (Filtering & Sorting)
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC
Pruning & Ordering Timeline
03-01, 03-02, 03-03, 03-04
・<> 0: エンゲージメントが全く発生しなかった日 (休業日やトラフィックゼロの日) を最終結果から除外。
・ASC: 時系列順に並び替え、BIツールやレポートでの即時利用を可能に。
ORDER BY ymd ASC
Code Minimap
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC;

# Page. 10

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

Phase 4: CTEの呼び出しと最終出力 (Final Execution)
SELECT * FROM Engagement_Time
Output Data Matrix
ymd (DATE): 2026-03-01, 2026-03-03, 2026-03-04
_engagement_time_seconds (INT64): 3665, 1820, 45
format_second (STRING): '01時間 01分 05秒', '00時間 30分 20秒', '00時間 00分 45秒'
これまでに構築した Engagement_Time CTE (一時テーブル) を展開。
分析者にとって直感的に理解可能な、クリーンで構造化されたデータセットが完成。
Code Minimap
SELECT * FROM Engagement_Time

# Page. 11

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

Synthesis: エンゲージメント時間の変換ライフサイクル
Stage 1: Raw Partition (events_20260301)
Stage 2: Nested Array ([{key: 'engagement_time_msec', ...}])
Stage 3: Extracted Scalar (msec) (1500, null, 2100)
Stage 4: Cleansed & Converted (sec) (1, 0, 2)
Stage 5: Aggregated Daily Total (3665)
Stage 6: Formatted Final Output ('01時間 01分 05秒')
複雑にネストされたクエリは、単なるコードの羅列ではなく、「抽出・クレンジング・集計・成形」というデータエンジニアリングの基本パイプラインを1つのSQLステートメント内で美しくカプセル化したものです。