コホート分析をExcelで始める｜継続率の表の作り方と読み方

コホート分析とは？登録時期ごとの利用状況を比べる方法

コホート分析は、同じ時期に登録・購入した人をひとまとまりにして、その後の利用状況を比べる方法です。コホートは「共通の条件を持つ集団」という意味です。この記事では登録月でグループを作り、1か月後、2か月後に何人が利用しているかをExcelで集計します。

4月登録者の1か月後は5月、5月登録者の1か月後は6月です。カレンダー上は違う月でも、登録してから同じ時間がたった状態として比較できます。

4月登録者の2か月後と、6月登録者の登録直後を比べると、使い続けた時間が違います。最新の登録者ほど数字がよく見えたり、まだ観測していない月をゼロと誤解したりしないようにします。

継続の定義も先に決めます。ログイン、特定の機能の利用、契約の継続、購入など、何を数えるかで結果は変わります。この記事では「その月に1回以上、対象の機能を使った人」とします。売上の維持率とは別です。

• 出典：Amplitude：定義・手順の参考資料（新しいタブ）

必要な元データは、顧客ID・登録日・利用日

同じ人が1か月に10回使っても、月間の利用人数としては1人です。操作ログの行数をそのまま数えないよう、顧客IDと利用月の組み合わせで重複を除きます。

Excelへ出すデータには、顧客ID、登録日、利用日を用意します。メールアドレスや氏名は、この集計に必要でなければ入れません。登録者数の表は、利用しなかった人も含む登録一覧から作ります。利用ログだけから作ると、一度も使っていない人が抜けます。

日付が文字列になっている場合は、日付として扱える状態へ直します。タイムゾーンの違いで月末の利用が翌月へずれることもあるため、集計する基準をそろえます。

Excelで登録月と経過月を作る

A列を顧客ID、B列を登録日、C列を利用日とする練習表を作ります。D2に「=DATE(YEAR(B2),MONTH(B2),1)」と入力すると、登録月の1日が得られます。表示形式をyyyy-mmにすれば月単位で読みやすくなります。

E2には「=12*(YEAR(C2)-YEAR(B2))+MONTH(C2)-MONTH(B2)」と入力します。同じ月なら0、翌月なら1です。登録から30日たったかではなく、カレンダー上の月の差を求めています。この違いは資料にも書いておきます。

利用日が空欄の行や、登録日より前の利用がある行は、式を広げる前に確認します。日付の誤りをそのまま集計すると、マイナスの経過月や不自然な古い月ができてしまいます。

登録月ごとの人数を、ピボットテーブルで集計する

練習用に、4月登録者100人、5月80人、6月120人とします。登録月の利用者はそれぞれ100、80、120人、翌月は60、56人、2か月後は50人とします。6月末時点なので、まだ来ていない月は空欄です。

実データを集計するときは、顧客ID・経過月で重複を除いた作業用コピーを使い、登録月を行、経過月を列にしたピボットテーブルなどで人数を数えます。元データは残してください。データモデルの重複しない数を使う場合は、利用できるExcel環境か確認します。

作業用の表を選択し、Excelの「データ」から「重複の削除」を開きます。見出しがある表として扱い、重複の判定に使う列を「顧客ID」と「経過月」に絞ります。利用日まで判定に含めると、同じ人の別の日の利用が残るため、月ごとの人数にはなりません。この手順は、1人にひとつの登録日が対応する今回のデータを前提としています。

次に、重複を除いた表を選び、「挿入」から「ピボットテーブル」を作ります。フィールドの「登録月」を行、「経過月」を列、「顧客ID」を値へ入れます。値が「合計」になっていたら、値フィールドの設定で「個数」へ変えます。IDの番号を足しても人数にはならないためです。

ここで得られるのは、利用があった人の人数です。分母にする登録者数は、利用しなかった人も含む登録一覧から別に集計して添えます。観測済みで利用者がいなかったセルは0、まだ来ていない月は「未観測」と区別してください。空白を全部0へ置き換えると、この違いが消えてしまいます。

• 出典：Microsoft サポート：Excelの重複を削除する（新しいタブ）

継続率は、それぞれの登録者数で割る

4月の1か月後は60÷100で60%、5月の1か月後は56÷80で70%です。人数だけなら60人の方が多いですが、登録者のうち残った割合は5月の方が高くなります。

練習表で登録者数がB2、1か月後の利用者数がD2なら、継続率は「=D2/$B2」です。右へコピーしても分母のB列を動かさないために$を付けます。パーセント表示にして、式の中でさらに100を掛けないようにします。

空欄をそのまま割ると0として扱われる場合があります。未観測のセルは計算対象に入れず、空欄や「未観測」を保ってください。0%は観測したが誰も利用しなかった状態で、意味が違います。

横は時間の変化、縦は同じ経過月の比較

4月の行を横に読むと、100%から60%、50%へ変化しています。1か月後の列を縦に読むと、4月60%、5月70%です。前者は同じ人たちの時間変化、後者は異なる登録者群の比較です。

5月から初回案内を変えていたとしても、70%になった理由がその変更だけとは限りません。流入元、顧客の属性、休日、料金なども変わった可能性があります。まず変えたことと対象の違いを記録します。

4月に休み、5月に戻るような利用もあります。「その月に使った割合」と「一度も途切れず継続した割合」は別です。戻った人を数える今回の定義では、途中で割合が上がることもあり得ます。

小さなデータで、重複の数え方を練習する

大きな表へ進む前に、3人分だけで考えてみます。Aさんは4月登録で5月に3回利用、Bさんは4月登録で5月に1回利用、Cさんは4月登録で5月の利用なしとします。5月の利用行は4行ありますが、利用した人はAさんとBさんの2人です。

この3人を4月登録の集団として見るなら、登録から1か月後の利用率は2÷3で約66.7%です。利用行の4を分子にすると133.3%になり、人の継続率としておかしな結果になります。先に『顧客IDと経過月の組み合わせ』で重複を取り除く理由はここにあります。

Excelでは元データをコピーした作業用の表で、顧客ID、登録月、経過月を残し、同じ組み合わせの重複を削除します。その後に、登録月を行、経過月を列、顧客IDの件数を値とするピボットテーブルを作る方法があります。原本を直接削除せず、元の行数と除いた行数を記録してください。

ただし利用ログだけにCさんが存在しなければ、登録者数をそのログから正しく出せません。分母の3人は登録者一覧から数えます。利用しなかった人を分母から消すと、継続率が高く見えるためです。日付の形式やタイムゾーンが混ざっている場合も、月の区切りをそろえます。

最後に、完成した表の1マスを元データへ戻って確かめます。『4月登録・1か月後が2人』なら、実際の顧客IDがA、Bの2つかを確認します。この小さな検算を通してから全件へ広げる方が、きれいな色付き表を先に作るより間違いを見つけやすくなります。

• 出典：Microsoft サポート：Excelの重複を削除する（新しいタブ）
• 出典：Microsoft サポート：ピボットテーブルの集計（新しいタブ）

継続率が変わった月から、改善の手掛かりを探す

ヒートマップは数値の大小を色で表す方法です。便利ですが、赤や緑だけで判断せず、割合と人数を読めるようにします。登録者が5人しかいない群と500人の群では、1人の変化の大きさが違います。

最初に見る問いは「初月から翌月で大きく減っていないか」「同じ経過月で違う群がないか」です。違いが見つかったら、案内内容や利用目的を調べます。すべての表へ一律の良否基準を置く必要はありません。

契約金額を見たい場合はNRRとGRRへ、利用途中のどの操作で止まるか知りたい場合はファネル分析へ進みます。まず今回の小さな表で、人数、割合、未観測の違いを説明できれば、実データへ進む準備ができます。