ノーコードでクラウド上のデータとの連携を実現。
詳細はこちら →
Google Analytics 4 の生データを Amazon Redshift に継続的レプリケーション
Google Analytics 4 (GA4) は生データをBigQuery にシームレスにエクスポート可能なため、Webサイトの閲覧情報と実際の購買情報など、BigQuery で管理している様々なデータと組み合わせて分析できる点が魅力です。
しかし、Amazon Redshift などBigQuery 以外からは通常GA4 の生データにアクセスできず、Google Analytics API や CSV、サードパーティ製品のコネクタ経由で取得可能な集計データのみの利用となってしまうため、精度の高い柔軟な分析が難しい点が悩みの種です。
今回は上記の課題を解決するため、CData が提供しているパイプラインツール「CData Sync」を使用して BigQuery に蓄積されたGA4 の生データを Redshift に自動で継続的にレプリケーションするフローを構築してみたいと思います。
レプリケーションまでの流れ
CData Sync を使ったレプリケーションの手順は以下です。
- テスト環境の準備
- Google Cloud にてBigQuery の設定
- GA4 をBigQuery のプロジェクトに連携させる
- CData Sync にRedshift とBigQuery への接続設定を追加
- CData Sync にジョブを追加
- CData Sync にレプリケーションタスクの追加
- CData Sync でレプリケーションジョブのスケジュール実行
なお今回の構成ではRedshift はすでに構築済みで、CData Sync はRedshift にアクセス可能なVPC 内のWindows Server 上にインストールする前提です。
Redshift 環境がない場合は以下のチュートリアルを参考に構築してみてください。
手を動かしなら学ぶ Analytics サービス入門
それではさっそく始めてみましょう。
1. テスト環境の準備
テスト環境の準備として、Web ページの作成やGA4 の初期設定を行います。
すでに環境がある場合はこのセクションは飛ばしてください。
CData Sync 環境の準備
CData Sync は30日間の無償トライアルが可能ですので、CData Sync をインストールするWindows サーバー上にインストーラをダウンロードしてexe ファイルを実行すればすぐに利用が開始できます。
https://www.cdata.com/jp/sync/
Amazon Linux などWindows 以外のプラットフォームをお使いの場合はクロスプラットフォーム用インストーラー (.tar) をダウンロードしていただければOK です。
Amazon S3 にてテスト用Webページの設置
GA4 でWeb ページのアクセス情報を取得するためにテストページを作成します。
今回は「Amazon S3 を使用して静的ウェブサイトをホスティングする」を参考にテスト用のホスティング環境を作成しました。
S3 上にホスティング環境ができたらファイル名を index.html としたシンプルな HTML ファイルを作成してS3 バケットにアップロードします。
HTML ファイルが保存できたら、バケットの「プロパティ」タブ下部にある「静的ウェブサイトホスティング」のエリアに表示されているURL にブラウザでアクセスしてWeb ページが表示可能か確認します。
GA4 によるデータ収集の設定
作成したテストページの閲覧情報をGoogle Analytics 4 で取得するため、以下のドキュメントを参考に設定します。
[GA4] アナリティクスで新しいウェブサイトまたはアプリのセットアップを行う
テストページ用のHTMLファイルにGoogle タグを設定したら、S3 バケットに上書き保存します。
Google タグを追加したHTML ファイルをS3 バケットに保存できたら再度ブラウザでWebページにアクセスし、 Google アナリティクスのダッシュボードでテストページへのアクセス状況が表示されるかを確認しましょう。
2. Google Cloud にてBigQuery の設定
続けてGA4 データのエクスポート先となるBigQuery 環境を追加します。
GA4 と同様のGoogle アカウントでGoogle Cloud の管理画面にログインし、以下公式ガイドを参考にして Google Cloud プロジェクトを 新しく追加後、BigQuery API を有効化します。
ステップ 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. CData Sunc に 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」に登録しています。
<api:set attr="out.env:yesterday" value="[x|now|dateadd(day, -1, yyyyMMdd)]" />
<api:push item="out" />
6-2. CData Sync にレプリケーションタスクの追加
Pre-Job イベントで設定した環境変数を使って動的にテーブル名を取得する処理を追加するため、「ジョブ」にある「タスク」タブを開いて「タスクを追加」をクリックし、カスタムクエリを有効にします。
カスタムクエリの登録画面が開いたら以下のクエリを指定します。
REPLICATE [events_pre-job] SELECT * FROM [analytics_300500035.events_{env:yesterday}]
- 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 で可視化もできました。
ぜひみなさんの環境でもお試しください！