ブログ

GA4

【GA4 SQL】新規ユーザーのユーザーセグメントを日次で適用

GA4の分析において新規とリピーターを分けて分析したいケースがよくある。GA4の新規ユーザーの定義では初回訪問は新規ユーザーとしてカウントされるが、その日の2回目以降のセッションではリピーターとしてカウントされてしまう。ここでは初回訪問であれば、その日は何回セッションが変わったとしても初回訪問した新規ユーザーと見なす集計方法を採用している。

カスタマイズした新規ユーザーとリピーターの定義について

改めて私が採用している「新規ユーザー」と「リピーター」の定義について解説する。というのもGA4の新規とリピーターの定義をそのまま採用してしまうと、新規ユーザー数とリピーター数の合算が総合計数と合致しなくなるなど、集計上の見え方としてあまりよくないと考えるため。また、必ずしもGA4の定義をそのまま使わなければならない理由もないため、よりよい定義があればカスタマイズしていくことになんら問題はない。以下がそのカスタマイズした定義となる。

新規ユーザー：初回訪問し、その日は何回セッションが変わっても新規ユーザー
リピーター：アクティブユーザー数 – 新規ユーザー数

リピーター数はそれ自体の集計をせず、新規ユーザー数を元にリピーター数を割り出す。この方法の方が確実に新規ユーザー数とリピーター数の合算値が総合計数とイコールになるためだ。

日次単位でユーザーセグメントを適用するとは？

例えば「新規ユーザー」と「リピーター」であれば、新規ユーザーとみなすのは「当日のみ」であるため、ユーザーのみにセグメントを適用してしまうと、そのユーザーは新規ユーザーとして固定化されてしまう問題がある。このため、結合条件ではユーザーの他に日付でも結合することで、日次単位でユーザーセグメントを適用させることができ、新規とリピーターの正しい集計が可能になる。

新規ユーザーの日次のユーザーセグメントを作成するクエリ

まず適用させるセグメントを用意する。ここでは日次の新規ユーザーを例にユーザーセグメントを作成する。

WITH
  Seg_NewUU AS (
    SELECT
      PARSE_DATE("%Y%m%d", event_date) AS ymd,
      user_pseudo_id AS uuid,
    FROM
      `<project>.<dataset>.events_*`
    WHERE
        _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND
        event_name = 'first_visit'
    GROUP BY
      ymd,
      uuid
  )

SELECT
  *
FROM
  Seg_NewUU

クエリ解説

新規ユーザーは’first_visit’イベントが付いているため、これを日次で’user_pseudo_id‘を集計する。セグメントを適用させるに重複行は不要なため、GROUP BY句で重複をなくす。

ページ別訪問ユーザーに新規ユーザーセグメントを適用させるクエリ

WITH
  Seg_NewUU AS (
    SELECT
      PARSE_DATE("%Y%m%d", event_date) AS ymd,
      user_pseudo_id AS uuid,
    FROM
      `<project>.<dataset>.events_*`
    WHERE
        _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND
        event_name = 'first_visit'
    GROUP BY
      ymd,
      uuid
  ),
  PageUU AS (
    SELECT
      PARSE_DATE("%Y%m%d", event_date) AS ymd,
      REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
      user_pseudo_id AS uuid
    FROM
      `<project>.<dataset>.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))      
    AND
      event_name = 'page_view'
  ),
  JoinSeg AS (
    SELECT
      u.ymd,
      u.page_location,
      u.uuid AS _new_uuid
    FROM
      PageUU AS u
    INNER JOIN
      Seg_NewUU AS s
    ON
      u.ymd = s.ymd
    AND
      u.uuid = s.uuid
  ),
  NewUU AS (
    SELECT
      ymd,
      page_location,
      COUNT(DISTINCT _new_uuid) AS _new_uu
    FROM
      JoinSeg
    GROUP BY
      ymd,
      page_location
    ORDER BY
      ymd ASC,
      _new_uu DESC
  )

SELECT
  *
FROM
  NewUU

ChatGPTによるクエリ解説

1. Seg_NewUU CTE（共通テーブル式）

  • このCTEは、指定された期間（2024年4月1日から現在の日付の前日まで）にウェブサイトを初めて訪れたユーザー（イベント名が first_visit）の日付（ymd）とユーザーID（uuid）を抽出します。
  • これにより、新規ユーザーのリストを作成し、その日にどのユーザーが訪問したかが分かります。

2. PageUU CTE

  • このCTEでは、上記と同じ期間にすべてのページビューイベント（イベント名が page_view）を取得します。ここでの目的は、ユーザーがどのページを訪れたかとその日付を記録することです。
  • page_locationはURLからクエリパラメータを除去しています。

3. JoinSeg CTE

  • ここで、Seg_NewUUから取得した新規ユーザーリストとPageUUから取得したページビューリストを結合します。これにより、新規ユーザーがその日にどのページを訪れたかが分かります。

4. NewUU CTE

  • 最後に、結合されたデータを日付 (ymd) とページ (page_location) ごとにグループ化し、各ページに対する新規ユーザーの数（_new_uu）をカウントします。
  • 結果は日付と新規ユーザー数の多い順に並べられます。

結果

  • 最終的にこのクエリは、特定の期間における各ページの新規ユーザー数を日付ごとにリストアップします。これにより、どのページが新規訪問者にとって魅力的かが分かり、マーケティングやコンテンツ戦略の改善に役立てることができます。

探索でよくみられる新規ユーザー数の誤った使い方

よく新規ユーザー数の指標について誤解しているのをみかける。例えばディメンションにページ、指標に新規ユーザー数を並べた場合には、ページごとに新規ユーザーが閲覧したページは確認できない。新規ユーザー数は’first_visit’イベントをカウントした指標であるため、ページのディメンションに新規ユーザー数の指標を並べても、実質ランディングページに着地した新規ユーザー数が集計されるためだ。そのため、新規ユーザーがページを閲覧しても、この探索の並べ方では新規ユーザーが閲覧したページは集計されない。

セッションセグメントを適用してセッションを新規訪問のみに限定する

上記で解説したユーザーセグメントを適用させる方法では、セッションスコープを扱う上では少々扱いづらいケースに出くわすことがある。また、日付とユーザーIDで結合させるため、少し難しく感じるかもしれない。そのため、ユーザーセグメントではなく、新規ユーザーのセッションセグメントを適用させる方法をここでは解説する。

新規ユーザーのセッションセグメントとは

GA4の定義では’first_visit’のイベントが付き新規訪問となっても、その日に2回目以降のセッションでは新規訪問ではなく、リピーターとして扱われる。この定義だと、集計上ややこしくなるのと直感にも反するため、その日に’first_visit’が付いたユーザーは、その日のうちに何回セッションが変わっても新規訪問者とみなすというのが、再定義したディメンションというのは既に述べた。

このカスタマイズした定義の上で、セッションセグメントを定義すると、’first_visit’が付いたその日のセッションは全て「新規セッション」とみなすことができる。そのため、新規ユーザーのセッションセグメントでも、日にち単位で新規ユーザーセグメントを適用させても結果は同じになる。方法としては、どちらでも構わないが、セッションスコープを扱う場合は、セッションセグメントを適用させた方がクエリを複雑化せずに済むかもしれない。

新規ユーザーのセッションセグメントを生成するクエリ

‘first_visit’が付いた訪問日の全てのセッションを新規セッションとするセグメント

WITH
  SEG_FirstVisitSession AS (
    SELECT
      DISTINCT ssid
    FROM (
      SELECT
        ssid,
        ymd,
        CASE
          WHEN ymd = first_visit_date THEN 'first_visit'
          ELSE 'subsequent_visit'
        END AS visit_type
      FROM (
        SELECT
          CONCAT(e.user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
          PARSE_DATE("%Y%m%d", e.event_date) AS ymd,
          first_visit.ymd AS first_visit_date
        FROM
          `<project>.<dataset>.events_*` AS e
        INNER JOIN (
          SELECT
            user_pseudo_id,
            MIN(PARSE_DATE("%Y%m%d", event_date)) AS ymd
          FROM
            `<project>.<dataset>.events_*`
          WHERE
            _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
            AND event_name = 'first_visit'
          GROUP BY
            user_pseudo_id
        ) AS first_visit
        ON e.user_pseudo_id = first_visit.user_pseudo_id
        WHERE
          e._TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      )
    )
    WHERE
      visit_type = 'first_visit'
)

SELECT
  *
FROM
  SEG_FirstVisitSession

ページ別訪問ユーザーに新規ユーザーのセッションセグメントを適用させるクエリ

日にち単位の新規ユーザーセグメントではなく、セッションセグメントを適用させたバージョンを提示する。結果は同じになる。

WITH
  SEG_FirstVisitSession AS (
    SELECT
      DISTINCT ssid
    FROM (
      SELECT
        ssid,
        ymd,
        CASE
          WHEN ymd = first_visit_date THEN 'first_visit'
          ELSE 'subsequent_visit'
        END AS visit_type
      FROM (
        SELECT
          CONCAT(e.user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
          PARSE_DATE("%Y%m%d", e.event_date) AS ymd,
          first_visit.ymd AS first_visit_date
        FROM
          `<project>.<dataset>.events_*` AS e
        INNER JOIN (
          SELECT
            user_pseudo_id,
            MIN(PARSE_DATE("%Y%m%d", event_date)) AS ymd
          FROM
            `<project>.<dataset>.events_*`
          WHERE
            _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
            AND event_name = 'first_visit'
          GROUP BY
            user_pseudo_id
        ) AS first_visit
        ON e.user_pseudo_id = first_visit.user_pseudo_id
        WHERE
          e._TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      )
    )
    WHERE
      visit_type = 'first_visit'
  ),
  PageUU AS (
    SELECT
      ymd,
      page_location,
      uuid AS _new_uuid
    FROM (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS ymd,
        REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
        user_pseudo_id AS uuid,
        CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))    
      AND
        event_name = 'page_view'
    ) AS event_data
    WHERE
      event_data.ssid IN (SELECT ssid FROM SEG_FirstVisitSession)
  ),
  NewUU AS (
    SELECT
      ymd,
      page_location,
      COUNT(DISTINCT _new_uuid) AS _new_uu
    FROM
      PageUU
    GROUP BY
      ymd,
      page_location
    ORDER BY
      ymd ASC,
      _new_uu DESC
  )

SELECT
  *
FROM
  NewUU

関連記事

【GA4 SQL】ページごとのページビュー数をBigQueryから集計

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

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