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

Last updated at 2025-05-24Posted at 2025-05-24

目的と結果

目的はタイトルのとおりGoogle Analytics 4 BigQuery exportのデータをS3へ転送し、Athenaからクエリできるようにすることです

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

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

流れ

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

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

別の方法は？

AWS AppFlow

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

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

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

Amazon Athena Google BigQuery コネクタ

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

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

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

セットアップ

GA4 Export BigQueryについては既にされている環境を想定しています。

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

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

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

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

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

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

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

GCS & S3設定

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

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

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

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

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

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

AWS Secrets Manager 設定

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

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エンコード文字列を貼ります。

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

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

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

ECR Repository登録

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

$ 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テンプレートファイルやリソース設定を以下リポジトリに置いています

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

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

Deploy後

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

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

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

Athenaテーブル作成

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

AthenaでCREATE TABLEを実行しテーブルを作っておきます。

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

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でクエリができる状態になります！

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

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

さいごに

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

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up