アーキテクチャ
(だいぶシンプルにしています。CSVはGCSにある想定です。)
データはCSVデータ（顧客の購買情報をイメージしてください）、Google Analytics、Google 広告のデータがあることを想定しています。
データの流れとしてはBigQuery Data Transfer Service
を活用して一つのDWHにまとめます。
まとめた後はスケジュールクエリでデータマートを作成します。
データマートを作成した後はLooker Studioから接続して可視化します。
課題
企業で蓄積されたデータの多くは、各所に分散して保存されており利用しづらい状況がありました。
また、データを利用するにあたりシステム部に連絡をしてアドホック的にクエリを実施し、データを取得してもらうなどデータ利用者がすぐに利用しづらい状況を仮定します。
目的
分散されたデータを1つのDWHにまとめることで、データ利用者が取り扱いやすい環境を構築します。
BigQuery Data Transfer Serviceについて
BigQuery Data Transfer Service
BigQuery Data Transfer Serviceは、あらかじめ設定されたスケジュールに基づき、BigQueryへの転送処理を自動化することが可能になります。
Google Analytics 4 カスタムディメンション設定
BigQueryでデータを活用しやすくするために事前にカスタムディメンション（ユーザープロパティ）の設定を完了しておきます。
※他のデータと紐づくユニークなユーザーIDが望ましいです。
設定>プロパティ>カスタム定義>右上カスタムディメンションを作成
カスタムディメンションの送信方法についてはこちらを参照してください
Googleタグマネージャー、あるいはgtag.jsでWEBサイトに実装をしデータを送信できる状態にします。
BigQueryでは、user_properties RECORDに格納されます。
[GA4] BigQuery Export スキーマ
| フィールド名 | データ型 | 説明 |
|---|---|---|
| user_properties.key | STRING | ユーザー |
| user_properties.value | RECORD | ユーザー |
| user_properties.value.string_value | STRING | ユーザー |
| user_properties.value.int_value | INTEGER | ユーザー |
| user_properties.value.double_value | FLOAT | ユーザー |
| user_properties.value.float_value | FLOAT | このフィールドは現在使用されていません。 |
| user_properties.value.set_timestamp_micros | INTEGER | ユーザー プロパティが最後に設定された時刻（マイクロ秒単位）。 |
クエリで取得する場合は、UNNEST関数を利用します。
SELECT
(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'c_customer_id') AS c_customer_id,
COUNT(distinct user_pseudo_id) AS users
FROM
`analytics_99999999.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20230601' and '20230630'
AND event_name = 'user_engagement'
GROUP BY 1
ORDER BY 2 DESC
Google Analytics 4 転送設定
先にGCP側でプロジェクトの作成を完了してください。GCP BigQuery
左下の歯車アイコンの設定>プロパティ>BigQuery のリンク>右上のリンクよりGCPのBigQueryと連携します。
連携が完了すると、翌日よりBigQueryにデータが蓄積されます。(例はGoogle提供のサンプルデータ)
Google 広告転送設定
Google 広告の転送
Google 広告の転送設定を完了すると、レポートデータを定期的に読み込むジョブのスケジューリングと管理を自動化できます。
先に下記の３点を完了してください
・BigQuery Data Transfer Service の有効化
・データセットの作成
・Pub/Sub の転送実行通知を設定
転送設定
1.Google Cloud Console の [BigQuery] ページに移動します。
2.[データ転送] をクリックします。
3.[転送を作成] をクリックします。
4.[ソースタイプ] セクションで、[ソース] として [Google Ads] を選択します。
5.[転送構成名] セクションの [表示名] に、転送名（例: My Transfer）を入力します。転送名には、後で修正が必要になった場合に簡単に識別できる任意の名前を使用できます。
6.[スケジュール オプション] セクションで:
a.[繰り返しの頻度] で、転送を実行する頻度のオプションを選択します。
・カスタム（デフォルト）
・毎日
・オンデマンド
b.[カスタム] または [毎日] を選択した場合は、カスタム スケジュールか有効な時刻をそれぞれ UTC で入力します。
c.デフォルト値（[すぐに開始可能]）のままにするか、[設定した時間に開始] をクリックします。前に [オンデマンド] を選択した場合、これらのオプションは無効になります。
d.[開始日と実行時間] に、転送を開始する日付と時刻を入力します。[すぐに開始可能] を選択した場合、このオプションは無効になります。
7.[転送先の設定] セクションの [データセット] には、データを保存するために作成したデータセットを選択します。
8.[データソースの詳細] セクションで、次の操作を行います。
9.（省略可）[通知オプション] セクションで:希望する通知にチェックを入れます。
10.保存
必要な権限
転送を作成するユーザーに、次の必要な権限が付与されていることを確認します。
BigQuery Data Transfer Service:
・bigquery.transfers.update（転送を作成する権限）
・bigquery.datasets.get と bigquery.datasets.updateの両方（抽出先データセットに対する権限）
・bigquery.transfers.update 権限、bigquery.datasets.update 権限、bigquery.datasets.get 権限は IAM 事前定義ロール bigquery.admin に含まれています。
下位互換性 ads_AccountBasicStatsのビューの例
たとえば、2022-05-01 で AdWords から Google 広告への移行が発生したとします。次の表に、ads_AccountBasicStats ビューの列を示します。
| customer_id | metrics_clicks | metrics_cost_micros | _LATEST_DATE | _DATA_DATE |
|---|---|---|---|---|
| 1234567890 | 1212 | 430900867 | 2023-01-31 | 2022-12-07 |
| 1234567890 | 1163 | 647597711 | 2023-01-31 | 2023-01-06 |
| 1234567890 | 1156 | 384169744 | 2023-01-31 | 2022-12-11 |
| 1234567890 | 1150 | 372517099 | 2023-01-31 | 2022-12-08 |
| 1234567890 | 1058 | 323873436 | 2023-01-31 | 2022-12-07 |
キャンペーンの掲載結果
次のサンプルクエリでは、過去 30 日間の Google 広告キャンペーンの掲載結果を分析します。
SELECT
c.customer_id,
c.campaign_name,
c.campaign_status,
SUM(cs.metrics_impressions) AS Impressions,
SUM(cs.metrics_interactions) AS Interactions,
(SUM(cs.metrics_cost_micros) / 1000000) AS Cost
FROM
`DATASET.Campaign_CUSTOMER_ID` c
LEFT JOIN
`DATASET.CampaignBasicStats_CUSTOMER_ID` cs
ON
(c.campaign_id = cs.campaign_id
AND cs._DATA_DATE BETWEEN
DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
c._DATA_DATE = c._LATEST_DATE
GROUP BY
1, 2, 3
ORDER BY
Impressions DESC
BigQuery Load Data ステートメント
[Cloud Storage からの CSV データの読み込み]
次の例ではGCSに配置されているcsvを読み込むためのサンプルクエリです。
OVERWRITEを指定した場合上書きされます。INTOで末尾にデータを追加することもできます。
skip_leading_rows=1を指定すると、ヘッダー行がインポートされません。
LOAD DATA OVERWRITE mydataset.mytable
FROM FILES (
format = 'CSV',
uris = ['gs://bucket/path/file.csv']);
LOAD DATA INTO mydataset.mytable
FROM FILES (
skip_leading_rows=1,
format = 'CSV',
uris = ['gs://bucket/path/file.csv']);
LOAD DATA OVERWRITE mydataset.mytable
FROM FILES (
format = 'CSV',
uris = ['s3://<S3バケット名>/bq-s3-access/file.csv']);
データマートの作成
上記までで、BigQueryに「GA4のデータ」、「CSVのデータ」、「Google広告のデータ」のテーブルが作成されました。BIツールからそれぞれ別のデータセットとして読み込んでも良いのですが、
直感的に利用できるようにするため、ストアドプロシージャを作成し、スケジュールクエリとして登録します。
BigQueryスケジュールクエリ
Looker StudioからBigQueryに接続
Looker Studio
Looker Studioに接続し、新規のレポートを作成します。
データソースでBigQueryを選択します。
作成したデータセットを選択します。
以上となります。
Discussion