GA4データの分析用SQLまとめ

2023年2月6日 11:00

電通デジタルの中野です。
今回は、Google Analytics 4（GA4）をBigQueryで分析する際によく使うSQLをまとめました。
自身の業務でもよく使用するため備忘録的な側面もありますが、参考までに活用いただければと思います。

前提

BigQueryに連携したGA4データにはいくつか前提条件があります。
その中で最も大きな点は、GA4のレポート画面と数値が一致しない場合がある、ということです。
詳細については、こちらのページに記載があります。

ユーザー数やセッション数といったユニーク数を算出する場合、データ量が多いと計算に時間がかかります。そこでGA4レポートではHyperLogLogというアルゴリズムを使用して近似値を算出しています。
精緻な値を計算する場合は、現状BigQuery以外に手段がありません。こういった制約からもBigQuery上でSQLを使った分析をする場面は増えると思われます。

GA4用のUDF

GA4ではイベントパラメータ、ユーザープロパティデータはRECORD型で格納されています。
これを取り出すSQLは何度も書くため、以下のようなUDFを用意しておくと便利です。

create temporary function 
   get_value(params ANY TYPE,name STRING ) AS  
   ( ( 
       select 
           coalesce( 
               value.string_value 
               , cast(value.int_value as string) 
               , cast(value.float_value as string) 
               , cast(value.double_value as string) 
               ) 
       from 
           unnest(params) AS x 
       where 
           x.key = name 
   ) );

この get_value 関数を使うことで、イベントパラメータが簡単に取得できます。

get_value(event_params, ‘page_location') as page_location

指標算出用SQL

ページビュー

ページビューは event_name = ‘page_view’ の件数をカウントすれば良いです。

select 
   count(1) 
from 
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
where 
   _table_suffix between '20210101' and '20210131' 
   and event_name = 'page_view'

セッション数

セッション数はユーザーIDとセッションIDをつなげた文字列をユニークカウントします。
イベント名が session_start の場合のユニークカウントという定義での集計も考えられますが、より簡単で理解しやすい定義のほうが分析に適しています。

select 
   count(distinct concat(user_pseudo_id,get_value(event_params, 'ga_session_id')) 
from 
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
where 
   _table_suffix between '20210101' and '20210131'

ユーザー数

ユーザー数はユーザーIDのユニークカウントなのでシンプルです。

select 
   count(distinct user_pseudo_id) 
from 
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
where 
   _table_suffix between '20210101' and '20210131'

ディメンション算出用SQL

ページURL

ページURLはイベントパラメータ page_location に入っています。
基本的にはクエリパラメータが含まれているため、分析しやすいように正規化する事が多いです。

select 
   regexp_extract(lower(get_value(event_params, 'page_location')),r'^([^\?]+)') as page_location 
   , count(1) as pv 
from 
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
where 
   _table_suffix between '20210101' and '20210131' 
   and event_name = 'page_view' 
group by 
   page_location

日付や時間など

デフォルトで event_timestamp というカラムがあります。こちらを加工することで、イベントが発生した日付や時間を抽出できます。
代表的な変換例は以下のとおりです（その他変換に使用できる形式はこちらを参照）。

select 
    format_timestamp('%Y',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%m',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%d',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%F',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%H',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%M',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%S',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%X',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
    , format_timestamp('%a',timestamp_micros(event_timestamp), 'Asia/Tokyo') 
from 
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` 
where 
    _table_suffix between '20210101' and '20210101'

セッションスコープの流入元情報

BigQueryのデータで流入元情報として保持されているのは、ユーザーレベルの最初のチャネルだけです。セッションレベルで最後に接触したチャネルや最初に接触したチャネル情報は有りません。
イベントパラメータを元にして作成する必要があります。下記の例ではイベントパラメータから source データを取りだし、セッションレベルで最初に接触した source と最近接触した source を計算しています。

with ga as (
 select
   user_pseudo_id
   , get_value(event_params, 'ga_session_id') as ga_session_id
   , lower(get_value(event_params, 'source')) as source
   , event_timestamp
   , event_name
 from
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
)
 
select
 user_pseudo_id
 , ga_session_id
 , event_timestamp
 , event_name
 , source
 , last_value(source ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as source_ss_last
 , first_value(source ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as source_ss_first
from
 ga
order by
 user_pseudo_id
 , ga_session_id
 , event_timestamp asc

抽出結果としては以下のようになり、意図通りに計算できていることがわかります。

Eコマース系データの算出用SQL

Eコマースデータは ecommerce.hogehoge というカラムに決済関連の情報が入り、 items.hogehoge というカラムに商品に関するデータが入ります。
厄介なのは itemsカラムです。商品は1行のデータに対して複数紐づく場合があるためです。

例
- １回の商品購入（purchaseイベント）で3つの異なる商品を購入した

items のデータを使用する際には unnest して joinする必要があります。

select
    event_name
    /* Ecommerce関聯ディメンション */
    ecommerce.total_item_quantity
    , ecommerce.purchase_revenue_in_usd
    , ecommerce.purchase_revenue
    , ecommerce.refund_value_in_usd
    , ecommerce.refund_value
    , ecommerce.shipping_value_in_usd
    , ecommerce.shipping_value
    , ecommerce.tax_value_in_usd
    , ecommerce.tax_value
    , ecommerce.unique_items
    , ecommerce.transaction_id

    /* アイテム系ディメンション */
    , items.item_id
    , items.item_name
    , items.item_brand
    , items.item_variant
    , items.item_category
    , items.item_category2
    , items.item_category3
    , items.item_category4
    , items.item_category5
    , items.price
    , items.quantity
    , items.item_revenue
    , items.item_refund

from
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    , unnest(items) as items
where
    _table_suffix between '20210101' and '20210101'

, unnest(items)

の箇所は cross join と同義です（詳細はこちら）。
なお、 items を cross join した場合、 itemsの中にデータが存在するレコードのみが出力されます。itemsにデータがないレコードも表示したい場合は、以下のように記述します。

left join unnest(items) as items

GA4分析用の中間テーブル作成用SQL

いままで紹介したようなSQLを踏まえ、必要そうなカラムを整えたSQLが下記です。
適宜カスタムで計測しているイベントパラメータやユーザープロパティを追加すれば、分析に適した中間テーブルが作成できます。

/*
GA4の event_params や user_properties から値を取得する関数
実際のデータは値によって string / int など型が分かれるため、すべて string に変換する
*/

create temp function get_value(arr any type, params string) as
((
    select
        coalesce(
                cast(value.string_value as string)
                , cast(value.int_value as string)
                , cast(value.float_value as string)
                , cast(value.double_value as string)
        )
    from
        unnest(arr)
    where
        key = params
));


/*
GA4のローデータから必要なカラムを抽出する
*/
with t1 as (
    select
        /* ユーザー,セッション系データ */
        user_pseudo_id
        , user_id
        , get_value(event_params, 'ga_session_id') as ga_session_id

        /* イベント, 画面系データ */
        , event_timestamp
        , event_name
        , get_value(event_params, 'ga_session_number') as ga_session_number
        , lower(get_value(event_params, 'page_location')) as page_location
        , get_value(event_params, 'firebase_screen_class') as firebase_screen_class
        , get_value(event_params, 'page_title') as page_title
        , get_value(event_params, 'firebase_screen') as firebase_screen
        , lower(get_value(event_params, 'page_referrer')) as page_referrer
        , get_value(event_params, 'firebase_previous_screen') as firebase_previous_screen
        
        /* 流入元系データ（イベントレベル） */
        , get_value(event_params, 'source') as source
        , get_value(event_params, 'medium') as medium
        , get_value(event_params, 'campaign') as campaign

        /* 決済全体に関わるデータ */
        , ecommerce.transaction_id as transaction_id
        , ecommerce.total_item_quantity as total_item_quantity
        , ecommerce.purchase_revenue as purchase_revenue
        , ecommerce.refund_value as refund_value
        , ecommerce.shipping_value as shipping_value
        , ecommerce.tax_value as tax_value

        /* アクション対象の商品に関わるデータ */
        , items.item_id 
        , items.item_name
        , items.item_variant
        , items.item_category
        , items.item_category2
        , items.item_category3
        , items.item_category4
        , items.item_category5
        , items.price
        , items.quantity
        , items.item_revenue
        , items.item_refund

        /* 以後 GA4でデフォルト取得しているデータ */
        , privacy_info.ads_storage
        , privacy_info.analytics_storage
        , privacy_info.uses_transient_token

        , traffic_source.medium as traffic_source_medium
        , traffic_source.source as traffic_source_source
        , traffic_source.name as traffic_source_name

        , geo.continent
        , geo.sub_continent
        , geo.country
        , geo.region
        , geo.metro
        , geo.city
        
        , app_info.id
        , app_info.firebase_app_id
        , app_info.install_source
        , app_info.version

        , 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.language
        , device.time_zone_offset_seconds
        , device.web_info.browser
        , device.web_info.browser_version

        , stream_id
        , platform
        , user_first_touch_timestamp
        , user_ltv.revenue
        , user_ltv.currency
    from
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
        left join
        unnest(items) as items
    where
        _table_suffix between '20210130' and '20210131'
)

/*
セッションレベルの最後の source / medium / campaign を計算する
*/
select
    *
    , last_value(source ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as latest_source
    , last_value(medium ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as latest_medium
    , last_value(campaign ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as latest_campaign
from
    t1

以上で、GA4のデータをBigQueryにて分析するための一通りのSQLについて説明しました。
あとはLooker StudioやTableauに接続することで、制限なく自由に分析できる環境が整えられます。

電通デジタルにはGA4の計測設計からDWHの構築、BI構築まで幅広いスキルを持ったチームがあります。

お困りのことがあれば、ぜひ以下までお問い合わせください。

■　お問合せ先
電通デジタル　データアクティベーション事業部
[email protected]