タグ別アーカイブ: 連番

【M言語実践】顧客の2回目のリピート率を分析する方法

 パワークエリはとても便利ですが、M言語となると使い道がよく分からないと思います

今回は、M言語を使用した実践的な分析手法を紹介したいと思います

私は以前、EC通販会社に勤めていました

 EC通販では顧客の顔が見えないので、データからどういう顧客がいて・どういう行動をしているのか・を分析する必要があります

様々な分析を行いましたが、指標として一番重視していたのが「顧客の2回目リピート率」です

顧客の2回目のリピート率が何故重要なのか?

 これには様々な理由がありますが、一番の理由は2回目のリピート率を少しでも改善すれば、売上が長期的に増加するからです

読者の方でも外食をした際に「あの店には2度と行かない・・・」という経験をした方も多いと思います

 逆に同じ店で2度食事をして、その店に慣れてくると3回目、4回目とリピートする意欲する気が高くなると思います

 ビジネスでも「2回目のリピート」というハードルをクリアすることの意義はとても大きいのです

ただし、普通にエクセルで2回目のリピート率を算出しようとするとかなり面倒です

私は一時期、IF関数で注文回数を付与して、2回目の注文だけシートを分けたりしていました

ましてや、2回目のリピートの有無別に顧客分析をしようとするとかなりハードルが高いです 

ところが、M言語を使えば2回目のリピート率は意外と簡単に算出できます!

ポイント

今回使用するデータは会員別の注文データです

 上の画像では2回目の注文があった会員に黄色の印を付けましたが、2回目の注文があった会員もいれば、そうでない会員もいます

ここからまず、会員番号毎に注文回数を付与します

そして、上の画像の表から次のような表を作成します

上の画像では、その前の画像の表から注文回数を「1回」に絞り込んであります

結果として、会員番号の列は会員番号が重複なく並んでいます

そして「2回目判定用」なる列が追加されています

こちらは注文回数の差分です

こちらの差分は注文回数を「1回」に絞り込んだ場合にはになります

この列が1の会員は2回目の注文があった会員です

 つまり、「2回目判定用」の列の合計を「注文回数」の列の合計で割れば2回目のリピート率が出るという仕掛けになります

(注)尚、上記の画像のデータ以前に注文履歴は無いという前提で解説を行わせて頂きます。ですので、本当は初回の注文ではないのでは?という疑問は持つ必要はありません

注文回数の付与

ポイントで解説した通り、まずは会員番号別に注文回数を付与します

こちらについての詳細については、過去の記事をご確認をお願いします

大きく分けて2つ行うことがあります

1つ目は、グループ化です

会員ID別(会員番号別)に「すべての行」でグループ化を行い、会員ID別にテーブルを作成します

次にカスタム列・作成画面にて、テーブル別にM関数/Table.AddIndexColumnを使用して連番を付与します

すると、注文回数が連番で付与されます

注文回数の差分の算出

こちらについても詳細は過去の記事をご参照願います

この差分を算出するには、パワークエリでは本来は困難な「セル単位」や「行単位」の処理に踏み込む必要があります

ポイントとなるのは、こちらも「連番」です

上の画像のように連番をつけると、連番と画面左の行番号とが対応するようになります

ちなみに、M言語は0ベースなので、実際にM言語で使用する1行目は0になります

ここから波括弧:{}を使用します

波括弧は行番号を表します

例えば、注文回数{0}とした場合には、注文回数列の1行目のデータなります

この波括弧と連番をうまく組み合わせることで、注文回数の差分を算出します

上の画像にて黄色の印を付けた波括弧内は、注文回数の各行の1つ下の行番号の値になります([注文回数]の前のDataは前ステップの名称になります)

つまり上の画像内では、注文回数列をA列だとすると<=A3-A2>と同様の計算が行われています

ちなみに、try~otherwiseとすることでエラーを回避しています

注文回数の絞り込み

 上記まで行ったところで、エディタを確認すると以下の画像のような状態になっています

ここから注文回数の列にフィルターをかけます

こうすれば注文回数の列は「1」のみ、2回目判定用の列は0か1になります

ここまでくれば、後は各列を合計して2回目のリピート率を算出するだけです

合計にはM関数/List.Sum関数を使用します

 上の画像では、2つの合計値を/で割り算をすることでリピート率を算出しています

%の表示にしたい場合には、List.Sum関数でリスト化した内容をテーブル化した上で%に型式を変えます

<まとめ>

今回はM言語の仕組みを使用して、2回目のリピート率を算出しました

2回目のリピート率を算出するのに、主に2つのM言語の技術を活用しました

1つ目はグループ化を通じて各グループ毎に連番を付与する仕組みです

2つ目は注文回数の差分を連番と波括弧を組み合わせて算出する仕組みです

 この2つの技術を通じて、注文回数と2回目の注文有無を0か1で表現できるようにしました

 今回は2回目のリピート率を算出しましたが、本当に大事なのは2回目のリピートがあった会員とそうでない会員との違いは何かを調べることです

 今回作成したクエリを複製して途中のステップを削除すれば、会員番号別に注文の有無が表示できます

 他のデータを会員別に紐づければ、2回目の注文があった会員とそうでない会員の購入している商品の違いなども調べることができるはずです

 更に注文回数の差分を抽出する仕組みを応用して、初回から2回目までの日数なども調べることができます

M言語が本当に有効なのはこういった複雑な分析が必要な場面だと思います

 特にグループ化から連番を付与する仕組みと差分を算出する仕組みは、分析作業にて使う場面も多いと思いますので、しっかり活用できるようにしておきましょう!


にほんブログ村

M言語に慣れる_10回目~グループ毎に連番作成~

【M言語は難しくない】今回はデータをグループ化した際に、1から始まる連番をグループ毎に作成する方法について解説します。この処理の仕方を覚えると、エクセルの使い方の幅が広がります

コードの採番や顧客の2回目のリピート状況把握など、使いみちは沢山あります!

しかも知られざるグループ化機能を使えば、1つのM関数を入力するだけでできてしまいます

まさに魔法です

ぜひ、実際に手を動かしてこの魔法を体験してください!

目次

今回のポイント

今回の使用データ

グループ化の実施

M関数の入力

<まとめ>

今回のポイント

今回のポイントは2つあります

グループ化

グループ化する際に、よく選択される操作は「合計」などです

今回は「すべての行」という操作を選択します

 この「すべての行」を選択して操作を行うと、グループ毎テーブルが作成されます

M関数作成

連番は「列の追加」タブの「インデックス列」のメニューから、クリック操作で簡単に作成できます

 今回は、既存のメニューは使用せず、カスタム列の作成画面から「Table.AddIndexColumn」というM関数を使用して「連番」を作成します

 M関数で作成することで、ポイントの1点目で作成されたグループ毎のテーブルを、関数の引数として指定できるようになります

今回の使用データ

今回は下の画像にあるデータの「部門」列をグループ化します

グループ化した後は、グループ毎に連番を振ります

以下が実際に使用するサンプルデータです

グループ化の実施

解説は、前述のデータをテーブル化し、エディタを開いたところから行います

エディタを開いたらまずは、グループ化を行います

グループ化は「ホームタブ」の「グループ化」メニューから行います

次に開いた画面では、前述のように操作を「全ての行」で設定します

・グループ化対象列 ➡ 「部門」列

・新しい列名 ➡ 「部門列」

・操作 ➡ 「すべての行」

空欄のままでOKです

上の内容で指定してOKボタンをクリックすると、エディタ内では次の画像のように、テーブルがグループ別に作成されています

M関数の入力

関数の参照

次に「列の追加」タブから「インデックス列」を挿入してみます

すると、確かに連番は作成されますが、グループ毎の連番ではありません

こちらのステップについては後で削除するとして、まずは過去の記事のように関数の解説を参照してみましょう

関数の後の丸括弧を全て削除した後にエンターキーを押し、解説を表示します

テーブルに新しい名前の列を追加するという内容から解説が始まっていますが、関数の作成の仕方については、次のような内容が書いてあります

=Table.AddIndexColumn(テーブル名,”新列名”,連番の開始番号,連番の増分)

このM関数では、引数を4つ指定します

・テーブル名

・新列名

・連番の開始番号:通常は1から指定します

・連番の増分:通常は1つずつ連番を増やします

この数式の内容に従い、カスタム列作成画面から「連番」を作成し直します

なお、既に追加した連番については削除しておきましょう

カスタム列作成

それでは、「列の追加」タブから「カスタム列」をクリックします

次に開いた画面では、M関数にてカスタム列を作成していきます

使用するM関数は、前述の次の関数です

=Table.AddIndexColumn(テーブル名,新列名,連番の開始番号,連番の増分)

こちらは、入力補完機能を活用して入力していきます

まずは、「INDEX」と入力してみましょう

すると上のGIF画像のように「Table.AddIndexColumn」が選択できるようになります

↓で2つ下にカーソルを移動し、TABキーで該当のM関数を選択します

次に、前述のようにテーブルを引数の1つ目に指定します

こちらは、列の挿入で代用します

画像に alt 属性が指定されていません。ファイル名: 3つのテーブル.gif

上のGIF画像のように「部門別」の列は3つのテーブルから構成されています

ですので、下の画像の「部門別」を挿入すれば、自動的に3つのテーブルを引数として設定できます

下のGIF画像のように「部門別」列を挿入したら、残りの3つの引数もそれぞれ指定します

残りの3つの引数は、次の内容となります

・新列名 ➡ 連番

・連番の開始番号 ➡ 1

・連番の増分 ➡ 1

下の画像のように、上で紹介した3つの引数を設定したら、OKボタンを押します

すると、新しく追加した「連番」の列名の列に更に3つテーブルが作成されます

この後は、下の画像の赤い丸印をつけた箇所から展開する処理になります

その前に、他の列は削除しておきます

他の列を削除したら展開処理します

上のGIF画像のように、次に開いた画面で「元の列名をプレフィックスとして使用します」のチェックは外しておきましょう!

すると上の画像のように展開されます

きちんとグループ毎に連番も作成されています

グループ毎に適切に連番が作成されていることを確認したら、読み込み処理を行います

<まとめ>

今回はグループ毎に連番を作成する方法について解説しました

作成にあたってはポイントが2点ありました

1つ目は、グループ化の際に「操作」の指定を「すべての行」にする点です

画像に alt 属性が指定されていません。ファイル名: グループ化全ての行.jpg

こちらの処理により、グループ毎にテーブルが新規に作成されます

 2つ目は、既存のメニューにより「連番」を作成するのではなく、カスタム列・作成画面からM関数にて「連番」を作成する点です

 カスタム列からM関数を作成することにより、1つ目のポイントで作成した「グループ毎のテーブル」を引数にして関数を設定できます

 今回の方法はエディタ内の1つの列に、複数のテーブルが作成される点が少しトリッキーです

 但し、今回の処理はデータ分析やデータ整理などで使える場面が多いはずです

 グループ毎に連番を作成した後、クエリを「複製」していけば、連番別に更に階層化してデータ整理が行えたりできます(例:連番1のレコードのみを集める等)

 今回解説した方法の活用事例等についても、今後はこのブログで発信していきたいと思います!

長文に最後までお付き合い頂きありがとうございました

参考までに今回使用したファイルを添付します

次回は、前行を参照する方法を解説します

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

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

にほんブログ村