エンゲージメント時間の抽出_段階的SQL構造解析

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

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秒」という形式の文字列に変換し、最終的 な結果として出力します。

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月) をハードコ
ード。これにより、クエリ全体で日付
条件を統一し、保守性を向上。

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

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で定義した日付を利用してスキャン範囲を制限 (パーティションプルーニング)。
クエリコストと実行時間を最小化。

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

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行に圧縮される。

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日の 総秒数を合算。

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秒

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)

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秒 ✓

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秒

データ変換プロセスの総括: 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秒
結論: ネストされたクエリは単なる複雑化ではなく、データの「抽出・クレンジング・
フォーマット」という役割を明確に分離した堅牢なシステムアーキテクチャである。