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 この１文で会員だけのデータを抽出できます。
コードを日本語に直すとわかりやすいと思います。
「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なので結果は１行となります。
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で除外をしているという点です。
考え方が非常に重要なのですが、１回のセッション内でuser_idが存在した場合、そのセッション全てを会員とみなすという考え方が根本になっています。
例えば、１回のセッション内でuser_idが変更された（＝ログインがあった）場合は別のユーザーとして計測するという考え方の場合は別のSQLが必要になってきます。
no_member_tblはmember_tblとほぼ同じなので割愛します。
最後のクエリです
SELECT *
FROM member_tbl AS t1
LEFT JOIN no_member_tbl AS t2 USING (hitdate)これで下記のような結果が出ます。
| hitdate | member_count | no_member_count |
| 2022-09-01 | 会員UU数 | 非会員UU数 |
GA4の非サンプリングデータが欲しい場合や、UUを軸にトラフィックを分析をする場合などに役に立つと思います。
コメント