BigQuery
GA4×BigQuery

【BigQuery×GA4】外部リンクURLとそのクリック率を出すSQLクエリ

【BigQuery×GA4】外部リンクURLとそのクリック率を出すSQLクエリ BigQuery

本記事では、BigQueryにエクスポートしたGA4のデータから、外部リンククリックが発生したページと遷移先URLの組み合わせごとに、クリック数やクリック率を抽出するSQLクエリを紹介する。

クリック率の定義とカウント方法

本記事で言及する外部リンクのクリック率は、以下のようにして計算する。

クリック率（%）　=　clickイベントoutboundパラメータの数　÷　クリック元ページの表示回数　×　100

ここで、clickイベントとかoutboundパラメータとかよくわからない単語が出てきたかと思うのでそれぞれ解説する。

まずclickイベントとは、ユーザーが外部サイトへつながるリンクをクリックした際に自動で計測されるイベントのことで、GA4の拡張計測機能をONにするだけで取得できる（GTMによる計測設定やGA4上でのイベント作成等は不要）。

そしてoutboundパラメータだが、これは通常clickイベントに紐づくイベントパラメータで、外部リンクのクリックが発生すると「outbound = true」が記録される。
下は、clickイベントが発生した際に格納されるデータのスキーマである。

なぜclickイベントだけでなくoutboundパラメータまで考慮する必要があるのかというと、clickイベントはまれに外部リンククリックではないのに発生してしまうことがあるからだ。
原因については計測実装状況の問題などいろいろあるのだが、いずれにしろ、clickイベントの数を単に数えるだけでは実際の数よりも膨らんでしまう可能性があるため、outboundパラメータの数と一緒にカウントするのがより正確かつ確実な方法である。

このように、特定のページが何回表示され、そのうち、clickイベントのoutboundパラメータが何回発生したかが分かれば、これらを割り算することで外部リンクのクリック率が算出できるというわけだ。

外部リンククリックが発生したページと遷移先URLの組み合わせごとにクリック率を出すSQLクエリ

WITH page_views AS (
  -- 1. ページごとの表示回数（PV）を計算
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS page_location,
    COUNT(*) AS pageviews
  FROM `analytics_280494569.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<dataset>.events_*`）
  WHERE event_name = "page_view"
    AND _table_suffix BETWEEN "20240801" AND "20241130" -- ご自身で抽出したい期間に変更する
  GROUP BY 1
),

outbound_clicks AS (
  -- 2. ページごとの外部リンククリック数を計算
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "link_url") AS link_url,
    COUNT(*) AS clicks
  FROM `analytics_280494569.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<dataset>.events_*`）
  WHERE event_name = "click"
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "outbound") = "true"
    AND _table_suffix BETWEEN "20240801" AND "20241130" -- ご自身で抽出したい期間に変更する
  GROUP BY 1, 2
)

-- 3. 2つのテーブルを結合してクリック率（CTR）を算出
SELECT
  c.page_location,
  c.link_url,
  v.pageviews,
  c.clicks,
  ROUND(c.clicks / v.pageviews * 100, 1) AS ctr_percent
FROM outbound_clicks AS c
INNER JOIN page_views AS v ON c.page_location = v.page_location
ORDER BY clicks DESC;

クエリ結果は下のようになる。

クエリ解説

クエリの構成は以下の3つのステップに分かれている。

  1. ページごとの表示回数を抽出したテーブル（page_views）をつくる
  2. ページと外部リンクURLの組み合わせごとに外部リンククリック数を抽出したテーブル（outbound_clicks）をつくる
  3. 2つのテーブルを内部結合し、クリック率を算出する
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS page_location,
    COUNT(*) AS pageviews
 -- 中略 --
  WHERE event_name = "page_view"

ページURLを抽出するには、「event_params」 というネストされた構造から 「page_location」の値を参照する。

またWHERE句では「event_name = “page_view”」としており、page_viewイベントに限定したデータに絞っているため、COUNT(*)はページビュー数を算出する。

  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "link_url") AS link_url,
    COUNT(*) AS clicks
-- 中略 --
  WHERE event_name = "click"
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "outbound") = "true"

リンク先URLを抽出するには、「event_params」というネストされた構造から「link_url」の値を参照する。

またWHERE句では、「event_name = “click”」でclickイベントに限定したデータに絞っており、さらに、「event_params」 というネストされた構造から 「outbound」の値を参照し、それが“true”に合致した場合のデータに絞っているため、COUNT(*)は外部リンククリック数を算出する。

SELECT
  c.page_location,
  c.link_url,
  v.pageviews,
  c.clicks,
  ROUND(c.clicks / v.pageviews * 100, 1) AS ctr_percent
FROM outbound_clicks AS c
INNER JOIN page_views AS v ON c.page_location = v.page_location

ステップ2でつくったoutbound_clicksテーブルを“c”、ステップ1でつくったpage_viewsテーブルを“v”と名づけ、「page_location」列を結合キーとして内部結合（INNER JOIN）する。

あとは各テーブルから必要なカラムを抽出し、クリック率は、百分率になるかたちで小数点第一位まで算出している。
ROUND関数は、値を指定した桁数で丸めるための関数で、ROUND(フィールド名, 桁数)という記述方法をする。

おわりに

今回は外部リンクのクリック率を、clickイベントかつoutboundパラメータの数を参考にして抽出する最も厳密なやり方を紹介した。

正確なデータを取り出すには単にSQLが書けるだけでなく、どのイベントにどんなパラメータが紐づいているかに対する深い理解が必要になるので、この機会にGA4のデータ構造をあらためておさらいしておくのもよいだろう。

タイトルとURLをコピーしました