Snowflakeの新機能 google analytics コネクタを試してみた！
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までお問合せください。
このページをシェアする：
