こんにちは、High Linkでデータエンジニアをしている谷口(@ytaniguchi811)です。
私たちは、Webサービス「カラリア 香りの定期便」を運営しており、ユーザーの行動データを基にサービス改善を日々行っています。
その中でも、ユーザーがどんな経路でサイトに訪問したかという情報を分析するために「Googleアナリティクス4」(以下、GA4)とBigQueryを組み合わせて利用しています。
ユーザーの流入経路の定義はさまざまですが、私たちはその中でも「セッションごとの最後の流入チャネル」を採用しています。
しかしながら、BigQuery Exportされたデータにはデフォルトでこの情報は用意されておらず、自前で抽出する必要があります。
本記事では、BigQuery ExportされたGA4データをもとに「セッションごとの最後の流入チャネル」を分析するときの注意点と実際の抽出方法をご紹介しようと思います。
BigQuery ExportされたGA4データを使って流入チャネルを分析しようとしている方たちのご参考になれば幸いです。
セッションごとの最後の流入チャネル
「セッションごとの最後の流入チャネル」は、サイトへの流入チャネルを分析するときに一般的によく使われる軸で、1セッションの中で複数のチャネルから流入があるとき最後の流入チャネルに注目したい場合に用います。
他によく使われる定義としては、「セッションの最初のチャネルを採用する」、「CV直前のチャネルを採用する」、「複数チャネル間で流入・CVを按分する」といったものがあります。
※セッションとは、簡単に言うと「Webサイトを訪れてから離脱するまでの一連の流れ」のことです。詳しい情報は公式ドキュメント(https://support.google.com/analytics/answer/9191807?hl=ja)を参照ください。
BigQuery上で「セッションごとの最後の流入チャネル」を抽出する
「セッションごとの最後の流入チャネル」の情報は、GA4テーブルのカラムとしてドンピシャでは存在しません。そのため、クエリを書いて抽出する必要があります。
注意点: eventsテーブルのtraffic_sourceは「ユーザーの最初の流入チャネル」
BigQuery Exportされたeventsテーブルには、デフォルトで「traffic_source」というカラムが用意されているのですが、これは「そのユーザーの最初の流入チャネル」を表しているため、求めているものとは異なります。例えば、「あるユーザーAが広告流入したあと、同じセッションでオーガニック流入した。その後、別のセッションで検索流入した」という場合、そのユーザーのイベントのtraffic_sourceはずっと「広告」のままになります。(同セッション・別セッションかも考慮されません。)
実際の抽出ステップ
以下に、私たちが現在採用している抽出方法を、クエリの一部もあわせてご紹介します。
このクエリを作るうえで役に立ったのが、GA4 SQL(https://www.ga4sql.com/)という、BigQuery上のGA4データに対するクエリを生成できるサービスです。当初は、自力でテーブルの仕様を読み解こうとして難航していたのですが、GA4 SQLを見つけたことにより高速で解決しました。GA4 SQLはさまざまな軸・指標に対応しており、GA4テーブルを使って分析をするあらゆる方におすすめです。
step1: 各イベントごとの流入チャネルを抽出
まず「各イベントごとの流入チャネル」を出します。
基本的には event_params の source medium campaignを参照すればOKです。それに加えて、閲覧開始(ランディング)イベントにはevent_paramsが付与されないことがあるため、「ユーザーの最初の流入チャネル」を補完しています。
以下は各イベントの source を抽出する式です。
, CASE -- 閲覧開始イベントには「ユーザーの最初の流入チャネル」補完 WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = "page_view" AND key = "entrances" ) = 1 AND ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = "page_view" AND key = "source" ) IS NULL THEN traffic_source.source -- event_paramsのsourceを取り出す ELSE ( SELECT value.string_value FROM UNNEST(event_params) WHERE key = "source" ) END AS session_source
step2: セッションごとに、最後の流入チャネルを計算する
step1でイベントごとの流入チャネルが出せたので、次は ARRAY_AGG 関数を使ってそれをセッションごとにまとめます。event_timestamp が最も遅い時刻であるものを「最後の流入チャネル」とします。
, ARRAY_AGG(
IF(
-- source, medium, campaignのうち少なくともひとつがNULLでなければ採用
session_source IS NOT NULL OR session_medium IS NOT NULL OR session_campaign IS NOT NULL
, STRUCT(session_source, session_medium, session_campaign)
, NULL
)
IGNORE NULLS ORDER BY event_timestamp DESC
)[SAFE_OFFSET(0)].*
ポイント
step2の処理で、source, medium, campaignのうち一部だけがNULLになっているチャネルが存在するため、source, medium, campaignそれぞれのカラムごとにNULLを無視して集約すると、以下のように異なるイベントのチャネルが組み合わせられて出力されてしまいます。実はGA4 SQLが生成したクエリをそのまま使うと、この問題が起こってしまいました。
入力
| event_timestamp | source | medium | campaign |
|---|---|---|---|
| 1702455470861148 | organic | organic | |
| 1702455473441971 | coloria_magazine | item_button | NULL |
| 1702455481338865 | NULL | NULL | NULL |
↓
GA4 SQLが生成したクエリの出力結果: カラムごとに最新の値(赤字部分)をとってしまっている
| source | medium | campaign |
|---|---|---|
| coloria_magazine | item_button | organic |
そこで、一部がNULLのチャネルにも対応できるよう、source, medium, campaignはSTRUCTにまとめたうえで集約するようにしたところ、以下のように想定通りの出力を得ることができました。
入力
| event_timestamp | source | medium | campaign |
|---|---|---|---|
| 1702455470861148 | organic | organic | |
| 1702455473441971 | coloria_magazine | item_button | NULL |
| 1702455481338865 | NULL | NULL | NULL |
↓
修正版の出力結果 : レコードごとにSTRUCTでまとめて扱っている
| source | medium | campaign |
|---|---|---|
| coloria_magazine | item_button | NULL |
おわりに
ここまで読んでいただきありがとうございます。
「セッションごとの最後の流入チャネル」は、セッションあたりのチャネルを一意に定めたいときによく使われる軸ですが、BigQueryのGA4イベントから出そうとすると意外と複雑です。この記事が、BigQueryのGA4データを使って流入チャネルを分析している・しようとしている方たちのご参考になれば幸いです。
High Linkデータチームでは、データ基盤構築やデータ分析を通じて、ともに事業に貢献をしていくメンバーを募集しています！
追記
上で紹介したGA4 SQLですが、2023年12月現在は「GA4 SQL GPT(https://chat.openai.com/g/g-3WsWtge73-ga4-sql)」なるものが公開されており、自然言語で分析したいテーマを入力すると、クエリを返してくれます。(※利用にはChatGPT Plusが必要)
ただし「セッションごとの最後の流入チャネルを求める方法」と入力するとtraffic_sourceを使ったクエリが返ってきたりと、現時点では100%信頼できる状態ではないので注意が必要です。
カジュアル面談はこちら
求人はこちら herp.careers
