GA4のBigQueryエクスポートの仕様とデータの扱い方を説明する。GA4のログはネストされたレコードなど特殊な形式になっており、そのままでは扱いにくいのでいくつかテクニックがある。 BigQueryへのエクスポート設定 GA4の管理画面で設定するが、手順はこちらの記事を参照。 テーブルの場所 プロジェクト：GA4のBigQueryエクスポート設定で指定したプロジェクト データセット「analytics_999999999」（「999999999」の部分はプロパティID） テーブル名 前日までのデータ（日付別に）「events_20210101」 「20210101」の部分は日付 GA4のB…
2種類のBigQueryエクスポート GA4のBigQueryエクスポートデータには以下の2種類がある。 イベントデータ ユーザーデータ ユーザーデータは1行1人で、user_pseudo_id単位で集約したデータと、user_id単位で集約したデータのそれぞれのテーブルが生成される。 所属するオーディエンスの情報やユーザ（user_pseudo_id / user_id）単位の通算指標が含まれる。 分析によく使うのがイベントデータで、1行1イベントでイベントパラメータなどがネストされて含まれている。 エクスポートの頻度 イベントデータの出力頻度は以下の3種類がある。 ストリーミング＝リアルタ…
GA4（Firebase）のログを扱う際、ネストされているイベントパラメータやユーザープロパティをフラット化しないと使いにくい。ところが格納されているパラメータやプロパティは決まっているわけではないため、通常はそれをハードコーディングで指定することが多い。つまり使用しているパラメータやプロパティに応じてその都度クエリを手動作成することになる。 しかしそれでは面倒なので、どんなイベントパラメータやユーザープロパティを使っていても、それがどんな型であっても、オールマイティにフラット化するクエリを作る。存在するパラメータやプロパティに基づいて動的にクエリを作って実行する。このクエリひとつあればどんな…
使うデータセット Firebaseのパブリックデータが以下にあるのでこれを使う。 firebase-public-project.analytics_153293282.events_* 0612～1003の114日分のデータ 基本集計（EDA） イベントの数の種類別カウント select event_name, count(0) from `firebase-public-project.analytics_153293282.events_20181003` group by 1 order by 2 desc; スクリーンビューのスクリーン別カウント select (select ep….
GA4の計測の検証はBigQueryエクスポートを使って行うのが普通になる。検証の方法にはいくつかあるのだが、ほかの方法だと欠点がある。 リアルタイムレポートではイベントが発生し、パラメータに値が入ってきているのはわかるが、どのイベントでどのパラメータの値が入っているかまではわからない。DebugViewは有効化しないと使えない。 BigQueryエクスポートを使うとすべてのサイト訪問に対して計測して数分以内には各パケット（イベント）でどのパラメータにどの値が入っているかがわかるし、詳細な検証ができる。これはユニバーサルアナリティクスにはなかったメリットである（GA360を除く）。 GA4のB…
Googleアナリティクス4プロパティが登場し、誰でもBigQueryにログを出力できるようになった。ログ分析を始める環境は揃ったわけだが、ログ分析のノウハウはあまり世に出ていない。SQLを使ってこれらを分析する方法を少し紹介する。どんな高度なログ分析をするにしても、これが基本となる。 ウェブ分析の指標 ウェブ分析の基本は ページビュー数 セッション数 人数 のカウントである。複雑な分析も、結局カウントしているのはこの3つの指標に集約されることが多い。Eコマースになると購入金額の合計なども入ってくることはある。 そしてこれに「○○した」という条件が付いて イベント○○が発生した回数 ○○したペ…
Googleアナリティクス4ではサイト訪問の行動ログをBigQueryに出力できるようになった。 従来のGoogleアナリティクス（ユニバーサルアナリティクス）ではGoogleアナリティクス360（GA360）を利用し、そのうえでGoogleに申請しないとログデータをBigQueryに出力することはできなかった。そのためコスト面での高いハードルがあったのだが、Googleアナリティクス4ではGoogle Cloud Platform（GCP）の従量課金コストだけでログをBigQueryに出力できるようになったのである。以前はこの設定で直接Firebaseの管理画面からダミーアプリを作る手順が必…
Googleアナリティクスで収集しているデータ（メジャメントプロトコル）をBigQueryに送り、集計・可視化できるようにする。無料版のGAにも対応し、しかもサーバレスでシンプルに実装できる。 本来BigQueryを使ったウェブ行動の詳細分析はGA360を使うべきなのだが、限りなく低コストでこんなことができるという参考程度で紹介する。 通常無料版のGoogleアナリティクスではできないログベースの行動分析をするのに使ったり、有料版であったとしてもGAはどのようなトラフィックを除外して集計しているのか、Googlebotなどのクローラがどんな動きをしているのかなどを検証するのに使うといいかもしれ…
アクセスログデータの前処理 これまでの手順で取り込んだログデータはそのままでは分析に使いにくい。 今後の分析がやりやすいように、ある程度の前処理が必要になる。 ここでは前処理のポイントになるものを列挙する。 分析対象とするリクエスト行の抽出／削除 取り込んだままのログデータに不要な情報が含まれることがある場合、必要に応じてそれらを削除する。アクセスログファイルからのデータ抽出時にフィルタリングしていない場合、ここで削除する。 ボットの抽出／削除 画像の削除 ユニークユーザとセッションの作成 疑似ユニークユーザ セッション 集計単位URLの指定 URLの分割 ダミーパラメータの除外 URLごとの…
セッションやユーザの識別にはSQLiteでは対応していないウィンドウ関数が必要になるし、データのサイズが大きくなるとSQLiteでは不安なところもある。 ということで、データサイズがある程度大きくなっても対応可能で、オープンソースでは珍しくウィンドウ関数など集計・分析機能に優れたPostgreSQLへアクセスログを取り込む方法を解説する。 アクセスログをPostgreSQLに取り込む NCSA拡張形式のログを取り込む 環境の準備（linux） SQLiteと違って環境の準備が必要になる。 PostgreSQLのインストール、initdbが完了している前提で ユーザを作成 $ su postgr…