実演！Excelを使った身近なデータ分析～3.実践②データ基礎俯瞰２

こんにちは、阿久津恵子です。
「ユーザ視点、データ起点」としてExcelを使った身近なデータ分析を、ここで紹介いたします。

具体的には、企業にある身近なデータの１つ顧客管理データを使って、ＣＲＭ(顧客関係管理)のためのデータ分析をします。

2017年JSDG東京研修会でご紹介した内容を５回にわたって説明いたします。この連載の概要はご紹介ページをご覧くださいませ。

３．実践①データ基礎俯瞰２

前回はデータ基礎俯瞰として、期間中の購買金額、購買人数の推移を確認しました。
今回は更に、下図のように期間を３つにわけ、分類した顧客が各期間でどのように推移したか、詳細を確認していきます。
・第１ターム(1st)：2010年12月～2011年3月
・第２ターム(2nd)：2011年4月～2011年7月
・第３ターム(3rd)：2011年8月～2011年11月

第１ターム⇒第２ターム、第２ターム⇒第３タームと、隣り合う２タームの顧客の購買実績推移から、以下の顧客タイプ分類を定義します。
・購買なし顧客：前後２タームとも購買がなかった顧客
・休眠顧客：前のタームで購買があったが、後のタームで購買がなかった顧客
・新規・復帰顧客：前のタームで購買がなかったが、後のタームで購買があった顧客
・継続顧客：前後２タームとも購買があった顧客

以下から、具体的に顧客全体の何％がそれぞれの顧客タイプであったか分析していきます。

まず、前回作成したExcelをつかって、１～３ターム毎の顧客ごとの購買金額を集計します。

分かりやすいようにAF4に「1st」AG4に「2nd」AH4に「3rd」と項目名を記載します。
続いてAF5に式「=SUM(R5:U5)」AG5に式「=SUM(V5:Y5)」AH5に式「=SUM(Z5:AC5)」をかく事で、5行目の顧客の１～３ターム毎の購買金額を集計する事ができます。

さらに、この式を一番下の行までコピーすると、全顧客ごとの各ターム毎の購買金額を集計することが出来ます。

次に、第１ターム⇒第２ターム、第２ターム⇒第３タームの購買金額推移より、各顧客をタイプ分類します。

分かりやすいようにAJ4に「1st⇒2nd」AK4に「2nd⇒3rd」と項目名を記載します。

続いて
AJ5に式「=IF(AND(AF5>0,AG5>0),”継続”,IF(AND(AF5>0,AG5<=0),”休眠”,IF(AND(AF5<=0,AG5>0),”復帰・新規”,”購買無”)))」
AK5に式「=IF(AND(AG5>0,AH5>0),”継続”,IF(AND(AG5>0,AH5<=0),”休眠”,IF(AND(AG5<=0,AH5>0),”復帰・新規”,”購買無”)))」
をかく事で、購買金額の推移からタイプ分類ごとのラベルを表記する事ができます。

さらに、この式を一番下の行までコピーすると、全顧客をタイプ分類することが出来ます。

※Excel関数の説明
IF：条件式をかく事で、それが真のときと偽のときとで出力内容を場合分けする事ができます。
また、IF内に更にIFをかく事で複数の条件分岐を行う事ができます。
AND：カッコ内に書いた全条件式が真の場合にTRUEを返します。

※Clickで図が拡大されます。

続いて、顧客全体の何％がそれぞれの顧客タイプであったか集計していきます。

AM5に「購買無」AM6に「継続」AM7に「復帰・新規」AM8に「休眠」と各タイプ分類のラベルを記載します。

分かりやすいようにAN4に「1st⇒2nd」AO4に「2nd⇒3rd」と項目名を記載します。

AN5に式「=COUNTIF(AJ:AJ,CONCATENATE(“=”,$AM5))」を記載しAN6～AN8へコピー、
AO5に式「=COUNTIF(AK:AK,CONCATENATE(“=”,$AM5))」を記載しAO6～AO8へコピーします。

これで「1st⇒2nd」、「2nd⇒3rd」それぞれの各タイプ分類顧客が何人だったか集計する事ができます。

※Excel関数の説明
COUNTIF：指定した集計範囲内で条件式にあうセルの数を集計します。
CONCATENATE：文字列を結合する関数(＆よりも見やすいです)。

更に、AN9に式「=SUM(AN5:AN8)」、AO9に式「=SUM(AO5:AO8)」を記載して全顧客数を集計します。
また、AQ5に式「=AN5/$AN$9」を記載しAQ6～AQ8へコピー、AR5に式「=AO5/$AN$9」を記載しAR6～AR8へコピーし、「1st⇒2nd」、「2nd⇒3rd」それぞれの各タイプ分類顧客が何％だったか集計します。