BigQueryでのデータ革新: UNNEST関数の活用
BigQueryは膨大な量のデータを扱う上での柔軟性と強力な分析能力を提供します。特にGoogle Analytics 4 (GA4) のようなネストされたデータ構造を持つ複雑なデータセットを分析する際には、UNNEST関数の使用がデータ分析の新たな地平を開きます。この関数により、一つのイベントに関連付けられた複数のアイテムを含むデータを、個々のレコードとして扱うことが可能になります。
データ分析の深化
UNNEST関数の使用は、イベントレベルだけでなくアイテムレベルでの分析を可能にします。これにより、各アイテムのパフォーマンス評価や、顧客の関心が具体的にどのアイテムに集中しているかなど、より深い洞察を得ることができます。これは、特定の条件下でのアイテムの動向を理解するためにも役立ちます。
コード例
SELECT
A.event_timestamp,
date(timestamp_micros(A.event_timestamp),'Asia/Tokyo') AS date,
A.event_name,
StringFromEventParams(A.event_params, 'page_location') AS page_location,
StringFromEventParams(A.event_params, 'page_title') AS page_title,
CAST(NumberFromEventParams(A.event_params, 'ga_session_id') AS INT64) AS ga_session_id,
NumberFromEventParams(A.event_params, 'ga_session_number') AS ga_session_number,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(timestamp_micros(A.event_timestamp), SECOND), 'Asia/Tokyo') AS time,
StringFromEventParams(A.event_params, 'currency') AS currency,
NumberFromEventParams(A.event_params, 'value') AS value,
StringFromEventParams(A.event_params, 'session_engaged') AS session_engaged,
NumberFromEventParams(A.event_params, 'engagement_time_msec') AS engagement_time_msec,
StringFromEventParams(A.event_params, 'search_term') AS search_term,
NumberFromEventParams(A.event_params, 'percent_scrolled') AS percent_scrolled,
geo.country as geo_country, --イベントが報告された国
geo.city as geo_city, --イベントが報告された都市
geo.region as geo_region, --イベントが報告された地域
device.category as device_category, --デバイスのカテゴリ（モバイル、タブレット、PC）
CONCAT(traffic_source.source, " / ", traffic_source.medium) AS referrer_media, --参照元/メディアの作成
A.user_id,
A.user_first_touch_timestamp,
A.user_pseudo_id,
A.event_bundle_sequence_id,
A.ecommerce.total_item_quantity AS ecommerce_total_item_quantity,
A.ecommerce.purchase_revenue AS ecommerce_purchase_revenue,
A.ecommerce.refund_value AS ecommerce_refund_value,
A.ecommerce.shipping_value AS ecommerce_shipping_value,
A.ecommerce.tax_value AS ecommerce_tax_value,
A.ecommerce.unique_items AS ecommerce_unique_items,
A.ecommerce.transaction_id AS ecommerce_transaction_id
,B.item_id AS items_item_id --アイテムID
,B.item_name --アイテム名
,B.item_revenue --売上金額
,B.quantity --売上アイテム個数
FROM
`project.dataset.events_*` AS A
LEFT JOIN
UNNEST(A.items) AS B
アイテムレベルでのデータ分析の革新
特に注目すべき点は、このクエリがUNNEST(A.items)を活用している部分です。Google Analytics 4 (GA4) で収集されるデータは非常に複雑で、特定のイベントやトランザクションに関連する複数のアイテム情報がネストされた形で格納されていることがよくあります。従来のデータ分析手法では、これらの詳細なアイテムレベルの情報を一度に扱うことは困難でした。
このアプローチによって、以下のような複数のメリットがもたらされます：
詳細なアイテム分析: 各トランザクションやイベントに紐づくアイテムごとに、ID、名前、売上、数量といった詳細なデータを取得できます。これにより、個々のアイテムのパフォーマンスを正確に把握し、在庫管理やマーケティング戦略をより精密に調整することが可能になります。
ユーザー行動の深層理解: ユーザーがどのアイテムに興味を持ち、どれを購入したかをアイテムレベルで分析することで、ユーザーの好みや行動パターンをより詳細に理解することができます。
改善点の特定: 特定のアイテムが期待通りに売れていない場合、その原因を探るための詳細なデータが提供されます。例えば、ページのエンゲージメントが低い、特定の地域からのアクセスが少ないなど、具体的な改善点を特定できます。
マーケティング戦略の最適化: アイテムごとの売上や人気度を分析することで、プロモーションや広告の戦略を最適化できます。最も効果的なキャンペーンを実行するために必要なデータを提供します。
分析時の注意点
UNNEST関数を利用する際には、特にイベントカウントのような分析を行う場合に、展開による行の重複を考慮する必要があります。LEFT JOIN される側のテーブルの行が重複されることに注意してください。
これは、view_item_listやpurchaseイベントなどのitmesがネストされているイベントのみが重複されるという意味です。
そのため正確な分析結果を得るためには、データの重複や集計時の正確さに注意を払うことが重要です。