Help us understand the problem. What is going on with this article?

CData SyncでスタンダードプランのGoogleAnalyticsからBigQueryへデータ連携

本記事は、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種類のプランがあります。
1つは、大多数の方々が使用している無料のスタンダードプラン。実はこのスタンダードプラン以外に大企業向けのプランである「アナリティクス360」というものが存在します。

image.png

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

image.png

CData Sync でBigQueryにレプリケート

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

image.png

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

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

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

CData Sync ハンズオン 2019

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

Google Analytics コネクタの追加

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

image.png

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

image.png

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

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

image.png

接続設定

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

image.png

Google BigQuery への接続設定

接続設定

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

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

image.png

ジョブ作成

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

image.png

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

image.png

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

image.png

GoogleAnalytics から取得できるデータ

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

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

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

CData Sync - Google Analytics ヘルプ

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

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

image.png

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

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  

クエリをセット

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

image.png

オプションの設定

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

image.png

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

ジョブの実行

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

image.png

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

image.png

BigQuery を確認

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

image.png

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

image.png

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away