GA4で外部リンクのクリック数を集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、外部リンクへのクリック数を集計するSQLクエリについて解説をおこなう。UAでは外部リンクのクリックをトラッキングするためには、カスタムイベントを設定する等の工数が発生していたが、GA4ではclickが拡張計測機能による自動収集イベントに含まれているため、データ取得が簡単になっている。

ぜひSQLを書いて活用していきたい。

SQLクエリの概要

まずGA4では、ユーザーが現在のドメインから移動するリンクをクリックするたびに clickイベント（離脱クリック）が測定される。そしてclick イベントが発生すると、それに付随する以下の各種パラメータが event_params に格納される。

また、下記のSQLクエリサンプルでは以下3つのパラメータを使用している。

page_location : 外部リンクのクリックが発生したページ
link_domain : リンク先のドメイン
link_url : リンク先のURL

そして、COUNT IF で outbound = true の場合のみ外部クリックとしてカウントする。

SQLクエリサンプル

今回のSQLクエリは、以下のようになる。

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_domain') AS link_domain,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_url') AS link_url,
  COUNTIF((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'outbound') = 'true') AS click_count,
FROM
  `technogram-354804.analytics_291973511.events_intraday_*`
WHERE
  event_name = 'click'
GROUP BY
  page_location,
  link_domain,
  link_url
ORDER BY
  click_count DESC
;

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになった。

ChatGPT（GPT-4モデル）によるSQL解説

ChatGPTによる解説をつけるので、学習の参考にしてほしい。

このSQLクエリは、ページ上でのクリックイベントを分析し、各ページ上の各リンクがクリックされた回数をカウントするものです。

まず、SELECT句で以下の情報を選択します：

1.ページのロケーション（URL）：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘page_location’) AS page_location。これはevent_paramsという配列型のカラムから、キーがpage_locationのものを探し出し、その値を取得しています。

2.リンクのドメイン：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘link_domain’) AS link_domain。これはevent_paramsから、キーがlink_domainのものを探し出し、その値を取得しています。

3.リンクのURL：(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘link_url’) AS link_url。これはevent_paramsから、キーがlink_urlのものを探し出し、その値を取得しています。

4.アウトバウンド（外部サイトへの）クリックのカウント：COUNTIF((SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘outbound’) = ‘true’) AS click_count。これはevent_paramsから、キーがoutboundのものを探し出し、その値がtrueであるものの数をカウントしています。

そしてFROM句で、データセットtechnogram-354804.analytics_291973511.events_intraday_*からデータを取得します。ここで*を使って、events_intraday_で始まるすべてのテーブルを対象にしています。

また、WHERE句で、イベント名がclickであるレコードのみを対象にしています。

さらにGROUP BY句で、結果をページのロケーション、リンクのドメイン、リンクのURLによりグループ化します。これにより、同じページの同じリンクに対するクリックは一つにまとまります。

最後にORDER BY句で、結果をクリックカウントの降順に並べます。これにより、最もクリックされたリンクが先頭に表示されます。

このクエリを実行することで、各ページ上の各リンクがクリックされた回数を取得できます。これは、ウェブサイトやアプリのユーザー行動を理解し、UI/UXの改善やマーケティング戦略の策定に役立つ情報です。

今回の記事では、外部リンクへのクリック数を集計するSQLクエリについて解説を行った。GA4で外部リンククリック数を集計する際のSQLを書く際に参考にして欲しい。

GA4で外部リンクのクリック数を集計するSQLクエリ | GA4QUERY

SQLクエリの概要

SQLクエリサンプル

BigQueryでの集計結果

ChatGPT（GPT-4モデル）によるSQL解説