Looker StudioのタイムライングラフでCVユーザーのセッションを可視化してみた
BigQuery, GA4, Looker Studio, SQL

Looker StudioのタイムライングラフでCVユーザーのセッションを可視化してみた

2024年4月25日に、Looker Studioに「タイムラインチャート」が登場しました。こちらが、タイムラインチャートの登場を知らせるリリースノートの該当部分（英語）です。ジャンプするとアナウンスが掲載されているページにジャンプします。

　

　

上記のリリースノートは英語でしたが、Looker Studioのタイムライングラフの公式リファレンスは日本語で用意されています。

レポートの仕様をざっと確認しながら、GA4データの分析の応用例を考えてみました。もし、開始日、終了日として「日付と時刻」型が選べれば、一つのセッション中にどのページを何秒みて、次に別のページを何秒見て、それからコンバージョンしたのか？を可視化すると面白いかな？と思ったのですが、残念ながら、（必須フィールドである）「開始日時」は、その名前に反して？「日付」型である必要があるので、穏当に、「CVしたユーザーのCVするまでのセッション」を可視化してみました。

前提

今読んでいただいている、このブログサイトのGA4データを例にしています。このサイトを計測しているGA4では、いくつかの記事の末尾にある「宣伝」セクションから、書籍を購入してくれるかもしれない、という意味でのAmazonへのジャンプ、動画講座を購入してくれるかもしれない、という意味でのUdemyへのジャンプ、その2種類のサイト外ジャンプをコンバージョンとして設定しています。

イベント名は、”purchase”です。

　

Looker Studioタイムライングラフの例

こちらがLooker Studioの、タイムライングラフを使った作例です。

　

上段で利用しているのがタイムライングラフです。user_pseudo_idごとに、「コンバージョンしなかったセッション」を”ss”（画面中、青）とし、「コンバージョンしたセッション」を”cv”（画面中、赤）として可視化しています。対象はコンバージョンしたユーザーだけに絞り込んでいます。結果、分かることの例は以下の通りです。

  • ①のユーザーは、同じ日に、コンバージョンしたセッションと、コンバージョンしなかったセッションをもたらしてくれたことが分かります。
  • ②のユーザーはいきなりやってきて、いきなりコンバージョンしてくれました。
  • ③のユーザーは、コンバージョンしないセッションを3回繰り返したあと、4回目のセッションでコンバージョンしてくれています。

　なにか、特別素敵なことが分かる。ということはないものの、ユーザーがコンバージョンに至るまでのサイトの利用状況を「セッション」単位で俯瞰することはできます。

　

「セッションの詳細」というタイトルを付けている下の表は、セッションスコープの以下のディメンションと指標を「表」で示しています。

  • 参照元
  • メディア
  • セッションのランディングページ
  • コンバージョンしたページ（AmazonやUdemyにジャンプした際の「ジャンプ元」のページ）
  • PV per session
  • セッションのエンゲージメント時間

　

上段のタイムライングラフのオプションである、「クロスフィルタリング」をオンにしていますので、バー（一つのセッションを表しています）をクリックすると、下段の表が絞り込まれます。44秒の動画にしましたので、どのような挙動をするか、確認したい場合には再生してください。

　

　

タイムライングラフ作成上の注意

タイムライングラフを作成するうえで気をつけるべきこととしては、開始日時（実際には時刻は入れられないので、開始日）と、終了日時（実際には終了日）が同じ日だとバーは描画されません。期間が0とみなされてしまうからだと思います。そのため、セッションが発生したり、コンバージョンを伴うセッションが発生したのは「ある、単一の日」ですが、可視化する上では、便宜上「ある日に開始し、翌日に終了した」ことにしなくてはいけません。

　

実害はありませんが、少し気持ち悪いといえば、気持ち悪いですね。

　

あとは、横スクロールはしないので、長い期間を対象とすると、バーが小さくなります。以下の画像は3月1日から5月31までの3ヶ月間を可視化したものですが、「1日」を示すバーが結構短くなっているのが分かると思います。まぁ、期間は3ヶ月くらいが限界なのではないかと思いました。

　

あとは特に大きな問題はなく、公式ヘルプのリファレンスを参照すれば作成は難しくないものと思います。では、どのようなデータがあれば上記のような可視化ができるのかについて、次で説明します。

　

タイムライングラフ作成に利用したデータ

タイムライングラフに作成したデータは、BigQueryからカスタムSQLで取得しています。スキーマは以下の通りです。

　

  1. session_type（文字列型） : コンバージョンのあったセッションか、なかったセッションかのフラグ<バーラベルとして使用>
  2. start_date（日付型）：セッションの開始日<開始日時として利用>
  3. end_date（日付型）：セッションの開始日の翌日<終了日時として利用>
  4. user_pseudo_id（文字列型）：匿名でのユーザー識別子<行ラベルとして使用>
  5. ga_session_number：（整数型）：セッション番号
  6. session_id（文字列型）：user_pseudo_idとga_session_numberを文字連結したもの。セッションを識別できる
  7. session_engagement_time（フロート型）：engagement_time_msecをセッション単位で合計したもの
  8. source（文字列型）：セッションの参照元
  9. medium（文字列型）：セッションの参照メディア
  10. source_media（文字列型）：sourceとmediumを文字列連結したもの
  11. landing_page_title（文字列型）：ランディングページのタイトル
  12. landing_page_location（文字列型）：ランディングページのURL
  13. converted_page_title（文字列型）：コンバージョンの発生したページのタイトル
  14. converted_page_location（文字列型）：コンバージョンの発生したページのURL

　

Looker Stuidioのレポートの下の段の「セッションの詳細」をなるべくリッチで意味のあるものにしようとして、「余計な」カラムを取得していますが、上段のタイムライングラフを作成するだけであれば、上記のリストの1～4のフィールドだけがあれば大丈夫です。

　

Looker Studioから接続したカスタムSQL

GA4がエクスポートしたデータから、上記の14カラムのテーブルを取得するSQL文を一応掲載しておきます。もちろん、コピー自由、改変自由です。ただし、自己責任でお願い致します。

　

WITH timeline AS (
  SELECT
    event_timestamp
  , event_name
  , PARSE_DATE("%Y%m%d", event_date) AS event_date
  , IF(event_name="purchase", 1, 0) AS cv_flag
  , user_pseudo_id
  , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number
  , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec
  , (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_title') AS page_title
  , collected_traffic_source.manual_source AS source
  , collected_traffic_source.manual_medium AS media
  , CONCAT(collected_traffic_source.manual_source, " / ", collected_traffic_source.manual_medium) AS source_media
  , IF(event_name="page_view", 1, 0) AS pageview
  FROM `bigquerytableauoct.analytics_323400862.events_2024*`
  WHERE _table_suffix BETWEEN "0501" AND "0531"
), timeline2 AS (
  SELECT
    *
  , FIRST_VALUE(page_location) OVER (PARTITION BY user_pseudo_id, ga_session_number ORDER BY event_timestamp) AS landing_page_location
  , FIRST_VALUE(page_title) OVER (PARTITION BY user_pseudo_id, ga_session_number ORDER BY event_timestamp) AS landing_page_title
  , IF(event_name="purchase", page_location, NULL) AS converted_page_location
  , IF(event_name="purchase", page_title, NULL) AS converted_page_title
  FROM timeline
), timeline3 AS (
  SELECT
    IF(MAX(cv_flag) = 1, "cv", "ss") AS session_type
  , event_date AS start_date
  , event_date + 1 AS end_date
  , user_pseudo_id
  , ga_session_number
  , CONCAT(user_pseudo_id, "-", ga_session_number) AS session_id
  , SUM(pageview) AS pv_per_session
  , SUM(engagement_time_msec) / 1000 AS session_engagement_time
  , MAX(source) AS source
  , MAX(media) AS medium
  , MAX(source_media) AS source_media
  , MAX(landing_page_title) AS landing_page_title
  , MAX(landing_page_location) AS landing_page_location
  , MAX(converted_page_location) AS converted_page_location
  , MAX(converted_page_title) AS converted_page_title
  FROM timeline2
  GROUP BY event_date, start_date, end_date, user_pseudo_id, ga_session_number
)

SELECT
  *
FROM timeline3
WHERE user_pseudo_id IN (
  SELECT user_pseudo_id
  FROM `bigquerytableauoct.analytics_323400862.events_2024*`
  WHERE _table_suffix BETWEEN "0501" AND "0531"
  GROUP BY user_pseudo_id
  HAVING MAX(IF(event_name = "purchase", 1, 0) = 1)
)
ORDER BY user_pseudo_id, ga_session_number;

　

宣伝

ここからは宣伝です。3月末に、Udemyに新講座を公開しました。「BIツールで100種類以上のレポートを作成できるWebサイト分析用のたった5個のSQL文」という講座です。この講座では、SQL文を上手に書けない人に対して、a.人にお願いする、b.生成AIにお願いするに次ぐ、第三の選択肢、c.汎用的に記述されたSQL文を利用するを提供します。

講座を購入していただくとGA4がBigQueryにエクスポートした5つのSQL文が手に入ります。それだけでなく、そのSQL文には非常に詳しい解説がついていますので、自分で改造することも比較的容易です。第三の選択肢を得たいという方は是非、ご利用ください。

　

いやいや、わたしは僕は、ちゃんとSQLを勉強して、身につけたいよ。という方に対しては、学習用の良い動画講座があるんですよ。たくさんのレビューを頂き、5点満点中4.6点と、お陰様で評判も良いようです。よろしければどうぞ。（画像をクリックするとUdemy.comにジャンプします。）

　

え？SQLは本で勉強したい？ごもっとも！！では、こちらをどうぞ。クリックするとアマゾンにジャンプします。