---
title: エンゲージメント時間の抽出_段階的SQL構造解析
tags: 
author: [渋谷TAIZI](https://docswell.com/user/4514287294)
site: [Docswell](https://www.docswell.com/)
thumbnail: https://bcdn.docswell.com/page/LE3WWDK4E5.jpg?width=480
description: エンゲージメント時間の抽出_段階的SQL構造解析 by 渋谷TAIZI
published: May 07, 26
canonical: https://docswell.com/s/4514287294/ZL3JE6-2026-05-07-114804
---
# Page. 1

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

[SYSTEM.DOCUMENTATION_MODE: TRUE]
エンゲージメント時間の
抽出：段階的SQL構造解析
生データの抽出から時間フォーマットまで
の階層的クエリ設計と変換プロセス
TARGET_QUERY: ENGAGEMENT_TIME_FORMAT.SQL

# Page. 2

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

Full Architecture Query
CREATE TEMP FUNCTION date_from() RETURNS DATE AS (DATE_SUB(CURRENT_DATE(),
INTERVAL 30 DAY));
CREATE TEMP FUNCTION date_to() RETURNS DATE AS (CURRENT_DATE());
WITH Engagement_Time AS (
SELECT
ymd,
SUM(FLOOR(engagement_time_msec / 1000)) AS daily_engagement_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
`ga4_project.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', date_from()) AND
FORMAT_DATE('%Y%m%d', date_to())
AND event_name = 'user_engagement'
) AS extraction_core
GROUP BY
ymd
)
SELECT
ymd,
CONCAT(
CAST(FLOOR(daily_engagement_seconds / 3600) AS STRING), '時間 ',
CAST(FLOOR(MOD(daily_engagement_seconds, 3600) / 60) AS STRING), '分 ',
CAST(MOD(daily_engagement_seconds, 60) AS STRING), '秒'
) AS formatted_engagement_time
FROM
Engagement_Time
ORDER BY
ymd DESC;
※ 本ドキュメントでは、データ処理の実際の流れに沿って「内側(最深部)から外側へ」段階的に解説します。
クエリの全体アーキテクチャと4つの処理階層
Block 0 (Preparation): UDF (ユーザー定義関数) による期間指定。
このブロックは、データ抽出期間を決定するためのカスタム関数(UDF)
を定義します。ここでは直近30日間の開始日 (date_from) と終了日
(date_to) を動的に計算し、後続のクエリで再利用可能な形で提供します。
Block 1 (Layer 1: Extraction Core): 最深部のFROM句。GA4生データ
の取得と配列の展開 (抽出)。
クエリの最深部にあるこの層は、GA4の生データ (events_*) から対象期
間のデータを抽出し、`event_params` 配列をUNNEST して
`engagement_time_msec` (ミリ秒単位のエンゲージメント時間) を取
得します。また、`event_date` を日付型に変換します。
Block 2 (Layer 2: Aggregation Engine): 中間層。ミリ秒から秒への安
全な変換と日次集計。
抽出されたデータを基に、ミリ秒単位のエンゲージメント時間を秒単位に
変換 (FLOOR関数を使用) し、日付ごと (ymd) に合計して日次の総エン
ゲージメント秒数 (daily_engagement_seconds) を集計します。
Block 3 (Layer 3: Formatting Wrapper): 最外層のCTE。秒数を「XX
時間 XX分 XX秒」の文字列へ変換。
クエリの最外層では、集計された日次の総エンゲージメント秒数を、人間
が読みやすい「XX時間 XX分 XX秒」という形式の文字列に変換し、最終的
な結果として出力します。

# Page. 3

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

ARCHITECTURE CONTEXT
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
SELECT
SELECT (
.query,
date_bench9_couneter,
date_from_rotines,
roloate_time, SQL
FROM
SELECT * *RBIMDI{ date_from()
GROUP.axg(date_to(, date_to) == NULL
)
EVRECT <couv>
)
FIXED PERIOD: MARCH 2026
2026-03-01
(date_from)
2026-03-31
(date_to)
Block 0: UDFによる抽出対象期間の固定
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260301');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260331');
SELECT * *TCMRE finull date_from(
date_from_patch (date_thow!)
GROUP.gav(date_to(1, date_to) => NULL
クエリ実行時のみ有効な一時関を定義。
クエリ実行時のみ有効な一時関数を
定義。
対象期間 (2026年3月) をハードコ
ード。これにより、クエリ全体で日付
条件を統一し、保守性を向上。

# Page. 4

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

ARCHITECTURE SCHEMA REFERENCE
SELECT PARSE_DATE('%Y%m%d',
event_date) AS ymd,
event_params
WHERE key =
UNNEST(event_params)
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()
Layer 1: Extraction Core (最内層による生データの抽出)
役割: GA4のワイルドカードテーブルにアクセスし、複雑なネスト構造から目的のエンゲ
ージメント時間 (ミリ秒) のみをフラットな形式で抽出する。
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()
Raw Complex
JSON Data
[event_date
event_params
object
...]
[event_date
event_params
object
...]
[event_date
event_params
object
...]
FILTER LOGIC
(UNNEST & WHERE)
Data Funnel
ymd
20260301
20260301
20260301
20260304
_engagement_time_msec
45000
12300
12300
12300

# Page. 5

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

ARCHITECTURE SCHEMA REFERENCE
SELECT PARSE_DATE('%Y%m%d',
event_date) AS ymd,
event_params
WHERE key =
UNNEST(event_params)
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()
PARSE_DATE('%Y%m%d',
event_date) AS ymd
文字列
(String):
'20260301'
PARSE_DATE
DATE型:
2026-03-01
GA4の文字列形式の日付を、BigQueryの標準的なDATE型オブジェクトへ変換。
FROM <project>.<dataset>.events_*
WHERE _TABLE_SUFFIX BETWEEN
date_from() AND date_to()
events_20260228
events_20260301
events_20260301
events_20260331
events_20260401
X
✓
✓
X
UDFで定義した日付を利用してスキャン範囲を制限 (パーティションプルーニング)。
クエリコストと実行時間を最小化。

# Page. 6

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

ARCHITECTURE SCHEMA REFERENCE
SELECT PARSE_DATE('%Y%m%d',
event_date) AS ymd,
event_params
WHERE key =
UNNEST(event_params)
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()
Layer 1: UNNESTによるネストされた配列データの展開
(SELECT value.int_value
FROM UNNEST(event_params)
WHERE key = 'engagement_time_msec')
AS _engagement_time_msec
・GA4の仕様上、イベントパラメータは配列 (Array) として格納されている。
・相関サブクエリとUNNEST関数を組み合わせることで、特定のキーを持つ
レコードの整数値をピンポイントで抽出。
Array Unnesting Diagram
Event Row
event_params
(Array)
...
...
UNNEST()
Row A: key = 'page_location'
Row B: key = 'engagement_time_msec'
value.int_value = 5432
Row C: key = 'session_id'
WHERE key = ...
_engagement_time_msec
5432

# Page. 7

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

ARCHITECTURE SCHEMA REFERENCE
Layer_Output,
_engagement_time,
_engagement_time_msec,
_engagement_time_msec
SELECT ymd,
SUM(FLOOR(SAFE_DIVIDE(COALESCE(
_engagement_time_msec, 0), 1000)))
AS _engagement_time_seconds
FROM (
[Layer 1 Output])
GROUP BY ymd
_ymd,
_cont_id,
_account_date,
_ascount_namec,
_engagement_time_msec,
_engagement_time_seconds,
_catnin_time,
_data_number,
_assess_date
Layer 2: Aggregation Engine (中間層によるデータクレンジングと日次集計)
役割: 抽出されたイベント単位のミリ秒データを、エラー処理を施しながら秒単位へ変換
し、日付 (ymd) ごとに合計値を算出する。
SELECT ymd,
SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000)))
AS _engagement_time_seconds
FROM(
[Layer 1 Output]
)
GROUP BY ymd
Data Assembly Line
Raw Milliseconds
(Event Level)
GROUP BY ymd +
Transformations
1 Day Total
(Seconds)
何千ものイベント行が、クリーンな日次集計データの1行に圧縮される。

# Page. 8

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

ARCHITECTURE SCHEMA REFERENCE
Layer 2: Aggregation Engine
Layer 1: Aggregation Engine
SELECT ymd,
SUM(FLOOR(SAFE_DIVIDE(
COALESCE(_engagement_time_msec,
0), 1000)))
AS _engagement_time_seconds
FROM (
[Layer 1 Output]
) GROUP BY ymd
数学的処理と安全保護のための関数の階層 (内側から外側へ)
SUM(FLOOR(SAFE_DIVIDE(COALESCE(_engagement_time_msec, 0), 1000)))
COALESCE(..., 0)
Gate 1
NULL防御:
値がNULLの場合は
0に置換し、計算
エラーを防止。
SAFE_DIVIDE(..., 1000)
Gate 2
単位変換: ミリ
秒を1000で割り、
秒に変換 (ゼロ除
算エラーも回避)。
FLOOR(...)
Gate 3
丸め処理:
小数点以下を切り
捨て、完全な整数
(秒) にする。
SUM(...)
Gate 4
日次集計:
GROUP BY ymd
に基づき、1日の
総秒数を合算。

# Page. 9

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

ARCHITECTURE SCHEMA REFERENCE
Layer 2: Aggregation Engine
WITH Engagement_Time AS (...)
Layer 3: Formatting Wrapper (最外層での文字列成形)
役割: 集計された日次合計秒数を「時間・分・秒」に分割し、視認性の高いレポ
ート用の文字列フォーマットに変換する。同時に不要なデータを除外。
WITH Engagement_Time AS (
SELECT ymd, _engagement_time_seconds, CONCAT(...) AS format_second
FROM(
[Layer 2 Output]
)
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC
)
Raw Integer
(Seconds)
3665
Formatted String
(Report Ready)
01時間 01分 05秒

# Page. 10

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

ARCHITECTURE SCHEMA REFERENCE
Layer 3: Formatting Wrapper
WITH Engagement_Time AS (...)
時間算出の演算ロジック: 除算とMOD (剰余) の組み合わせ
Total Seconds (Example: 3665秒)
3665 / 3600
1時間
FLOOR(_engagement_time_seconds / 3600)
Remainder Pool: 65秒
65 / 60
1分
FLOOR(MOD(_engagement_time_seconds, 3600) / 60)
Remainder Pool: 5秒
5秒
MOD(_engagement_time_seconds, 60)

# Page. 11

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

ARCHITECTURE SCHEMA REFERENCE
Layer 3: Formatting Wrapper
WITH Engagement_Time AS (...)
視認性を高めるゼロパディングと文字列結合
CONCAT(
FORMAT('%02d時間 ',
FORMAT('%02d分 ',
FORMAT('%02d秒',
CAST(... AS INT64)),
CAST(... AS INT64)),
CAST(... AS INT64))
)
・FORMAT('%02d', ...): 1
桁の数字を「05」のように2桁で
ゼロ埋め (ゼロパディング)。
これにより、データの桁数が揃い、
BIツールやレポートでの視認
性が劇的に向上。
・CAST(... AS INT64): FORMAT
関数が要求する厳密な型定義を
満たすための整数型へのキャスト。
・CONCAT: 成形された3つの文
字列部品を最終的に1つのカラム
として結合。
01時間 "
01分 "
05秒
01時間 01分 05秒
Bad vs Good
Non-padded: 1時間 1分 5秒 X
Padded: 01時間 01分 05秒 ✓

# Page. 12

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

ARCHITECTURE SCHEMA REFERENCE
Layer 3: Formatting Wrapper
WITH Engagement_Time AS (...)
最終出力: クリーンアップと結果の取得
WHERE _engagement_time_seconds <> 0
ORDER BY ymd ASC
)
SELECT *
FROM Engagement_Time
・WHERE _engagement_time_seconds <> 0:
エンゲージメントが発生しなか
った日 (0秒) をレポートから
除外。
・ORDER BY ymd ASC: 日付の
昇順でソートし、時系列分析
に最適な状態に整える。
ymd
2026-03-01
2026-03-02
2026-03-03
_engagement_time_seconds
3665
4120
1845
format_second
01時間 01分 05秒
01時間 08分 40秒
00時間 30分 45秒

# Page. 13

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

データ変換プロセスの総括: Data Transformation Matrix
Layer 1 (抽出: Raw)
event_date (String),
event_params (Nested
Array)
複雑なJSON構造、数百万
行のレコード、扱いにくい
ミリ秒。
{key: '...',
value: {int_value: 5432}}
Layer 2 (集計: Aggregated)
ymd (Date),
_engagement_time_seconds
(INT64)
日次で1行に集約、秒単位
への変換完了、NULL排除
済み。
2026-03-01 | 3665
Layer 3 (成形: Formatted)
ymd (Date),
...,
format_second (String)
人間が直感的に理解できる
レポート対応の文字列、ゼロ
データ除外済み。
2026-03-01 | 01時間 01分 05秒
結論: ネストされたクエリは単なる複雑化ではなく、データの「抽出・クレンジング・
フォーマット」という役割を明確に分離した堅牢なシステムアーキテクチャである。