GA4の会員/非会員のユニークユーザー数をSQLで取得する実例

BigQuery

and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太

導入

会員制サイトを分析するにあたり、会員/非会員軸で分析をすることは多々あります。
そこで今回はGA4とBigQueryの現場で非常によく使われる会員/非会員軸の実践的な方法をご紹介いたします。

この記事の対象

  • 会員制サイトのアナリストでSQLが不慣れな方
  • A4の会員/非会員UU数を抽出し、分析に役立てたい方

前提の実装

サイト側に生成されている会員IDをGA4で計測できるようにする準備をします。
※会員IDのサイト側への生成方法はサイトによって異なるため今回は詳細解説を省きます。

はじめにGTM側で会員IDをGA側に送れるように設定をします。

ポイントは「設定フィールド」にuser_idを指定します。
こちらの設定で会員IDをGA4に送り、GA4側で認識できるようになりました。
このIDをGA4が受け取ることでGA4側でサイトに生成された会員IDを識別できるようになります。
BigQueryに反映されるのはGTMの設定を反映した次の日からとなりますのでご注意ください。

※会員IDの取り扱いについては個人情報が特定できない範囲で取得をしてください

BigQueryで実際に会員/非会員を出力してみる

STEP1

では早速、先ほどの会員IDを出力するSQLです。
※以降、登場する以下の`ga4-quick.analytics_302974976.events_*`の箇所をお使いのGA4のデータセット名に置換し、日付の箇所を調整するだけで簡単にお使い頂けます。

SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
    user_id,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM `ga4-quick.analytics_302974976.events_*`
  WHERE
  _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
  AND platform = 'WEB'
  AND user_id IS NOT NULL

解説

一見複雑そうに見えますが重要なのはこの1文だけです。

 AND user_id IS NOT NULL  

この1文で会員だけのデータを抽出できます。
コードを日本語に直すとわかりやすいと思います。
「user_idがNULLじゃない対象」を検索しているだけです。

  AND user_id IS NULL  

逆に言ってしまえばNULLなら非会員です。簡単です。

STEP2

では、先ほどのSQLを少し発展させてみましょう。
会員/非会員のユーザー数を取得します。

WITH
GA_web_tbl AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM `ga4-quick.analytics_302974976.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
    AND platform = 'WEB'
),
_member_raw_tbl AS (
  SELECT
    DISTINCT hitdate,
    user_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NOT NULL
),
member_tbl AS (
  SELECT
    DISTINCT hitdate,
    COUNT(DISTINCT user_id) OVER(PARTITION BY hitdate) AS member_count,
  FROM _member_raw_tbl
),
_no_member_web_tbl AS (
  SELECT
    DISTINCT hitdate,
    COALESCE(user_id, '0') AS user_id,
    user_pseudo_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NULL
  AND NOT EXISTS (
    SELECT * FROM _member_raw_tbl WHERE sid = GA_web_tbl.sid
  )
),
no_member_tbl AS (
  SELECT
    DISTINCT hitdate,
    COUNT(DISTINCT user_pseudo_id) OVER(PARTITION BY hitdate) AS no_member_count,
  FROM _no_member_web_tbl
)

SELECT *
FROM member_tbl AS t1
LEFT JOIN no_member_tbl AS t2 USING (hitdate)

WITH句が出てきて記述量が増え、複雑に見えますが大したことはしていません。

解説

WITH句のサブクエリごとに解説していきます。
GA_web_tbl についてはGAからデータを抽出してきているため省略します。

_member_raw_tbl AS (
  SELECT
    DISTINCT hitdate,
    user_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NOT NULL
),

こちらの出力結果は日本語に直すと「user_idがNULLではないレコードのhitdate, user_id, sidをGA_web_tblから取得。ただしhitdateは重複なし」という形です。

member_tbl AS (
  SELECT
    DISTINCT hitdate,
    COUNT(DISTINCT user_id) OVER(PARTITION BY hitdate) AS member_count,
  FROM _member_raw_tbl
),

こちらは集計のためのテーブルで、hitdateごとにuser_idをカウントして出力します。今回は対象の2022/09/01なので結果は1行となります。

COUNT(DISTINCT user_id) OVER(PARTITION BY hitdate) AS member_count,

こちらの構文については下記に詳細に記載されています。​​https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls?hl=ja#syntax

使い方が難しいと感じる方もいらっしゃるかもしれませんが思いの外簡単です。日本語に直すと「user_idを重複なしで日毎にカウントする」という感じです。

_no_member_web_tbl AS (
  SELECT
    DISTINCT hitdate,
    COALESCE(user_id, '0') AS user_id,
    user_pseudo_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NULL
  AND NOT EXISTS (
    SELECT * FROM _member_raw_tbl WHERE sid = GA_web_tbl.sid
  )
),

次のテーブルは前述の_member_raw_tblとよく似ています。
違いはuser_IdがNULL(非会員)であることとEXISTS演算子の登場です。
また、user_idをNULL(非会員)で検索をかけていますNULLの取り回しが悪いので、念のためCOALESCE関数で文字列の0として置換します。

  AND NOT EXISTS (
    SELECT * FROM _member_raw_tbl WHERE sid = GA_web_tbl.sid
  )

こちらは、_member_raw_tblのsidとGA_web_tblのsidが一致していたら結果に含まないようにするという処理を行なっています。つまりこれで、user_idの無いGA_web_tblから_member_raw_tblのsidと一致しないものを抽出しています。

EXISTS演算子の使い方についてはこちら
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#exists_operator

ここでのポイントは、sidで除外をしているという点です。
考え方が非常に重要なのですが、1回のセッション内でuser_idが存在した場合、そのセッション全てを会員とみなすという考え方が根本になっています。
例えば、1回のセッション内でuser_idが変更された(=ログインがあった)場合は別のユーザーとして計測するという考え方の場合は別のSQLが必要になってきます。

no_member_tblはmember_tblとほぼ同じなので割愛します。

最後のクエリです

SELECT *
FROM member_tbl AS t1
LEFT JOIN no_member_tbl AS t2 USING (hitdate)

これで下記のような結果が出ます。

hitdatemember_countno_member_count
2022-09-01会員UU数非会員UU数

GA4の非サンプリングデータが欲しい場合や、UUを軸にトラフィックを分析をする場合などに役に立つと思います。

GA4のSQLについてご支援をご希望の方は、お気軽にand,a(このサイトの運営元)までご連絡ください。

関連記事

特集記事

コメント

この記事へのコメントはありません。

ランキング(週間)

  1. 1

    【UA→GA4】プロパティ 自動作成  2023.3.1 GA4 補完計画

  2. 2

    GA4 × ChatGPT 爆速!GA4 設定 & 解析 革命 第1回

  3. 3

    GA4の指標「総ユーザー数」VS「アクティブ ユーザー数」

  4. 4

    ユニバーサル アナリティクス終了 過去データはどうなる?

  5. 5

    GA4 「ページ ロケーション」と「ページ パス と スクリーン クラス」何が違う?ドメインの有無以外にもパラメータの扱いに違いあり

  6. 6

    GA4の探索で「ページ別訪問数」を見ることは可能か?

  7. 7

    GA4 レポート用識別子、user_id の活用

  8. 8

    GA4 & Looker Studio(旧データポータル) 「割り当てエラー」問題 原因と打開策「データの抽出機能」(2023年1月14日 修正)

  9. 9

    GA4 拡張計測機能の新顔「フォームの操作」 form_start, form_submit

  10. 10

    GA4で異なるドメイン(サブドメイン)の同一ページパスをドメイン付きで表示する

最近の記事

  1. GA4 × ChatGPT 爆速!GA4 設定 & 解析 革命 第1回

  2. ユニバーサル アナリティクス終了 過去データはどうなる?

  3. 【UA→GA4】プロパティ 自動作成  2023.3.1 GA4 補完計画

カテゴリー

 
TOP