- TOP
- google big query
- GoogleBigQueryを活用したGA4データ分析,SQL活用術初級編
GoogleBigQueryを活用したGA4データ分析,SQL活用術初級編
- 2021/12/17
目的
GA4からBigQueryに保存されたAnalyticsデータを利用しSQLで分析をする方法を説明をします。
BigQueryを利用する事で、必要に応じたデータを自由に取得することができます。
GA4の標準レポートや探索レポート(カスタムレポート)では出ないレポートが必要な際に役立ちます。
課題
日々のアクセスデータをBigQueryに保存し続けていても、
どのように活用していくのかが難しいと思います。
Google Analyticsの画面から参照できるデータと同じような結果を
BigQueryから取得する方法を説明します。
※ 逆にいうと、GA4の管理画面、探索機能やレポートで
再現できないデータ分析がBigQueryでは可能となります。
理由としては、最小単位でのローデータをBigQueryで持ってるためです。
詳細説明
基本 : Google Analyticsからデータを参照する
GoogleAnalyticsを利用して下記のようなデータを画面がら簡単に参照することができます。
Google Analyticsの画面
Big Queryからデータを参照する
Big Queryを利用して下記のようなデータを画面から簡単に参照することができます。
Big Queryから取得したデータ
データを取得するためのSQL
SELECT
geo.country,
geo.region,
geo.city,
count(*) AS access_count
FROM
`just-genius-216220.analytics_264011635.events_202107*`
GROUP BY
geo.country, geo.region, geo.city
応用
SQLによってデータの抽出・集計をする事ができるので
数種類のデータ抽出のアイディアを説明します。
都道府県単位のアクセス数を多い順番で取得する
上述のSQLを下記のように修正します。
- geo.cityを削除して、区市町村を取得対象外にします。
- ORDER BYを追加して、並び替え順序を降順に指定します。
ORDER BYの役割
並べ替え対象にしたいカラムに下記の引数を指定して利用します。
- ASC : 昇順(初期値、無指定の場合は昇順が採用される)
- DESC : 降順
データを取得するためのSQL
SELECT
geo.country,
geo.region,
count(*) AS access_count
FROM
`just-genius-216220.analytics_264011635.events_202107*`
GROUP BY
geo.country, geo.region
ORDER BY
access_count DESC
Big Queryから取得したデータ
このように都道府県毎のアクセス数を一覧化して降順で並び替る事ができます。
流入経路毎のアクセス数を取得する場合
サイトの流入経路毎のアクセス数を取得する場合は少し工夫が必要になります。
流入経路は下記のようにネストされた項目に保存されいてるので、
ネストを分解してデータを取得する必要があります。
データを取得するためのSQL
SELECT
param.value.string_value AS page_referrer,
count(*) AS access_count
FROM
`just-genius-216220.analytics_264011635.events_202107*`,
UNNEST(event_params) as param
WHERE
param.key = "page_referrer"
GROUP BY
param.value.string_value
ORDER BY
access_count DESC
ネストされた項目の値を取得する際にはUNNEST関数を利用して
ネストされている項目名を指定することによって分解された結果を取得する事ができます。
下記のようにネストされたカラムのトップにあたるevent_paramをUNNEST関数に指定します。
UNNEST(event_params) as param
これによりparamという名称でデータを参照することが可能になります。
Big Queryから取得したデータ
日単位のページビューの一覧化
特定月のデータを抽出するだけではなく、開始終了日を指定したデータ抽出をする事ができます。
日単位のページビューを一覧化するSQLを参考にすると下記のようになります。
データを取得するためのSQL
SELECT
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS target_date,
COUNT(1) AS page_views
FROM
`just-genius-216220.analytics_264011635.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20210715' AND '20210815' AND
event_name = 'page_view'
GROUP BY 1
ORDER BY 1
ポイント
_TABLE_SUFFIXを利用して対象テーブル指定することができます。
FROM句のテーブル名末尾の日付部分を*(アスタリスク)にすることによって、
WHERE句でBETWEEN句を利用して抽出することが可能です。
GROUP BY, ORDER BY による集計、並び替えカラムは明示的にカラム名を指定する以外に、SELECT句の表示順を指定することも可能です。
上記の例では1行目に指定したtarget_dateをキーに指定しています。
Big Queryから取得したデータ
まとめ
今回説明したようなシンプルなSQLによってBig Queryからデータを抽出することができます。
応用的な使い方として用途に合わせた関数を利用して
下記のような抽出をするSQLを説明していく予定です。
- 抽出した値をもとに計算する
- 条件によって値を分岐させる
- 複数のSQL結果を組み合わせた結果を抽出する
- テーブルを集約した結果を抽出する
基本的なSQLの使い方を覚えながら、
用途に応じたSQLを組み立てていき必要に応じて関数を利用するような流れで
少しずつSQLに触れる機会を設けていくことが
要求に基づいた効果的なデータ解析を実現していくための道筋になると思います。
最新記事
-
2024/01/18
-
無料で使えるBI(可視化)ツールのご紹介 2022/11/25
-
GTMでpageQery変数の頭に?マークをつけるカスタムjavascript 2022/05/24
-
GCPエンジニアが語るBigQueryを使うメリット 2021/12/18
-
GA4のデータをBigQueryから参照する方法 2021/12/18
- abテスト…(0)
- adwords…(0)
- cookie…(0)
- cv…(0)
- cvr…(0)
- ecサイト…(0)
- ec情報…(0)
- eコマース…(0)
- facebook…(0)
- firebug…(0)
- ga_social_tracking-js…(0)
- google…(0)
- google-adsense…(0)
- google-optimize…(0)
- googleanalytics…(0)
- googleanalyticsトラッキングコード…(0)
- googleアカウント…(0)
- googleグーグルatmosphere…(0)
- gtm…(0)
- html…(0)
- ltv…(0)
- notprpvited…(0)
- omnibug…(0)
- quillengage…(0)
- seo…(0)
- sko…(0)
- social…(0)
- utm…(0)
- utma…(0)
- yahoo…(0)
- ydn…(0)
- ytm…(0)
- アクセス解析…(0)
- アクセス解析業界…(0)
- アドセンス…(0)
- アドバンスセグメント…(0)
- アドワーズ…(0)
- アナリティクス分析…(0)
- いいね計測…(0)
- イベントトラッキング…(0)
- ウェブマスターツール…(0)
- オプティマイズリー…(0)
- カイゼンプラットフォーム…(0)
- カグア…(0)
- カグア吉田…(0)
- カスタムフィルタ…(0)
- カスタムレポート…(0)
- カスタム変数…(0)
- キャンペーン…(0)
- グーグル…(0)
- クライアントデータ…(0)
- クラウド…(0)
- クリックカウント…(0)
- クリックテール…(0)
- クロスデバイス…(0)
- クロスドメイン…(0)
- コンサルタント…(0)
- コンテンツマーケティング…(0)
- コンテンツ分析…(1)
- コンテンツ改善…(1)
- コンバージョン…(0)
- サードパーティタグ…(0)
- サブドメイン…(0)
- サンクスページ…(0)
- スクロール率取得…(1)
- スマートフォン…(0)
- セッション…(0)
- セミナー…(0)
- ソーシャル…(0)
- ターゲティング…(0)
- タグマネージャー…(0)
- ツリーマップ…(0)
- データバインディングエクスプレッション…(0)
- ディスプレイネットワーク…(0)
- デフォルトタグ…(0)
- ドメイン跨ぎ…(0)
- トラッキング…(0)
- トラッキングコード…(0)
- トラフィック…(0)
- トランザクション…(0)
- トリガー…(0)
- パラメータ…(0)
- ファーストビュー…(0)
- ページの価値…(0)
- マーケティング…(0)
- マルチチャネル…(0)
- メトリクス…(0)
- ヤフー…(0)
- ヤフータグマネージャー…(0)
- ヤフーリスティング…(0)
- ユーザ属性…(0)
- ユーザ行動…(0)
- ユニークイベント…(0)
- ユニバーサルアナリティクス…(0)
- ユニバーサルタグ…(0)
- ライフタイムバリュー…(0)
- リアルタイムレポート…(0)
- リスティング広告…(0)
- リスト…(0)
- リファラ…(0)
- リマーケ…(0)
- リンクタグ…(0)
- 共有…(0)
- 別ドメイン…(0)
- 同期…(0)
- 実店舗…(0)
- 広告…(0)
- 有償ツール…(0)
- 無料ツール…(0)
- 直帰率…(1)
- 間接効果…(0)
- 集客レポート…(0)
- 高機能…(0)