顧客分析をする上で、購入順を意識することは多々あるかと思います。
少なくとも初回購入と2回目の購入とが売上データ上、すぐに判断できるなにかしらの項目は持っておいたほうが良いです。
一般的に初回購入をされた顧客がリピートし、2回目の購入に至ることをF2転換とよく言います。
F2のFはFrequency=頻度や回数を表し、数字の2は2回目の購入を意味します。
もちろん、3回、4回、5回と続けば、F2→F3、F3→F4、F4→F5とそれぞれの分析、場合によっては
F1→F2→F3→F4→F5と継続的にご購入いただく顧客が、全く同じ商品を定期的に購入しているのか
類似商品、あるいは全く別のカテゴライズされた商品を購入されているか
顧客の特性を時系列で持って分析する上で、購入順を明らかにしていることは
後々役立つことになりますし、
顧客育成という観点から商品シリーズの拡大縮小の判断基準としても使うことになります。
そこで今回は「row_number」を使って、売上データの顧客別の購入順をオーダー日の順に連番していくSQLをご紹介します。
なお、環境はPostgreSQL 9.6.2で確認しています。
まずデータの準備。
今回はデータ量が多いので、テーブル定義のみをご紹介します。
データはそのものは以下のGitHubをご確認ください。
missing_dates
sales.csv、product.csvをご利用ください。
https://github.com/Taihei19/missing_dates
sales table = 売上データ
No. | column id | data type | column name |
---|---|---|---|
1 | orderid | varchar(8) | オーダーID |
2 | orderdate | date | オーダー日 |
3 | customerid | varchar(6) | 顧客ID |
4 | store | varchar(1) | 購入店舗 |
5 | productid | varchar(3) | 商品ID |
6 | cost | numeric | 仕入単価 |
7 | salesprice | numeric | 売上単価 |
8 | quantity | numeric | 数量 |
9 | amountcost | numeric | 仕入金額 |
10 | amountsales | numeric | 売上金額 |
11 | profit | numeric | 粗利 |
product = 商品マスタ
No. | column id | data type | column name |
---|---|---|---|
1 | productid | varchar(3) | 商品ID |
2 | productname | varchar(50) | 商品名 |
3 | categoryid | varchar(3) | カテゴリID |
4 | categoryname | varchar(50) | カテゴリ名 |
5 | cost | numeric | 仕入単価 |
6 | salesprice | numeric | 売上 |
こちらのデータはとある架空のボクシングメーカーの2020年の卸売上データです。
正直、先に述べたF2転換の分析をする上ではBtoBデータとなるため適していませんが、今回はあくまでも購入順を連番することが目的となりますので、ご了承ください。
まずは以下のSQLを実行します。
こちらは売上データ(sales)と商品マスタ(product)を結合した大副帳型のフラットな結果となります。
--分析用データの大福帳型のフラットデータ
select s.orderid
,s.orderdate
,s.customerid
,s.productid
,p.productname
,p.categoryid
,p.categoryname
,s.salesprice
,s.quantity
,s.amountsales
from sales s
left join product p
on s.productid = p.productid
order by s.customerid
,s.orderdate
,s.orderid
結果は画像となり、恐縮です。
顧客customeridのA00001が複数に渡って購入していることがわかります。
早速、row_number()を使って、顧客の購入順をつけていきたいと思います。
そのためにはまず、以下の通り、orderid→orderdate→customeridで絞る必要がございます。
今回のデータは商品情報も含む明細データとなっている関係上、group by する必要がありますね。
select orderid
,orderdate
,customerid
from sales
group by orderid
,orderdate
,customerid
order by customerid
,orderdate
,orderid
こちらの結果に購入順をつけていきます。
以下がそのSQLです。
--顧客単位にオーダー日→オーダーID順に1から連番をふる
select orderid
,orderdate
,customerid
,row_number() over (partition by customerid order by orderdate,orderid) as frequency --顧客単位に購入順を連番
from sales
group by orderid
,orderdate
,customerid
order by customerid
,orderdate
,orderid
partition by は連番をつけていくグループの指定を意味します。省略可能ですが、省略した場合、全体に対して連番を振ることになるため、顧客分析する上では必須になります。
order by はそのままですね。partitionのグループ指定内での連番を振る順番指定ですね。こちらは必須となります。
結果、frequency列に顧客単位でオーダー日、オーダーID順で1から連番が振られたことを確認できます。
そして、初めに作成した大福帳のクエリにrow_numberで顧客単位に連番を振った結果を外部結合します。
select s.orderid
,f.frequency
,s.orderdate
,s.customerid
,s.productid
,p.productname
,p.categoryid
,p.categoryname
,s.salesprice
,s.quantity
,s.amountsales
from sales s
left join product p
on s.productid = p.productid
left join (select orderid
,orderdate
,customerid
,row_number() over (partition by customerid order by orderdate,orderid) as frequency
from sales
group by orderid
,orderdate
,customerid) f
on s.customerid = f.customerid
and s.orderid = f.orderid
and s.orderdate = f.orderdate
order by s.customerid
,s.orderdate
,s.orderid
これによりどの伝票が何回目の購入にあたり、どのような商品をどれだけ購入できたかが分かるようになり、分析する上でのデータ構造となりました。
以上、row_numberを使って顧客の購入順を連番する方法でしたが、実際の利用方法については別途ご紹介をいたします。
その際はBtoCデータでもって、F2転換やバスケット分析なども取り扱いやすいデータにいたします。
テストデータ作るプログラムを作成しますので、しばらくお待ちください。
なお、row_numberと似た関数で「rank」というものがございます。
こちらは順位をつけるときに使われますが、row_numberとの違いはrankは同値の場合、同じ順位(同じ値を返す)をつけてしまいます。
以下は例です。
こちらはあるボクサーの勝利回数の表です。
boxer | winfrequency |
---|---|
Saul Alvarez | 54 |
Anthony Joshua | 24 |
Gervonta Davis | 24 |
Naoya Inoue | 20 |
Teofimo Lopez | 16 |
Gennadiy Golovkin | 41 |
こちらについて勝利回数で順位を1位から振っていくSQLでrankとrow_numberとの違いをお見せします。
データは以下の通りです。
--テーブル作成
create table rankrow(
boxer varchar(50) --ボクサー選手名
,winfrequency numeric --勝利回数
);
--データセット
insert into rankrow (boxer,winfrequency)
values
('Saul Alvarez',54),
('Anthony Joshua',24),
('Gervonta Davis',24),
('Naoya Inoue',20),
('Teofimo Lopez',16),
('Gennadiy Golovkin',41);
以下のSQLで確認します。
select boxer
,winfrequency
,row_number() over (order by winfrequency desc)
,rank() over (order by winfrequency desc)
from rankrow
結果は以下の通りです。
boxer | winfrequency | row_number | rank |
---|---|---|---|
Saul Alvarez | 54 | 1 | 1 |
Gennadiy Golovkin | 41 | 2 | 2 |
Anthony Joshua | 24 | 3 | 3 |
Gervonta Davis | 24 | 4 | 3 |
Naoya Inoue | 20 | 5 | 5 |
Teofimo Lopez | 16 | 6 | 6 |
Anthony JoshuaとGervonta Davisと同じ勝利数の24勝ですが、row_numberは3、4と続く中、rankは同じ3が返されています。
この様に似たような値を返す関数とはいえ、適切な使い方をしていきたいですね。
以上、row_numberによる顧客別の購入順を連番する方法でした。
では。