GA4×BigQuery完璧なSQL JOINの解剖学_正確なセッション集計を保証する「1対1」のデータ・ブループリント

GA4 × BigQuery: 完璧なSQL JOINの解剖学 正確なセッション集計を保証する「1対1」のデータ・ブループリント FROM INNER JOIN WHERE HAVING DISTINCT ON COUNT(*) GROUP BY SUM(sessions) SELECT FROM WHERE HAVING ON DISTINCT DISTINCT SUM(sessions)

なぜ単純な COUNT(*) で正確な 「セッション数」が導けるのか？ 一見すると、単に2つのテーブルを結合して行を数えて いるだけに見えます。SQLにおいて JOIN は行数の膨 張（重複）リスクを伴う処理です。しかし、このクエリ が完璧に機能する裏には、「1対1の関係」という数学的 な保証が存在します。 Joined_Data AS ( 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 (slp) Session_Engagement (se) 一般的なJOINが抱える行膨張のリスク

2つの異なる性質を持つデータの結合 Session_Landing_Page (slp) 「起点の特定」(Point) 「そのセッションの入り口 (LP) はどこ か？」という単一の地点データ。 Session_Engagement (se) 「全体の合算」(Line) 「そのセッション中に発生した全ての閲覧時 間の合計は？」という連続的な実績データ。 「LPが特定できているセッション」に対し、「そのセッションで稼いだ合計時間」を横に 並べることで、「そのLPから始まったからこそ得られた時間」という評価軸が完成します。

左テーブルの条件：「1セッション＝1行」の保証 Session_Landing_Page (slp) がユニークなリストである理由。 page_view scroll click page_view page_view entrances = 1 1行 = 1つのセッションID 1 (条件) event_name = 'page_view' かつ entrances = 1 2 (GA4の仕様) 1つのセッションにおいて「入り口」 となるページビューは、原則として 1回しか存在しません。 3 (結果) この時点で、テーブルは「1行 = 1つ のセッションID」という重複のない状 態になります。

右テーブルの条件：イベントの凝縮による「1行化」 Session_Engagement (se) の事前処理の重要性。 12s 45s 0s 120s 8s 115s GROUP BY 300s 1セッションの合計秒数 1 (処理) 事前に GROUP BY user_pseudo_id, ga_session_id を実行。 2 (効果) 膨大なイベントデータがセッション 単位でギュッと凝縮されます。 3 (結果) 「1つのセッションIDにつき、合計秒 数が書かれた1行」へと変換され、左 テーブルと結合する準備が整います。

なぜ結合キーが2つ必要なのか？ (Compound Key) Left Table Right Table AND + user_pseudo_id ブラウザ/ユーザー を識別 ga_session_id 訪問ごとの番号 ! ga_session_id 単体では、全ユーザー間で 使い回される（被る）可能性がある数値です。 2つをANDでつなぐことで、初めて 「〇〇さんの、△回目の訪問」という世界 に一つだけのセッションを特定できます。

1対1の「ガッチャンコ」(水平結合) [1] 結合前 (Before) 左側:1行 入口: /home 右側:1行 実績: 計300秒 [2] INNER JOIN (During) 入口: /home ON (ID一致) 実績: 計300秒 [3] 結合後 (After) /home | 計300秒 結果:1行のまま 「セッションA」は、左側に1行、右側にも1行。結合しても結果は「1行」のまま。行が増減す ることなく、「左側のLP情報」と「右側の滞在時間」が1つの横長い行に統合されるだけです。

意図的なINNER JOIN：分析精度の向上 エンジニア的な補足：なぜLEFT JOINではないのか？ INNER JOIN Filter A B entrances=1が存在しない 特殊なセッション C 滞在時間が計測され なかったセッション A B C 高精度な分析データ A Noise Rejection (ノイズ除去) 双方にデータが存在するセッションのみを残すことでノイズを除去し、「そのページ から始まった『ユーザー体験の総量』」を正確に計測する理にかなった構造です。

レポート形式への集約 (GROUP BY landing_page) /home /home /home /about GROUP BY landing_page Translation Matrix COUNT(*) セッション数 (_ss) 結合されたセッションの 行数をそのまま数える。 (例: 3行=3セッション) SUM(se.sess on_seconds) 合計エン ゲージメント 時間 各セッションが持っていた 全体の滞在時間をLPごと にすべて足し合わせる。

診断：もしこれが「1対多」の結合だったら？ 【正】1対1の結合 (Perfect Blueprint) /home 300s 結果:1行 COUNT(*) = 1 (セッション数として正確) 【誤】1対多の結合 (The Flawed Assembly) /home /about /contact 300s /home /about /contact 結果: 3行(行が膨張) COUNT(*) = 3 (セッション数ではなくPV数に近い無意味な数値) entrances = 1 の指定を忘れると、行数が膨れ上がり集計が破綻します。 この「1対1の紐付け」感覚が、計算の狂いを防ぐ要です。

The Perfect SQL Join: 総括 左 (LP): 1セッション1行(入り口のみ) 右 (時間): 1セッション1行(合計時間のみ) 結合 (JOIN): 1セッション1行を維持 (横に並べる) 集計 (COUNT): その行数を数える 完璧な精度での「セッション数」の算出 このクエリは「1対1」の整合性が完璧に保たれているため、単なる「そのページにいた時間」ではなく、 「そのページから始まった『ユーザー体験の総量』」を正確に計測できています。 この構造を理解すれば、CVRなど他の指標を結合する際にも応用が可能です。