ユニバーサル アナリティクス のデータを BigQuery に入れて集計　ランディングページ、参照元、メディア

ページ別ランディング数、離脱数、直帰数、参照元、メディアを集計しました。

こちらの記事を参考にさせていただき、参照元、メディアを追加させていただいた形です。

Google Analytics 360 + BigQueryでよく使うSQL例 6選 | エクスチュア株式会社ブログ (ex-ture.com)

上記の記事にありますように、

hits.page.pagePathがページ。
hits.isEntranceが1になってるページがランディング。
hits.isExitが1になってるページが離脱。
totals.bouncesが1だと直帰。

Google Analytics 360 + BigQueryでよく使うSQL例 6選 | エクスチュア株式会社ブログ (ex-ture.com)

となっています。

集計した様子はこちらです。

SQLは下記の通りです。

WITH master AS (
  SELECT
    hits.page.pagePath AS pagePath,
    trafficSource.medium AS utm_medium,
    trafficSource.source AS utm_source,
    CASE WHEN isEntrance THEN 1 ELSE 0
    END AS is_ent,
    CASE WHEN isExit THEN 1 ELSE 0
    END AS is_exit,
    CASE WHEN totals.bounces = 1 THEN 1 ELSE 0
    END AS is_bounce
    FROM
     /* ↓この行はデータがある場所に書き替えていただく必要があります, */ 
     `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
      UNNEST(hits) AS hits
      
  WHERE
     /* 【コストにご留意ください】↓長い期間にすると、費用が大きくなりますのでご注意ください */ 
  _TABLE_SUFFIX between  '20170701' AND '20170731'
)
SELECT
  master.pagePath AS LandingPagePath,
  master.utm_medium,
  master.utm_source,
  SUM(master.is_ent) AS entrance,
  SUM(master.is_exit) AS exit,
  SUM(master.is_bounce) AS bounce

  
FROM master
GROUP BY 1, 2, 3
ORDER BY 4 DESC