最初と最後の支払い日しかないデータから支払い履歴のデータを作成する方法
こちらのノートでは月額課金のサブスクリプション型のビジネスで取得した、1行が1顧客を表していて、最初の支払い日と最後の支払い日の情報を列に持つ、生存分析に利用できるようなデータから、支払い履歴のデータを作成する方法を紹介します。
支払い履歴のデータを作成するステップ
今回は、以下のステップで支払い履歴のデータを作成していきます。
- 最初と最後の支払い日を一つの列にまとめる
- 顧客IDでグループ化する
- seq.Date関数とcomplete関数を使って最初の支払い日と最後の支払い日の間にある期間のデータを月ごとに生成する
- 欠損値になっている費用を埋める
「最後の支払い日」が欠損値のときに値を埋める
こちらのセクションの内容は、「最後の支払い日」が欠損値になるようなデータを持っている場合にのみ対応が必要となりますので、「最後の支払い日」の列に欠損値がない場合は次のステップにお進みください。
こういったデータを扱っていると、サービスを解約していない顧客（行）には「最後の支払い日」の情報がなく欠損値になっていることがあります。
そこで、こういった顧客（行）は、今日まで支払いが続いていると想定をして、欠損値を「今日」の日付で補完することが可能です。詳しいやり方につきましては以下のノートで紹介していますので、ご参考ください。
- 日付列の欠損値を今日の日付で埋める方法 - リンク
最初と最後の支払い日を一つの列にまとめる
今回は、「最初の支払い日」と「最後の支払い日」の間にあるデータを生成するために、最初の支払い日と最後の支払い日をまとめて一つの列にしたロング型（データが増えると行数が増えるようなデータ）のデータに変換する必要があります。
まず、ShiftキーまたはCommandキー(Windowsの場合はCtrlキー)をクリックしながら、「最初の支払い日」と「最後の支払い日」の列を選択します。
次に、列ヘッダメニューから「ワイド型からロング型へ(Gather)」 を選び、「選択された列」を選択します。
すると、ワイド型からロング型へ変換するためのダイアログが表示されるので、キー列と値の列に、任意の列名を入力します。
なお、キー列には「最初の支払い日」と「最後の支払い日」の列名が値としてまとめられ、値の列にはそれぞれの値（今回の場合は日付）がまとめられます。
今回はキー列に「支払いタイミング」、値に「支払い日」と入力して実行します。
「最初の支払い日」と「最後の支払い日」の列を一つの列にまとめることができました。
顧客IDでグループ化する
次に、「顧客ID」ごとに最初の支払い日と最後の支払い日の間にある期間のデータを生成したいため、「顧客ID」の列ヘッダメニューから「グループ化」を選択します。
「顧客ID」ごとに色が分かれ、グループを作成できました。
seq.Date関数とcomplete関数を使って入社日と退社日の間にある期間のデータを生成する
次に、seq関数とcomplete関数を使って、最初の支払い日と最後の支払い日の間にある、毎月の支払い日のデータを生成していきます。
なおseq関数やcomplete関数についての詳しい説明は、下記のノートをご覧ください。
ステップメニューから「カスタムRコマンド」を選択します。
すると、カスタムRコマンドのダイアログが表示されるので、下記のコードをエディタに入力し、実行します。
complete(支払い日 = seq.Date(min(支払い日), max(支払い日), by="month"))
なお、seq.Date関数は、1番目の引数と2番目の引数に指定された期間の日付データを作ります。
今回はデータから動的に、最初の日と最後の日を取得したいので、1番目の引数にmin関数を使い、2番目の引数にmax関数を使います。
次に、どういう単位で日付データを作るのかを指定します。これが3番目のby=という引数です。
今回は月額のサブスクリプションサービスの支払いデータを作成したいので、日付のデータは「月」の単位でデータを生成したいため、3番目の引数には、「month」を指定します。
前述のコードをエディタに入力して実行すると、「顧客ID」ごとに「最初の支払い日」と「最後の支払い日」の間の期間のデータを「月ごと」に生成することができました。
欠損値になっている費用を埋める
顧客ごとに、最初と最後の支払い日の間の支払い日を生成することができましたが、1つだけ問題があります。
それは、生成した期間の行は、顧客の支払い金額を表す「費用」の値が欠損値になっているため、このままでは、月ごとに支払い金額を集計できないということです。
今回、「最初の支払い日」あるいは「最後の支払い日」の行に、「費用」の情報があるので、このいずれかの値で、欠損値を埋めることで、月ごとに支払い金額を集計することが可能です
そこで、「費用」の列ヘッダメニューから「欠損値(NA)の加工」、「欠損値(NA)を前か後ろの行の値で埋める」を選択します。
すると、「欠損値を前後の行の値で埋める」ダイアログが表示されるので、「欠損値を...で埋める」に「前の行の値」または「次の行の値」を選択し、実行します。
今回は最初の支払い日の行にも、最後の支払い日の行にも同じ費用の値が入っているので、「前の行の値」または「次の行の値」のどちらを選んでも同じ値で欠損値が埋められることになります。
これで、「最初の支払い日」や「最後の支払い日」以外の支払い日の行の費用を埋めることができました。
なお、今回は元のデータに最後の利用プランの情報しかなかったため、その情報を元に過去の支払いも同じでプランであることを想定して、欠損値を補完していることになりますので、ご注意ください。
月ごとの収益を可視化する
データの準備が整ったので、月ごとの収益であるMRR（月間定期収益）を可視化していきます。
チャートビューに移動し、チャートピンが最後のステップに紐づけられていることを確認してください。
チャートのタイプに「バー」を選択し、X軸には「支払い日」を選び、スケールには丸め処理の「月」を選択します。
続いて、Y軸には支払い金額である「費用」の列を選び、集計関数に「合計値(SUM)」を選択します。
これで、月ごとの収益であるMRR（月間定期収益）を可視化することができました。
