カテゴリー別アーカイブ: 分析

【分析】Power Queryで手軽にABC分析

 

 ABC分析は「重点分析」とも言われ、分析手法の中でも最も基本的なものです。ところが、実際にエクセルでABC分析用の表作成を行おうとすると「データのバケツリレー」の手間を要します。データのバケツリレーは「列から列」へと続き、数式を細かく設定する手間もかかります。今回紹介する方法であれば、Power Queryエディタ画面(以降、エディタ)でシンプルに処理していけます!

以下が、通常のABC分析の表を作成するときの手順です

①重点管理する項目(金額など)を降順で並び替え

②①を累計で表示 ➡「=」から始まる数式を入力

③②を比率に変換➡ 事前に累計の値を算出 ➡累計の値で各行を割り算

④③をランク付け➡IF式を入力

もし、データの追加などがあったら、①~④の修正作業が必要です

もちろん、データ自体が変われば、①~④の更新処理が必要です

Power Queryであれば、一度、クエリを作成してしまえばデータ更新時に「ソース変更」「クエリの更新」をクリック処理をするだけで済みます

今回使用するデータと行いたいこと

使用データ

今回は、下の画像にある2つの列から構成されるデータを使用します

行いたいこと

前述の使用データの各行に、次の内容でランクを付けます

A ➡全体・販売個数の50%以下

B ➡ 全体・販売個数の80%以下

C ➡ 全体・販売個数の80%超

今回のポイント

今回使用する主な技術は、主に以下の1~3の内容です

1と2は過去に紹介した内容です

最後の3.List関数が今回の一番のポイントになります

1.並び替え/降順

まずは元データを降順で並び替えます

2.条件列の追加

「列の追加タブ」の「条件列」メニューでランク付けを行います

3.List関数


このList関数で累計値と比率を計算します

List関数は2種類使用します

まず1つ目は、List.Select関数です

リストから条件に沿う値を抽出し、リストを再作成します

書き方は次の通りです

List.Select(リスト,条件)

上の画像では、1~4の値からなるリストから、2超の値を抽出した上でリストを再作成しています

2つ目は、List.Sum関数です

こちらの関数は、直感的にも分かり易いと思います

List.Sum()の丸括弧の中に入れたリスト内の各値を合計します

但し、丸括弧の中に入るのはあくまでリストであり、列ではないことに注意しましょう!

では、今回のポイントを紹介したところで本格的な解説を始めます

尚、解説はエディタ内のみの内容になります

並び替え

まずは、エディタ内で販売個数を降順で並び替えます

累計値

算出ロジック

List関数を組み合わせて累計値を計算するのが、今回の記事の最大ポイントです

累計値を計算するロジックについては、次の画像を基にして解説します

通常のABC分析では、N行目のとN-1行目の累計値の合計を計算します

今回の記事では、上の画像のようにⅰ)N行目の値以上の値のリストを作成、ⅱ)ⅰのリストを合計、というⅰ)⇒ⅱ)の流れで行います

List.Select関数

まず、カスタム列・作成画面で「販売個数」列を挿入してA列を作成してみます

この処理だと下の画像のように、販売個数と同じ列ができるだけです

では、カスタム列・作成画面で前ステップ名(カスタム列を作成するステップの1つ前)を入れてみましょう

この段階では、前述の「降順に並び替えられたステップ」が前ステップです

下の画像が、前ステップ名を入れたカスタム列・作成画面の画像です

上の画像の内容でカスタム列を作成すると、次の画像のように各行にてリストが作成されます

このリストの中味は、一律に販売個数の列の内容です

この各行のリストから、前述のロジックの通り「各行の値以上」のリストを各行に再作成していきます

こちらのリストの再作成は、List.Select関数で行います

条件式は、一部、過去の記事で解説したカスタム関数の内容を使います

上の画像のように、「(x)=>」にてxを変数として宣言し、「x>=」を条件式とします

この内容でカスタム列を作成すると、行毎に作成されるリストの内容が変わっています

例えば、上の画像のように2番目の行であれば。2つの値しかリストの中にありません

では、前述の紹介したロジックの通りにリストが再作成できたので、List.Sumの解説に移ります

List.Sum関数

では、List.Select関数で作成したリストをList.Sum関数で合計し、累計を作成します

上の画像のようにカスタム列・作成画面に数式をセットしてOKボタンを押すと、累計の列がエディタ内に追加されます

比率

では、累計を計算したので「比率 =各行の累計値÷販売個数の合計値」 を計算します

販売個数の合計値の算出には、前述のList.Sum関数を使います

ちなみに、上の画像の「追加されたカスタム」とは前ステップ名です

ランク付け / 条件列

では、最後にランク付けを行います

ランク付けは「列のの追加タブ」にある「条件列」で行います

記事の冒頭にあったように、ランク付けの条件を「条件列の追加」画面に設定します

比率が0.5以下であれば ➡ A

比率が0.8以下であれば ➡ B

とします

上記の条件以外はCとして出力するようにします

この条件列を作成すれば、ABC用の表作成は完成です

<まとめ>

今回は、Power QueryでABC分析の表を作成しました

一番のポイントは、List関数を2つ組み合わせて「累計」の列を作成する点です

List関数の中には、常に「リスト」を指定します

M言語では、「リスト」と「」は明確に違います

今回の内容では、List関数の丸括弧の中は「ステップ名[列名]」で指定しました

この「ステップ名」を指定する場合があることを強く意識しておけば、後はそれ程難しい点はありません

 私は以前、企画の仕事をしている時にABC分析表を毎月作成しておりましたが、面倒で仕方ありませんでした

 今回、紹介したPower Queryの仕組みであれば、一度クエリを作成してしまえば、毎月ほぼ「ソース変更」「クエリ更新」をクリック処理で行うだけで済みます

分析で一番大事なのは、数字を解釈することです

 今回紹介した仕組みで、表作成を効率化して有意義な分析を行えるようにしていきましょう!

次回からはRFM分析の解説をはじめます!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

【分析】RFM分析の「R」を集計する

RFM分析は顧客を次の3つの指標で分類して、顧客別に施策を講じる手法です

Recency ➡ いつ?、Frequency ➡ 頻度?、Monetary ➡ いくら?

今回は、Recencyを顧客別に集計する方法を解説させて頂きます

 このRecencyとは、会員IDが含まれる注文データ内に出現する「顧客ID別の最終注文日」のことです

 まず何故、このRecency/最終注文日を分析するかという話しをしたいと思います

 例として最終注文日が1日前の顧客グループと、1年前の顧客グループを比較して考えて見ましょう

 最終注文日が1日前の顧客グループであれば、集計日段階では「稼働」の状態であり、こちらからアクションをしなくても再度リピートしてもらえる可能性が高いです

 一方、1年前の顧客グループに対しては「休眠」している可能性が高く、何らかのアクションをしないと、再度リピートしてもらえる可能性が低いです

 このように、顧客の最終日がいつか?によりアクションをすべき内容が違ってくるのです

今回はこの「最終注文日」をPower Queryでサクッと集計する方法を解説します

 こちらの「最終注文日」を集計する処理は、ピボットテーブルでも集計は可能ですが、Power Queryで行うと大きなメリットがあります

 Power Queryでは集計内容をテーブルに直接読込を行うことができるため、直接データを編集することができます

 一方、ピボットテーブルだとシートの別な場所にコピーしないとデータの編集が行えないのです

今回の使用データと行いたい事

今回の解説で使用するデータは、次の画像の注文データです

注文データは、注文日が2021年1月から3月までの期間で集計されています

 ですので、注文データ内では1つの顧客IDに対して、上の画像のように複数の注文日が存在したりしています

 これらの「1顧客IDに対して複数存在する注文日」の中から「最終注文日」を顧客IDごとに集計することが今回行いたい事です

 例えば、上の画像であれば「顧客ID / CO6324」の最終注文日「2021/03/12」を集計します

最終注文日の集計

では、実際に使用データから最終注文日を顧客ID毎に集計します

解説は、エディタ上から始めます

画像に alt 属性が指定されていません。ファイル名: エディタ-2.jpg

 こちらの集計処理は、過去の記事でも紹介したグループ化により一瞬で終了します

まずは「ホーム」タブの「グループ化」をクリックします

するとグループ化・画面が開くので、次の画像のように各項目を設定します

グループ化項目:顧客IDの列

新しい列名:最終日

操作:最大

列:注文日

この上記の設定により、顧客ID別に最大/最終の注文日が集計されます

 例えば、3つ注文履歴がある顧客ID:C06234であれば下のGIF画像のように「2021/03/12」のみが集計されます

<まとめ>

 今回は、グループ化機能により注文データから顧客ID毎に最終注文日を集計しました

 グループ化・画面にて集計方法(操作)を「最大」で指定すれば、瞬時に最終注文日を集計できます

尚、「最終注文日」を集計した後は「最終注文日からの経過日数」を集計します

こちらについては、起算日を指定する必要があると思います

起算日の指定方法は主に2つあります

本日の日付を指定するM関数を使用する ➡ DateTime.LocalNow

・カスタム列・作成画面で日付を指定する ➡ #date(年,月,日)

今回は、#dateで日付を指定してみます

 上のGIF画像ではカスタム列・作成画面にて[最終注文日]から2021年5月14日の日付を差し引いています

 ちなみに、新たに作成された列は小数点が付いているので、後で整数に直す必要があります

  

 顧客ID別に「最終注文日からの日数」を集計し、過去の記事で紹介したABC分析の内容と顧客IDと紐づければ、顧客ランクとRecencyの関係性を分析することができます!

ぜひ実践してみてください!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

【分析】RFM分析の「F」を集計する

RFM分析は顧客を3つの指標で分類して、顧客別に施策を講じる手法です

Recency  いつ?、Frequency  頻度?、Monetary  いくら?

今回は、上のFrequencyを顧客別に集計する方法を解説させて頂きます

 このFrequencyとは、データ内に出現する「顧客ID別の出現回数」のことです

まず何故、このFrequency/頻度を分析するかという話しをしたいと思います

 例として同期間内の注文頻度が10回で注文総額が10万円の顧客グループと、注文頻度が1回で注文総額が10万円の顧客グループを比較して考えて見ましょう

平均注文金額は前者が1万円であり、後者は10万円となります

両グループとも、同期間内の注文総額は一緒です

 ですが注文単価が違うことから、注文に含まれる商品の単価も注文の仕方も違う可能性が高いです

 加えて、後者は期間限定の「値引き商品」をまとめて購入している可能性も高いです

つまり「頻度」を抽出することにより、顧客の注文行動における特性を炙り出せるのです

今回の使用データと行いたいこと

今回の解説で使用するデータは、次の画像の注文データです

使用データ

注文データは、注文日が2021年1月から3月までの期間で集計されています

このデータから顧客ID別に、注文頻度を抽出します

例えば、上の画像にある顧客ID「C00564」の顧客ならば2回と抽出できるようにします

頻度の集計

解説は元データをPower Queryエディタで開くところからはじめます

こちらの集計処理は、過去の記事でも紹介したグループ化により一瞬で終了します

まずは「ホーム」タブの「グループ化」をクリックします

するとグループ化・画面が開くので、次の画像のように各項目を設定します

グループ化項目:顧客IDの列

新しい列名:頻度

操作:行数のカウント

この上記の設定により、顧客ID別にIDの登場頻度が集計されます

画像に alt 属性が指定されていません。ファイル名: image-22.png

<まとめ>

 今回はRecency  いつ?、Frequency  頻度?、Monetary  いくら?の内、Frequencyを集計しました

グループ化の機能を使えば、簡単に頻度も集計できます

画像に alt 属性が指定されていません。ファイル名: 頻度_画像.png

グループ化機能は分析を行う上では欠かせない機能です

実際に手を動かして実践的に活用できるようになりましょう

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

【分析】RFM分析の「M」を集計してまとめる

RFM分析は顧客を3つの指標で分類して、顧客別に施策を講じる手法です

Recency  いつ?、Frequency  頻度?、Monetary  いくら?

今回は、上のMonetaryを顧客別に集計する方法を解説させて頂きます

そしてMonetaryを抽出した後、前々回前回の分析内容もまとめて整理します

Monetaryとは?

 顧客が「どのくらいの金額を使ってくれているのか?」を分析することは当然、重要なことです

今回は、データ内の顧客ID別に注文金額の総計を抽出します

使用するデータは前々回前回と同じく次のデータです

今回のポイント

 今回は、「前々回/Recency」と「前回/Frequency」に作成したクエリの内容を、「今回/Monetary」作成するクエリとともに、一つの「クエリ/まとめ」にまとめます

 今回の記事では、「まとめクエリ」を作成するにあたって2つのテクニックを活用します

1つ目は既に作成済みのクエリを参照・複製すること

2つ目はクエリの結合です

 Monetaryを抽出するにあたっては、シート上の元データから作業を始めるのでなく、Frequey/クエリを複製して活用します

 その後は、Recencyクエリを参照して他の「Frequency」と「Monetary」のクエリをまとめる「まとめクエリ」を作成します

 まとめクエリは「クエリの結合」の技術を使用して、「Frequeny」と「Monetary」の内容を抽出して紐づけます

Monetaryの抽出/クエリ作成

解説はPower Queryエディタ(以降エディタ)上からはじめます

まず、Frequencyのクエリを複製します

Frequencyクエリ上で右クリックして複製を選択します

複製されたクエリ上でF2キーを押し、名前を「Monetary」に変更します

複製されたクエリは適用したステップも複製されています(参照の場合は最終ステップが参照されているだけです)

上の画像の「グループ化された行」のステップを修正してMonetaryを抽出します

下の画像にて黄色にマークされた箇所をクリックします

開いたグループ化画面の各項目を修正します

新しい列名:頻度➡使用金額

操作:行数のカウント➡合計

:空欄➡注文金額

この際に、「並べ替えられた行」のステップは「頻度」という列がないためにエラーになりますので削除しておきましょう

エラーの原因のステップが削除されれば、下の画像のように「使用金額」が抽出されています

まとめクエリを作成

まず、前々回作成したRecencyクエリを参照します

Recencyクエリの参照を行ったら、Frequencyクエリと同じ様にF2キーにてクエリ名を「まとめ」に変更しましょう

 なお、Recencyについては、最終注文日でなく経過日(集計期間・最終日-最終注文日)をまとめる項目として使用します

 では、ここから2つのクエリを「クエリの結合」を通じて、「頻度」「使用金額」を紐づけます

 上の画像のように「顧客ID」を共通のキーにして紐づけ(結合)を行って行きます

<まとめ>

 今回は、RFM分析のMonetaryを抽出した上で前々回、前回のクエリ内容を紐づけました

Monetaryを抽出する上では、「複製」を使用しました

 そして複製したクエリの「グループ化」の内容を変更することによりMonetaryを抽出しました

そして最後は、顧客IDをキーにして「クエリの結合」をおこないました

 今回の解説ではRecency、Frequency、Monetaryをまとめただけですが、実際にまとめた内容を分析する上では、もう少し工夫が必要です

 RecencyやFrequency、Monetaryを更にグループ化して、グループ毎の違いを分析できるようにすべきです

この辺りはまた、別途、記事を書きたいと思います 

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村