日別の直帰率を集計するSQLクエリを書いてLooker Studioでグラフにする｜GA4×BigQuery
BigQueryに連携したGA4のデータから「日別の直帰率」を集計して、Looker Studioでグラフにしてみましょう。
今回も「Googleが公開しているBigQuery連携したGA4デモデータに接続する｜GA4×BigQuery」で紹介したデモデータに接続します。
BigQueryのコンソールで新しいエディタを開いたら、まずは下のクエリをコピペして「実行」してみてください。
SELECT
event_date,
SAFE_DIVIDE(COUNT(DISTINCT CONCAT(user_pseudo_id,(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'ga_session_id'))) - COUNT(DISTINCT
CASE
WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'session_engaged') = '1' THEN CONCAT(user_pseudo_id,( SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id')) END
),COUNT(DISTINCT CONCAT(user_pseudo_id,(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'ga_session_id')))) AS bounce_rate
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY
event_date
こんな感じにevent_dateごとのbounce_rateが集計できました。
ここで計測している直帰率はUAとは若干、定義が異なります。
ご存知のようにGA4では「エンゲージメント」というコンセプトに基づいて計測されているため、「サイトに訪問＞そのページだけ見る＞離脱」＝「直帰」というユーザ行動はUAの時ほど重視されていません。
「サイトに訪問して、そのページをちゃんと読んで、目的を達成してそのページだけで離脱した」ユーザを「１ページだけしか見てないからよろしくない」というネガティブな意味の「直帰」にカテゴライズしてしまうとGA4の「エンゲージメント」というコンセプトとは齟齬がありますよね。
ですので、ここでの「直帰」とは「エンゲージメントしていない」ユーザを「直帰」というUA時代の表現で集計しています。
上のSQLでは
（「エンゲージメントしたセッション」ー「エンゲージメントしていないセッション」）／セッション
という計算で直帰率を算出しています。
過去28日間の折れ線グラフにしてみました。薄いブルーの折れ線が前の期間のラインです。
前の期間の後半から直帰率が低下しているのがわかります。
もう少し長期間で直帰率の推移を見てみましょう。このデータセットにある全期間（92日）の推移です。
やはり劇的に直帰率が改善されているのがわかります。どういった施策を実施したのでしょうか？ 分析してみたいですね。
以上、BigQuery連携したGA4の日別の直帰率を集計するSQLクエリを書いて、Looker Studioでシンプルな折れ線グラフにするサンプルでした。
GA4やBigQuery連携したGA4のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。
