DATUM STUDIOの稲岡です。

本記事では「GAコネクタの実装方法」と「データの取得方法」について説明します。
また重要な点として取得できるデータは生のログデータではなく、集計済みのデータになります。

注意事項
1. 本検証はPrivate Previewの機能を対象としたものです。
2. 将来的に機能や仕様の変更の可能性があります。

実装方法

1.必要なアカウント・環境

・google analytics4 (GA4)の環境（旧バージョンであるUA (universal analytcis)ではsnowflake連携はできません。）
・GA4が利用可能になっているgoogleアカウント
・Snowflake環境とAccountadminロールをもったSnowflakeユーザー
・Client ID, Client Secret, 認証キー取得用URL（Snowflake社のサポートに連絡して、取得できます）

またgoogleのユーザーにGA4の管理者権限は不要であり、設定のために新たにGCP環境を用意する必要はありません。

2.コネクタ用ウェアハウス, DB, 統合の作成

まずGAコネクタが利用するウェアハウスを作成します。

use role accountadmin;

create or replace warehouse google_analytics_connection_warehouse 
    with warehouse_size = 'xsmall' warehouse_type = 'standard'   
    auto_suspend = 60 
    auto_resume = true 
    min_cluster_count = 1 max_cluster_count = 1 
    scaling_policy = 'economy'
    initially_suspended = true;

次にデータベース(my_connector_google_analytics)を作成します。こちらのデータベースがGAコネクタとしての役割を果たします。

create or replace database my_connector_google_analytics
    from share snowflake.connector_google_analytics_v4;

最後にセキュリティ統合を作成します。Snowflake社より取得するclient ID, Client Secretが必要になります。
後述するsecretではoauth refresh tokenを保存しますが、こちらではそれ以外の認証情報を保存します。

create or replace security integration integration_ga
    type = api_authentication
    auth_type = oauth2
    oauth_client_id = 'snowflake社より取得した値を入力'
    oauth_client_secret = 'snowflake社より取得した値を入力'
    oauth_token_endpoint = 'https://oauth2.googleapis.com/token'
    enabled = true;

3.データ保存先DB, スキーマ, ロールを作成

取得したデータを保存するDB(ga_connector_db), スキーマを作成します。

use role accountadmin;
create or replace database ga_connector_db;
create or replace schema ga_connector_db.ga_connector_schema;

次にデータの所有者となるロール(google_analytics_resources_provider)を作成し、タスクの実行権限やDB、スキーマ、ウェアハウスの実行権限を付与します。

create or replace role google_analytics_resources_provider;
grant execute task on account to role google_analytics_resources_provider;



use role securityadmin;
grant usage on warehouse google_analytics_connection_warehouse to role google_analytics_resources_provider;
grant usage on database ga_connector_db to role google_analytics_resources_provider;
grant usage on schema ga_connector_db.ga_connector_schema to role google_analytics_resources_provider;
grant create table on schema ga_connector_db.ga_connector_schema to role google_analytics_resources_provider;
grant create view on schema ga_connector_db.ga_connector_schema to role google_analytics_resources_provider;

最後に、作成したロールを最初に作成したDBへ付与します。(DBにロールを付与するというのはsnowflakeで新たに登場した概念になります。)
また管理するユーザーにも付与します。

use role accountadmin;
grant role google_analytics_resources_provider to database my_connector_google_analytics;
grant role google_analytics_resources_provider to user "name@datumstudio.jp";

4.Secretオブジェクトの作成

secret オブジェクト(認証情報)を保存するためのDB(ga_connector_secret_db)、スキーマを作成します。
ここではoauthのリフレッシュトークンを保存します。

use role accountadmin;
create or replace database ga_connector_secret_db;
create or replace schema ga_connector_secret_db.api_auth;

またsecretオブジェクトを管理するロール(secretadmin)を作成し、DB、スキーマ、統合の使用権限を付与します。

use role accountadmin;
create or replace role secretadmin;

use role securityadmin;
grant usage on database ga_connector_secret_db to role secretadmin;
grant usage on schema ga_connector_secret_db.api_auth to role secretadmin;
grant create secret on schema ga_connector_secret_db.api_auth to role secretadmin;
grant usage on integration integration_ga to role secretadmin;

grant role secretadmin to user "name@datumstudio.jp";

リフレッシュトークンを生成します。はじめに、Snowflake社より取得したURLからgoogleのページに行きアクセスコードを取得し、ローカルから次のコードを実行します。

curl \
  -d code=<上で取得したアクセスコード> \
  -d client_id= 'snowflake社より取得した値を入力' \
  -d client_secret= 'snowflake社より取得した値を入力' \
  -d redirect_uri=urn:ietf:wg:oauth:2.0:oob \
  -d grant_type=authorization_code \
  https://accounts.google.com/o/oauth2/token

api_authスキーマにsecret (google_oauth_token)を作成します。
oauth_refresh_token_expiry_timeは作成日から7日以内が推奨されています。

use role secretadmin;
use schema ga_connector_secret_db.api_auth;

use role accountadmin;
create or replace secret google_oauth_token
  type = oauth2
  oauth_refresh_token = 'curlで取得したトークン'
  oauth_refresh_token_expiry_time = '2022-08-30 08:00:00' 
  api_authentication = integration_ga;

コネクタを利用するロールにsecretの使用権限を付与します。

use role securityadmin;
grant usage on database ga_connector_secret_db to role google_analytics_resources_provider;
grant usage on schema ga_connector_secret_db.api_auth to role google_analytics_resources_provider;
grant usage on secret ga_connector_secret_db.api_auth.google_oauth_token to role accountadmin;

最後にGAにアクセスするAPI統合を作成し、統合の利用権限をコネクタのroleに付与します。

use role accountadmin;

create or replace api integration google_analytics_api_integration
  api_allowed_prefixes = (
    'https://analyticsdata.googleapis.com',
    'https://analyticsadmin.googleapis.com'
  )
  default_authentication_secret = ga_connector_secret_db.api_auth.google_oauth_token
  enabled = true;
  
use role securityadmin;
grant usage on integration google_analytics_api_integration to role google_analytics_resources_provider;

5.Snowflake Google Analytics Connectorの設定

次のコマンドでプロビジョニングを行い、DB(my_connector_google_analytics)にコネクタをインストールします。

use role accountadmin;
use database my_connector_google_analytics;

call provision_connector(
  'google_analytics_connection_warehouse', 
  'ga_connector_db.ga_connector_schema', 
  'google_analytics_resources_provider', 
  'google_analytics_api_integration'
);

6.実装まとめ

ここまで複数のDBやロール、統合が登場し混乱したかと思います。それぞれの関係を図示して整理しました。図からスキーマは省いています。

データの取得方法

1.プロパティIDの取得

登録したGAアカウントのプロパティID(9桁の数字)を取得します。GA4のUIからも確認できますが、こちらから取得する方が容易です。

use role accountadmin;
use database my_connector_google_analytics;

call get_properties();

2.データ取得可能なディメンジョン・メトリクスを表示

各プロパティIDで使用できるディメンジョン、メトリクスを表示します。

call get_dimensions('propertyID'); 
call get_metrics('propertyID');

カテゴリ名、ディスクリプション(説明文)、ディメンジョン名/メトリクス名が表示されます。
なおメトリクス、ディメンジョンの説明はこちらからも確認が可能です。

https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema

3.レポートの取得

最後にレポートの取得を行います。

use role accountadmin;
use database my_connector_google_analytics;

call configure_report(
  'TEST_REPORT',   --レポート名(任意)
  プロパティ名, --プロパティ名(固定)
  'date,city,deviceCategory', --ディメンジョン
  'active1DayUsers,activeUsers', --メトリクス
  '2022-08-01', --集計開始日
  'every 12 hour' --インターバル
);

一般的には次のコマンドで取得します。

call configure_report(
  '<report_name>',
  '<property_id>',
  '<dimensions>',
  '<metrics>',
  '<start_date>',
  '<refresh_interval>'
);

ここでは
・report_name: 任意のレポート名
・property_id: 上記の値
・metrics: 上記で表示した、ディメンジョンから最大9個’,’区切りで指定する。日付単位でデータを取得する使用のため、「date」は自動で指定される。
・metrics: 上記で表示した、メトリクスから最大10個’,’区切りで指定する。
・start_date: 集計開始日
・refresh_interval: 集計間隔
となっています。

以下は得られたデータの例です。

use role google_analytics_resources_provider;
use schema ga_connector_db.ga_connector_schema;

select * from TEST_REPORT;

データ保存用DBである、GA_CONNECTOR_DBのGA_CONNECTOR_SCHEMAのビューを確認すると見やすく整形されたデータが確認できます。テーブルを確認すると、Rawカラムが非構造化データになっており見にくいためビューを確認するとよいでしょう。

4.取得履歴・削除

データの取得(更新)履歴についてはこちらから確認可能です。

use role accountadmin;
use database my_connector_google_analytics;

select
   *
from "MY_CONNECTOR_GOOGLE_ANALYTICS"."PUBLIC"."CONNECTOR_EXECUTION_LOG";

レポートの定期取得を止める場合は

use role accountadmin;
use database my_connector_google_analytics;

call delete_report(‘TEST_REPORT’);

を実行します。

まとめ

このように他のETLツールを使うことなく、GAのデータをsnowflake上に取得することができました。詳細なログデータではありませんが、日付や他のディメンジョンごとに値が集計されたレポートが取得可能であり、社内で運用しているtableauやlookerにて、すでに運用しているダッシュボードに加えGAのデータも可視化が可能になり、統合的な分析環境を実現することができるでしょう！

Snowflakeについてお困りごとがあればDATUM STUDIOまでお問合せください。