コンテンツに進む

日別の直帰率を集計する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が集計できました。

日別の直帰率を集計するSQLクエリを書いてLooker Studioでグラフにする|GA4×BigQuery

ここで計測している直帰率はUAとは若干、定義が異なります。

ご存知のようにGA4では「エンゲージメント」というコンセプトに基づいて計測されているため、「サイトに訪問>そのページだけ見る>離脱」=「直帰」というユーザ行動はUAの時ほど重視されていません。

「サイトに訪問して、そのページをちゃんと読んで、目的を達成してそのページだけで離脱した」ユーザを「1ページだけしか見てないからよろしくない」というネガティブな意味の「直帰」にカテゴライズしてしまうとGA4の「エンゲージメント」というコンセプトとは齟齬がありますよね。

ですので、ここでの「直帰」とは「エンゲージメントしていない」ユーザを「直帰」というUA時代の表現で集計しています。

上のSQLでは
(「エンゲージメントしたセッション」ー「エンゲージメントしていないセッション」)/セッション
という計算で直帰率を算出しています。

過去28日間の折れ線グラフにしてみました。薄いブルーの折れ線が前の期間のラインです。

 前の期間の後半から直帰率が低下しているのがわかります。

もう少し長期間で直帰率の推移を見てみましょう。このデータセットにある全期間(92日)の推移です。

やはり劇的に直帰率が改善されているのがわかります。どういった施策を実施したのでしょうか? 分析してみたいですね。 

以上、BigQuery連携したGA4の日別の直帰率を集計するSQLクエリを書いて、Looker Studioでシンプルな折れ線グラフにするサンプルでした。

GA4やBigQuery連携したGA4のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。

記事に戻る