GA4

【GA4 SQL】セッションの参照元情報をBigQueryから取得

※2024年7月~にセッション情報のエクスポートが開始されたため、セッションの参照元情報を取得できるようになりました。
参考:session_traffic_source_last_click

GA定義のセッションの参照元情報の取得が可能に

2024年10月頃にBigQueryにエクスポートされるデータにsession_traffic_source_last_clickのフィールドが追加されセッションの参照元情報が含まれるようになった。これによりGA4の探索・標準レポートの指標と同一のセッションの参照元・メディアのディメンションを扱えるようになったため、単純にGA4定義のセッションの参照元情報に合わせたければsession_traffic_source_last_clickの指標を扱えばよい。

session_traffic_source_last_clickは「直接セッション」ではない

UA時代から引き続きGA4のセッションの参照元情報も「直接セッション」とはなっておらず、そのユーザーの最後に確認された参照元情報に紐づく。結論から言えば、これを気にせずGA4の標準レポート・探索で表示される指標と合わせたければ「session_traffic_source_last_click」フィールドの値を活用すればよく、コード量も簡潔になるため私自身もこちらを今後は採用していく予定である。

では、そもそも「直接セッション」とは何かという点であるが、UA後期に出てきたディメンションであり、参照元がダイレクトなど直接的に訪問してきた際に、直接的に訪問したよねというフラグを確認することができるようになったものである。これにより、ダイレクト訪問であっても参照元が例えばオーガニックに付くことはなくなり、より直接的に参照元情報の貢献度が分かりやすくなったというメリットが得られるようになった経緯がある。

そのため、個人的にも「直接セッション」で判断した方がよいと思うが、「session_traffic_source_last_click」フィールドが扱えるようになったことで、複雑な参照元情報の取得式を模索しなくとも探索の値と合致できる点から、こちらの値でも実用上問題はないので「session_traffic_source_last_click」の値で集計してもよいと考えている。ただ、このようなBigQueryにエクスポートされるスキーマ情報は過去に遡らないため、過去の「session_traffic_source_last_click」情報はNULL値となることに注意は必要。

参考:【新機能】今までのGAの参照元は何だったの? “本当の参照元”がわかる「直接セッション」
参考:[GA4] トラフィック ソースのディメンションのスコープ

セッションの参照元情報を取得するクエリ

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20241101');

WITH
  SMC_01 AS (
    SELECT
      PARSE_DATE("%Y%m%d", event_date) AS ymd,
      event_timestamp,
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
      CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
      session_traffic_source_last_click.cross_channel_campaign.source AS source,
      session_traffic_source_last_click.cross_channel_campaign.medium AS medium,
      session_traffic_source_last_click.cross_channel_campaign.campaign_name AS campaign
    FROM
      `<project>.<dataset>.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
    AND
      event_name = 'session_start'
  ),
  SMC_02 AS (
    SELECT
      ymd,
      ssid,
      CONCAT(source,' / ', medium) AS source_medium,
      source,
      medium,
      campaign
    FROM
      SMC_01
  ),
  SMC_03 AS (
    SELECT
      source_medium,
      source,
      medium,
      campaign,
      COUNT(DISTINCT ssid) AS _ssuu
    FROM
      SMC_02
    GROUP BY
      source_medium,
      source,
      medium,
      campaign      
  )

SELECT
  *
FROM
  SMC_03

以下、過去の情報

「直接セッション」の参照元情報を取得することを試みたクエリは以下となる。100%の完成形ではないが今後、修正していくため記載する。

セッションの直接の参照元情報を取得するクエリ

WITH
  SMC_01 AS (
    SELECT
      PARSE_DATE("%Y%m%d", event_date) AS ymd,
      event_timestamp,
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
      CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign
    FROM
      `<project>.<dataset>.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
  ),
  SMC_02 AS (
    SELECT
      ymd,
      ssid,

      ARRAY_AGG(
        (
          CASE
            WHEN REGEXP_CONTAINS(page_location, r'gclid') THEN 'google'
            WHEN REGEXP_CONTAINS(page_location, r'yclid') THEN 'yahoo'
            ELSE source
          END
        ) IGNORE NULLS
        ORDER BY
          event_timestamp ASC
        LIMIT
          1
      ) [SAFE_OFFSET(0)] AS source,

      ARRAY_AGG(
        (
          CASE
            WHEN REGEXP_CONTAINS(page_location, r'gclid') THEN 'cpc'
            WHEN REGEXP_CONTAINS(page_location, r'yclid') THEN 'cpc'
            ELSE medium
          END
        ) IGNORE NULLS
        ORDER BY
          event_timestamp ASC
        LIMIT
          1
      ) [SAFE_OFFSET(0)] AS medium,

      ARRAY_AGG (
        campaign IGNORE NULLS
        ORDER BY
          event_timestamp ASC
        LIMIT
          1
      ) [SAFE_OFFSET(0)] AS campaign,

    FROM
      SMC_01
    GROUP BY
      ymd,
      ssid
  ),
  SMC_03 AS (
    SELECT
      ymd,
      ssid,
      CONCAT(COALESCE(source, '(direct)'), ' / ', COALESCE(medium, '(none)')) AS source_medium,
      COALESCE(source, '(direct)') AS source,
      COALESCE(medium, '(none)') AS medium,
      COALESCE(campaign, '(not set)') AS campaign,
    FROM
      SMC_02
  ),
  SMC_04 AS (
    SELECT
      ymd,
      ssid,
      source_medium,
      source,
      medium,
      CASE
        WHEN medium = 'cpc' AND campaign = '(organic)' THEN '(not set)'
        ELSE campaign
      END AS campaign
    FROM
      SMC_03
    ORDER BY
      ymd ASC
  )

SELECT
  *
FROM
  SMC_04

セッションの直接の参照元情報を取得するにはARRAY_AGG関数を利用する。ARRAY_AGG関数は指定した列の値を集約して配列として返す関数であるが、オプション指定によりセッションの最初の参照元情報のみを返す処理を行っている。

  1. 条件分岐(CASE文): page_locationgclidまたはyclidが含まれている場合、これらはGoogle AdsやYahoo!広告などの有料検索キャンペーンに由来するパラメータです。これらが含まれていれば、媒体を「cpc」(コストパークリック)として認識します。それ以外の場合は、そのセッションのmediumフィールドの値を使用します。
  2. ARRAY_AGG()関数: この関数は条件分岐の結果を集約して配列に格納します。しかし、ここでの目的は配列を作ることではなく、セッションの最初のイベントでのmedium値を取得することです。そのため、event_timestampの昇順(ASC)で並べ替えて、最初に登場する値を取得します。
  3. IGNORE NULLS: このオプションは、NULL値を無視して非NULLの値のみを集約することを指定します。これにより、何らかの理由でmediumが欠落している場合に無視され、利用可能な最初の値が採用されます。
  4. LIMIT 1: これは集約された値の中から最初の1つだけを取得することを指定します。この場合、最初の参照元情報だけが必要なため、セッションの開始時点のmedium値のみを抽出します。
  5. [SAFE_OFFSET(0)]: ARRAY_AGGで作成された配列から最初の要素を安全に取得します。この操作により、配列の最初の要素(このクエリでは最初の参照元情報)が返されます。もし何らかの理由で結果が空の場合、SAFE_OFFSETはNULLを返します。

関連記事

【GA4 SQL】2つのevent_timestampの日付比較

【GA4 SQL】ページ単位のエンゲージメント分析

【GA4 SQL】セッションセグメントを適用させる方法

PAGE TOP