条件違いを一つのテーブルにJOINしたい
全UU・月内新規UU・前月既存UUを一つのテーブルに
下記のようなテーブルを作成したいときに使います
| event_date | all_uu | new_uu | repeat_uu |
|---|---|---|---|
| 2022-04-01 | 50 | 20 | 30 |
| 2022-04-02 | 30 | 10 | 20 |
方法①新規ユーザーと既存ユーザーそれぞれテーブルを新しく作る
WITH tmp AS(--テンプレートテーブルを作成
SELECT DISTINCT
DATE(timestamp_micros(event_timestamp),"Asia/Tokyo") AS event_date
,user_pseudo_id
,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_number") AS ga_session_number
FROM テーブル名
WHERE _TABLE_SUFFIX BETWEEN "20220401" AND "20220402"
AND
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_number") IS NOT NULL
),new_uu AS(--新規ユーザーテーブルを作成
SELECT
event_date,
user_pseudo_id
FROM tmp
WHERE ga_session_number=1
),repeat_uu AS(--既存ユーザーテーブルを作成（セッションナンバーが1以外）
SELECT
event_date,
user_pseudo_id
FROM tmp
WHERE ga_session_number <> 1
)
SELECT
tmp.event_date,
COUNT(DISTINCT tmp.user_pseudo_id) AS all_uu,
COUNT(DISTINCT new_uu.user_pseudo_id) AS new_uu,
COUNT(DISTINCT repeat_uu.user_pseudo_id) AS repeat_uu,
FROM tmp
LEFT JOIN new_uu
ON new_uu.event_date=tmp.event_date
LEFT JOIN repeat_uu
ON repeat_uu.event_date=tmp.event_date
GROUP BY 1
問題点
指定期間内の新規ユーザー、かつ再訪したユーザーが新規と既存どちらにも集計され重複する
方法②全ユーザーから新規ユーザーを差し引いたテーブルを作る
WITH tmp AS(--テンプレートテーブル（全ユーザー）を作成
SELECT DISTINCT
DATE(timestamp_micros(event_timestamp),"Asia/Tokyo") AS event_date
,user_pseudo_id
,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_number") AS ga_session_number
FROM テーブル名
WHERE _TABLE_SUFFIX BETWEEN "20220401" AND "20220402"
AND
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_number") IS NOT NULL
),new_uu AS(--新規ユーザーテーブルを作成
SELECT
event_date,
user_pseudo_id
FROM tmp
WHERE ga_session_number=1
),repeat_uu AS(--全ユーザーテーブルから新規ユーザーテーブルを引く（全ユーザー - 新規ユーザー）
SELECT event_date,
user_pseudo_id
FROM tmp --テンプレートテーブル（全ユーザー）
EXCEPT DISTINCT
SELECT event_date,
user_pseudo_id
FROM new_uu --新規ユーザーテーブル
)
SELECT
tmp.event_date,
COUNT(DISTINCT tmp.user_pseudo_id) AS all_uu,
COUNT(DISTINCT new_uu.user_pseudo_id) AS new_uu,
COUNT(DISTINCT repeat_uu.user_pseudo_id) AS repeat_uu,
FROM tmp
LEFT JOIN new_uu
ON new_uu.event_date=tmp.event_date
LEFT JOIN repeat_uu
ON repeat_uu.event_date=tmp.event_date
GROUP BY 1
問題点解決
指定期間内の新規ユーザー、かつ再訪したユーザーが重複されずに済む（新規のみに集計される）