ブログ

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

### クエリの全体的な流れ

1. **サブクエリで最初の訪問日を特定**:
– `first_visit`というサブクエリを作成し、`user_pseudo_id`ごとに最初の訪問日 (`event_date`) を取得します。この最初の訪問日は、GA4で初めて記録されたイベント `first_visit` に基づいています。

2. **メインクエリでセッションIDを生成**:
– メインクエリでは、各イベントに対してユーザーの `user_pseudo_id` と `ga_session_id` を結合してセッションID (`ssid`) を生成します。
– イベントの日付 (`event_date`) とそのユーザーの最初の訪問日 (`first_visit_date`) を比較して、そのセッションが最初の訪問 (`first_visit`) か、次の訪問 (`subsequent_visit`) かを判定します。

3. **最初の訪問のセッションIDを抽出**:
– `visit_type` が `first_visit` であるセッションIDを抽出し、最終的に `SEG_FirstVisitSession` に結果を格納します。

### クエリの各部分の解説

1. **`WITH SEG_FirstVisitSession AS (…)`**:
– `WITH`句は共通テーブル式 (CTE) を定義して、後で参照できるようにします。ここでは、最初の訪問セッションを取得するためのロジックが定義されています。

2. **`DISTINCT ssid`**:
– これにより、重複するセッションIDが排除され、ユニークなセッションIDのみが選択されます。

3. **`CASE`文**:
– `ymd`がそのユーザーの`first_visit_date`と一致する場合、その訪問を「first_visit」とし、それ以外の訪問を「subsequent_visit」としています。

4. **`CONCAT(e.user_pseudo_id, ‘-‘, CAST((SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = ‘ga_session_id’) AS STRING)) AS ssid`**:
– ユーザーID (`user_pseudo_id`) とセッションID (`ga_session_id`) を結合して、一意のセッションID (`ssid`) を生成しています。これは、ユーザーごとにセッションを識別するために使用されます。

5. **`INNER JOIN`**:
– この部分では、各イベント (`e`) を最初の訪問日 (`first_visit`) と結びつけ、どのイベントが最初の訪問であるかを判断します。

6. **`_TABLE_SUFFIX`**:
– このフィールドは、どのテーブルが対象であるかを動的に決定するために使用されます。`_TABLE_SUFFIX BETWEEN ‘20240401’ AND …` によって、特定の期間内のテーブルだけを対象にクエリが実行されます。

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

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

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】2つのevent_timestampの日付比較

【GA4SQL】イベント・パラメータの対応一覧を確認

【GA4SQL】最新のページタイトル（page_title）を取得

おすすめ記事

書評『BigQueryではじめるSQLデータ分析』木田和廣

【GA4 SQL】アクティブユーザー数の定義と総ユーザー数との違い

【GA4 SQL】ページの前後をBigQueryから取得
最近の記事
おすすめ記事

  1. 【Looker Studio】GA4ランディングページのページタイトル取得

  2. 【GTM】ページ遷移(特定のページ経由)のトリガーを作成しタグを発火させる手順

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

  1. 【GA4 SQL】初回訪問から初回CVまでの日数をBigQueryから取得

  2. 【GA4SQL】日次のセッション数をBigQueryから取得するクエリ

  3. 初心者におすすめ「分析SQL本」のレビュー【2024年随時更新】

目次

PAGE TOP