Google Analytics 4の生データをAmazon Redshiftに継続的レプリケーション
本記事はCDataが提供する「CData Software Blog」に掲載された「Google Analytics 4 の生データを Amazon Redshift に継続的レプリケーション」を再編集したものです。
Google Analytics 4(GA4)は生データをBigQueryにシームレスにエクスポート可能なため、ウェブサイトの閲覧情報と実際の購買情報など、BigQueryで管理している様々なデータと組み合わせて分析できる点が魅力です。
しかし、Amazon RedshiftなどBigQuery以外からは通常GA4の生データにアクセスできず、Google Analytics APIやCSV、サードパーティー製品のコネクタ経由で取得可能な集計データのみの利用となってしまうため、精度の高い柔軟な分析が難しい点が悩みの種です。
今回は上記の課題を解決するため、CDataが提供しているパイプラインツール「CData Sync」を使用してBigQueryに蓄積されたGA4の生データをRedshiftに自動で継続的にレプリケーションするフローを構築してみたいと思います。
レプリケーションまでの流れ
CData Syncを使ったレプリケーションの手順は以下です。
1. テスト環境の準備
2. Google Cloud にてBigQueryの設定
3. GA4をBigQueryのプロジェクトに連携させる
4. CData SyncにRedshiftとBigQueryへの接続設定を追加
5. CData Syncにジョブを追加
6. CData Syncにレプリケーションタスクの追加
7. CData Syncでレプリケーションジョブのスケジュール実行
なお今回の構成ではRedshiftはすでに構築済みで、CData SyncはRedshiftにアクセス可能なVPC内のWindows Server上にインストールする前提です。
Redshift環境がない場合は以下のチュートリアルを参考に構築してみてください。
それではさっそく始めてみましょう。
1. テスト環境の準備
テスト環境の準備として、ウェブページの作成やGA4の初期設定を行います。
すでに環境がある場合はこのセクションは飛ばしてください。
CData Sync環境の準備
CData Syncは30日間の無償トライアルが可能ですので、CData SyncをインストールするWindowsサーバー上にインストーラをダウンロードしてexeファイルを実行すればすぐに利用が開始できます。
https://www.cdata.com/jp/sync/
Amazon LinuxなどWindows以外のプラットフォームをお使いの場合はクロスプラットフォーム用インストーラー(.tar)をダウンロードしていただければOKです。
Amazon S3にてテスト用ウェブページの設置
GA4でウェブページのアクセス情報を取得するためにテストページを作成します。
今回は「Amazon S3を使用して静的ウェブサイトをホスティングする」を参考にテスト用のホスティング環境を作成しました。
S3上にホスティング環境ができたらファイル名をindex.htmlとしたシンプルなHTMLファイルを作成してS3バケットにアップロードします。
HTMLファイルが保存できたら、バケットの「プロパティ」タブ下部にある「静的ウェブサイトホスティング」のエリアに表示されているURLにブラウザーでアクセスしてウェブページが表示可能か確認します。
GA4によるデータ収集の設定
作成したテストページの閲覧情報をGoogle Analytics 4で取得するため、以下のドキュメントを参考に設定します。
[GA4] アナリティクスで新しいウェブサイトまたはアプリのセットアップを行う
テストページ用のHTMLファイルにGoogleタグを設定したら、S3バケットに上書き保存します。
Googleタグを追加したHTMLファイルをS3バケットに保存できたら再度ブラウザでウェブページにアクセスし、Googleアナリティクスのダッシュボードでテストページへのアクセス状況が表示されるかを確認しましょう。
2. Google CloudにてBigQueryの設定
続けてGA4データのエクスポート先となるBigQuery環境を追加します。
GA4と同様のGoogle アカウントでGoogle Cloudの管理画面にログインし、以下公式ガイドを参考にしてGoogle Cloudプロジェクトを新しく追加後、BigQueryAPIを有効化します。
ステップ 1: Google API Consoleプロジェクトを作成し、BigQueryを有効にする
3. GA4をBigQueryのプロジェクトに連携させる
以下のドキュメントを参考に、GA4側でBigQueryとのリンクを設定します。
ステップ 3: Googleアナリティクス4プロパティをBigQueryにリンクする
設定ができたらGA4にて 「管理 > サービス間のリンク設定 > BigQueryのリンク > リンクしたBigQueryのプロジェクト >データ ストリームとイベントの設定」を開き、エクスポートするデータ ストリーム の項目にテストページに設定したデータストリームが選択されているか確認しておきましょう。
3-1. GA4からBigQueryへエクスポートされたかを確認
GA4からBigQueryへのエクスポートタイプが「毎日」になっている場合はデータのエクスポートに1日程度かかります。
作成したBigQueryのプロジェクトにanalytics_xxxxxxのようなデータセットが作成され、その配下に日次のテーブルがevents_YYYYMMDDの形式で作成されていれば成功です。
参考までに、今回の検証では翌日の午前6時ごろにテーブルが作成されていました。
4. CData SyncにRedshiftとBigQueryへの接続設定を追加
いよいよCData Syncを使ってレプリケーションの設定を行なっていきます。
まずは CData SyncにRedshiftとBigQueryの接続設定をそれぞれ追加します。
4-1. CData SyncにRedshiftとの接続を追加
4-1-1. CData Syncのダッシュボードの「接続」メニューにて「接続を追加」をクリック
4-1-2. 「コネクタを選択」画面でRedshiftを選択
4-1-3. 「新しい接続」接続画面でRedshiftへの接続情報を登録して「保存およびテスト」をクリックし、CData Syncから Redshiftへの接続を確認
参照:CData Sync- Amazon Redshift
4-2. CDataSuncにBigQueryとの接続を追加
Redshiftの接続設定と同様にCData SyncからBigQueryへの接続を追加します。
Redshiftとの接続設定と異なる点は、「Auth Scheme」に「OAuth」指定をしている点です。
参照:CData Sync- Google BigQuery
5. CData Syncにジョブを設定
CData Sync管理画面左側の「ジョブ」メニューから「ジョブ」に移動後、右上の「ジョブを追加」をクリックしてソース(BigQuery)への接続情報と同期先(Redshift)への接続情報を登録します。
6. CData Syncにレプリケーションタスクの追加
一般的なタスク設定では、「ジョブ」にある「タスク」タブを開いて「タスクを追加」をクリックし、データソースとなるテーブル名を選択しますが、BigQueryにエクスポートされるGA4のデータは、「events_YYYYMMDD」のテーブル名で1日ごとに保存されるため、固定のテーブル名での指定ができません。
こういった場合、CData Syncでは以下の手順のようにレプリケーション時にBigQuery側のテーブル名を動的に生成して指定する処理を追加して対応します。
6-1. ジョブにPre-Jobイベントを設定
「Events」タブの「Pre-Job」にて変数にジョブ実行時の月日を取得してセットする処理を追加します。
<!-- Code goes here -->の直後にジョブ実行日の前日の日付を「yyyyMMdd」のフォーマットで取得し、環境変数「yesterday」に登録しています。
6-2. CData Syncにレプリケーションタスクの追加
Pre-Jobイベントで設定した環境変数を使って動的にテーブル名を取得する処理を追加するため、「ジョブ」にある「タスク」タブを開いて「タスクを追加」をクリックし、カスタムクエリを有効にします。
カスタムクエリの登録画面が開いたら以下のクエリを指定します。
events_pre-job : Redshift側に自動で作成する任意のテーブル名
env:yesterday : Pre-Job イベントで設定したジョブ実行日の前日の日付
analytics_300500035 : BigQueryのデータセット名
events_{env:yesterday} : 日次で作成されるテーブル名
カスタムクエリで利用できる日付フォーマッタの詳細については公式ドキュメントをご参照ください。
なお、同期先のテーブルを月毎に作成したり、年月カラムの追加も可能ですのでこちらも必要に応じてご活用ください。
6-3. CData Syncでレプリケーションタスクの動作確認
ソースデータへの接続、同期先への接続、およびデータフローとなるジョブとタスクが作成されましたので、実際にタスクを実行してレプリケーションを行なってみます。
手動でタスクを実行したらジョブの履歴で、レプリケーションが実行されたかを確認します。
RedshiftのクエリエディタV2でもテーブル名 「events_pre-job」で新規にテーブルが追加され、データが保存されたことが確認できます。
補足として、今回はBigQuery側に日次で個別に作成されるテーブルからレプリケーションを行うため、差分同期の設定は不要でしたが、固定のデータソースから定期的に差分のみ同期を行いたい場合は以下のように差分レプリケーションの対応も可能です。
7. CData Syncでジョブのスケジュール実行
意図したレプリケーションの実施が確認できたら、ジョブの「概要」タブ内にある「スケジュール」の項目で自動実行するスケジュールを設定します。
以上でBigQueryに蓄積されたGA4の生データをRedshiftに自動で継続的にレプリケーションするフローが完成しました。
最後にAmazon QuickSightでRedshiftにレプリケーションされたデータを可視化してみましょう。
簡単なサンプルですが、QuickSightで可視化もできました。
ぜひみなさんの環境でもお試しください!