こんにちは、エクスチュアの權泳東(権泳東/コン・ヨンドン)です。
Google Analytics 4 (以下GA4)がリリースされましたね。
というわけでGA4のBigQueryエクスポート機能を使って早速データを抽出してみましょう!
以前、GA360+BigQueryで私がよく使う基本的なSQL例を紹介しましたが、今回はそれのGA4バージョンです。
1. ページビュー数
GA4の日別ページビューを出してみます。
タイムゾーンを任意で変更したいので、event_dateカラムではなくevent_timestampを「Asia/Tokyo」にして使ってます。
それがメンドクサイならevent_dateでもOK。
event_name = ‘page_view’ に絞るとページビューになる。
※アプリのスクリーンビューは’screen_view’を使う
1 2 3 4 5 6 7 8 9 10 11 | SELECT DATE (timestamp_micros(event_timestamp), 'Asia/Tokyo' ) AS hitdate, COUNT (1) AS pageviews FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' AND '20201031' AND event_name = 'page_view' GROUP BY 1 ORDER BY 1 |
結果はこうなります。
なお、ページURL別にページビューを出すならこうです。
1 2 3 4 5 6 7 8 9 10 | SELECT ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' ) AS page_location, COUNT (1) AS pageviews FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' AND event_name = 'page_view' GROUP BY 1 ORDER BY 2 DESC |
ページURLは、event_params.key が「page_location」になっている行のstring_value列に入ってます。
event_paramsはネストされたRECORD型のカラムになってるので、UNNEST関数でフラットにしてあげる必要があります。
以前書いた、「GoogleAnalytics Apps+Web プロパティをBigQueryと連携したついでにフラット化する」の記事を参考。
結果ははこうなります。
2. セッション数
user_pseudo_id と event_params.key=ga_session_idのint_valueを文字列にしたものをCONCATして、ユニーク数をカウントしたのがセッション数。
これも日別で出します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | WITH t1 AS ( SELECT DATE (TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo' ) AS hitdate, CONCAT(user_pseudo_id, CAST (( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id' ) as STRING)) AS sid, FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' AND '20201031' AND event_name = 'session_start' ) SELECT hitdate, COUNT ( DISTINCT sid) AS sessions FROM t1 GROUP BY 1 ORDER BY 1 |
結果はこうなります。
なお、Webとアプリ両方でUserIDを使ってる場合は user_pseudo_id の代わりに user_id を使えばクロスデバイスで同じユーザーとして扱えます。
3. ユーザー数
user_pseudo_id のユニーク数をカウントするとユーザー数。
下記はhitdateでGROUP BYしてるので、日別のユーザー数を出してます。
1 2 3 4 5 6 7 8 9 10 11 | SELECT DATE (TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo' ) AS hitdate, COUNT ( DISTINCT user_pseudo_id) AS users FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' AND '20201031' AND event_name = 'session_start' GROUP BY 1 ORDER BY 1 |
結果はこうなります。
4. ページ別ランディング数・離脱数・直帰数
先程出したセッションIDを使います。
これをevent_timestampを昇順ソートして、一番目のページがランディングページ。
逆にevent_timestampを降順ソートしたら、一番目のページが離脱ページ。
そしてセッション中に見たページの数が「1」なら直帰。
event_name = page_viewで絞ってます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | WITH t1 AS ( SELECT event_timestamp, ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' ) AS page_location, CONCAT(user_pseudo_id, CAST (( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id' ) as STRING)) AS sid FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' AND event_name = 'page_view' ) ,t2 AS ( SELECT page_location, CASE WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY event_timestamp) = 1 THEN 1 ELSE 0 END AS is_entry, CASE WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY event_timestamp DESC ) = 1 THEN 1 ELSE 0 END AS is_exit, CASE WHEN COUNT (1) OVER(PARTITION BY sid) = 1 THEN 1 ELSE 0 END AS is_single FROM t1 ) SELECT t2.page_location AS page_location, SUM (t2.is_entry) AS entry, SUM (t2.is_exit) AS exit, SUM (t2.is_single) AS singlevisit FROM t2 GROUP BY 1 ORDER BY 2 DESC |
結果はこうなります。
ただし、この書き方だと集計期間を跨いでる訪問の場合には集計期間中の最初に見たページがランディングページになるけどキニシナイ
5. ページ別合計滞在時間・平均滞在時間
これもセッションIDを使う。
ページ別に次ページとのタイムスタンプの差分を合算した秒数をページ別トータル滞在時間として抽出。
次ページとのタイムスタンプ差が1800秒以上あるものはセッションが切れてるものとして除外する。
滞在時間の合計を、ページのセッション数で割れば平均滞在時間になる。
event_name = page_viewに絞ってないので、スクロールイベントや離脱クリックなどのエンゲージイベントも滞在時間に含まれます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | WITH t1 AS ( SELECT timestamp_micros(event_timestamp) AS event_timestamp, CONCAT(user_pseudo_id, CAST (( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id' ) as STRING)) AS sid, ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' ) AS page_location FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' ) ,t2 AS ( SELECT event_timestamp, sid, page_location, CASE WHEN timestamp_diff(LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp), event_timestamp, second ) > 1800 THEN NULL ELSE LEAD(event_timestamp) OVER(PARTITION BY sid ORDER BY event_timestamp) END AS next_hit FROM t1 ) SELECT t2.page_location, COUNT ( DISTINCT t2.sid) AS sessions, SUM (IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second ), 0)) AS total_time, ROUND(SAFE_DIVIDE( SUM (IFNULL(TIMESTAMP_DIFF(t2.next_hit, t2.event_timestamp, second ), 0)), COUNT ( DISTINCT t2.sid)), 2) AS avg_time FROM t2 WHERE t2.page_location IS NOT NULL GROUP BY 1 ORDER BY 2 DESC |
結果はこうなります。
滞在時間の単位は「秒」です。
6. イベントパラメータの集計
サイト内検索ワードをイベントパラメータで計測してるという前提です。
view_search_resultsイベントが発生した時に、検索ワードがevent_params.key = search_term の string_value列に入るので、それを集計します。
1 2 3 4 5 6 7 8 9 10 | SELECT ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'search_term' ) AS int_search_term, COUNT (1) AS int_search FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' AND event_name = 'view_search_results' GROUP BY 1 ORDER BY 2 DESC |
結果はこうなります。
event_name と event_paramsのキー名はイベント内容に合わせて書き換えてください。
7. ユーザープロパティの集計
ユーザープロパティは event_params と同じく縦持ちのレコードなのでUNNEST関数でフラットにする必要があります。
ユーザープロパティの値別にユーザー数を出してみます。
1 2 3 4 5 6 7 8 9 10 | SELECT ( SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'weather' ) AS weather, COUNT ( distinct user_pseudo_id) AS users FROM `analytics_12345678.events_*` WHERE _TABLE_SUFFIX BETWEEN '20201001' and '20201031' AND event_name = 'user_engagement' GROUP BY 1 ORDER BY 2 DESC |
結果はこうなります。
event_name と user_propertiesのキー名はユーザープロパティをセットするイベント内容に合わせて書き換えてください。
まとめ
SQLでWeb解析ログを分析すると、GA4で集めたデータを自由にドリルダウン出来るのがメリットです。
GA4には標準でBigQueryエクスポート機能が備わっています。
みなさんもこの機会にぜひBigQueryを使った自由で柔軟なWeb解析を始めましょう。
弊社ではGoogleAnalytics/AdobeAnalyticsなどの各Martechツールの導入実装コンサルティングサービスや、GCP/AWSなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。
お問い合わせはこちらからどうぞ。