[データ分析のためのBigQueryクエリ]SQLで1年LTVの算出
概要:
この記事では、BigQueryを使用してデータ分析を行う方法を紹介します。顧客ごとに、最初の注文日から1年間の間に発生した購入金額の合計を算出します。1年LTVを算出することで優良顧客のセグメント発見や複数回購入要因の発掘などが可能になります。
概要
この記事では、BigQueryを使用してデータ分析を行う方法を紹介します。顧客ごとに、最初の注文日から1年間の間に発生した購入金額の合計を算出します。1年LTVを算出することで優良顧客のセグメント発見や複数回購入要因の発掘などが可能になります。
実装概要
顧客の注文単位のレコードを持つテーブルから1年間の購買金額を計算します。顧客の初回購入日から1年間の購入金額合計を算出していますが、商材などに合わせて期間を変更してください。
実装コード
コードは下記の通りです。
WITH first_date_add as (
SELECT *
FROM `dataset.table` as table1
JOIN (
SELECT #注文単位のテーブルを顧客ごとに集計し、初回注文日を抽出
customer_No as customer_No_,
min(order_datetime) as first_datetime
FROM `dataset.table`
GROUP BY customer_code
) #顧客Noをキーにして、オーダー単位のテーブルに初回注文日を結合
as table2 ON table1.customer_code=table2.customer_code_ )
SELECT
customer_No_,
SUM(total_price) AS total_purchase_amount_1year,
min(order_datetime) as min_order_datetime
FROM first_date_add
WHERE #初回注文から1年間のみのレコードを指定 order_datetime >= DATE_SUB(first_datetime, INTERVAL 1 YEAR)
GROUP BY customer_code_ ORDER BY total_purchase_amount_1year
手順
利用するテーブルのカラム
ECサイトであればシステムがデフォルトで持っているテーブルかと思います。なおオーダーされたプロダクトごとのテーブルの場合は、注文IDなどで括って下記のテーブルを作成してください。
クエリの説明
WITH句: first_date_addという名前の一時テーブルを作成し、最初の注文日時と顧客コードを集計します。
SELECT文: 顧客ごとの購買金額と最初の注文日時を取得します。
FROM句: first_date_addテーブルからデータを選択します。
WHERE句: 注文日時が最初の注文日時から1年以上経過しているデータをフィルタリングします。
GROUP BY句: 顧客コードごとにデータをグループ化します。
まとめ
上記のクエリによって初回購入からの一年間のLTVの算出を行いました。算出したLTVを利用して、千円単位でLTVの顧客数分布を出すことで、顧客のセグメントや複数回購入の要因などの洞察を得ることができます。