1. ホーム
  2. データ活用塾
  3. ビッグデータ活用講座
  4. 第5回 多重クロス集計1

第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日)
利用曜日商品を買上げた曜日(1~7:1が日曜日)
利用時刻商品を買上げた時刻
カード番号会員が持っているカードの番号
会員区分0:非会員 1:会員
税抜金額税抜きの買上げ金額
税込金額税込みの買上げ金額
点数買上げた商品の数
利益買上げた時の利益
生年月日会員で生年月日を登録してある人のみ記入
商品名商品KEYに対応した商品名(部門名4に対応:複数有り)
部門名1嗜好食品固定
部門名2菓子固定
部門名3菓子の分類
部門名4部門名3の分類

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

データクレンジング

最初に、データの抜けや異常値がないかを確認し、データのクレンジングを行います。
今回は、Excelのフィルター機能を使用します。
  • ID-POSデータをExcel上に展開し、[データ]タブをクリック
  • 表内のセルをクリック(場所はどこでも構いません)
  • [並べ替えとフィルター]グループの<フィルタ>ボタンをクリック
  • 調べたい項目の表頭の<▼>ボタンをクリック

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

各項目の信頼度の結果は次の通りです。
列名項目名内容(値)信頼度
A売上KEY201307010000400001001210~201307050000410006004212問題なし
B店舗CD40、41、45、46問題なし
C商品KEY49~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商品名11栗原さんちのまろにが抹茶85g+5g~萬藤粉未寒天4g×5商品KEYの数と商品名の数が一致すると問題なし
O部門名1嗜好食品問題なし
P部門名2菓子問題なし
Q部門名3アイスクリーム~冷凍菓子問題なし
R部門名4アイスクリーム他~和風半・生菓子問題なし


    クリックして拡大
  • 「利用曜日」については、曜日に対応した値「1」~「7」となっており、昇順で並べ替えを行い、最初と最後の値を確認することで、値の信頼度を確認することができます。
  • 「商品名」と「商品KEY」は、1対1に対応していれば問題ありません。
    ピボットテーブルを使った検証方法は次の通りです。

    クリックして拡大
    1.「商品KEY」を[行]ボックスへドラッグします。
    2.[行ラベル]の「商品KEY」の数を数えます。
    ※[行ラベル]以外の行(セル)で、「COUNT関数」を使って数を数えます(行番号から計算しても「4,672個」と、同じ結果が得られます)。
    3.ピボットテーブルのフィールドで、[商品名]を[商品KEY]の下にドラッグします。
     ※「COUNT関数」の値は2倍に変化します。
      (各「商品KEY」に対応する「商品名」も表示され、[行ラベル]の範囲が倍になったため)

    クリックして拡大
    4.そのほかに行った計算と結果
     ・数値の入っているセル(COUNT関数):4,672個
     ・数字も含めた文字の入っているセル(COUNTA関数):9,344個
     ・ブランクのセル(COUNTBLANK関数):0個
    「商品KEY」は「4,672個」、「商品名」は「9,344-4,672=4,672個」あり、ブランクのセルがないことから「商品名」は「商品KEY」と1対1で対応していることが分かります。

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

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

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

年齢

年齢は、次のような操作を行うことで求められます。
  • 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))
S12月年齢12月31日現在の会員の年齢。
T商品名商品個々の名前。商品KEYと1対1に対応。
U部門名1嗜好食品(固定)
V部門名2菓子(固定)
W部門名3アイスクリーム~冷凍菓子
X部門名4アイスクリーム他~和風半・生菓子

売れ筋商品分析

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

全体の上位20品目

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

具体的な操作方法(個数の場合)
  • [挿入]タブの[テーブル]グループの[ピボットテーブル]をクリック
  • [ピボットテーブルの作成]ダイアログボックスの<OK>ボタンをクリック

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

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

各店舗の上位20品目

全体の上位20品目で求めたピボットテーブルを使います。
操作方法は、次の通りです。
  • [ピボットテーブルのフィールド]を表示
  • [店舗CD]を[列]ボックスへドラッグ

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

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

この表から次のことがわかります。
・各店舗により上位20品目が順位も含め変わる
・総計で1位の「洋風半・生菓子」の半分以上は「店舗41」で売られた
・最終行の各店舗の総計から、「店舗46」が他の店舗の6割程度しか売っていない

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

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

●「店舗41」で「洋風半・生菓子」の半分以上を売っている理由
・全体傾向は、「洋風・和風」を含め「半生菓子」が上位を占めている
・「アイスクリーム系」も上位を占めている
・特に「店舗45」は、「点数」・「売上高」ともにベスト10の半分以上が「アイスクリーム系」
・「店舗41」では、「洋風・和風」も含め「半生菓子」がベスト10に4品目入っている
・「店舗40」は「店舗41」と同じような傾向だが、「アイスクリーム系」の割合が少ない
・「店舗46」は「アイスクリーム系」の割合が多いが、「半・生菓子類」も多い

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

●各店舗の総計では、「店舗46」が他の店舗の6割程度しか売っていない
売れない要因として内的要因と外的要因があります。
内的要因としては、「販売員が少ない」、「サービス・対応が悪い」、「店舗が小さい」、「宣伝が少ない」等々があり、これらは、今回のデータからは分析できません。
外的要因としては、「地域の人口が少ない」、「工業地域でお菓子を買う人が少ない」等々の地域特性があります。

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

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

全体の上位20品目で作成したピボットテーブルを利用し、次の操作を行います。
  • ピボットテーブルの[利用日]を[列]ボックスへドラッグ
    [列ラベル]の下に[20130701(2013年7月1日)]が表示され、右に次の日が表示され、最終の[20131231]まで表示されます(最後は「総計」で各商品の合計が表示されます)。
  • [20130701]のセルから[20130731]のセルまでを範囲指定
    (最初のセル[20130701]のセルをクリックし、[Shift]キーを押しながら[→]キーを押すとグレーの部分(範囲)が右に移動するので、最後の[20130731]まで移動したら、キーを押すのをやめます)
  • [ピボットテーブルツール]の[分析]タブ - [グループ]の[グループの選択]をクリック
  • [行ラベル]の[グループ1]をクリックし、「7月」と入力して[Enter]キーを押します
  • 「7月」の右に<->ボタンをクリックすると7月分の合計が表示されます
1~5までの操作を12月まで繰り返すと月別の売り上げ推移が得られます。


(画像をクリックすると拡大表示されます)

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

販売価格

「販売価格」は、「売上」や「販売個数」とも密接に関連します。「販売価格」は単純に決められませので、今回は傾向を見るだけにします。
  • [ピボットテーブルのフィールド]の[商品名]を[行]ボックスへドラッグ
  • [単価]を[行]ボックスにある[商品名]の下にドラッグ
  • [ピボットテーブルのフィールド]の[点数]を[Σ値]ボックスにドラッグ
  • 「点数」は「売上KEY」の個数(データの数)で、一つの「売上KEY」で複数個の購入もあるので、これを合計にする必要があります。
    [データの個数/点数]の列の中のどれか一つのセルを右クリック、あるいは[Σ値]ボックスの[データの個数/点数]の<▼>ボタンをクリックして表示されるプルダウンメニューから[値フィールドの設定]を選択します。
  • [値フィールドの設定]ダイアログボックスから[集計方法]タブの[合計]を選択し、<OK>ボタンをクリック
各商品の「販売単価」と単価に対応した「販売点数」が表示されます。
表を分析すると、次の傾向があることが分かります。

・多くを販売している価格帯が存在する
・安く販売するものがある(見切り品などと思われる)

また、「曜日」による販売の違いを見るため、今の表に「曜日」を加えたクロス集計を行います。
この表を作成するためには、現在の[ピボットテーブルのフィールド]の[利用曜日]を[列]ボックスにドラックします。 結果、下表が表示されます。
総計からは、「曜日1」が一番多く、次に「曜日3」、「曜日7」と続きます。
「曜日1」は「日曜日」で、「曜日7」は「土曜日」なので、「土曜日曜」に多く売れていると思われます。 個別でも、多く売れている価格では、同じような傾向があります。
次回は、ID-POSデータから会員データを抽出して年齢別の再カテゴリー化を行い、店舗別・年齢別・商品別の売上高や、さらに月別を加えた季節ごとの売上高を分析します。


< 第4回 Excelを使ったクロス集計
第6回 多重クロス集計2 >
未来を作る意思決定に。新世代統計集計システム【Z-Adam】