第5回　多重クロス集計1
ビッグデータによる売れ筋商品の分析

店舗ごとの売れ筋商品の分析と販売価格の分析

前回は、2万3千件弱の販売管理のデータを使って分析を行いました。 今回は、ID-POSのデータを使って分析を行います。

Excelで取り扱えるデータ量ですが、最大104万8,576行の制限があるため、ID-POSの全データのうち、4店舗、6ケ月分の菓子の売上データ、83万件のデータを使います。

全商品を対象とした2年分のデータを分析する場合、ほかのツールを使用することになりますが、本講座で解説している手順と手法は、その場合でも適用することができます。

はじめに、｢おさらい｣もかねて、次の手順を行ったあとに、商品と年齢、店舗による3次元のクロス集計について解説します。

・目標の設定
・データクレンジング
・確定データによる分析

ID-POSのデータを使ったExcelによる多重クロス集計の仕方を動画で紹介しています。
項目ごとにご覧いただく方法と、まとめてご覧いただく方法がございます。

【項目ごとにご覧いただく場合】
【まとめてご覧になる場合】 ※動画で紹介している操作などの詳細については、以下をお読みください。

目標の設定

分析をはじめる前に、どのような項目があるかを示します。

項目名	意味
売上KEY	レシート番号に相当
店舗CD	店舗ごとに振られた番号(今回は4店舗)
商品KEY	商品に割り当てられた番号
利用日	商品を買上げた日(今回は2013年7月1日～2013年12月31日)
利用曜日	商品を買上げた曜日(１～７：１が日曜日)
利用時刻	商品を買上げた時刻
カード番号	会員が持っているカードの番号
会員区分	0：非会員　1:会員
税抜金額	税抜きの買上げ金額
税込金額	税込みの買上げ金額
点数	買上げた商品の数
利益	買上げた時の利益
生年月日	会員で生年月日を登録してある人のみ記入
商品名	商品KEYに対応した商品名(部門名４に対応：複数有り)
部門名１	嗜好食品固定
部門名２	菓子固定
部門名３	菓子の分類
部門名４	部門名３の分類

これらの項目から、｢売れ筋商品の選定｣を目的に分析を行います。

データクレンジング

最初に、データの抜けや異常値がないかを確認し、データのクレンジングを行います。
今回は、Excelのフィルター機能を使用します。

• ID-POSデータをExcel上に展開し、[データ]タブをクリック
• 表内のセルをクリック(場所はどこでも構いません)
• [並べ替えとフィルター]グループの＜フィルタ＞ボタンをクリック
• 調べたい項目の表頭の＜▼＞ボタンをクリック

各項目でフィルター機能を使うと、項目に存在する値がすべて表示されます。
データの抜けがあった場合、値の最終セルに｢空白セル｣という名前で表示されます。
ただし、異常データかどうかは、値を見て判断します。

各項目の信頼度の結果は次の通りです。

列名	項目名	内容(値)	信頼度
A	売上KEY	201307010000400001001210～201307050000410006004212	問題なし
B	店舗CD	40、41、45、46	問題なし
C	商品KEY	49～113431	商品KEYの数と商品名の数が一致すると問題なし
D	利用日	20130701～20131231(連続で日付順)	問題なし
E	利用曜日	1、2、3、4、5、6、7	問題なし
F	利用時刻	114452～114927(不連続で57種類)	異常値(使用できない)
G	カード番号	0,2900010074184～2900013557585	問題なし
H	会員区分	0、1	問題なし
I	税抜金額	-6349～16286	問題なし
J	税込金額	-6656～17100	問題なし
K	点数	-63～450	問題なし
L	利益	空白セル	データ抜け(項目自体不要)
M	生年月日	19030101～20050101、空白セル	一部虚偽の可能性あり
N	商品名	１１栗原さんちのまろにが抹茶85g＋5g～萬藤粉未寒天4g×5	商品KEYの数と商品名の数が一致すると問題なし
O	部門名１	嗜好食品	問題なし
P	部門名２	菓子	問題なし
Q	部門名３	アイスクリーム～冷凍菓子	問題なし
R	部門名４	アイスクリーム他～和風半・生菓子	問題なし

以上で今回のデータには問題がないことが分かりました。

データ加工と確定データの作成

項目｢生年月日｣のデータから｢年齢｣を求め、年齢別のデータ分析に利用できるようにします。 また、｢金額｣を｢点数｣で割って｢単価｣を求めデータ分析で利用できるようにします。
このように、分析しやすいように計算を行って作成したデータを｢加工データ｣といいます。

年齢

年齢は、次のような操作を行うことで求められます。

• M列[生年月日]とN列[商品名]の間に列を挿入し、項目名を｢年齢｣とします。
  (｢年齢｣がN列となります)
• セル｢N2｣に次の数式を挿入します。
  
  ｢=IF((D2-M2)/10000>200," ",INT((D2-M2)/10000))｣
  (D列は、｢利用日(購入日)｣)
• セル｢N2｣をコピーし、最後の行まで貼り付けます。

※数式で作成したデータは、そのまま分析に使用すると誤った集計を行う可能性があります。
　作成したデータ全てをコピーし、[貼り付け]オプションの[値]で貼り付けましょう。
　貼り付ける場所は、作成したデータの隣に｢I列｣に挿入する形が良いでしょう。

単価

単価は、次のような操作を行うことで求められます。

• L列[利益]とK列[点数]の間に列を挿入し、項目名を｢単価｣とします。
  (｢単価｣がL列となります)
• セル｢L2｣に次の数式を挿入します。
  =ROUND(I2/ABS(K2),1)
• セル｢L2｣をコピーし、最後の行まで貼り付けます。

※数式で作成したデータは、そのまま分析に使用すると誤った集計を行う可能性があります。
　作成したデータ全てをコピーし、[貼り付け]オプションの[値]で貼り付けましょう。
　貼り付ける場所は、作成したデータの隣に｢I列｣に挿入する形が良いでしょう。

データ加工を行って加工データを作成し、データ分析用に出来上がったデータを｢確定データ｣といいます。

分析を行う前に、｢確定データ(加工データを加えた全体データ)｣について、各項目の意味も含めて示します。

列名	項目名	内容(意味)
A	売上KEY	レシート番号に相当。商品を複数購入した場合は、購入商品ごとに同じ売上KEYとなる(全体で833 359件)。
B	店舗CD	店舗ごとにつけられるコード。今回は4店舗。
C	商品KEY	商品に対応したコード。
D	利用日	商品購入日。2013年7月1日～ 2013年12月31日
E	利用曜日	商品購入曜日。1は日曜日。以降順番で7は土曜日。
F	利用時刻	商品購入時間。今回は間違ったデータ。(使用不可)
G	カード番号	会員のカード番号。0は未会員。
H	会員区分	0は未会員。1は会員。
I	税抜金額購	入商品の税抜きの金額。マイナスの場合は返品。
J	税込金額購	入商品の税込の金額。マイナスの場合は返品。
K	点数	購入商品の個数。マイナスの場合は返品。
L	単価(式)	税抜価格を点数の絶対値で割る式(単価も返品の場合はマイナス)。
M	単価	単価(式)をコピーし、値で貼り付けたもの。(分析ではこれを使用)
N	利益	未使用。(使用不可)
O	生年月日	会員で生年月日を登録している人の生年月日。未登録者有り。
P	年齢式1	=IF((D2-O2)/10000>200," ",INT((D2-O2)/10000))
Q	購入年齢	購入時の年齢。会員の生年月日によっては2つの年齢がある。
R	年齢式2	=IF((20131231-O2)/10000>200," ",INT((20131231-O2)/10000))
S	12月年齢	12月31日現在の会員の年齢。
T	商品名	商品個々の名前。商品KEYと1対1に対応。
U	部門名１	嗜好食品(固定)
V	部門名２	菓子(固定)
W	部門名３	アイスクリーム～冷凍菓子
X	部門名４	アイスクリーム他～和風半・生菓子

売れ筋商品分析

Excelのピボットテーブルを使い、｢個数｣と｢売上金額｣の上位商品を調べ､｢売れ筋商品｣を見つけます｡ 全体の結果はもちろん、実際に販売している店舗ごとの｢売れ筋商品｣の方が重要です｡ また、季節により｢売れ筋商品｣が違ってくる可能性もあります。たとえば、夏季にはアイスクリームが売れることが想定できます。
｢売れ筋商品｣の分析として、次の分析を行います。

全体の上位20品目

・商品が売れた個数を調べる場合：
　｢商品名(T列)｣を｢点数(K列)｣で集計
・商品の売れた金額を調べる場合：
　｢商品名(T列)｣を｢税抜金額(I列)｣あるいは｢税込金額(J列)｣で集計
※集計する時は、いずれも｢データの個数｣ではなく｢合計｣で集計します。
※｢データの個数｣で集計した場合、｢A列｣の｢売上KEY(商品ごとのレシート)｣の個数となり、目的の集計とは異なる値になりますので、必ず｢合計｣で集計するようにします。

具体的な操作方法(個数の場合)

• [挿入]タブの[テーブル]グループの[ピボットテーブル]をクリック
• [ピボットテーブルの作成]ダイアログボックスの＜OK＞ボタンをクリック

クリックして拡大
• [ピボットテーブルのフィールド]が表示されますので、次の操作を行います。
  (1)[商品名]を[行]ボックスにドラッグ
  (2)[点数]を[Σ値]ボックスにドラッグ
  ※商品ごとのレシートの枚数が表示されます。
  
• [Σ値]ボックスの[データの個数]を[合計]に変更し、購入された｢商品数｣にします。
  次のいずれかの操作を行います。
  ・[Σ値]ボックスの[データの個数／点数]の右にある＜▼＞ボタンをクリックすると表示されるプルダウンメニューから、[値フィールドの設定(N)]をクリック
  ・ピボットテーブル上の[データの個数／点数]の行を右クリックすると表示されるプルダウンメニューから、[値フィールドの設定(N)]をクリックします。[値フィールドの設定]ダイアログボックスの[集計方法]タブから、[合計]をクリック
• 次の操作を行い｢点数｣を降順に並べ替えます。
  (1)[データの個数／点数]の行を右クリック
  (2)プルダウンメニューから、[並べ替え]をクリック
  (3)プルダウンメニューから[降順]をクリック
  

全体における上位の売れ筋商品がわかります。

各店舗の上位20品目

全体の上位20品目で求めたピボットテーブルを使います。
操作方法は、次の通りです。

• [ピボットテーブルのフィールド]を表示
• [店舗CD]を[列]ボックスへドラッグ

店舗別の売れ筋商品が分かります。

ただし、総計で降順になっているので、店舗別の降順にはなっていません。
店舗別に降順にするには、店舗別のデータのあるセルを右クリックし、後は前節の全体の上位20品目と同様の方法で行います。
この表から次のことがわかります。

実際にどのようになっているのか、あるいはどうしてそうなったのかを分析してみます。
分析の概要を次に示します。

●各店舗および全体の上位20品目
｢点数｣と｢売上高(税抜)｣で調べ、｢売れ筋商品｣を分析します。
｢売上高｣は、[税抜金額]を[Σ値]ボックスにドラッグします。
｢点数｣で得られた上位20品目と｢税抜金額｣で得られた上位20品目を並べ、同じ名前の品目があれば｢売れ筋商品｣と思われます。
結果を次に示します(画像をクリックすると拡大表示されます。背景が黄色のセルが該当)。

●｢店舗41｣で｢洋風半・生菓子｣の半分以上を売っている理由

店舗により｢売れ筋商品｣が違うのは、その店舗のある地域特性の可能性があります。
商品購入者の嗜好が影響するので、その要因を考えて分析する必要があります｡ 嗜好に影響を与える要因としては、年齢が考えられます｡ 以降、｢年齢によって好まれるお菓子に違いがある｣と仮説を立て分析を行います。
この分析については、年齢を何種類かでカテゴライズする必要があるため、次回の｢再カテゴリー化｣で行います。

●各店舗の総計では、｢店舗46｣が他の店舗の6割程度しか売っていない
売れない要因として内的要因と外的要因があります。

今回は、｢他店と比べ会員数が6割ほど少ない｣という仮説をたて分析します。
この分析も会員に関する分析を行うため、次回の｢再カテゴリー化｣で行います。

全体の上位20品目の月別の推移

全体の上位20品目で作成したピボットテーブルを利用し、次の操作を行います。

• ピボットテーブルの[利用日]を[列]ボックスへドラッグ
  [列ラベル]の下に[20130701(2013年7月1日)]が表示され、右に次の日が表示され、最終の[20131231]まで表示されます(最後は｢総計｣で各商品の合計が表示されます)。
• [20130701]のセルから[20130731]のセルまでを範囲指定
  (最初のセル[20130701]のセルをクリックし、[Shift]キーを押しながら[→]キーを押すとグレーの部分(範囲)が右に移動するので、最後の[20130731]まで移動したら、キーを押すのをやめます)
• [ピボットテーブルツール]の[分析]タブ - [グループ]の[グループの選択]をクリック
• [行ラベル]の[グループ１]をクリックし、｢7月｣と入力して[Enter]キーを押します
• ｢7月｣の右に＜－＞ボタンをクリックすると7月分の合計が表示されます

1～5までの操作を12月まで繰り返すと月別の売り上げ推移が得られます。


(画像をクリックすると拡大表示されます)
●｢アイスクリーム系｣に関しては、7月・8月の｢販売個数｣や｢売上｣が多く、それ以降は少なくなる傾向があります。 ｢全体(総計)｣にも同様な傾向がありますが、12月に盛り返しています。これは、クリスマスや年末商戦などの12月のイベントが原因と考えられます。
なお、詳細の分析に関しては、商品のカテゴリー化とも関連するので、次回の｢再カテゴリー化｣で行います。

販売価格

｢販売価格｣は、｢売上｣や｢販売個数｣とも密接に関連します。｢販売価格｣は単純に決められませので、今回は傾向を見るだけにします。

• [ピボットテーブルのフィールド]の[商品名]を[行]ボックスへドラッグ
• [単価]を[行]ボックスにある[商品名]の下にドラッグ
• [ピボットテーブルのフィールド]の[点数]を[Σ値]ボックスにドラッグ
• ｢点数｣は｢売上KEY｣の個数(データの数)で、一つの｢売上KEY｣で複数個の購入もあるので、これを合計にする必要があります。
  [データの個数／点数]の列の中のどれか一つのセルを右クリック、あるいは[Σ値]ボックスの[データの個数／点数]の＜▼＞ボタンをクリックして表示されるプルダウンメニューから[値フィールドの設定]を選択します。
• [値フィールドの設定]ダイアログボックスから[集計方法]タブの[合計]を選択し、＜OK＞ボタンをクリック

各商品の｢販売単価｣と単価に対応した｢販売点数｣が表示されます。
表を分析すると、次の傾向があることが分かります。


また、｢曜日｣による販売の違いを見るため、今の表に｢曜日｣を加えたクロス集計を行います。
この表を作成するためには、現在の[ピボットテーブルのフィールド]の[利用曜日]を[列]ボックスにドラックします。 結果、下表が表示されます。
総計からは、｢曜日１｣が一番多く、次に｢曜日３｣、｢曜日７｣と続きます。
｢曜日１｣は｢日曜日｣で、｢曜日７｣は｢土曜日｣なので、｢土曜日曜｣に多く売れていると思われます。 個別でも、多く売れている価格では、同じような傾向があります。