SQLでバスケット分析(信頼度・リフト値算出)を実行

18284 ワード
データ分析 アソシエーション分析 SQLite3 SQL SQL テキストリンク

はじめに

こんにちは。2018年9月入社でデータ分析担当の@Tawasshyです。
この記事はEnigmo Advent Calendar 2018の3日目です。

弊社ではファッションECサイトであるBUYMAを展開しております。
売り手となるバイヤー(パーソナルショッパー)は世界中に在住しており、多種多様な商品を買い付けして膨大なSKUが存在します。
一方で、買い手側の購買行動も多岐に渡るのも必然となります。
そのような複雑な関係性を分析する状況においても、今回紹介するバスケット分析のような、基本的とも言える分析手法は解釈もしやすく、効果的な手段の一つになり得ると思っております。

バスケット分析について

バスケット分析は何と何の商品が一緒に買われているかに着目する分析方法であり、マーケット・バスケット分析、アソシエーション分析とも呼ばれています。

一般的な話

有名な事例としては、「おむつとビール」が同時に買われやすい、というルールの発見があります。
また、Agrawal氏らが1994年に発表した論文「Fast algorithms for mining association rules」（IBM Almaden Research Center）が、この分析手法が普及したきっかけとなったことが有力な説のようです。

なぜSQLでやるのか

バスケット分析はR言語のarulesというライブラリを使うのが、もっとも手軽な方法であると思いますが、今回はSQLでの実行を紹介します。

弊社ではエンジニアだけではなく、ディレクターやマーケター、カスタマサポートといったビジネスサイドのメンバーもSQLを日常的に叩いており、SQLでの分析の幅を拡げることに貢献できたら良いなと思ったことがSQLでやる理由です

バスケット分析詳細

まず、二つの商品X、Yがあったとします。
商品Xが買われた場合、商品Yという商品を買う確率を扱います。その指標として以下の指標を扱います。

今回算出する指標

  • 信頼度(confidence)
    商品Xが買われた場合に、商品Yを買う確率。

  • リフト値
    商品Yが買う確率が、商品Xを買った場合に商品Yを買う確率がどれだけ変化したかの倍率。
    (信頼度➗商品Yが買われる確率)
    Xを買ってYを買う確率がどれだけ持ち上がったかと解釈できる。

今回算出しない値

  • 支持値(support) XとYが同時に買われている確率を示します。バスケット分析においてはこれも重要な指標ですが、今回は説明を割愛します。

環境構築について

手元のローカルマシンで簡単に再現できるのでSQLiteでやってみます。
macOSの場合は以下のようにbrewで一発です。なお、Windowsでも手軽にインストールできますが、ここでは割愛します。

$ brew install sqlite3

データの準備について

SQLiteを起動します。カレントディレクトリにbasket.sqlite3というの名のDBが生成され、DBに接続した状態となります。

$ sqlite3 basket.sqlite3

無事に起動できたらこのようにterminalにこのように表示されます。

sqlite>

まず、にテーブルの定義を行います。

create table sales_history(
  item_category char(12),
  user_id char(8)
);

購買履歴を作成します。

insert into sales_history values
  ('トップス','UID100001'),
  ('財布','UID100001'),
  ('財布','UID100002'),
  ('トップス','UID100002'),
  ('靴','UID100002'),
  ('トップス','UID100002'),
  ('アウター','UID100002'),
  ('アウター','UID100102'),
  ('ワンピース','UID100103'),
  ('靴','UID100104'),
  ('財布','UID100104'),
  ('靴','UID100105'),
  ('靴','UID100106'),
  ('トップス','UID100107'),
  ('トップス','UID100108'),
  ('靴','UID100109'),
  ('財布','UID100110'),
  ('靴','UID100111'),
  ('トップス','UID100111'),
  ('靴','UID100112'),
  ('靴','UID100112'),
  ('トップス','UID100113'),
  ('財布','UID100113'),
  ('ワンピース','UID100114'),
  ('ワンピース','UID100114')
;

これでデータの準備はできました。

バスケット分析の実行

信頼度の算出


select
    combi_count.item_category,
    combi_count.item_category2,
    cast(combi_count.order_count as real)/item_count.order_count as confidence
from(
  select
      item_category,
      count(distinct user_id) as order_count
  from
    sales_history
  group by
    item_category
  )item_count
inner join(
  select
      X.item_category,
      Y.item_category as item_category2,
      count(distinct X.user_id) as order_count
  from
    sales_history as X
  inner join
    sales_history Y
       on X.user_id = Y.user_id
       and X.item_category <> Y.item_category
  group by
    X.item_category,Y.item_category
)combi_count on combi_count.item_category = item_count.item_category
;

以下のように出力されます。
商品X、商品Y、信頼度という順番です。

アウター|トップス|0.5
アウター|財布|0.5
アウター|靴|0.5
トップス|アウター|0.166666666666667
トップス|財布|0.5
トップス|靴|0.333333333333333
財布|アウター|0.2
財布|トップス|0.6
財布|靴|0.4
靴|アウター|0.142857142857143
靴|トップス|0.285714285714286
靴|財布|0.285714285714286

財布を買ったユーザーがトップスを買う信頼度がもっとも高いことが分かります。

リフト値も追加して算出

上のsqlが書ければ、全体でYが買われている確率を算出し、joinして信頼度を割ればリフト値が算出できます。

select
    combi_count.item_category,
    combi_count.item_category2,
    cast(combi_count.order_count as real)/item_count.order_count as confidence,
    cast(combi_count.order_count as real)/item_count.order_count / AllBuyY.order_count as lift
from(
  select
      item_category,
      count(distinct user_id) as order_count
  from
    sales_history
  group by
    item_category
  )item_count
inner join(
  select
      X.item_category,
      Y.item_category as item_category2,
      count(distinct X.user_id) as order_count
  from
    sales_history as X
  inner join
    sales_history Y
       on X.user_id = Y.user_id
       and X.item_category <> Y.item_category
  group by
    X.item_category,Y.item_category
)combi_count on combi_count.item_category = item_count.item_category
-- 全体でYが買われている確率を算出してjoinする
inner join
(
  select
      item_category,
      cast(count(distinct user_id) as real) /
      (select count(distinct user_id) from sales_history) as order_count
  from
    sales_history
  group by
    item_category
) AllBuyY on combi_count.item_category2 = AllBuyY.item_category
;

以下のように、出力されます。
商品X、商品Y、信頼度、リフト値を出しております。

アウター|トップス|0.5|1.25
アウター|財布|0.5|1.5
アウター|靴|0.5|1.07142857142857
トップス|アウター|0.166666666666667|1.25
トップス|財布|0.5|1.5
トップス|靴|0.333333333333333|0.714285714285714
財布|アウター|0.2|1.5
財布|トップス|0.6|1.5
財布|靴|0.4|0.857142857142857
靴|アウター|0.142857142857143|1.07142857142857
靴|トップス|0.285714285714286|0.714285714285714
靴|財布|0.285714285714286|0.857142857142857

信頼度、リフト値について

ある商品を買う確率としての指標として、どちらが大事というのはケースバイケースとなりますが、
両方が高い値でないとユーザーの傾向を示す効果的な分析にならないことが言えます。

リフト値が高く、信頼度が低い場合

全体で商品X, あるいは商品Y自体を買われている確率が全体で低い場合に起きます。
商品Xが買われることが少ない為、リフト値が高い値を示してもYを買う確率は小さいままになります。
また、商品Y自体を買われている確率が全体で低い場合は、リフト値が掛けかれる元の確率が低いことになる為、Yを買われる確率は低いままということになります。

リフト値が低く、信頼度が高い場合

何もしなくてもYを買われる確率が高く、Xを買っても買わなくても、Yを買う確率の増減が小さい状態を示します。

そもそも購買履歴が少ない場合…

偶然、信頼度、リフトが高く出るということが起こり得るので、Xを買ったユーザーYの購買促進を行うキャンペーンなどの施策を行っても有効に働かない場合があります。

実際にDBに接続して行う場合は

同じテーブルへの問い合わせが散在しているので、PostgreSQL, MySQL, SQLServer等ではwith句は必須であると思います。

SQLでやるのがめんどくさいなと思ったら..

データを抽出する必要がありますが、R言語でarulesというライブラリを使えばもっと手軽にできますし、無料です。

さいごに

弊社ではデータ分析に取り組む一方、データ分析基盤を整え、データ活用の効率化とアクセスビリティを推進しております。
エンジニアはもちろん、データを活用しながら「世界を買える」ビジネスに挑戦してみたいメンバーのジョインをお待ちしております！

やんちゃであれ！
ENIGMO7より

参考

perl学習1
Pythonファイル傍受、SSHアップロードサーバー