GA4×BigQueryで外部リンクの遷移先を取得するSQLクエリ

BigQueryでGA4データを扱うと、簡単にどのページでいつ外部リンクに遷移したかを把握することができます。

用途としては、アフィリエイト成果の発生元（CVリファラ）を特定するなどです。CVリファラを特定することで成果が出やすい記事を把握することができ、改善に注力するべき記事が見えてきます。

本記事では、それを実現するためのSQLクエリを紹介していきます。

GA4×BigQueryで外部リンクの遷移先を取得するSQLクエリ

さっそく下記にSQLクエリを紹介していきます。

SQLクエリ

/*
外部リンクの遷移先取得
*/

select
  user_pseudo_id,
  (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,
  format_timestamp('%Y-%m-%d %H:%M:%S', timestamp_micros(event_timestamp), 'Asia/Tokyo') as jst_time
from
  `ga4-bigquery-pj-392206.analytics_342165491.events_*` 
where
  event_name = 'click'
  and _table_suffix between '20240301' and '20240331'
group by
  user_pseudo_id,
  page_location,
  link_url,
  jst_time
order by
  jst_time

WHERE句で期間を指定しており、時系列順にどのユーザーがどのページでどういった外部リンクに遷移したかを把握できるようなSQLクエリです。

実行結果

上記の “link_url” が外部リンクの遷移先URLとなります。

あわせて、どのユーザーであるかを “user_pseudo_id” で、どのページでリンクがクリックされたかを “page_location” で取得できるようにしています。

アフィリエイト成果の発生元（CVリファラ）を特定する方法

ここまでで、外部リンクの遷移先を取得するSQLクエリを紹介しました。

ここからは紹介した分析結果を応用して、A8.netともしもアフィリエイトでのCVリファラを特定する方法についても解説していきます。

A8.netのCVリファラを特定する方法

A8.netでログイン → 「詳細レポート」 → 「確定件数の数字リンク」をクリックしてあげると下記のような表示が出てきます。

上記表の一番左にクリック日があるので、そちらと実行結果の “jst_time” と照らし合わせることでCVリファラを特定することができます。

もしもアフィリエイトのCVリファラを特定する方法

A8.netでのやり方とほぼ一緒なのですが、もしもアフィリエイトでログイン → 「売上レポート」 → 「今月支払いの承認成果を表示」をクリックしてあげると下記のような表示が出てきます。

先ほどと同様、広告クリック日時と実行結果を照らし合わせていきましょう。

CVリファラを特定したら、ユーザー行動も把握しよう

CVリファラを特定したら、成果が発生するまでに至ったユーザー行動を知りたいというモチベーションが生まれてくる方もいるかと思います。

そういった方は “user_pseudo_id” をもとにCVしたユーザーの行動を追跡してみましょう。

詳しくは下記記事にて解説しています。サイトに流入してすぐCVするユーザーもいれば、色んなページに訪れてCVするユーザーもいるので、分析していて面白いですよ。

あわせて読みたい

GA4×BigQueryで特定ユーザーの行動を把握するSQLクエリ GA4のuser_pseudo_idをもとにBigQueryで特定ユーザーの行動を把握するSQLクエリを紹介しています。お問い合わせが入ったなどの重要度の高そうなユーザーに対する行動データを分析することで、Webサイト運営における改善や勝ちパターンの発見に繋がるかもしれないので、ぜひ参考にしてみてください。

まとめ：外部リンクの遷移先を取得して、CVリファラを特定しよう！

本記事では、GA4×BigQueryで外部リンクの遷移先を取得するSQLクエリを紹介しました。

あわせて、応用例としてアフィリエイト成果の発生元（CVリファラ）を特定する方法についても紹介しました。

CVリファラを特定することで成果が発生しやすい記事がわかり、改善すべき記事が見えてくるので、ぜひ試してみてください。

それでは本記事は以上とします。最後まで読んでいただきありがとうございました。

あわせて読みたい

GA4とBigQueryを連携するメリット& 基本的なSQLの使い方を解説 GA4とBigQueryを連携するメリットについて海外情報も交えつつ解説しています。あわせてSQLの基本的な使い方についても紹介しているので、ぜひ参考にしてみてください。