小売業の顧客データ分析SQLクエリを “Pythonで” 書く方法

SQL

2021.10.15

INDEX

小売業の顧客データ分析SQLクエリを “Pythonで” 書く方法

小売業の顧客データ分析SQLクエリを “Pythonで” 書く方法

今回の記事は、次のような方を対象としております。

・クロスセル・アップセルを促進したいマーケティング担当者
・SQLとPythonを使って、データの集計を効率よく行いたい方
・自社の購買データを用いて分析を行いたい方
・顧客があわせ買いをしているかどうかに注目して集計を行いたい方

具体的には、あるユーザーがいままでにどのような商品を購入しているかに注目して、顧客をパターン分けするSQLクエリを、Pythonですぐ出力する方法の紹介です。コピー・ペーストして一部変更することで、様々なパターンに対応ができます！

次のようなケースを考えてみます

■ケース
データサイエンスに関わる電子書籍を複数発行している、D社がある。D社の電子書籍は、「マーケティング」「統計」「プログラミング」「機械学習」の4ジャンルがある。　D社内で、複数のジャンルを購入する顧客の特徴を掴んで、分析をしようという提案がなされた。これは例えば、「プログラミング」ジャンルのみ購入する顧客、「マーケティング」「統計」2つを購入する顧客、と購入属性ごとに顧客を分類し、その後に分析を行いたいということである。そのためにはまず、SQLを用いて、自社の購買データベースから、顧客の抽出を行う必要がある。どのようなSQLクエリを書けばよいだろうか？

データベースは今回の課題に関係ないところは省略した、簡略化したものを題材にします。上の一部からは、顧客IDが2の方が、プログラミングと機械学習を購入しているということがわかります。これを一部だけからでなく、データベースすべてを見てどのように抽出すべきか？が今回の課題です。

課題に取り組む前になぜ今回の課題が難しい・大変かを述べます。

A.今回は「マーケティング」「統計」「プログラミング」「機械学習」の4ジャンルを扱います。4つ以上のジャンルを扱うと、ベン図で表すことが不可能となります。(注1)カテゴリが2つ、3つのときは次図のようにベン図を書いてイメージがしやすいのですが、ベン図が書けないとなると途端に難しく感じられますね。

B.4ジャンルそれぞれについて、買う・買わないの2通りがあるので、買う・買わないの組み合わせが、2 × 2 × 2 × 2 =16 通りあります。すべて入力するのも可能な範囲ではありますが、16分類は人力でクエリを作るのはいかにも面倒そうですね。また、もしジャンルが7種類だとすると、128通りにユーザーを分類することとなり、手作業で打ち込むとミスの危険が非常に高くなります。

注1.厳密には複雑な図形を使うと4ジャンルのときのベン図も書けますが難しいので今回は割愛します。また、今回の趣旨とは異なりますが、カテゴリが4つ以上のときにも対応して全体の場合の数を求められる、「包除の原理」という定理があります。ベン図を考えなくても場合の数が求められて便利なので、興味のある方は調べてみてください。

今回は、WITH句を用いてカテゴリ別に顧客を分類するクエリを、Pythonで一瞬で出力する雛形となるものを紹介します。いくつのジャンルに分類する場合であっても、次の Python コードの一部を変更してから実行して、出力をSQLクライアントにコピー・ペーストすることで、抽出が行なえます。

実行結果の見方について解説します。target_1011 , target_1001 などをWITH句で作成しております。これらは、1が当てはまる、0が当てはまらないを表し、Yで入力したカテゴリに当てはまっているかどうかで、16通りに顧客IDを分類しています。

今回は、Y = [‘マーケティング’ , ‘統計’ , ‘プログラミング’,’機械学習’]ですので、target_1011 は、

　マーケティング：購入した
　統計：購入していない
　プログラミング：購入した
　機械学習：購入した

となっている人の顧客IDをWITH句でまとめたものということです。

やや難しいコードも使っていますが、そういったことを理解しなくても、応用できるコードとなっております。Y,X,table,pmid,group に入力するものを変更するだけで、様々なケースに対応できます。

# Y に区分けしたいカテゴリ名を入力 いくつでもOK
Y = ['マーケティング' , '統計' , 'プログラミング','機械学習']

# n はカテゴリ数

n= len(Y)

# X に出力時の名称を入力、なんでもいいのでとりあえず target_ にしてある

X = ['target_'] * len(Y)

# table に大本のテーブル名を入力

table = 'target'

# pmid にカテゴリ別にわけたい対象(顧客idなど)を設定

pmid = '顧客ID'

# group に今回分けたい対象を設定

group = '商品ジャンル'
# ’分けたい対象＋カテゴリ名’の文字列が要素であるリスト、CTGを作成
# この例だとCTG=[’target_マーケティング’,’target_統計’,’target_プログラミング’,'target_機械学習']となっている

CTG = [x+y for (x,y) in zip(X,Y)] 

#　各カテゴリ別の定義 カテゴリ定義により毎回異なる

for i in range(len(CTG)):
if i == 0 :
　　　print('WITH {} as ('.format(CTG[i]))
else:
　　　print(',{} as ('.format(CTG[i])) 
print('SELECT {} FROM {}'.format(pmid,table))
print('WHERE {} = \'{}\''.format(group,Y[i]))
print(')')

# itertools のインポート 今回は説明しないが便利

import itertools 

# 1,0 に対応する文字列の設定 適宜 IN と NOT IN から変更

def func(x):
if x == 1:
　　print('{} IN'.format(pmid))
else:
　　print('{} NOT IN'.format(pmid))

# 定型文 今回は、D = ( SELECT 顧客ID FROM
 
D = '( SELECT {} FROM '.format(pmid)

# 1,0 の組み合わせをlist にする。itertoolsを用いている

CL = list(itertools.product([1,0],repeat = n))

# WITH句を用いて、全パターンにユーザーを分けたテーブルを作成する

for i in range(len(CL)):
ind = ''
for j in range(n):
  ind += str(CL[i][j])
print(',target_{} as ( '.format(ind))
print('SELECT * FROM {}'.format(table))
print('WHERE ')
for k in range(n):
  if k != 0:
     print('AND')
  func(CL[i][k])
print(D+CTG[k]+')')
print(')')

ここまでがコードとなります。次が実行結果です。実行結果を見ても、長いクエリを一瞬でPythonが書いてくれていることが伝わるのではないかと思います。是非ご利用ください！

↓ここから実行結果

WITH target_マーケティング as (
SELECT 顧客ID FROM target
WHERE 商品ジャンル = 'マーケティング'
)
,target_統計 as (
SELECT 顧客ID FROM target
WHERE 商品ジャンル = '統計'
)
,target_プログラミング as (
SELECT 顧客ID FROM target
WHERE 商品ジャンル = 'プログラミング'
)
,target_機械学習 as (
SELECT 顧客ID FROM target
WHERE 商品ジャンル = '機械学習'
)
,target_1111 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_1110 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_1101 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_1100 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_1011 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_1010 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_1001 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_1000 as ( 
SELECT * FROM target
WHERE 
顧客ID IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0111 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0110 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0101 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0100 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0011 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0010 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0001 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID IN
( SELECT 顧客ID FROM target_機械学習)
)
,target_0000 as ( 
SELECT * FROM target
WHERE 
顧客ID NOT IN
( SELECT 顧客ID FROM target_マーケティング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_統計)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_プログラミング)
AND
顧客ID NOT IN
( SELECT 顧客ID FROM target_機械学習)
)

書いた人：藤田直樹（ふじたなおき）株式会社データミックス所属

東京大学理学部数学科を卒業。東京大学大学院数理科学研究科を修了。
大学受験の月刊誌「大学への数学」の執筆者であり、現在も不定期に同雑誌へ寄稿をしている。
大手生命保険会社での保険数理業務などを経て、2021年に株式会社データミックスに入社。主にデータサイエンスに関わるコンテンツの作成を担当。