ウェブ解析

GA4、ユニバーサル アナリティクス

BigQuery Google Analytics SQL ウェブ解析 ユニバーサル アナリティクス

ユニバーサル アナリティクス のデータを 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)

となっています。

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

image

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

-BigQuery, Google Analytics, SQL, ウェブ解析, ユニバーサル アナリティクス

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

GA4 ⇒ BigQuery のリンクの削除方法

GA4 ⇒ BigQuery のリンクの削除方法

ユニバーサル アナリティクス を新規設定する

※以下のコンテンツは、私が講師を務めさせていただいた、研修、セミナー参加者の方に読んでいただくことを想定して書かせていただいております。もちろん、それ以外の方にとっても役立つ内容を目指しております。 …

GA デモアカウント ( 1 Master View) トランザクション数 < コンバージョン数=Purchase Completed(目標 1 の完了数 ) となるケースについて

GAデモアカウント( 1 Master View) で、トランザクションが発生していないのに、 /ordercompleted.html に訪問したセッションが時々出現します。 この場合、こうなる可能 …

GA4( Google アナリティクス 4)クロスドメイン トラッキング

まだまだ進化の途上にある GA4 ですが、今回は、クロスドメイン トラッキングを設定してみたいと思います。

GA4( Google アナリティクス 4)で BigQueryを使おう!

ユニバーサル アナリティクスの時は、GA360でしか使えなかったBigQueryですが、GA4からは、360でなくても使えるようになりました。