Claudeでヘルススコアの重みを決める — 解約実績をClaude for Excelで後ろ向きに検証

ヘルススコアの重みは、勘で決めると「利用ログが多ければ安全」という思い込みがそのまま数字になります。過去に解約した顧客と継続した顧客を並べ、各指標がどれだけ差を持っていたかを見れば、重みに根拠が付きます。Claude for Excelは、この検証用のシートを数式つきで組むところまで任せられます。ただし、何を解約と数えるか、いつの時点の数字を使うかは人が決めます。

この記事では、検証用の表の作り方、解約組と継続組の差から重みを出す式、スコア帯別の解約率で当たり具合を見る方法、期間を分けた検証までを順に扱います。数値は仕組みを示すための例示で、実在の顧客データではありません。

すでに運用しているツールのスコアを使って解約リスクの顧客を洗う手順は、ClaudeとGainsightを連携して解約リスクの顧客を洗い出す手順にあります。こちらは、そのスコア自体の重みを実績で見直す側の話です。解約理由の分類はStripeの解約理由をClaudeで分類し打ち手つき月次報告にするが受け持ちます。

検証用の表は「観測日」で切る — 解約後のデータを混ぜない

後ろ向き検証で最も多い失敗は、解約が決まった後のデータで解約を説明してしまうことです。解約の直前には問い合わせが急増し、利用ログはゼロに近づきます。その数字を指標に使えば、どの指標も解約と強く結びつき、重みはどれも大きく出ます。しかし運用で知りたいのは、解約が決まる前に気づけるかどうかです。

そこで、顧客ごとに「観測日」を1つ決めます。指標は観測日より前の期間だけで集計し、解約したかどうかは観測日より後の期間で判定します。

観測日を「更新日の90日前」のように契約に連動させると、更新時期がばらばらな顧客でも同じ条件で並べられます。月額契約が中心なら、毎月の月初を観測日にして顧客×月の行を積み上げる形も使えます。この場合は同じ顧客が複数行に出るため、後述の期間分割で同じ顧客を学習側と検証側にまたがせないよう注意が要ります。

Claude for Excelに頼む前に人が決める3つの定義

Claude for Excelに任せられるのは、表の結合、数式の作成、集計の作り込みです。数字の意味づけにかかわる次の3点は、先に人が決めて依頼文に書きます。

• 解約の定義: 契約の満了での不更新、途中解約、プランの大幅な格下げのどこまでを1にするか
• 予測の期間: 観測日から何日以内の解約を当てにいくか。運用で動ける猶予より短くしない
• 除外する顧客: 解約が会社都合の倒産や合併によるものは、行動の指標では説明できないため外すかどうか

定義が曖昧なまま進めると、重みは計算できても何の重みなのかが言えなくなります。定義はシートの先頭に3行で書き残しておくと、後から見直す人が迷いません。

利用ログ、問い合わせ、請求は別々のシステムから出てくる表なので、顧客IDで結合する作業が最初に発生します。Claude for Excelは、開いているブックの質問に答えたり、新しいシートを作ったりできます。各表を別シートに貼り、結合先の列をどう作るかを頼みます。

「ログ」「問い合わせ」「請求」「契約」の4シートを、顧客IDで結合した
「検証表」シートを新しく作ってください。
- 観測日は「契約」シートの更新日の90日前です
- ログイン日数は観測日より前の30日、問い合わせ数は前の90日で集計します
- 解約フラグは、観測日から90日以内に解約日があれば1、なければ0です
- 結合できなかった顧客IDは別の列に件数を出してください
既存のシートは書き換えないでください。

最後の2行に意味があります。結合できなかったIDの件数を出させると、IDの表記ゆれや欠損を最初に見つけられます。既存のシートを書き換えない指定は、元データを守る保険です。

解約組と継続組の差から重みを出す

検証表ができたら、指標ごとに「解約した顧客と継続した顧客で、平均がどれだけ離れているか」を測ります。単位が違う指標を並べて比べるため、平均の差を全顧客の標準偏差で割り、単位を消します。

重みは、差の絶対値を合計で割って出します。この例では4つの値の合計が約3.27で、ログイン日数の1.23を割ると約38%です。向きは指標ごとに違います。ログインや契約月数は大きいほど安全、問い合わせや支払遅延は大きいほど危険です。向きを間違えないよう、重みとは別の列に「大きいほど良い=1、悪い=-1」の符号を置きます。

Excelの関数で書くと、次のような式の組み合わせです。

解約組の平均   =AVERAGEIFS(ログイン列, 解約フラグ列, 1)
継続組の平均   =AVERAGEIFS(ログイン列, 解約フラグ列, 0)
全体の標準偏差 =STDEV.S(ログイン列)
差の大きさ     =ABS(継続組の平均-解約組の平均)/全体の標準偏差
重み           =差の大きさ/SUM(差の大きさの列)

Claudeへの依頼は、次の形にします。

「検証表」を使い、ログイン日数・問い合わせ数・支払遅延日数・契約月数の4指標について、
解約フラグ別の平均、全体の標準偏差、差の大きさ、重みを出す
「重み計算」シートを作ってください。
- 数式で組み、値の貼り付けにしないでください
- 向き(大きいほど良いか悪いか)を別の列に入れ、重みと分けてください
- 解約フラグ=1の顧客数と、0の顧客数をシートの上部に出してください

「数式で組み、値の貼り付けにしないでください」は外せません。公式の説明では、Claude for Excelは数式の関係を保ったまま値を更新でき、回答には該当セルを示す引用が付きます。観測日や解約の定義を変えて再計算するとき、数式なら重みが追随します。値の貼り付けだと、定義を変えるたびにシートを作り直すことになります。

相関係数で見る方法もあります。CORREL(指標列, 解約フラグ列) は、0と1のフラグとの相関を1つの式で出せます。平均の差とほぼ同じ順位になるため、2つの方法で順位が食い違う指標があれば、外れ値の影響を疑って分布を見る合図になります。

スコアを5段階に切って当たり具合を見る

重みが決まったら、顧客ごとのスコアを作ります。各指標を0〜100の順位に直し、向きを揃えて、重みつきで足し合わせます。順位への変換には PERCENTRANK.INC が使えます。

指標の順位(良いほど高い) =PERCENTRANK.INC(指標列, 顧客の値)*100
  ※ 大きいほど悪い指標は =100-PERCENTRANK.INC(...)*100
スコア =SUMPRODUCT(重みの範囲, 指標の順位の範囲)

次に、スコアの高い順に顧客を5つの帯へ分け、帯ごとの解約率を出します。ここが後ろ向き検証の本体です。重みが妥当なら、スコアが低い帯ほど解約率が高く、並びが崩れません。

この例は1,000社で、全体の解約率は14.2%です。最も低い帯の解約率は全体の約2.2倍あり、帯を上がるごとに単調に下がっています。見るべき点は、倍率の大きさと、並びが逆転していないかの2つです。中位の帯で解約率が上下に跳ねるなら、重みの組み合わせが解約の構造をうまくとらえていません。

Claude for Excelは、ピボットテーブルの編集や条件付き書式を直接頼めます。帯の分け方と集計は、次のように任せられます。

「スコア」列を高い順に並べて5等分した「スコア帯」列を「検証表」に足し、
スコア帯ごとの顧客数・解約数・解約率を出す集計表を新しいシートに作ってください。
解約率が帯をまたいで逆転している箇所には色を付けてください。

色を付ける条件を数式で指定させると、定義を変えて再計算したときも逆転の有無が自動で更新されます。

期間を分けて検証し、重みの安定性を確かめる

同じデータで重みを決めて同じデータで当たり具合を見ると、実績に合わせ込んだ分だけ結果が良く見えます。期間を2つに分けるのが最も手軽な対策です。

1. 古い方の期間(たとえば前年度の観測日)で重みを計算する
2. その重みを固定したまま、新しい方の期間のスコアを出す
3. 新しい期間だけで、スコア帯別の解約率を出す

新しい期間でも並びが保たれていれば、その重みは時期をまたいで使えそうです。並びが崩れるなら、料金改定や機能追加のような構造変化があったか、古い期間の解約数が少なく重みが偶然に引きずられた可能性があります。

もう1つの見方は、期間ごとに重みを計算して並べ、指標の順位が入れ替わるかを見ることです。ログイン日数が一貫して最大なら、スコアの柱として扱いやすくなります。順位が毎期入れ替わる指標は、重みを小さく置くか、翌期も様子を見る扱いにします。

重みの計算を平均の差ではなく回帰で行いたい場合は、Excelの LINEST で解約フラグを目的変数にした重回帰を組めます。0と1を直接当てはめる簡易な方法で、ロジスティック回帰のような確率の形は出ません。係数は指標の単位に左右されるので、あらかじめ各指標を標準化してから入れます。結果は平均の差で出した重みと見比べる補助に留め、置き換える必要はありません。

つまずきやすい4か所

指標どうしが同じことを測っている。契約月数が長い顧客はログイン日数も安定していることが多く、両方に大きな重みを置くと同じ情報を二重に数えます。指標の間の CORREL を一覧表にして、強く相関する組は片方の重みを下げるか、1つの合成指標にまとめます。

解約を告げた後の問い合わせを数えている。解約の相談や解約手続きの問い合わせが、問い合わせ数に含まれると先ほどの失敗が再発します。問い合わせの分類列があれば、解約関連の分類を集計から外す指定を依頼文に入れます。

解約数が少なくて重みが安定しない。解約フラグが1の顧客が少ないと、平均の差は少数の顧客に引きずられます。重み計算シートの上部に1の件数を出させるのは、このためです。件数が少ないときは、期間を延ばして観測日を増やすか、重みを丸めて粗い配分にします。

スコアの精度を、特定の顧客層の偏りで読み違える。企業規模やプランが解約率を大きく左右する場合、スコアが規模の違いを拾っているだけの可能性があります。プラン別や規模別にスコア帯別の解約率を出し、各層の中でも並びが保たれるかを確認します。

Claude for Excelの対応範囲と顧客データの扱い

ここまでの手順で使う操作は、数式、別シートの作成、ピボットテーブル、条件付き書式で、公式が対応する操作に含まれます。一方で、データテーブル(ExcelのWhat-If分析の機能)とマクロ・VBAには対応していません。重みの感度を見るために重みを少しずつ動かす表を作るときは、データテーブルではなく通常の数式で格子を組むよう頼みます。

利用できるプランはPro、Max、Team、Enterpriseで、Excelの版にも条件があります。Excel on the webのほか、WindowsはMicrosoft 365のbuild 16.0.13127.20296以降、Macはversion 16.46以降(build 21011600以降)が対象です。Excel 2016・2019の永続ライセンス版、iPad、Androidでは動きません。

顧客データを扱う場合に押さえておきたい点は次のとおりです。

• 顧客名や担当者名は、検証には不要です。IDだけを残してマスクしたコピーで作業します
• 公式の制限事項は、機密性の高いデータや規制対象のデータを、適切な管理なしに載せたモデルへの利用を勧めていません
• 外部から受け取ったファイルには、Claudeを誤作動させる指示が隠れている場合があります。他社のシステムからエクスポートした表は、信頼できるコピーに値だけを貼ってから使います
• 入力と出力は、バックエンドで受信または生成から30日以内に削除されます。Enterpriseの監査ログには、Claude for Excelの活動は含まれません

編集は、書き換える前に確認が入ります。Claude for Excelは既存のデータを上書きする前に警告を出し、危険な操作は実行前に承認を求めます。元の表を残したコピーのブックで作業し、書き換え後はセルを開いて数式の中身を自分の目で追います。

まとめ

ヘルススコアの重みは、観測日で切った検証表を作り、解約組と継続組の差の大きさを重みに直すだけで、勘から実績へ根拠を移せます。当たり具合はスコア帯別の解約率の並びで見ます。重みを決めた期間とは別の期間でも並びが保たれるかを確かめて、初めて運用に使える重みと言えます。

Claude for Excelに任せるのは、結合と数式とピボットの組み立てです。解約の定義、予測の期間、除外する顧客は人が決め、依頼文の先頭に書きます。この3点が動くと重みの意味が変わるので、定義を変えたときは検証を最初から回し直します。

値上げ前後の離脱率を試算する側の組み方は値上げシミュレーションを離脱率つきでClaude for Excelに組ませる手順に、MRRと解約の推移を会話で引く方法はClaudeとChartMogulを連携する方法にあります。