はじめに こんにちは。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、信頼度という順番です。 商品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、信頼度、リフト値を出しております。 商品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より 参考 https://www.albert2005.co.jp/knowledge/marketing/customer_product_analysis/abc_association https://codezine.jp/article/detail/10284