GA4

【GA4 SQL】CV（申し込みページ）に到達したセッション数を集計

2024.11.15

申し込み到達ページをコンバージョンとする場合は、そのページ到達をイベントとして設定することが望ましいが、イベントとして設定していなくともBigQueryではそのページに到達したことを条件とすることでCV件数をカウントすることが可能になる。このイベントの設定有無に関係なくデータがBigQueryにエクスポートされていれば過去に遡って集計することができる点がBigQueryを活用することのメリットの1つでもある。

【SQL】申し込みページに到達したセッション数を集計
- クエリ解説
【SQL】参照元情報付きの申し込みページに到達したセッション数を集計
【探索】申し込みページに到達したセッション数を集計

【SQL】申し込みページに到達したセッション数を集計

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');
CREATE TEMP FUNCTION CV_URL() RETURNS STRING AS ('CV_URL');

WITH
  CV_PageSessionCNT AS (
    SELECT
      ymd,
      COUNT(DISTINCT ssid) AS _ssuu
    FROM (
      SELECT
        ymd,
        CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
      FROM (
        SELECT
          PARSE_DATE("%Y%m%d", event_date) AS ymd,
          (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
          user_pseudo_id,
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
        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 = 'page_view'
      )
      WHERE
        page_location = CV_URL()
    )
    GROUP BY
      ymd
  )

SELECT
  *
FROM
  CV_PageSessionCNT
ORDER BY
  ymd

クエリ解説

申し込みページに到達したタイミングのみを集計対象としたいためpage_viewイベントの条件を指定している。理由としては申し込みページ到達後にそのページの中でセッションが変わることもあり、そのケースを考慮に入れてもCV件数としては意味を成さないため。

【SQL】参照元情報付きの申し込みページに到達したセッション数を集計

セッションの参照元情報ごとのCVページに到達したセッション数を集計するクエリは以下となる。通常は参照元情報は都度クエリ内にコーディングするとメンテナンスが困難になるため関数として定義して呼び出すがここでは解説のため参照元情報取得クエリもまとめて記載している。参照元情報を取得するクエリについては以下の記事を参照。

2024.06.16

2024.11.14

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

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

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

CREATE OR REPLACE TEMP TABLE SMC_ssid AS (
  SELECT
    ssid,
    -- manual_source_medium AS source_medium,
    -- manual_source AS source,
    -- manual_medium AS medium,
    -- manual_campaign AS campaign,
    cross_source_medium AS source_medium,
    cross_source AS source,
    cross_medium AS medium,
    cross_campaign AS campaign    
  FROM (
    SELECT
      ssid,
      -- セッションの手動参照元
      CONCAT(manual_source,' / ', manual_medium) AS manual_source_medium,
      manual_source,
      manual_medium,
      manual_campaign,
      -- セッションの参照元
      CONCAT(cross_source,' / ', cross_medium) AS cross_source_medium,
      cross_source,
      cross_medium,
      cross_campaign
    FROM (
      SELECT
        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.manual_campaign.source AS manual_source,
        session_traffic_source_last_click.manual_campaign.medium AS manual_medium,
        session_traffic_source_last_click.manual_campaign.campaign_name AS manual_campaign,
        -- セッションの参照元
        session_traffic_source_last_click.cross_channel_campaign.source AS cross_source,
        session_traffic_source_last_click.cross_channel_campaign.medium AS cross_medium,
        session_traffic_source_last_click.cross_channel_campaign.campaign_name AS cross_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'
    )
  )
);

WITH
  CV_PageSessionCNT AS (
    SELECT
      ymd,
      source_medium,
      source,
      medium,
      campaign,      
      COUNT(DISTINCT ssid) AS _ssuu
    FROM (
      SELECT
        id.ymd,
        id.ssid,
        SMC_id.source_medium,
        SMC_id.source,
        SMC_id.medium,
        SMC_id.campaign
      FROM (
        SELECT
          ymd,
          CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
        FROM (
          SELECT
            PARSE_DATE("%Y%m%d", event_date) AS ymd,
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
            user_pseudo_id,
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
          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 = 'page_view'
        )
        WHERE
          page_location = CV_URL()
      ) AS id
      LEFT JOIN (
        SELECT
          *
        FROM
          _SESSION.SMC_ssid
      ) AS SMC_id
      ON id.ssid = SMC_id.ssid
    )
    GROUP BY
      ymd,
      source_medium,
      source,
      medium,
      campaign
  )

SELECT
  *
FROM
  CV_PageSessionCNT
ORDER BY
  ymd

【探索】申し込みページに到達したセッション数を集計

探索でもアドホック的に数値を確認したいケースはあるので設定について解説すと、こちらについてもSQLと同様にpage_viewでイベント名でフィルターを設定している。外しても構わないが若干数値が増加するのは、その申し込みページの中でセッションIDが変わってしまうこともあり、そのようなケースは除外するためにpage_viewでフィルターをかけている。