uni-3 log

    Search by

    GA4のbigquery exportデータをフラットにする

    表題の通り

    GA4ではbigqueryへのexportを無料で行うことができるが、そのままだと少し扱いづらい

    特にevent_paramsがRECORDになっており、あるイベントのページURLやタイトルを取得する際の構文が長くなる

    フラット化前後のクエリ例

    生のままでは例えば↓のように書く

    # 直近30日のページごとPV
    select
      date(timestamp_micros(event_timestamp), 'Asia/Tokyo') as date
      -- ここがややこしい
      ,(select string_value from unnest(event_params) where key = 'page_title') as page_title
      ,(select string_value from unnest(event_params) where key = 'page_location') as page_location
      ,count(1) as pv
    from
      `analytics_xxxxxxxxx.events_*`
    where
      event_name = 'page_view'
    and
      _table_suffix between
        format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -30 day))    
      and
        format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
    group by 1,2,3

    フラット化したものだと以下のようにかける。行数は変わらないが、unnest構文を覚えておかなくて済む

    # 直近30日のページごとPV
    select
      date(date)
      ,page_title
      ,page_location
      ,count(1) as pv  
    from
      `analytics_xxxxxxxxx.flat_events_*`
    where
      event_name = 'page_view'
    and
      _table_suffix between
        format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -30 day))    
      and
        format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
    group by 1, 2, 3

    スケジュールドクエリの作成

    確定データをフラット化したテーブルを定期的に作成する

    exportタイミング

    https://support.google.com/analytics/answer/9823238?hl=ja#zippy=%2C%E3%81%93%E3%81%AE%E8%A8%98%E4%BA%8B%E3%81%AE%E5%86%85%E5%AE%B9

    前日のデータが格納された 1 ファイルが毎日(通常は、レポート作成用に設定したタイムゾーンの午後の早い時間帯に)エクスポートされます。

    何やら更新日時が曖昧なので、毎日0時に2日前のテーブルを転送するようにする。最速でやるなら夕方に前日のテーブルを抽出するのだろうがわかりやすい日時にしておく

    テーブル、スキーマ情報

    https://support.google.com/analytics/answer/7029846?hl=ja

    テーブルは2種類ある。intradayは未確定なデータが入っている

    これに加えて、1 日の途中で発生したアプリイベントを格納した表もインポートされます。この表には「eventsintradayYYYYMMDD」という形式の名前が付き、イベントが収集されるとデータがリアルタイムで追加されます。

    確定データを転写したいのでevents_を用いる

    クエリ

    user_propertiesprivacy_info、eコマース ecommerce、商品アイテム items

    は抽出していない

    クエリは完全に↓のコピペ

    https://ex-ture.com/blog/2019/08/07/google-analytics-apps-web-property-export-to-bigquery/

    >クエリ内容
    select
      event_date
      ,event_timestamp
      -- jst timestamp
      ,datetime(timestamp_micros(event_timestamp), 'Asia/Tokyo') as date
      ,event_name
      -- カスタム定義に応じたvalueの追加
      ,(select value.int_value from unnest(event_params) where key = 'engaged_session_event') as engaged_session_event
      ,(select value.string_value from unnest(event_params) where key = 'page_location') as page_location
      ,(select value.string_value from unnest(event_params) where key = 'page_title') as page_title
      ,(select value.int_value from unnest(event_params) where key = 'ga_session_number') as ga_session_number
      ,(select value.string_value from unnest(event_params) where key = 'page_referrer') as page_referrer
      ,(select value.int_value from unnest(event_params) where key = 'ga_session_id') as ga_session_id
      ,(select value.string_value from unnest(event_params) where key = 'medium') as medium
      ,(select value.string_value from unnest(event_params) where key = 'term') AS term
      ,(select value.string_value from unnest(event_params) where key = 'campaign') as campaign
      ,(select value.string_value from unnest(event_params) where key = 'source') as source
      ,(select value.string_value from unnest(event_params) where key = 'session_engaged') as session_engaged
      ,(select value.int_value from unnest(event_params) where key = 'entrances') as entrances
      ,(select value.int_value from unnest(event_params) where key = 'engagement_time_msec') as engagement_time_msec
      ,(select value.int_value from unnest(event_params) where key = 'percent_scrolled') as percent_scrolled
      ,event_previous_timestamp
      ,event_value_in_usd
      ,event_bundle_sequence_id
      ,event_server_timestamp_offset
      ,user_id
      ,user_pseudo_id
      ,user_first_touch_timestamp
      ,user_ltv.revenue
      ,user_ltv.currency
      ,device.category
      ,device.mobile_brand_name
      ,device.mobile_model_name
      ,device.mobile_marketing_name
      ,device.mobile_os_hardware_model
      ,device.operating_system
      ,device.operating_system_version
      ,device.vendor_id
      ,device.advertising_id
      ,device.language
      ,device.is_limited_ad_tracking
      ,device.time_zone_offset_seconds
      ,device.browser
      ,device.browser_version
      ,device.web_info.browser as device_web_info_browser
      ,device.web_info.browser_version as device_web_info_browser_version
      ,device.web_info.hostname
      ,geo.continent
      ,geo.country
      ,geo.region
      ,geo.city
      ,geo.sub_continent
      ,geo.metro
      ,app_info.id
      ,app_info.version
      ,app_info.install_store
      ,app_info.firebase_app_id
      ,app_info.install_source
      ,traffic_source.name
      ,traffic_source.medium as traffic_source_medium
      ,traffic_source.source as traffic_source_source
      ,stream_id
      ,platform
      ,event_dimensions.hostname as event_dimensions_hostname
    from `analytics_xxxxxxxxx.events_*`
    where
      _table_suffix = format_date('%Y%m%d', date_add(@run_date, interval -2 day))

    実行は毎日00:00

    destination tableは2日前の日付をつけてwildcard tableとして作成する

    flat_events_{run_time-48h|"%Y%m%d"}

    テーブル名は任意(ここではflat_events_

    過去データに対してスケジュールドクエリを適用

    過去分のテーブルについて適用することもできる

    スケジュールドクエリのバックフィル

    bigquery consoleより、スケジュールされたクエリ→作成したスケジュールドクエリを選択

    https://console.cloud.google.com/bigquery/scheduled-queries

    バックフィルのスケジュール構成より日付を選択したOKをクリックすると、

    run_dateなどの値が指定された日時で実行されるジョブが作成される(日別に作られる模様)

    backfill

    おまけ

    テーブルの復元

    作業中にテーブルを上書きしてしまったりして、復元もしたのでついでに残しておく

    2日くらいなら戻せるとのこと

    https://cloud.google.com/bigquery/docs/managing-tables?hl=ja#undeletetable

    10時間前の状態に復元

    # replace
    bq cp analytics_xxxxxxxxx.events_20210701@-7200000 analytics_xxxxxxxxx.events_20210701

    参考

    • ga4 export bigqueryの方法

    https://support.google.com/analytics/answer/9358801

    • exportしたデータのクエリ

    https://analytics-and-intelligence.net/article/2ipnnyjn5s6pcamk5pdy

    2021, Built with Gatsby. This site uses Google Analytics.