LookerStudioで画面遷移のサンキーグラフを作るためのデータの作成のコツ
アクセスログを画面遷移図（サンキー）で見たいという要望がありました。
それだけならGoogleAnalyticsだけで達成できそうなものですが、プレゼンスが高いユーザーに絞り確認したいという意向もありました。
それなら私がBigQuery - LookerStudioで表現しましょうと動いたのがきっかけで、用意すべきデータ列やSQLのコツがわかったため書き残します。
LookerStudioのサンキーのグラフドキュメントを読む
LookerStudio サンキーグラフのリファレンス
こちらの公式ドキュメントを読んだところ、
・以下の 2 つのディメンション:
・データの送信先（別名「ターゲット」）となるノードを表すディメンション
・データの送信元（別名「ソース」）となるノードを表すディメンション
・ノード間の重み付け（リンクの太さ）を示す 1 つの指標
ということが分かりました。
今回は画面遷移図を作りたいので、
データの送信元も送信先もWebページのURLです。AページからBページへの遷移を表したければ、ソースノードがAページで、ターゲットノードがBページということになるようです。
ノード間の重み付け（リンクの太さ）はWebページへのアクセス回数で表現し、そのリンクの太さでAページからBページへの遷移がどれほど重要なのかを視覚化したいと思います。
ここで必要なカラムが
- ターゲットノード = 遷移元Webページ
- ソースノード = 遷移先Webページ
- ノード間の重み付け = アクセス数
というカラムを持つテーブルを作成すればよいことが分かりました。
ただ アクセス数に関してはカラムとして用意するのではなく LookerStudio側で自動で作成されるカラム Record Count を利用するつもりなので、データを収集は遷移元Webページと遷移先Webページに絞られます。
ターゲットノード,ソースノードのカラムを持つテーブルを用意する
ターゲットノード,ソースノードを言い換えまして、遷移元Webページ,遷移先Webページ のカラムを持つテーブルを作成したいと思います。
SQLでこれらのデータを表現してテーブルを作成したわけですが（実際にはマテリアライズドビューを作成してのですがその点は本筋ではないため省略します）、その際にここはポイントだなと思った点の2点を書き残します。
アクセスした順に番号を振る
| アクセス日時 | ユーザーを一意に特定するキー | WebページURL |
|---|---|---|
| 2025-03-08T00:00:00 | 1 | /index |
| 2025-03-08T00:00:01 | 1 | /product/a |
| 2025-03-08T00:00:02 | 1 | /product/z |
| 2025-03-08T00:00:03 | 2 | /index |
| 2025-03-08T00:00:04 | 2 | /product/a |
| 2025-03-08T00:00:05 | 2 | /index |
以上のようなアクセスログがあった場合に、
| ユーザーを一意に特定するキー | 遷移元Webページ | 遷移先Webページ |
|---|---|---|
| 1 | 1:/index | 2:/product/a |
| 1 | 2:/product/a | 3:/product/z |
| 1 | 3:/product/z | null |
| 2 | 1:/index | 2:/product/a |
| 2 | 2:/product/a | 3:/index |
| 2 | 3:/index | null |
このようなデータを作ることが目標になります。
遷移先Webページがnullになる場合は、グラフにした場合ノイズになるためあらかじめSQLで除去するか、LookerStudioでnullカラムを除外するようにしてもよいでしょう。
さて番号を振るというお話ですが、ユーザーIDごとにアクセス日時昇順に番号のプレフィックスをつけて遷移元Webページ,遷移元先Webページを表現しています。
なぜこの番号プレフィックスが必要かと言うと、LookerStudioのサンキーグラフは循環するようなデータに対応していません。
Webサイトのアクセスには上記の表のユーザーを一意に特定するキー2のように /index -> /product/a -> /index というようなトップページから個別ページの遷移し、またトップページに戻るというような画面遷移が当たり前に起こりますが、そのようなサイクルを許容しません。
そのため
| ユーザーを一意に特定するキー | 遷移元Webページ | 遷移元先Webページ |
|---|---|---|
| 2 | /index | /product/a |
| 2 | /product/a | /index |
このような循環するデータをサンキーグラムで利用するとグラフ作成時にエラーとなりました。
それを回避するためアクセス順に番号を振ることになりました。
SQL例はこのようになります。
CONCAT(
ROW_NUMBER() OVER (PARTITION BY ユーザーを一意に特定するキー ORDER BY アクセス日時 ASC),
':',
WebページURL
) 遷移元Webページ,
ウィンドウ関数で "ユーザーを一意に特定するキー" 単位で番号が振られるようにしています。これは大抵のケースでやセッションIDやユーザーIDになるのではと思っています。
更にアクセス日時昇順とすることでアクセス昇順の番号となります。
それに文字列結合ができるCONCATを利用し、ROW_NUMBER()と:とURLを結合し遷移元Webページを表現しています。
LEAD関数で次の行を取得し"遷移先Webページ"のカラムを作る
「SQL 次の行 BigQuery」のような単語で検索して、このドキュメント
LEAD に辿り着きました。
この関数は後続の行の値を返します。OFFSETを指定して行数を指定することができますが、デフォルトが 1 なので 今回の用途「遷移先Webページ」の値を得るにはOFFSETは指定せず十分のようです。
SQL例はこちら。
CONCAT(
1+ROW_NUMBER() OVER (PARTITION BY ユーザーを一意に特定するキー ORDER BY アクセス日時 ASC),
':',
LEAD(WebページURL) OVER (PARTITION BY ユーザーを一意に特定するキー ORDER BY アクセス日時 ASC)
) AS 遷移先Webページ
LEAD(WebページURL) で次の行のWebページURLを取得しています。
SQL全体像
SELECT
ユーザーを一意に特定するキー,
CONCAT(
ROW_NUMBER() OVER (PARTITION BY ユーザーを一意に特定するキー ORDER BY アクセス日時 ASC),
':',
WebページURL
) 遷移元Webページ,
CONCAT(
1+ROW_NUMBER() OVER (PARTITION BY ユーザーを一意に特定するキー ORDER BY アクセス日時 ASC),
':',
LEAD(WebページURL) OVER (PARTITION BY ユーザーを一意に特定するキー ORDER BY アクセス日時 ASC)
) AS 遷移先Webページ,
アクセス日時 -- LookerStudio上で日時による絞り込みが必要な場合
FROM アクセスログ
WHERE アクセスログ.アクセス日時 >= '2025-03-01T00:00:00'
実際にはユーザーを絞り込むために複数のテーブルとの結合を行いましたが、ここでは省略しています。
このSQLをマテリアライズドビューかスケジュールされたクエリでテーブルへの書き込みに利用します。
まとめ
LookerStudioが循環するデータに対応していないため少し捻った対応が必要になりました。
他のBIツールなら問題なくできるのか気になるところです。
しかしLEAD関数はこれまで利用したことがなく知らない存在だったので今回利用できて今後の引き出しが増えました。
Discussion