[データ分析のためのBigQueryクエリ]　SQLで1年LTVの算出

2023年6月23日2023年8月9日

概要: この記事では、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句: 顧客コードごとにデータをグループ化します。