本記事は、CData Software Advent Calendar 2019 4日目の記事です。

はじめに

多くの方が使っている「Google Analytics」のデータを CData Sync を使って BigQuery へ連携させる方法の紹介です。

目次

Google Analytics 無料プランでの BigQuery へのエクスポート
CData Sync でBigQueryにレプリケート
- CData Sync のインストールから接続先の作成
  - Google Analytics コネクタの追加
  - 接続設定
- Google BigQuery への接続設定
  - 接続設定
- ジョブ作成
  - GoogleAnalytics から取得できるデータ
  - Webサイトのアクセス情報をBigQueryにレプリケート
  - クエリをセット
  - オプションの設定
- ジョブの実行
- オプションの設定

Google Analytics 無料プランでの BigQuery へのエクスポート

Google Analytics には2種類のプランがあります。
１つは、大多数の方々が使用している無料のスタンダードプラン。実はこのスタンダードプラン以外に大企業向けのプランである「アナリティクス360」というものが存在します。

公式サイトにはスタンダードプランと360プランの比較表があります。さまざま違いがありますが、その中でも「Google BigQuery との連携」がスタンダードプランでは出来ないというのは残念なところです。

CData Sync でBigQueryにレプリケート

CData Sync とは、ノーコードで様々なデータソースのデータをDBにレプリケートできるアプリケーションです。構成は以下のように至極シンプルなものとなってます。

CData Sync はローカルでもクラウド上のVMでも起動することができますが、今回はローカルマシンで起動させているものを使っていきます。

CData Sync のインストールから接続先の作成

以下の記事にCData Sync のインストール、基本的な接続設定の手順が載ってますので、以下の記事も参照しながらインストール後、GoogleAnalytics と BigQuery の接続設定を行ってください。
CData Sync ハンズオン 2019

CData Sync はデフォルトでは Google Analytics へのコネクタが含まれていないので、コネクタの追加が必要となります。

Google Analytics コネクタの追加

「接続」タブ → 「データソース」タブ → 「＋Add More」の順でクリックしてください。

モーダルウィンドウが表示されますので、検索窓に”Google”と入力してください。入力後、Google Analytics のアイコンが表示されますのでクリックします。

「ダウンロード＆インストール」ボタンをクリックすることで自動的に Google Analytics へのコネクタが追加されます。

OKボタンをクリックして CData Sync の再起動を行います。

接続設定

基本的には以下のように接続名の入力だけでOKです。入力後、「接続」ボタンをクリックし、接続確認ができたら保存して完了です。

Google BigQuery への接続設定

接続設定

BigQuery についてはデフォルトで同期先一覧に組み込まれています。万が一なかった場合は、GoogleAnalytics と同様の手順でコネクタの追加をしてください。

任意の接続名とBigQuery で事前に定義している「Dataset Id」と「Project Id」を入力し、「接続」ボタンをクリックします。CData Sync から BigQuery への接続許可を行ったあと、GoogleAnalytics のように接続情報を保存で接続設定が完了です。

ジョブ作成

「ジョブ」→「ジョブを追加」の順でクリックします。

ソースに「GoogleAnalytics」、同期先に「BigQuery」を選択します。

これでデータソースに「GoogleAnalytics」、同期先に「BigQuery」を設定したジョブのベース部分がセットされましたので、これよりどんなデータをBigQueryに同期するかを設定していきます。

GoogleAnalytics から取得できるデータ

GoogleAnalytics で接続設定した際のアカウントに関連する情報はテーブルから、AdSense データや、サイトコンテンツデータなどはビューで取得することができます。

1回のクエリで指標は10個まで、ディメンションは7個まで使用できます。そのため、すべての列を選択するクエリを発行すると、指標が10を超えるテーブルでは、デフォルトの指標列だけが選択されます。他のディメンション列を明示的に指定しない場合は、デフォルトのディメンションが使用されます。（デフォルトのディメンションはヘルプにて確認できます）

詳細は、以下リンクのテーブルとビューの内容をご参照ください。
CData Sync - Google Analytics ヘルプ

Webサイトのアクセス情報をBigQueryにレプリケート

WebサイトのPV数や滞在時間などは SiteContent テーブルから取得することができますので、今回はこのテーブルにあるデータをBigQuery に連携します。ちなみに、ディメンションを明示的に指定しない場合でも全項目が連携されますが、以下の赤枠項目以外はnullがセットされます。

というわけで、不要な項目は連携する必要がないので、赤枠部分を連携対象とした以下のクエリを使用します。

SELECT PagePath, PageViews, UniquePageviews, AvgTimeOnPage, Entrances, BounceRate, EntranceRate, Exits, PageValue, TimeOnPage 
FROM SiteContent

ただし、SELECT文だけだと CData Sync はデータのレプリケートを行うことはできませんので、頭に REPLICATE [レプリケート先テーブル名] を付与した以下のクエリをジョブにセットします。

REPLICATE [SiteContent1204] SELECT PagePath, PageViews, UniquePageviews,AvgTimeOnPage, Entrances, BounceRate, EntranceRate, Exits, PageValue, TimeOnPage FROM SiteContent

クエリをセット

「カスタムクエリを追加」ボタンを押下し、モーダルウィンドウにクエリをセットして保存ボタンをクリックすることで反映されます。

オプションの設定

ジョブごとに各種オプションを設定することができますが、今回はジョブ実行のたびに BigQuery 側のテーブルを削除するようにします。当画面は「Advanced」タブを選択することで表示されます。「テーブルを削除」にチェックを入れ保存します。

以上でジョブ作成が完了しました。

ジョブの実行

チェックボックスにチェックを入れ、「実行」ボタンをクリックするとジョブが実行されます。

実行が完了すると「最後の更新」、「ステータス」に実行結果が表示されます。

BigQuery を確認

BigQuery のコンソールを開いてみると、確かに”Test_Sync”というデータセット配下にテーブルが作成されているのが確認できました。

レコード数も3867件と、CData Sync のステータスに表示されていた件数と一致しています。

これで GoogleAnalytics から BigQuery へWebサイトの情報をノーコードで連携することができました。