BigQueryではじめるWeb解析入門 ③〜GA4 の基本的な指標を参照してみる〜
このシリーズではSQL初学者の方向けに、BigQueryでGA4のデータを使ってWeb分析を行う方法をご紹介してます。
なかなかBQMLに到達しなくて申し訳ありませんが、もう少しお付き合い頂ければと思います😆
イベント件数をみてみましょう
SELECT
COUNT(*) AS event_count, -- 件数をカウントしてevent_countという名前で表示
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
ここではCOUNT という関数が使われているのに注目ください。SQL では、このような集計関数がいくつか存在しており、COUNT を使うと件数を合計してくれます。さらに分かりやすくするため、イベントごとに件数がどれくらい発生しているか集計してみましょう。
SELECT event_name,
COUNT(event_name) as event_count --event件数をカウント
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
GROUP BY 1 -- event_name ごとに集計
ORDER BY 2 DESC -- event_count で降順ソートこの時、何の単位で集計するかを GROUP BY で指定する必要があります。GROUP BY 1 の1というのは、1番目に指定した列(つまりevent_name)ごとに集計してほしいという意味で、結果は以下のようになります。
当たり前ですが page_view が多いです。二行目のuser_engagementは、GA4で自動的に取得されるイベントの一つで、ウェブページがフォーカス状態にあった時間の長さを示します。訪問した人がどれくらい真剣にコンテンツ読んでくれているかというような指標と考えればよさそうです。次に多いscrollも自動収集されるイベントで、ユーザーが各ページの最下部までスクロールしたかを示します。Google Tag Managerなどで計測しないでも自動収集してくれるのは便利ですね。同様に、ユーザ数は以下のように件数を取得できます。
SELECT COUNT(DISTINCT user_pseudo_id) AS user_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
結果として、2546人のユーザが訪問していることが分かりました。user_pseudo_idはGA4によって自動的に生成されるユーザー（ブラウザ）識別子で、COUNT(DISTINCT..) と指定することで重複を排除したユニークなIDの数をカウントしています。device.advertising_id(ADID) という値もありますが、これはデバイス固有(ただしユーザがリセットできる)でuser_pseudo_idはアプリ固有(client id相当)のIDになります。
では、このuser_pseudo_idごとのイベント数を集計するにはどうすれば良いか考えてみてください。
正解は以下のようになります。user_pseudo_idの後にCOUNT(event_name)を指定し、GROUP BY でuser_pseudo_idごとの集計をするよう指示しています。
SELECT user_pseudo_id,
COUNT(event_name) AS event_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
GROUP BY 1
ORDER BY 2 DESC
22223267.9572233224という識別子の人が247もインタラクションがあったようです。熱心なGoogleファンの方でしょうか。次に、デバイス別でも集計してみましょう。
SELECT platform, device.category,
COUNT(device.category) AS devicecount
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
GROUP BY 1,2 -- platformとdevice.categoryごとに集計
PlatformはWEB(ブラウザ)もしくはアプリのOS、categoryはデバイスの種類を示します。当然ですが、Merchandise StoreのアプリはないのでWEBからしかアクセスがありません。また、意外とPCからアクセスしてる人が多いようです。憶測ですが、Googleグッズを買う方はPCを使っているエンジニアが多いのかもしれません。
今回は、SQLで数字の集計を行う基本的な方法をご紹介しました。もう少しさまざまなSQLをご自身で試してみたい方は開発者向けガイドを参照ください。次回はようやくBQMLで分析をする準備についてご紹介したいと思います。
