GA4ラストクリック単位の売上やトランザクション数をBigQueryで算出

GA4で参照元別のセッション数や購入数、収益を計算するのが一般的ですが、セッション中に別の参照元から訪問してから購入しても、この参照元は無効になってしまいます。そのため、出入りが激しかったり、セッションタイムアウトが長いECサイトではセッションの参照元では正しく計測できない場合があります。

アトリビューションモデルのラストクリック

「最後のクリック」別に収益を調べたい場合は、アトリビューション設定で対応することが可能です。

設定方法

1. 管理 > プロパティ設定 > データの表示 > アトリビューション設定
2. アトリビューション設定 > レポート用のアトリビューションモデル
3. 有料チャネルとオーガニックチャネル > ラストクリック を選択
   
4. 保存後、探索レポートに進み、ディメンション > アトリビューション内の参照元などを選択
   • ※ トラフィックソース内のセッションの参照元とは異なるため注意
     

この設定で最後の参照元別の収益を確認できます。

アトリビューションの制限

アトリビューションは便利ですが、使用可能な指標が限られており、トランザクション数や通常のイベントには使用できません。

BigQueryで購入数を抽出

アトリビューションレポートの制限を回避するため、BigQueryで各行にラストクリックの参照元を追加するクエリを作成します。

クエリ

WITH ga AS (
  SELECT *,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    NULLIF(REGEXP_REPLACE(collected_traffic_source.manual_source, r'(\(direct\)|\(not set\)|\(none\)|\(not provided\))', ''), '') AS manual_source,
    NULLIF(REGEXP_REPLACE(collected_traffic_source.manual_medium, r'(\(direct\)|\(not set\)|\(none\)|\(not provided\))', ''), '') AS manual_medium,
    NULLIF(REGEXP_REPLACE(collected_traffic_source.manual_campaign_name, r'(\(direct\)|\(not set\)|\(none\)|\(not provided\))', ''), '') AS manual_campaign_name
  FROM `project_id.analytics_property_id.events_YYYYMMDD`
),
event_order AS (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY ga_session_id, user_pseudo_id ORDER BY event_timestamp) AS rn
  FROM ga
),
temp_source_order AS (
  SELECT *,
    IF(manual_source IS NOT NULL, rn, NULL) AS source_rn
  FROM event_order
),
source_order AS (
  SELECT * EXCEPT(source_rn),
    LAST_VALUE(manual_source IGNORE NULLS) OVER (
      PARTITION BY user_pseudo_id, ga_session_id 
      ORDER BY event_timestamp 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_manual_source,
    LAST_VALUE(source_rn IGNORE NULLS) OVER (
      PARTITION BY user_pseudo_id, ga_session_id 
      ORDER BY event_timestamp 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_manual_source_rn
  FROM temp_source_order
),
temp_campaign AS (
  SELECT s.* EXCEPT(manual_medium, manual_campaign_name),
    e.manual_medium AS latest_manual_medium,
    e.manual_campaign_name AS latest_manual_campaign_name
  FROM source_order s 
  LEFT JOIN event_order e 
  ON s.user_pseudo_id = e.user_pseudo_id 
  AND s.ga_session_id = e.ga_session_id 
  AND s.latest_manual_source_rn = e.rn
),
result AS (
  SELECT 
    IFNULL(latest_manual_source, session_traffic_source_last_click.cross_channel_campaign.source) AS last_click_source,
    IFNULL(latest_manual_medium, session_traffic_source_last_click.cross_channel_campaign.medium) AS last_click_medium,
    IFNULL(latest_manual_campaign_name, session_traffic_source_last_click.cross_channel_campaign.campaign_name) AS last_click_campaign_name,
    * EXCEPT(latest_manual_source, latest_manual_medium, latest_manual_campaign_name)
  FROM temp_campaign
)
SELECT * FROM result;

セッション途中で外部流入がある場合の動作

このクエリを適用すると、セッションの途中でも外部から流入があれば、参照元（last_click_source など）が書き換えられます。

例えば、以下のようにデータが変化します。

⚠️ BigQueryでのGoogle広告流入時の注意点

BigQueryでは Google 広告からの流入時、collected_traffic_source.medium の値が "organic" になっている問題があります。

対処方法：

gclid カラムの値を確認し、medium カラムを "cpc" に書き換える必要があります。

CASE 
    WHEN gclid IS NOT NULL THEN 'cpc' 
    ELSE collected_traffic_source.medium 
END AS corrected_medium

この処理を加えることで、Google広告流入が "cpc" として適切に分類され、より正確なアトリビューション分析が可能になります。