[データ分析のためのBigQueryクエリ]SQLで年別優良顧客推移を算出
この記事では、BigQueryを使用してデータ分析を行う方法を紹介します。
別記事でご紹介した年別RFM分析を利用して、優良顧客数推移を算出します。時間経過ごとに顧客属性の変化を追うことで、顧客ニーズやターゲットを明確にできます。
概要
この記事では、BigQueryを使用してデータ分析を行う方法を紹介します。こちらの記事で算出した年別RFM分析を利用して、優良顧客数推移を算出します。これを用いて時間経過ごとに顧客属性の変化を追うことで、顧客ニーズやターゲットを明確にできます。
実装概要
RFM分析は前回の記事をご参照ください。
算出のためのステップは下記です。
01. 各年ごとにRFM分析を行い縦にユニオン
02. 各指標の人数分布で上位20％となる指標値を算出
03. 各年ごとに各指標が全て上位20%となる顧客を抽出
実装コード
01 各年ごとにRFM分析を行い縦にユニオン
SELECT
customer_No,
count(customer_No) as Frequency,
datetime_diff("2022-12-31",max(order_datetime),month) as Recency,
max(order_datetime) as Recentry_buy #後に使うため購入した日付も残しておきます。
sum(total_price) as Monetary
FROM `dataset.table`
WHERE order_datetime between "2022-01-01" AND "2022-12-31"
GROUP BY customer_No
UNION ALL
SELECT
customer_No, count(customer_No) as Frequency,
datetime_diff("2021-12-31",max(order_datetime),month) as Recency,
max(order_datetime) as Recentry_buy #後に使うため購入した日付も残しておきます。
sum(total_price) as Monetary
FROM `dataset.table`
WHERE order_datetime between "2021-01-01" AND "2021-12-31"
GROUP BY customer_No UNION ALL
#年ごとのRFM分析を必要な数だけUNIOで重ねる
#実行結果を保存02 各指標の人数分布で上位20％となる指標値を算出
#このクエリはFrequencyの指標で上位20％を探していますが、
#他の指標も同様です。
with frequency_table as (
SELECT
frequency,
count(customer_code) as number_of_customer
FROM `dataset.table` #先ほど保存した各年RFMのUNIOしたテーブル
GROUP BY frequency
ORDER BY frequency ASC )
SELECT *,
#累積割合を算出
SUM(rate) OVER (ORDER BY frequency) AS cumulative_rate
FROM (
SELECT *, number_of_customer/[`UNION`したテーブルの総レコード数] as rate FROM frequency_table )
ORDER BY frequecy ASC
クリエ実行結果から、上位20%に最も近い購入頻度を見つけます。他の指標に対しても同様の操作を行い上位20%となる指標値を算出してください。
03 各年ごとに各指標が全て上位20%となる顧客数を年ごとに算出
SELECT
count(customer_no) as blue_tips_customer,
#購入日から年を抽出
datetime_trunc(Recentry_buy,year) as year
FROM `dataset.table` #先ほど保存した各年RFMのUNIOしたテーブル
WHERE
frequency >=[先ほど求めた上位20%の指標値]
AND
Monetary>=[先ほど求めた上位20%の指標値]
AND
recency_<=[先ほど求めた上位20%の指標値]
GROUP BY year
まとめ
各コード自体は複雑ではありませんが、重要な部分にコメントアウトを行ったので、それを参照してください。3のコードで各年の優良顧客の数を算出しましたが、優良顧客の顧客Noにフラグをつけて顧客テーブルと結合することで、年齢や性別といった属性の分析も可能になります。
