GA4 export BigQueryデータをS3へ転送しAthenaでクエリする

## 目的と結果

目的はタイトルのとおり[Google Analytics 4 BigQuery export](https://support.google.com/analytics/answer/9823238?hl=ja#zippy=%2Cin-this-article%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%BA%8B%E3%81%AE%E5%86%85%E5%AE%B9)**のデータをS3へ転送し、Athenaからクエリできるようにすること**です

以下のような環境を作り、日レベルでExportされるBigQueryへのデータをS3へ転送しAthenaで見れるようにしました。

![structure.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/77ad4bea-50ca-4cbd-aadf-6bdc39b69cf9.png)


こんな感じでAthenaからクエリできます！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/fb8761f5-1da1-4182-8738-c2bb426482e6.png)


### 流れ

1. 1日1回2日前のデータを転送するようにEventBridge Schedulerが発火
2. AWS Batch上でbqコマンドを実行しBigQueryからGCSへデータをparquetでExport
3. AWS Batch上でgcloudコマンドを実行しGCSとS3でデータをsync
4. Glue CrawlerでS3のHive metastoreテーブルフォーマットのパーティションをロード
5. Athenaでクエリ可能

のような流れになっています。

### 別の方法は？

#### AWS AppFlow

https://aws.amazon.com/jp/appflow/

AWS AppFlowではBigQueryテーブルからS3への転送をサポートしています。

が、**ネストされた表形式ではないデータの場合20250524現在も非対応**なようです。
以下ページと同じことを改めて試しましたが同様のエラーになりました。

https://qiita.com/hayao_k/items/43c5e1389e528c4e562a


また、GA4 to S3のAppFlowもありますが転送カラム数に上限があるようです

https://qiita.com/sugimount-a/items/c3b1a79131c8784ed4a6

https://docs.aws.amazon.com/ja_jp/general/latest/gr/appflow.html

#### Amazon Athena Google BigQuery コネクタ

この方法であればわざわざ実データを転送せずともAthenaでクエリできるようになります。

が、**Lambdaで実行されているため1クエリ15分までの制約があります**

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/connectors-bigquery.html

> Lambda 関数における最大タイムアウト時間は 15 分です。分割が発生するたびに BigQuery に対しクエリが実行されます。Athena が読み込む結果を保存するためには、十分な時間のクエリが必要です。Lambda 関数がタイムアウトした場合には、クエリは失敗します。


## セットアップ

[GA4 Export BigQuery](https://support.google.com/analytics/answer/9823238?hl=ja#zippy=%2Cin-this-article%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%BA%8B%E3%81%AE%E5%86%85%E5%AE%B9)については既にされている環境を想定しています。


![20250524.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/39d6dc6b-f642-4d79-840f-038be8c355dd.png)

### GCP_サービスアカウント設定

GCPでサービスアカウントを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/63a4caa1-c391-4da2-a712-add9323a2ac3.png)

サービスアカウントに割り当てるロールについては以下のようにしました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/8eb6608a-4b3b-4925-9a56-692976607def.png)

詳細に権限を設定する場合は以下が必要です。

- [bq export](https://cloud.google.com/bigquery/docs/exporting-data?hl=ja#required_permissions)
- [gcloud storage rsync](https://cloud.google.com/storage/docs/access-control/iam-gcloud?hl=ja)

その後、JSONの秘密鍵を作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/3edb22a2-55fa-44c0-9976-3c085bc4ca65.png)

そののち、生成した秘密鍵に対して以下を実行し、結果をコピーしておきます。

```bash
$ base64 service_account_secret_key.json | tr -d '\n'
```

### GCS & S3設定

BigQueryからGCSへ転送する先を作ります

`gs://ga4_bigquery_export_data/prod`として作りました

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/ae5724f1-59a1-4fa7-bb56-7ea836c53f48.png)


:::note warn
警告
バケットですが、データの保護のチェックを`外してください`
:::

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/195eefd3-ec11-4230-8239-0f7d59a72776.png)

> 注意: Cloud Storage バケットにデータをエクスポートする場合は、バケットでバケットロックと削除（復元可能）の保持ポリシーを無効にすることを強くおすすめします。

https://cloud.google.com/bigquery/docs/exporting-data?hl=ja


GCSからS3に転送する先も作ります

`s3://quark-sandbox/ga4-bigquery-to-s3/`として作りました

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/bdc7bd55-0ada-451c-b4a7-15f7d0eb3883.png)

### AWS Secrets Manager 設定

名前を`ga4-bigquery-to-s3`として以下のようなキー/値のシークレットを作ります

![202505241.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/4ede2c74-c4b8-4ab6-b43a-934c91232e96.png)


```text
service_account_base64_encoded_key=ここにサービスアカウント base64エンコード結果をペースト
gcs_export_uri=ga4_bigquery_export_data/prod
bigquery_dataset_id={gcp-project-id}:{dataset-id}
s3_transfer_uri=quark-sandbox/ga4-bigquery-to-s3
```

service_account_base64_encoded_keyは[秘密鍵のbase64エンコード文字列](#GCP_サービスアカウント設定)を貼ります。

gcs_export_uri,s3_transfer_uriはGCSとS3に作ったURIの`gs://`,`s3://`抜きの文字列です。

bigquery_dataset_id=の値はBigQueryコンソールからコピーペーストできます。

:::note warn
警告
bigquery_dataset_idの値はペースト後`ドット.をコロン:に変更してください`
:::

![202505242.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/1d457526-4a60-4820-b030-7c18e6bbf5d0.png)

### ECR Repository登録

Amazon ECRにリポジトリを登録しておきます

```bash
$ aws ecr get-login-password --region $AWS_REGION | docker login --username AWS --password-stdin $AWS_ACCOUNT_ID.dkr.ecr.$AWS_REGION.amazonaws.com
$ aws ecr create-repository --repository-name ga4-bigquery-to-s3 --region $AWS_REGION --image-scanning-configuration scanOnPush=true --image-tag-mutability MUTABLE
```

## 環境Deploy

AWSリソースの為のCloudFormationテンプレートファイルやリソース設定を以下リポジトリに置いています

https://github.com/nijigen-plot/ga4-bigquery-data-s3-transfer

以下パラメータを設定してください。名前等気にしなければ`ResourceBucket`のS3バケット先だけの変更でOKです。

```yaml:cloudformation.yml
AWSTemplateFormatVersion: "2010-09-09"
Description: Set up GA4 BigQuery to S3 Infrastructure
Parameters:
  ResourcePrefix:
    Type: String
    Default: ga4-bigquery-to-s3
    Description: Prefix for all resources
  CidrBlock:
    Type: String
    Default: 192.168.0.0/22
  StateMachineVersion:
    Type: String
    Default: default
    Description: This parameter is used to update the Step Functions state machine by retrieving the version ID of the object stored in S3 with versioning enabled.
  ResourceBucket:
    Type: String
    Default: nijipro-ga4-bigquery-to-s3-resources # S3 バージョニングが有効になっているバケットを選択してください。
    Description: Please specify an S3 bucket with versioning enabled.
```

### AWS & Github コネクション作成

Github特定リポジトリでのアクションに応じてAWS CodePipelineが発火するように設定します。

これはこれで長くなるのでこの記事では割愛しますm(__)m

https://docs.aws.amazon.com/ja_jp/codepipeline/latest/userguide/connections-github.html



## Deploy後

問題無ければCFnからリソースが展開されます

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/6d06c8b9-b08e-445a-aea6-e110d9a0a0eb.png)


また、有効化されたスケジューラも配置されるため、自動でStepFunctionsが実行されます

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/eea56330-66e7-459c-8312-18541fb010ee.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/d0caab67-c7bd-4124-b299-89c11b8d44d8.png)

S3にオブジェクトが配置されているはずです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/99625ca0-04c6-4e30-8147-22e2b9bd8f72.png)

### Athenaテーブル作成

テーブルを作成していない場合、**転送は成功しているがGlue Crawlerが失敗している**状態になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/250d1fd9-0753-4d8a-b01f-a4ae0fa6c4ca.png)

Athenaで[CREATE TABLE](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-table.html)を実行しテーブルを作っておきます。

20250524現在GA4 BigQuery Exportデータを読むためのクエリは以下になります。`database_name`,`table_name`,`LOCATION`を適宜変更してください。

```sql
CREATE EXTERNAL TABLE database_name.table_name (
  event_date string,
  event_timestamp bigint,
  event_name string,
  event_params array<struct<
    key:string,
    value:struct<
      string_value:string,
      int_value:bigint,
      float_value:double,
      double_value:double
    >
  >>,
  event_previous_timestamp bigint,
  event_value_in_usd double,
  event_bundle_sequence_id bigint,
  event_server_timestamp_offset bigint,
  user_id string,
  user_pseudo_id string,
  privacy_info struct<analytics_storage:string, ads_storage:string, uses_transient_token:string>,
  user_properties array<struct<
    key:string,
    value:struct<string_value:string, int_value:bigint, float_value:double, double_value:double, set_timestamp_micros:bigint>>>,
  user_first_touch_timestamp bigint,
  user_ltv struct<revenue:double, currency:string>,
  device struct<
    category:string,
    mobile_brand_name:string,
    mobile_model_name:string,
    mobile_marketing_name:string,
    mobile_os_hardware_model:string,
    operating_system:string,
    operating_system_version:string,
    vendor_id:string,
    advertising_id:string,
    language:string,
    is_limited_ad_tracking:string,
    time_zone_offset_seconds:bigint,
    browser:string,
    browser_version:string,
    web_info:struct<
        browser:string,
        browser_version:string,
        hostname:string
        >
    >,
  geo struct<
	  city:string,
	  country:string,
	  continent:string,
	  region:string,
	  sub_continent:string,
	  metro:string
	>,
  app_info struct<id:string, version:string, install_store:string, firebase_app_id:string, install_source:string>,
  traffic_source struct<name:string, medium:string, source:string>,
  stream_id string,
  platform string,
  event_dimensions struct<hostname:string>,
  ecommerce struct<
    total_item_quantity:bigint,
    purchase_revenue_in_usd:double,
    purchase_revenue:double,
    refund_value_in_usd:double,
    refund_value:double,
    shipping_value_in_usd:double,
    shipping_value:double,
    tax_value_in_usd:double,
    tax_value:double,
    unique_items:bigint,
    transaction_id:string
>,
  items array<struct<
    item_id:string,
    item_name:string,
    item_brand:string,
    item_variant:string,
    item_category:string,
    item_category2:string,
    item_category3:string,
    item_category4:string,
    item_category5:string,
    price_in_usd:double,
    price:double,
    quantity:bigint,
    item_revenue_in_usd:double,
    item_revenue:double,
    item_refund_in_usd:double,
    item_refund:double,
    coupon:string,
    affiliation:string,
    location_id:string,
    item_list_id:string,
    item_list_name:string,
    item_list_index:string,
    promotion_id:string,
    promotion_name:string,
    creative_name:string,
    creative_slot:string,
    item_params:array<struct<
        key:string,
        value:struct<
            string_value:string,
            int_value:bigint,
            float_value:double,
            double_value:double
            >
        >
    >>>,
  collected_traffic_source struct<
    manual_campaign_id:string,
    manual_campaign_name:string,
    manual_source:string,
    manual_medium:string,
    manual_term:string,
    manual_content:string,
    manual_source_platform:string,
    manual_creative_format:string,
    manual_marketing_tactic:string,
    gclid:string,
    dclid:string,
    srsltid:string
    >,
  is_active_user boolean,
  batch_event_index bigint,
  batch_page_id bigint,
  batch_ordering_id bigint,
  session_traffic_source_last_click struct<
    manual_campaign:struct<
        campaign_id:string,
        campaign_name:string,
        source:string,
        medium:string,
        term:string,
        content:string,
        source_platform:string,
        creative_format:string,
        marketing_tactic:string
    >, google_ads_campaign:struct<
        customer_id:string,
        account_name:string,
        campaign_id:string,
        campaign_name:string,
        ad_group_id:string,
        ad_group_name:string
    >, cross_channel_campaign:struct<
        campaign_id:string,
        campaign_name:string,
        source:string,
        medium:string,
        source_platform:string,
        default_channel_group:string,
        primary_channel_group:string
    >, sa360_campaign:struct<
        campaign_id:string,
        campaign_name:string,
        source:string,
        medium:string,
        ad_group_id:string,
        ad_group_name:string,
        creative_format:string,
        engine_account_name:string,
        engine_account_type:string,
        manager_account_name:string
    >, cm360_campaign:struct<
        campaign_id:string,
        campaign_name:string,
        source:string,
        medium:string,
        account_id:string,
        account_name:string,
        advertiser_id:string,
        advertiser_name:string,
        creative_id:string,
        creative_format:string,
        creative_name:string,
        creative_type:string,
        creative_type_id:string,
        creative_version:string,
        placement_id:string,
        placement_cost_structure:string,
        placement_name:string,
        rendering_id:string,
        site_id:string,
        site_name:string
    >, dv360_campaign:struct<
        campaign_id:string,
        campaign_name:string,
        source:string,
        medium:string,
        advertiser_id:string,
        advertiser_name:string,
        creative_id:string,
        creative_format:string,
        creative_name:string,
        exchange_id:string,
        exchange_name:string,
        insertion_order_id:string,
        insertion_order_name:string,
        line_item_id:string,
        line_item_name:string,
        partner_id:string,
        partner_name:string
    >>,
  publisher struct<
    ad_revenue_in_usd:double,
    ad_format:string,
    ad_source_name:string,
    ad_unit_id:string
    >
)
PARTITIONED BY (
	event_date_part string
)
STORED AS PARQUET
LOCATION 's3://quark-sandbox/ga4-bigquery-to-s3';

```

これにより、毎日自動でデータがS3に転送されAthenaでクエリができる状態になります！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/fb8761f5-1da1-4182-8738-c2bb426482e6.png)


## 過去のデータを転送したい場合

AWS Batchでジョブを作成し、コンテナの上書きで`["--target-date", "YYYY-MM-DD"]`のように記載すれば特定日の転送が可能です

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/f1ad6364-552e-4a52-b28f-c80732b678f6.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/5eff1eb1-600b-4590-8592-0911a7baeea6.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/d48e9d6d-6a75-41c7-9c6b-fa3b3596f84a.png)

## さいごに

ここまで読んでいただきありがとうございました。同様のデータ転送で困っている方の解決になればと思い書きました。

ご不明点や修正点等あればコメントいただけると嬉しいです。