GA4_日次セッション集計_SQLロジックの構造的解剖

SELECT session_id FROM session_id WHERE with event_originontonn.admin JOIN user_pseudo_id GROUP BY DATE_TRUNC(".timestamp, event.") SELECT FROM session_id WHERE JOIN DATE_TRUNC(event_id) = ARRAY_AGG(COUNT(DISTINCT) ARRAY_AGG(event_timestamp, UNNEST(session_id) ).UNNEST GROUP BY session_id, event_timestamp, user_pseudo_id GA4 日次セッション集計: SQLロジックの構造的解剖 BigQueryデータパイプラインの段階的ビジュアライゼーション Data Extraction Transformation Aggregation SELECT session_id FROM event_timestamp WHERE user_pseudo_id JOIN DATE_TRUNC(COUNT(DISTINCT) session_id, event_timestamp, user_pseudo_id SELECT session_id FROM event_timestamp WHERE user_pseudo_id JOIN COUNT(DISTINCT) session_id, event_timestamp, user_pseudo_id

データ変換の4層アーキテクチャ CAATE TEMP FONCTION date_fron() RETURNS STRING AS ('20368281'); CREATE TCNV TONCTION date_to() RETURNS STRING AS ('28150328'); WTTO SS, Gally AE ( SE:217 ymd, COUNT(OISTINCT ssid) AS _SS FROW [ SELECT ymd, C06CAT(user_eseude_id, '.' CAST(ge_lesston_id AS 2791A0)) AS icid FNDH ( SELECT PARSE_DATE('%7%m%d', event_date) AS ynd, over_pvamc_16, [SELECT vclue,int_value FROM UNNEST(event_parms) WNESE 6ey = gs_lesston_16'1 AB ga_cession_id FROM sprejoti_sda(avot.evnote_7 NREAE _T48LC_30FFIX BETNEEN date_fron() AND date_tot) ) ) C8OUP BY ynd ORDER BY ynd ASC ) SELECT * FROM SS_Daily 1. 変数定義 (Initialization) date_from(), date_to() 2. 元データの抽出 (Extraction) events_*からの生データ取得 3. セッションIDの 生成 (Transformation) 複合キー (ssid) の結合 4. 日次集計 (Aggregation) 日付単位での ユニークカウント 各層がフィルタリング、正規化、集計の独立した役割を持ち、内側（深層）から外側 へとデータが精製されるプロセスを可視化する。

SQL MINIMAP 動的変数の定義と期間指定 (Phase 1) CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH daily_stats AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '_', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FROM events_* WHERE event_name = 'page_view' AND date_from() <= event_date AND event_date <= date_to() ) ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); Control Panel Start Date 20260201 End Date 20260228 Jan Feb 2026 18 16 24 Jan Feb 12 16 20 Mar 18 Mar User Defined Functions (UDFs) を用いたグローバル変数の定義。クエリ全体の期 間指定を最上部で一元管理し、保守性と再利用性を高める。

SQL MINIMAP CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH daily_stats AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FROM events_* WHERE event_name = 'page_view' AND date_from() <= event_date AND event_date <= date_to() ) ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; 深層クエリ：パーティションの最適化 (Phase 2) FROM <project>.<dataset>.events_* WHERE _TABLE_SUFFIX BETWEEN date_from() AND date_to() スキャン対象 (Partition Pruning) events_20260131 events_20260201 events_20260202 events_20260228 events_20260301 ワイルドカードテーブル (events_*) と_TABLE_SUFFIXの組み合わせにより、スキャン対象のテ ーブルを物理的に制限。BigQueryのクエリコストと処理時間を最小化する必須のアプローチ。

SQL MINIMAP CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH daily_stats AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FROM events_* (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id ) ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; ネスト解除による特定パラメータの抽出 (Phase 2.1) (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id Exploded View event_params (Array) UNNEST key: 'page_location', string_value: 'https... key: 'ga_session_id', int_value: 1706745600 key: 'session_engaged', int_value: 1 WHERE key = 'ga_session_id' GA4特有のネストされた配列データ (Repeated Record) を平坦化。条件指定により、複数 存在するパラメータ群の中からセッション識別子のみをピンポイントで抽出する。

SQL MINIMAP CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH daily_stats AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FROM events_* WHERE event_name = 'page_view' AND date_from() <= event_date AND event_date <= date_to() AND event_date BETWEEN date_from() A ) ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; 日付フォーマットの正規化 (Phase 2.2) PARSE_DATE('%Y%m%d', event_date) AS ymd STRING: event_date 20260201 PARSE_DATE('%Y%m%d') DATE: ymd 2026-02-01 文字列型のイベント日付を、タイムゾーンや後続の集計処理 (GROUP BY) に耐え うる厳密なDATE型へ変換。

SQL MINIMAP Deep Slate Noto Sans JP Medium CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH daily_stats AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FRON events_*, UNNEST(event_params) AS param WHERE param.key = 'ga_session_id' ) WHERE event_name = 'page_view' AND date_from() <= event_date AND AND event_date <= date_to() AND event_date BETWEEN date_from() AND date_to() ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; 中間層：複合セッションキーの生成 (Phase 3) Noto Sans JP Bold JetBrains Mono CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid Joining Nodes user_pseudo_id 12345.67890 ga_session_id 1706745600 CAST CONCAT & CAST ssid (Primary Key) 12345.67890-1706745600 抽出された要素を結合し、集計のための新たなユニークキーを生成。ga_session_idは 数値型であるため、文字列としての結合前に明示的な型変換 (CAST) を実行する。 Fira Code IBM Plex Sans JP Regular

SQL MINIMAP Deep Slate Noto Sans JP Medium CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH daily_stats AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FRON events_*, UNNEST(event_params) AS param WHERE param.key = 'ga_session_id' ) WHERE event_name = 'page_view' AND date_from() <= event_date AND AND event_date <= date_to() AND event_date BETWEEN date_from() AND date_to() ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; なぜ2つのIDを結合するのか？ (Concept Synthesis) Noto Sans JP Bold JetBrains Mono IDタイプ 特性 課題/解決策 `user_pseudo_id` ユーザー（デバイス） 単位で一意 課題: 複数回の訪問で同じ IDが使い回される。 `ga_session_id` セッション開始時の タイムスタンプ 課題: 異なるユーザー間で 偶然値が重複するリスクが ある。 `ssid` (結合後) グローバルで完全に一 意なセッションID 解決: ユーザーごとのタイ ムスタンプとなり、正確な ユニークカウントが可能。 GA4の仕様上、ga_session_id単体ではグローバルな一意性が担保されない。正確な セッション数を算出するためには、ユーザーIDとの複合キー化が不可欠である。 IBM Plex Sans JP Regular

SQL MINIMAP Deep Slate Noto Sans JP Medium CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH SS_Daily AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%Y%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FRON events_*, UNNEST(event_params) AS param WHERE param.key = 'ga_session_id' ) WHERE event_name = 'page_view' AND date_from() <= event_date AND AND event_date <= date_to() AND event_date BETWEEN date_from() AND date_to() ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; 最上位クエリ：日次の集計処理 (Phase 4) Noto Sans JP Bold JetBrains Mono SELECT ymd, COUNT(DISTINCT ssid) AS _ss ... GROUP BY ymd ORDER BY ymd ASC Filter and Funnel Mechanism 123.45.17067 123.45.17067 122.45.17087 123.45.17087 DISTINCT フィルタ 2026-02-01 2026-02-02 2026-02-03 2026-02-04 2026-02-05 2026-02-06 2026-02-07 CTE (共通テーブル式) 内での最終集計。生成された複合キーの重複を排除 (DISTINCT) し、日付単位でグループ化することで、純粋な日次ユニークセッション数を算出する。 IBM Plex Sans JP Regular

SQL MINIMAP CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH SS_Daily AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%YX%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FROM events_*, UNNEST(event_params) AS param WHERE param.key = 'ga_session_id' ) WHERE event_name = 'page_view' AND date_from() <= event_date AND AND event_date <= date_to() AND event_date BETWEEN date_from() AND date_to() ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM SS_Daily; 最終出力の実行 SELECT * FROM SS_Daily ymd ORDER BY ASC _ss 2026-02-01 14,502 2026-02-02 15,291 2026-02-03 14,888 ... ... 定義されたCTE (SS_Daily) を呼び出し、時系列順に整理されたクリーンな日次セッシ ョン数データを出力。ビジネスインテリジェンス (BI) ツールへ連携可能な最終形態。

SQL MINIMAP Deep Slate Noto Sans JP Medium CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260201'); CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20260228'); WITH SS_Daily AS ( SELECT ymd, COUNT(DISTINCT ssid) AS unique_users FROM ( SELECT ymd, CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid FROM ( SELECT PARSE_DATE('%YX%m%d', event_date) AS ymd, user_pseudo_id, ga_session_id FRON events_*, UNNEST(event_params) AS param WHERE param.key = 'ga_session_id' ) WHERE event_name = 'page_view' AND date_from() <= event_date AND AND event_date <= date_to() AND event_date BETWEEN date_from() AND date_to() ) GROUP BY ymd ORDER BY ymd ASC ) SELECT * FROM daily_stats; データパイプラインの全容 (Synthesis) Deep Slate Noto Sans JP Bold 深層：生データとUNNEST Noto Sans JP Medium 中間層：複合ID (ssid) 精製 Noto Sans JP Medium 最外層：DISTINCT 集計と出力 Noto Sans JP Medium 深層：生データとUNNEST Noto Sans JP Medium 最外層：DISTINCT 集計と出力 Noto Sans JP Medium 2026-02-01 2026-01-02 2026-03-01 2026-03-09 ネストされたサブクエリは、単なる構文ではなく「データのろ過装置」である。元データ の抽出から、キーの生成、最終的な集計まで、段階的な層を経ることで、膨大なGA4の ローデータが正確なビジネス指標へと変換される。 IBM Plex Sans JP Regular