目次
はじめに
社内業務の中で、月ごとに顧客ランクをもつデータマートを作成する機会がありました。顧客ランクはマーケティングの中でも重要な指標のひとつになることが多いので、参考になればと思います。
SQLで月ごとに顧客ランクを持つテーブルを作成する方法
今回はArrayを用いて購買のない月の欠損データを補いつつ、各月の顧客ランクを算出するデータテーブルを作成します。
顧客ランクの定義について
今回は下記のような定義で顧客ランクを設定しました。
クエリを参考にする場合は、最後のランク判定時に必要に応じてランク条件を変更してください。
▼顧客ランク
| 購入総回数/購入総金額 | 4,500円未満 | 4,500円以上~19,800円未満 | 19,800円以上 |
|---|---|---|---|
| 2回以上 | E | C | A |
| 1回 | F | D | B |
顧客ランクとは
顧客ランクとは、顧客を購買行動や実績に応じてランク付け（重み付け）をすることをいいます。
今回は、購入回数（Frequency）と購入金額（Monetary）でランク付けをしてセグメントを作成しています。
月ごとに顧客ランクを持つテーブルの作成クエリ（全体）
/*注文日のMIN値からMAX値までの年月一覧を作成*/
DECLARE date_array ARRAY<DATE>;
SET date_array = GENERATE_DATE_ARRAY(
(SELECT DATE(FORMAT_DATE('%Y-%m', MIN(order_date)) || '-01')
FROM `トランザクションデータテーブル`)
,(SELECT DATE(FORMAT_DATE('%Y-%m', MAX(order_date)) || '-01')
FROM `トランザクションデータテーブル`)
,INTERVAL 1 MONTH
);
/* customer_idごとの最初の注文月を抽出 */
WITH _user AS(
SELECT
customer_id
,DATE(FORMAT_DATE('%Y-%m', MIN(order_date)) || '-01') as min_order_date01 ---------- 最初の注文月をyyyy-mm-01の形に変換
FROM `トランザクションデータテーブル`
GROUP BY customer_id
)
/* customer_idごとに最初の注文月以降の年月を付与 */
,_user_dt AS(
SELECT
_user.customer_id
,car.dt
FROM (SELECT dt from UNNEST(date_array) as dt) AS car
INNER JOIN _user
ON _user.min_order_date01 <= car.dt
)
/* 顧客のごとのトランザクションデータ */
,_trn AS(
SELECT
tran_t.customer_id
,DATE(FORMAT_DATE('%Y-%m', tran_t.order_date) || '-01') AS ord_month --------------- 注文月をyyyy-mm-01の形に変換
,tran_t.order_id
,tran_d_t.price
FROM `トランザクションデータテーブル` AS tran_t
LEFT JOIN `トランザクション詳細テーブル` AS tran_d_t
ON tran_t.order_id = tran_d_t.order_id
)
/* 顧客のごとのdtから過去1年分のトランザクションデータ */
,month_grouped_trn AS(
SELECT
_user_dt.customer_id
,user_dt.dt
,_trn.ord_month
,COUNT(DISTINCT _trn.order_id) AS ord_cnt ---------------------- 購入回数
,IFNULL(SUM(_trn.price), 0) AS price
FROM _user_dt
LEFT JOIN _trn
ON _user_dt.customer_id = _trn.customer_id
AND (_trn.ord_month BETWEEN DATE_SUB(user_dt.dt, INTERVAL 11 MONTH) AND _user_dt.dt) -------- dtから過去1年分のデータを紐づける
GROUP BY
_user_dt.customer_id
,user_dt.dt
,_trn.ord_month
)
,_proceeds_by_customer AS(
SELECT
customer_id
,dt
,SUM(ord_cnt) AS ord_cnt_sum
,SUM(price) AS price_sum
FROM month_grouped_trn
GROUP BY
customer_id
,dt
)
-- ,customer_rank_t AS(
SELECT
customer_id
,dt
,ord_cnt_sum AS ord_cnt
,price_sum AS price
,CASE
WHEN ord_cnt_sum >= 2 AND price_sum >= 19800 THEN 'A'
WHEN ord_cnt_sum = 1 AND price_sum >= 19800 THEN 'B'
WHEN ord_cnt_sum >= 2 AND (price_sum BETWEEN 4500 AND 19799) THEN 'C'
WHEN ord_cnt_sum = 1 AND (price_sum BETWEEN 4500 AND 19799) THEN 'D'
WHEN ord_cnt_sum >= 2 AND price_sum < 4500 THEN 'E'
WHEN ord_cnt_sum = 1 AND price_sum < 4500 THEN 'F'
ELSE '休眠' END AS customer_rank
FROM _proceeds_by_customer
ORDER BY customer_id, dt
クエリをひとつずつ解説
- 注文日のMIN値からMAX値までの年月一覧を作成
DECLARE関数で年月一覧のマスタデータをdate_arrayという名前で定義します。
DECLARE date_array ARRAY<DATE>;
SET date_array = GENERATE_DATE_ARRAY(
(SELECT DATE(FORMAT_DATE('%Y-%m', MIN(order_date)) || '-01') ---- 年月一覧の開始月：注文日のMIN値の年月（yyyy-mm-01の形）
FROM `トランザクションデータテーブル`)
,(SELECT DATE(FORMAT_DATE('%Y-%m', MAX(order_date)) || '-01') ---- 年月一覧の終了月：注文日のMAX値の年月（yyyy-mm-01の形）
FROM `トランザクションデータテーブル`)
,INTERVAL 1 MONTH
)
- 顧客ごとに最初の注文月以降の年月をもたせる
GENERATE_DATE_ARRAYはネストされた状態で日付を生成するため、UNNEST処理をおこないます。この処理で、その顧客の注文がない年月のレコードも持てている状態が作成できました。
/* customer_idごとの最初の注文月を抽出 */
WITH _user AS(
SELECT
customer_id
,DATE(FORMAT_DATE('%Y-%m', MIN(order_date)) || '-01') as min_order_date01 ---- 顧客の最初の注文月をyyyy-mm-01の形に変換
FROM `トランザクションデータテーブル`
GROUP BY customer_id
)
/* customer_idごとに最初の注文月以降の年月を付与 */
,user_dt AS(
SELECT
_user.customer_id
,car.dt
FROM (SELECT dt from UNNEST(date_array) as dt) AS car
INNER JOIN _user
ON _user.min_order_date01 <= car.dt ---- 顧客の最初の注文月以降のデータが紐づくようにリレーション
)
- 過去一年分のトランザクションデータを紐づける
基準となる年月（dt）に対して、過去1年間のトランザクションデータを持たせます。これにより、各顧客のトランザクションデータを過去1年間のみに絞り込むことでランクを正しく定義することができます。
/* 顧客のごとのトランザクションデータ */
,_trn AS(
SELECT
tran_t.customer_id
,DATE(FORMAT_DATE('%Y-%m', tran_t.order_date) || '-01') AS ord_month --------------- 注文月をyyyy-mm-01の形に変換
,tran_t.order_id
,tran_d_t.price
FROM `トランザクションデータテーブル` AS tran_t
LEFT JOIN `トランザクション詳細テーブル` AS tran_d_t
ON tran_t.order_id = tran_d_t.order_id
)
/* 顧客のごとのdtから過去1年分のトランザクションデータ */
,month_grouped_trn AS(
SELECT
_user_dt.customer_id
,user_dt.dt
,_trn.ord_month
,COUNT(DISTINCT _trn.order_id) AS ord_cnt ---------------------- 購入回数
,IFNULL(SUM(_trn.price), 0) AS price ---------------- 購入金額：NULLのレコードは０円とする
FROM _user_dt
LEFT JOIN _trn
ON _user_dt.customer_id = _trn.customer_id
AND (_trn.ord_month BETWEEN DATE_SUB(user_dt.dt, INTERVAL 11 MONTH) AND _user_dt.dt) -------- dtから過去1年分のデータを紐づける
GROUP BY
_user_dt.customer_id
,user_dt.dt
,_trn.ord_month
)
- 顧客・年月ごとに購入回数・購入金額を集計し、その結果によって顧客ランクを付与する
ランクを付与できない（=1年以上購入がない）顧客を休眠顧客と定義します
,_proceeds_by_customer AS(
SELECT
customer_id
,dt
,SUM(ord_cnt) AS ord_cnt_sum
,SUM(price) AS price_sum
FROM month_grouped_trn
GROUP BY
customer_id
,dt
)
-- ,_customer_rank_t AS(
SELECT
customer_id
,dt
,ord_cnt_sum AS ord_cnt
,price_sum AS price
,CASE
WHEN ord_cnt_sum >= 2 AND price_sum >= 19800 THEN 'A'
WHEN ord_cnt_sum = 1 AND price_sum >= 19800 THEN 'B'
WHEN ord_cnt_sum >= 2 AND (price_sum BETWEEN 4500 AND 19799) THEN 'C'
WHEN ord_cnt_sum = 1 AND (price_sum BETWEEN 4500 AND 19799) THEN 'D'
WHEN ord_cnt_sum >= 2 AND price_sum < 4500 THEN 'E'
WHEN ord_cnt_sum = 1 AND price_sum < 4500 THEN 'F'
ELSE '休眠' END AS customer_rank
FROM _proceeds_by_customer
ORDER BY customer_id, dt
おわりに
顧客ランクは購買状況によって変動するので、一般的なUU数のカウントなどよりも複雑な処理が必要になることがあります。より実践的なクエリの書き方が必要な際は参考にしてみてください。
データ活用でお困りの方はこちら
データ活用でお困りの方へ
私たちDX-Accelerator事業では、データ活用についての様々なスキルを持った人材が常駐でデータ活用支援を行うサービスを提供しています。
当事業はローンチから約3年（24年9月時点）ですが、これまでに様々な業界・業種のお客さまのお手伝いをさせていただいております。
少しでも興味を持ってくださったり、すでにご相談をしたいことがある方はお気軽にご相談ください。現在あなたの組織のフェーズがどこにあるかは関係ありません。まずはお話をしましょう。
もう少しサービスについて知りたい方はサービス紹介資料もご用意しています。
この記事をシェア
クリップボードにコピーしました
