本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、特定ユーザーのLTV（顧客生涯価値）の推移を集計するSQLクエリについて解説をおこなう。このSQLクエリのユースケースとしては、例えば自社におけるロイヤルカスタマーや重要顧客を抽出し、そのLTVの推移を集計することで、価値の高いユーザーの特徴や傾向を分析して、LTVを伸ばすための施策へとつなげることが可能となる。

SQLクエリの概要

まず、LTV（顧客生涯価値）の一般的な説明は下記の通りである。

顧客生涯価値とは、顧客1人あるいは1社の顧客ライフサイクル全期間で、その顧客が企業にもたらした価値の総計のこと。顧客を獲得維持するためのコストと、顧客の購買額との差額が価値となる。Lifetime value、customer lifetime valueを略してLTV、CLVとも呼ばれる。

出典 : 顧客生涯価値｜グロービス経営大学院 創造と変革のMBA

BigQueryにエクスポートしたGA4データのスキーマにおいて、LTVに関するデータは user_ltv という RECORD タイプのフィールド（カラム）に格納されている。

この user_ltv 内にネストされた revenue というフィールドの値は user_ltv.revenue という形で記述することで取得することができる。

出典 : [GA4] BigQuery Export スキーマ

サンプルクエリでは user_pseudo_id が 12291446.3815334458 であるユーザーのLTV推移を抽出している。また、event_date、user_pseudo_id、user_ltv.revenue でグループ化することで、3つの組み合わせから重複を除いている。

今回はGoogleが用意しているGA4のサンプルデータセットを利用して集計をおこなっている。そのため、下記のSQLクエリサンプルをコピペしてそのままBigQueryで実行していただければ、同じ結果を得ることができるようになっている。

Google Merchandise Storeは Google ブランドの商品を販売するオンライン ストアです。このサイトでは、Google アナリティクス 4 の標準のウェブ e コマースの実装と拡張計測機能が使用されます。BigQuery 一般公開データセット プログラムを通じて利用可能な ga4_obfuscated_sample_ecommerce データセットには、2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月間の難読化した BigQuery イベント エクスポート データのサンプルが含まれています。

出典 : Google アナリティクス 4 e コマースウェブ実装向けの BigQuery サンプル データセット

SQLクエリサンプル

今回のSQLクエリは、以下のようになる。

SELECT
  event_date,
  user_pseudo_id,
  user_ltv.revenue AS LTV

FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  user_pseudo_id = '12291446.3815334458'
GROUP BY
  event_date,
  user_pseudo_id,
  user_ltv.revenue
ORDER BY
  LTV,
  event_date
;

BigQueryでの集計結果

今回のBigQueryでの集計結果は、以下のようになった。

ChatGPT（GPT-4モデル）によるSQL解説

ChatGPTによる解説をつけるので、学習の参考に活用してほしい。

このSQLクエリは指定したユーザー（user_pseudo_idが ‘12291446.3815334458’）の日付ごとの生涯価値（Lifetime Value、LTV）を取得するものです。具体的なステップは以下のとおりです。

1.SELECT句で、event_date（イベントが発生した日付）、user_pseudo_id（ユーザーの識別子）、user_ltv.revenue（ユーザーの生涯価値）を選択します。

2.FROM句で、データセットbigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*を指定します。ここでを使って、events_で始まるすべてのテーブルを対象にしています。

3.WHERE句を使って、特定のユーザー（user_pseudo_idが ‘12291446.3815334458’）のデータだけをフィルタリングします。

4.GROUP BY句を使って、結果をevent_date、user_pseudo_id、user_ltv.revenueによってグループ化します。これにより、同一日付、同一ユーザー、同一LTVのレコードは一つにまとまります。

5.ORDER BY句を使って、結果をLTVとevent_dateの昇順で並べます。

このクエリを実行することで、特定のユーザーの日付ごとの生涯価値（LTV）を把握することができます。これは、そのユーザーがビジネスにとってどれだけ価値があるかを評価するための一つの指標となります。また、ユーザーのLTVを時間軸で追うことで、そのユーザーの購買行動の変化や傾向を理解するための洞察を得ることができます。

今回の記事では、特定ユーザーのLTV（顧客生涯価値）の推移を集計するSQLクエリについて解説をおこなった。自社におけるロイヤルカスタマーや重要顧客の抽出、そのLTVの推移を集計し、価値の高いユーザーの特徴や傾向を分析、そしてLTVを伸ばすための施策へとつなげていく際に参考にしてほしい。