GA4×BigQueryでCTR（クリック率）を計測するSQLクエリ

Webサイトを運営していく上で、特定のリンクや広告が表示された際にどれだけの割合でクリックされるか（CTR：Click Through Rate）は、売上に直結してくる重要な指標です。

アクセスは取れているけどCTRが低くてコンバージョンにつながっていない記事は、改善の重要度が高い記事として認識することができ、CTR改善へ活かせます。

本記事では、その「CTR」をGA4×BigQueryで計測するSQLクエリについて紹介していきます。

本記事の筆者

国立理系修士2年（23歳 | 専攻は機械学習）
SEOマーケティング会社で実務を1年ほど経験
ブログ運営3年目（月間10,000PVほど）
2025年4月からIT事業会社のデータサイエンティストとして働く予定で、現在はBigQueryを活用したGA4やGSCのデータ分析に注力

GA4×BigQueryにおける基本的なSQLの使い方は下記記事で紹介してますので、参考までにどうぞ<(_ _)>
» GA4とBigQueryを連携するメリット& 基本的なSQLの使い方を解説

また、SQL（BigQuery）スキルを習得するために以下のUdemy講座はおすすめです。
» BigQuery で学ぶ非エンジニアのための SQL データ分析入門

GA4×BigQueryで内部リンク遷移に対するCTR（クリック率）を計測するSQLクエリ

ここでは、内部リンク遷移に対するCTRを計測するSQLクエリについて紹介していきます。（CTRというか遷移率）

事業サイトやオウンドメディアの運営者向けに役立つクエリとなっているかなと思います。個人ブログ向けにアフィリエイトに役立つSQLクエリは後述してますので、こちらをどうぞ。

SQLクエリ

with transition_event as (
  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 = 'page_referrer') as page_referrer
  from
    `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    _table_suffix between '20240301' and '20240331'
    and (select value.string_value from unnest(event_params) where key = 'page_referrer') is not null
    and event_name = 'page_view'
),

page_views as (
  select
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
    count(*) as pv_count
  from
    `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    _table_suffix between '20240301' and '20240331'
    and event_name = 'page_view'
  group by
    page_location
)

select
  te.page_referrer,
  te.page_location,
  count(te.page_referrer) as page_transition,
  pv_count, -- page_referrerのURLに対するPV数
  count(te.page_referrer) / pv_count * 100 as transition_rate
from
  transition_event te
  join page_views pv
  on te.page_referrer = pv.page_location
where
  te.page_referrer like '%sgs-prog%'
  and te.page_location = 'https://sgs-prog.com/contact/'
group by
  page_referrer,
  page_location,
  pv_count
order by
  pv_count desc
;

page_referrer（リンク元）からpage_location（リンク先）へ遷移した数を集計して、page_referrerのURLに対するPV数で割った数をCTR（transition_rate）としています。

SQLクエリや文字だけを読んでも分かりづらいと思うので、下記の実行結果をみていきましょう。

実行結果

上記はリンク先（page_location）を当サイトのお問い合わせページにした際のCTR（transition_rate）を集計しています。

page_transitionはリンク元URL（page_referrer）からリンク先（page_location）に遷移した数でして、pv_countはリンク元URL（page_referrer）のPV数となっています。

後は、page_transition / pv_count × 100（%）をしてCTR（transition_rate）を求めているだけです。

個人ブログを運営している身からしたら「お問い合わせページに対するCTR」はまったく気にしていませんが、何かしらサービスを提供している企業サイトでは、どのページからどれくらい “お問い合わせページ” や “資料請求ページ” へ遷移したか？は大事になってくるはずなので、参考にしていただければと思います。

GA4×BigQueryで外部リンク遷移に対するCTR（クリック率）を計測するSQLクエリ

ここからは個人ブログの運営者向けに、外部リンク遷移（アフィリエイトリンクなど）のCTRを計測するSQLクエリについて紹介していきます。

SQLクエリ

with click_event as (
  select
    (select value.string_value from unnest(event_params) where key = 'page_title') as page_title,
    (select value.string_value from unnest(event_params) where key = 'link_url') as click_link
  from
    `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    _table_suffix between '20240301' and '20240331'
    and event_name = 'click'
),

page_views as (
  select
    (select value.string_value from unnest(event_params) where key = 'page_title') as page_title,
    count(*) as pv_count
  from
    `ga4-bigquery-pj-392206.analytics_342165491.events_*`
  where
    _table_suffix between '20240301' and '20240331'
    and event_name = 'page_view'
  group by
    page_title
)

select
  ce.page_title,
  click_link,
  count(*) as count_of_click,
  pv_count,
  count(*) / pv_count * 100 as click_rate
from
  click_event ce
  join page_views pv
  on ce.page_title = pv.page_title
where
  click_link like '%a8%'
  or click_link like '%moshimo%'
group by
  page_title,
  click_link,
  pv_count
order by
  count_of_click desc,
  click_rate desc
;

上記SQLクエリでは、A8.netともしもアフィリエイトのリンク遷移のみを取得して、各ページごとにCTRを計測しています。