ExceV -WebマーケティングTips-

Webの仕事を捗らせる、ちょっとしたコツや自作ツールを紹介。

受注データから顧客ランク別のアクティブユーザー数を抽出する方法［Excelで顧客分析（中級編）］

Excel 顧客分析

前回、下記の投稿でアクティブユーザー数の抽出方法をご紹介しましたが、今回は更に一歩進んで、顧客ランク別のアクティブユーザー数を抽出する方法をご紹介します。

excev.hatenablog.com

顧客ランクとは

累計購入金額や購入頻度などから顧客を分類し、売上への貢献度などの要素によってランク付けしたもののことで、ランク別に異なる施策を行うことで、それぞれの顧客の状況に応じたサービスを提供でき、全体の満足度向上に繋がるといったものです。

RFM分析等ではもっと細かく定義する場合もありますが、マクロや複雑な関数が必要となってくるため、本記事では簡潔に以下のとおり分類します（そのうちRFM分析の方法も書きたいと思います）。

  • 新規顧客（アクティブユーザーの内、累計購入回数が1回の顧客）
  • 一般顧客（アクティブユーザーの内、累計購入回数が2～3回の顧客）
  • 優良顧客（アクティブユーザーの内、累計購入回数が4回以上の顧客）

単純に購入回数で分類しただけですが、これだけでも十分に有用な情報が得られます。なお、適切なランク分けの定義は取り扱っているサービスや商品によって異なります。また、特に決められたルールなどはありませんので、分析のしやすい形で適宜分類してみて下さい。

必要な知識

本記事の内容は、以下の知識があればスムーズに読み進められます。もし現時点で分からなくても、記事内で解説しているので大丈夫です。

  • IF関数
  • AND関数
  • COUNTIFS関数
  • セルの並べ替え
  • オートフィル
  • セルの絶対参照（$マーク）

　難易度：★★★☆☆
　所要時間：20分

COUNTIFS関数がちょっと複雑ですが、ある程度Excelを使い慣れている人であれば全く問題ないと思います。やり方さえマスターしてしまえば1回20分も掛からない作業ですし、毎日行う作業でもないので是非実際に試してみてください。


必要なデータ

顧客ランク別のアクティブユーザー数を抽出する際に必要なデータは以下の2つです。

  • 顧客ID（※１）
  • 受注日（※２）

※１、会員登録が不要なサイトやゲスト購入（会員登録なしでの購入）が可能な場合、顧客IDが存在しない受注があるかと思いますので、その場合はメールアドレスや顧客氏名など、固有の顧客を特定できるデータで代用して下さい。

※２、受注日は「サイト公開日」～「アクティブユーザー数を抽出したい期間の末尾」で用意してください。仮に2014年のアクティブユーザー数を取りたい場合は、「サイト公開日～2014年12月31日」のデータが必要となります。なお、時間が含まれたデータ（受注日時）でも構いません。

なお、前回の投稿のデータを持っている場合、手順１～４は読み飛ばして、手順５から進めてください。

手順１：必要なデータをExcelに貼り付ける

ExcelのA列に顧客ID、B列に受注日時を貼り付けます。

f:id:yo_kuriyama:20150814162313p:plain


以降はこのデータを「基データ」と呼びます。この基データから、アクティブユーザーの数を抽出していきます。

手順２：顧客ID昇順で並べ替え

まず、基データのセルを全て選択します。
（「セルA1をクリック」→「Ctlr＋Shift＋End」で簡単に選択できます。）

f:id:yo_kuriyama:20150814162319p:plain


次に「並べ替えとフィルター」→「ユーザー設定の並べ替え」を選択します。

f:id:yo_kuriyama:20150814161558p:plain


「並べ替え」のウインドウが立ち上がるので、「最優先されるキー」を「顧客ID」にします。続いて、左上のあたりにある「レベルの追加」をクリックし、「次に優先されるキー」を「受注日」にします。そのほかの項目は「値」「昇順」のままで構いません。

f:id:yo_kuriyama:20150814162327p:plain


これで基データのA列が顧客IDの昇順になり、顧客IDが同じ場合はB列が受注日の昇順となるように並べ替えられました。

f:id:yo_kuriyama:20150814162813p:plain


手順３：各顧客の最終購入を判別する

数式の意味は後ほど解説しますので、とりあえず「セルC2」に

=IF(A2<>A3,1,0)

と入力しましょう。

f:id:yo_kuriyama:20150814164421p:plain


次にオートフィル機能を使って、入力した数式を基データの末尾までコピーします（「セルC2」をクリック→セル黒枠右下の四角いマークをダブルクリック、または基データの末尾の行までドラッグ）。

f:id:yo_kuriyama:20150814164802p:plain


これで顧客IDごとの最後の購入の行に"1"、それ以外の行に"0"が入りました。

f:id:yo_kuriyama:20150814165127p:plain

※数式「=IF(A2<>A3,1,0)」の意味

『もし［IF］、A2とA3が違う値［A2<>A3］だったら、数式を入力したセル（＝C2）に1を表示［,1］、そうじゃない場合（A2とA3が同じ値の場合）は0を表示［,0］』

を意味します。これを一つのセル（＝C3）にコピーした場合、［A2<>A3］の部分が［A3<>A4］に置き換えられます。

つまり、数式がある行の顧客IDとその下の顧客IDを見て、違っていたら"1"、同じだったら"0"を表示しています。顧客IDは受注日昇順に並んでいるため、"1"が表示される行＝各顧客の最終購入となります。



手順４：最終購入日からアクティブか否かを判別する

まずはアクティブユーザーの定義（基準日から遡って○日以内の購入者）を入力しましょう。

ここでは例として、基準日を「2015年7月31日」、期間を「1年以内」とし、「7月31日から遡って365日以内の購入者」をアクティブユーザーとます。また分析がしやすいように、期間は後から自由に変更できるように作っていきます。

なお、基準日はB列（受注日）で最も遅い日付よりも前にすることはできません（図であれば、2015年7月11日以前は不可）。


それではセルG2に「2015/7/31」、セルG3に「365」と入力しましょう。

f:id:yo_kuriyama:20150817120105p:plain


続いて、セルD2に

=IF(C2=0,0,IF(AND(B2>$G$2-$G$3,B2<$G$2+1)=TRUE,1,0))

と入力します。いきなり複雑な数式ですが、細かく見ていきましょう。とりあえずデータが取りたいというだけでしたら、数式の意味は理解せず読み飛ばしてしまっても構いません。

f:id:yo_kuriyama:20150817123946p:plain

※数式「=IF(C2=0,0,IF(AND(B2>$G$2-$G$3,B2<$G$2+1)=TRUE,1,0))」の意味

まず数式内部の[AND(B2>$G$2-$G$3,B2<$G$2+1)]ですが、これは[B2>$G$2-$G$3]と[B2<$G$2+1]の2つの条件がどちらも成立すれば[TRUE]を返し、どちらか一方でも成立しなければ[FALSE]を返します。

その2つの条件ですが、左側の[B2>$G$2-$G$3]は「基準日－期間の日数」となるため、アクティブユーザーの定義の開始点となります。一方、右側の[B2<$G$2+1]はアクティブユーザー定義の終了点となります。

ここで終了点の基準日に[+1]をしているのは、Excelで「2015/7/31」と入力した場合、内部では「2015/7/31 0:00:00」とデータが格納されているためで、これをそのまま終了点とすると、「2015/7/31 10:30:15」のような受注日時の場合、アクティブユーザーの定義に含まれなくなってしまいます。そこで[+1]をして「2015/8/1 0:00:00」を終了点としています。

つまり、[AND(B2>$G$2-$G$3,B2<$G$2+1)]はB列の受注日がアクティブユーザーの定義に該当するか否かを判別しており、ここで[TRUE]が返ってくる場合、手順４で指定したアクティブユーザーの定義に該当する購入となります。

よって、[IF(AND(B2>$G$2-$G$3,B2<$G$2+1)=TRUE,1,0)]は、B列（受注日）がアクティブユーザーの定義に該当する場合[=TRUE]、1を表示［,1］、そうじゃない場合、0を表示［,0］という意味になります。

最後に[IF(C2=0,0,～)]の部分ですが、これはC列が0（＝最終購入ではない）の場合は0を表示[,0]、0でなければ上記内容の結果を表示します


次に手順３と同じく、オートフィル機能で数式をコピーします。

f:id:yo_kuriyama:20150817124634p:plain


これで、C列が0（＝最終購入ではない）の場合はD列も0、C列が1（＝最終購入）の場合、B列（受注日）の日付がアクティブユーザー定義の期間内であれば1を表示するようになりました（そうでなければ0を表示）。

手順５：顧客別の累計購入回数を抽出する

次に顧客別の累計購入回数を抽出します。セルE2に

「=IF(A1<>A2,1,E1+1)」

と入力して下さい。

数式の意味は、「A列に新しい顧客IDが出てきたら"1"、既に出ている顧客IDなら一つ上のセルに"1"を足す」というものです。A列は顧客ID順、B列は受注日順に並んでいるので、同じ顧客IDであれば累計購入回数が"1"ずつ増えていくという形になります。

f:id:yo_kuriyama:20150818145628p:plain


数式を入力したら、オートフィル機能でコピーしましょう。

f:id:yo_kuriyama:20150818145717p:plain


これで「各顧客の最終購入日がいつか」「その購入日がアクティブユーザーの定義に該当するか」「その顧客の累計購入回数は何回か」というデータが揃いました。

あとはこれを集計することで顧客ランク別のアクティブユーザー数が抽出できます。

手順６：顧客ランク別のアクティブユーザー数を集計する

まず集計のための下準備として、分類の基準となる累計購入回数を入力します。

図の通り、セルG6～H8に数値を入力して下さい。

f:id:yo_kuriyama:20150818154411p:plain


数値の意味ですが、新規顧客は「累計購入回数が1回の顧客」という定義でしたので、G列の"1"回～H列の"1"回の範囲内、一般顧客は「累計購入回数が2～3回の顧客」という定義でしたので、G列の"2"回～H列の"3"回の範囲内といったように、ここで各ランクごとの累計購入回数の定義を決めています。

なお、優良顧客の定義は「4回以上」でしたが、範囲の上限を指定しないとエラーになってしまうため、ここではH列を"999999"回としています。

さて、累計購入回数の定義を入力したら、続いて集計するための数式を入力します。セルI6に

=COUNTIFS(D:D,1,E:E,">="&G6,E:E,"<="&H6)

と入力しましょう。

※数式「=COUNTIFS(D:D,1,E:E,">="&G6,E:E,"<="&H6)」の意味

COUNTIFS関数は半角カンマ2つずつで区切って、各条件を全て満たす件数を抽出します。実際に区切ってみると、「D:D,1」「E:E,">="&G6」「E:E,"<="&H6」となりますが、半角カンマで区切られた前半部分（D:DやE:E）が対象の範囲、後半部分（1や">="&G6など）が抽出の条件になります。

また「D:D」は「D列全て」を表しており、「D:D,1」は「D列が"1"の行」という意味になります。続いて「E:E,">="&G6」ですが、「">="」は「～以上」を意味します。それを"&"で"G6"と結合しているため、「">="&G6」は「セルG6の値以上」となり、「E:E,">="&G6」は「E列が"1"以上の行」を意味しています。同様に「E:E,"<="&H6」ですが、「"<="」は「～以下」となるため、「E列が"1"以下の行」となります。

よって、「=COUNTIFS(D:D,1,E:E,">="&G6,E:E,"<="&H6)」は「D列が"1"、E列が"1"以上"、E列が1"以下」の全ての条件を満たす行数を表示するという意味になります。


この数式により、「D列が1」（＝アクティブユーザー）で、「E列が1以上1以下」（＝累計購入回数が1回）の顧客数が集計されます。また、この数式をセルI7～I8にコピーすると、各顧客ランクに応じた条件で集計することができます。

f:id:yo_kuriyama:20150818154402p:plain


まとめ

以上で完了です。期間や累計購入回数の定義は変更可能ですので、色々と数値を変えてみて、分析しやすい形を見つけましょう。

また、一度定義が決まったら、ぜひ定期的にデータを集計してみて下さい。もしかすると、「売上は右肩上がりだけど、新規顧客が減ってきた」といった課題が見つかるかもしれません。

ちなみにExcelのマクロ機能を使うと、一連の作業をほぼ全て自動化することができます。たいして時間の掛からない作業ですが、定期的に発生するものはできるだけ自動化したいですね。その内ツールを作って公開しようと思っていますが、それまでは今回の方法でお試し下さい。