「売上を伸ばしたいけれど、どうすればいいかわからない・・・」
そんな悩みをお持ちの方は多いのではないでしょうか。
日々の業務に追われ、膨大な売上データを前に、どのように分析すれば良いか悩んだ経験はありませんか？
実は、身近なExcelを活用することで、複雑な売上データをわかりやすく可視化し、顧客の行動パターンを発見することができます。
本コラムでは、CRMに取り組む際に必要なマーケティング分析のうち、Excelを使った実践的な売上分析の方法をわかりやすく解説していきます。
初級者から中級者の方向けに、Excelの基本的な関数を使った売上データの集計方法から、少し高度な分析手法である購買間隔分析まで、ステップアップできる内容となっています。
CRM分析の基本的な内容については、別記事「CRM分析とは？3つの分析軸と代表的な8つの分析手法」で解説していますので、あわせてご一読ください。
売上分析で使う基本ツール
売上分析には、様々なツールが用いられます。代表的なものとしては、Excel、BIツール、CRMツール、SFAツールなどが挙げられます。
●各ツールの特徴
- Excel：表計算ソフトとして広く知られており、基本的な集計やグラフ作成まで手広く行える。柔軟性が高く、様々な分析に対応可能である。
- BIツール：ビジネスインテリジェンスツールのこと。大量のデータを可視化し、複雑な分析が可能である。
- CRMツール：Customer Relationship Managementツールのこと。顧客の属性や購買履歴を一元管理し、顧客との関係性を深めるために用いる。
- SFAツール：Sales Force Automationツールのこと。見込み客管理、商談管理、売上管理などを一元化し営業活動を支援する。
売上分析にはこれらのツールが使われますが、中でもExcelは手軽に始められるという点で大きなメリットがあります。Excelさえあれば、特別なスキルや高価なソフトウェアを必要とせずに、基本的な売上分析を行うことができます。
売上分析とは？
Excelでの具体的な売上分析に入る前に、まずは売上分析とは何かを簡単におさえましょう。
売上分析とは、企業が今後の事業戦略に活かすため過去に得られた売上データを基に、何が売れたのか、いつ売れたのか、なぜ売れたのかなどを分析することです。
売上分析を行う目的は、大きく以下の3つに集約されます。
1.現状の把握
- どのような商品が売れているのか
- 購入している顧客は誰か
- どの販売チャネルが有効か
- 季節変動や地域差はあるか
現在の売上状況を詳細に把握することで、問題点や改善点を見つけ出します。
2.原因の究明
- 売上が伸び悩んでいる原因
- 新商品が思ったように売れない原因
- 特定の顧客の購買意欲が低下している原因
より効果的な対策を講じるために、各原因の背景にある要因を特定します。
3.将来の予測
- 将来の売上を予測し、事業計画を立てる
- 新商品の売上を予測し、生産計画を立てる
- マーケティング予算を最適化する
将来の事業展開の指標を決定するための基礎になります。
このように、売上分析は企業の成長には欠かせません。
次のパートからは専門的な売上分析ツールを使わずに、まずはみなさんに馴染みのあるExcelでできる売上分析を紹介していきます。
売上分析を行うときの準備
売上分析を行うには、明確な分析目的とそれに伴ったデータの準備が必要です。まずはやみくもに分析を進めるのではなく、売上分析から何を知りたいのかを明確にしましょう。分析の目的が曖昧なままでは、必要なデータが分からず分析結果も有効活用できません。
● 分析目的の代表例
- 売上向上：どの商品、顧客、販路に力を入れるべきか
- 新商品開発：新商品がどの程度売れるか予測する
- 顧客理解：顧客の購買行動を理解し、ターゲットを絞り込む
- 問題点の特定：売上減少の原因を特定する
分析目的が固まれば必要なデータも見えてきます。
ここでは売上分析に必要な基本的なデータを紹介します。
●売上分析に必要なデータ例
- 売上データ：売上高、販売数量、単価、販売日など
- 顧客データ：顧客ID、顧客属性、購入履歴、顧客満足度など
- 商品データ：商品コード、商品属性、商品カテゴリ、在庫状況など
- その他のデータ：販促活動データ、競合他社データ、GDP、消費者物価指数など
これらのデータを分析目的にあわせて収集し、適切に組み合わせて売上分析を進めていきましょう。
Excelで売上分析を行う方法
Excelを使って、店舗別に売上を集計したり、月別に集計したりすることを行っている方は多いのではないでしょか。簡単な集計であれば次に紹介するピボットテーブルを使わずにSUM関数（指定したセル内の数字の合計を求める関数）やAVERADGE関数（指定したセル内の数字の平均を求める関数）を用いて容易に行うことが可能です。
ここからは、関数での集計から一歩進んだピボットテーブルを使用しての商品別売上構成比と、購買間隔分析の方法を紹介していきます。
なお、今回の例では架空の売上明細から分析を行います。
分析にあたっては以下の項目が必要となります。
●必要な項目
- 注文を識別できる項目：注文番号など
- 商品を識別できる項目：分類コード、商品コードなど
- 顧客を識別できる項目：顧客IDなど
- 注文日
- 注文個数
- 注文金額
商品別売上構成比
ここからは、商品別に売上の構造を分析する方法を紹介します。
商品別売上構成比が明らかになると売れ筋の商品や、逆に売上に課題のある商品がわかります。これらがわかれば品揃えの方向性や販促方法の検討、商品開発などに活かすことができます。
また、購入された商品は顧客の最終的な選択といえるので、この分析を通して顧客の嗜好を理解することにもつながります。
それでは実際に商品別の売上を集計するために、売上明細を元にピボットテーブルを利用していきましょう。
まずは商品ごとの注文数と受注金額を下記の手順で集計しましょう。
1.明細部分全体を選択します
2.次に上部の「挿入」タブから「ピボットテーブル」「OK」の順にクリックします
3.下図のように行ラベルに「商品コード」、値に「合計/注文個数」「合計/注文金額」を設定します
これで商品ごとの注文数と注文金額が集計されました。
次に全体に占める商品ごとの売上個数や売上金額の割合を計算していきます。
1.出来上がったピボットテーブル全体をコピーして新しいシートに貼り付ける
2.D1に「売上構成比（個数）」、E1に「売上構成比（金額）」を入力する
3.D2に「＝B2/SUM(B:B)」を入力し、表の下までコピーする
4.同様にE2に「＝C2/SUM(C:C)」を入力し、表の下までコピーする
このように集計することで売上構成比を簡単に求めることができます。
また商品の種類が多い場合、個別の商品の売上を集計すると全体の傾向が見えづらくなることがあります。
そういった場合には、商品をカテゴリーに分類し、カテゴリーごとに傾向を見るといった方法を取ってみることもよいでしょう。
購買間隔分析の基本
次に紹介するのは購買間隔分析です。こちらもExcelを使用して分析を行うことが可能です。
購買間隔分析とは？
購買間隔分析はその名のとおり顧客が最後に商品を購入してから、次の購入を行うまでの期間を分析する手法です。売上分析の一種とも言えますが、より細かく顧客の行動パターンを把握するために行います。
顧客の行動パターンを把握できれば、より効果的なマーケティング戦略を立てることができるでしょう。
Excelで購買間隔分析を行う方法
それではここからは具体的にExcelを使って購買間隔分析を行っていきましょう。
ここでは売上分析に使用した売上明細データと同じデータを使用します。
1.売上明細全体を選択して、「データ」タブ内の「並べ替え」をクリックします
2.下図のとおり顧客ID（昇順）→商品ID（昇順）→購入日（昇順）でソートします
3.H1に「前回の購入からの経過日数」を入力します
4.H2に「=IF(AND(E2=E1,C2=C1),D2-D1,0)」を入力し表の下までコピーします
これで顧客ごとに同一商品を購入していた場合の購買間隔が計算できます。（経過日数が0となる場合は、その商品の初回購入であることになります）
次にピボットテーブルを使用してデータを可視化していきましょう。
5.上で作成した表全体を選択します
6.次に上部の「挿入」タブから「ピボットテーブル」「OK」の順にクリックします
7.下図のように行ラベルに「前回購入からの経過日数」、値に「データの個数/注文番号」を設定します
※列ラベルに「商品コード」や「分類コード」を選択することで、商品ごとやカテゴリーごとの購買間隔を集計することも可能です
8.作成したピボットテーブルの「行ラベル」のセルで右クリックし「グループ化」を選択します
9.下図のように「末尾の値」「単位」に数値を入力し「OK」をクリックします
今回は再購入までの期間が80日までかつ5日間区切りでデータをまとめます
ここまでの作業でピボットテーブルでの集計は完了です。
最後に集計結果をよりわかりやすくグラフ化します。
10.「挿入」タブの「ピボットグラフ」から「縦棒」のなかの「集合縦棒」を選択します
今回のサンプルでは、下図のように購入後30〜34日の時期に再購入の山があり、それ以降はほとんど再購入されていないことがわかります
このように再購入時期を可視化することで顧客が離脱するタイミングがわかります。この場合では、一例として購入から20日程度経過した顧客に再購入を促すメールやDMを送付し離脱を防ぐといった施策が考えられるでしょう。
売上分析・購買間隔分析結果をマーケティングに活かそう
ここまで紹介したとおり売上分析や購買間隔分析はマーケティング戦略を立てるうえで欠かせないツールです。これらの分析結果を基に、顧客が真に求める商品やサービスを提供することで、顧客満足度やリピート率向上につなげることができるでしょう。
顧客データは収集しているが分析まで手が回らない、もしくは分析をしてもうまく結果を活かしきれていないなどの課題をお持ちではないでしょうか。
フュージョンではそういった企業様向けに「CRM ANALYZER」という顧客基礎分析サービスをご用意しています。
CRM ANALYZERは、CRM課題を発見するための基礎分析レポートサービスです。詳しく知りたい方は、以下の資料をご覧ください。
