BigQuery連携したGA4のデータから「日別の初回訪問ユーザー／２回以上訪問ユーザー」を集計してLooker Studioでグラフにしてみましょう。
今回の「初回訪問ユーザー」「２回以上ユーザー」とは、探索レポートの「初回 / 既存」とは異なります。
※探索レポートの「初回 / 既存」の定義についてはこちらを参照ください。https://reposub.jp/blogs/ga4/ga4_new_established_users
今回も「Googleが公開しているBigQuery連携したGA4デモデータに接続する｜GA4×BigQuery」で紹介したデモデータに接続します。
まずはBigQueryのコンソール画面で下のSQLを実行してみてください
user_pseudo_idごとのga_session_numberの数をカウントしていきます。
WITH
prep AS (
SELECT
event_date,
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'ga_session_id') AS session_id,
(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'ga_session_number') AS session_number,
MAX((
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'engagement_time_msec')) AS engagement_time_msec
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY
event_date,
user_pseudo_id,
session_id,
session_number)
SELECT
event_date,
COUNT(DISTINCT
CASE
WHEN session_number = 1 AND engagement_time_msec > 0 THEN user_pseudo_id
ELSE
NULL
END
) AS new_users,
COUNT(DISTINCT
CASE
WHEN session_number > 1 AND engagement_time_msec > 0 THEN user_pseudo_id
ELSE
NULL
END
) AS returning_users
FROM
prep
GROUP BY
event_date
こんな感じにクエリ結果が得られると思います。
Looker Studioに読み込んでグラフにしてみましょう。
簡単な折れ線グラフにするとこんな感じになります。
対象の期間を最大にして積み上げ棒グラフにするとこんな感じになります。
以上、「日別の初回訪問ユーザー／２回以上訪問ユーザー」を集計するSQLを書いてLooker Studioで可視化する解説でした。
