LTVを算出するVBA

(いらすとやにもLTVっぽいイラストはなかった。。。）

ちょっと前、ネット上でゲームのマーケに関わってるっぽい人でLTVの定義、算出方法をいまいち理解してないっぽい人がいました。
また、実際にちゃんとしたゲーム会社に勤めてるマーケの方で、LTVを考慮せずに適当に？目標CPIを設定してる人もいました。

「そんな奴おらへんやろぉ～」と思うそこのキミ！
あなたは上位8割の人材です。下位2割は分かってなかったりします。

「2割も分かってない奴おらへんやろぉ～」って思うでしょ？いや、結構いるんですよ。。。

ということで、今回はその分かってない人に向けた記事ですが、
分かってる人にも役立つかもしれない物を最後に掲載しますので、分かってる人は記事末尾まで飛んでください。（今回の本題はこっち）


■LTVとは

LTV = Life Time Value・・・顧客生涯価値

です。以上！

・・・なんですけど、ゲームマーケの実地での使われ方をもうちょい説明。
言葉の通りの受け取り方だと、ユーザがそのサービスで最終的に使う金額の平均値です。
例えば、あるゲームタイトルがあり、ユーザが平均毎月1万円使っていき、平均12ヵ月でそのゲームを辞めたら、LTVは12万円です。

ですが、あまりこういう使われ方はせず大体1ヵ月単位で区切ります。
30日LTV、60日LTV、90日LTVなど。
つまり、ゲーム開始から30日、60日、90日経過した時点で合計いくら使ったか、という見方です。

じゃぁそれらの算出方法はどうするのか？

30日LTVであれば、下記のようになります。
1日目の利用額＋2日目の利用額＋3日目の利用額＋4日目の利用額・・・・+30日目の利用額

もうちょい抽象化すると、LTV = ARPU×継続率ということが言えます。

例えば、

ARPU：100円（※DAUに対しての）
継続率：
初日100%（これもたまによく理解してない人居るけど初日は必ず100%）
1日後50%
2日後40%
3日後30%
・
・
・
7日後20%
30日後10%

みたいな場合、30日LTVを算出しようとすると、

初日利用額・・・100%×100円=100円
1日後利用額・・・50%×100円=50円
2日後利用額・・・40%×100円=40円
3日後利用額・・・30%×100円=40円
・
・
・
7日後利用額・・・20%×100円=20円
30日後利用額・・・10%×100円=10円

となり、上記を合算した額がLTVになります（・・・と省略してる部分も合算しますよ。念のため）
ちなみにこの場合は30日LTVは253円くらいになります。

だいたいのマーケやってる人は手元のExcelとかでこういう計算していると思います。


■実運用時の使用

そのゲームのLTVが分かったところで、実運用時にどう使うのか？

LTVから広告出稿時に許容できるCPIを設定します。
どれ位許容できるのかは、タイトルによっちゃうんですが、オーソドックなのは90日～120日LTVで粗利回収とかじゃないでしょうか。

粗利って言ってるのはこのLTVからストアマージン30%を除いた金額です。
例えば30日LTV253円からストアマージン除いたら177円です。

で、例えば90日LTVが400円だったら、30%引いた280円が粗利で、90日で粗利回収したかったらCPIは280円を基準にする、というようなことです。

実際にはここから広告メニュー毎にROASを見てCPIを変えてくような運用になる訳です。


■とは言え計算面倒だよね（本題です）

運用が始まったらトラッキングツール上で同様のもの（LTV）を見れると思いますが、その前って実績の蓄積がないので手元で計算する必要があります。
皆さん、どうやってます？Excelでしこしこ計算してますか？

そこで、VBAでARPU・継続率を入力するとLTVを算出してくれる関数を組んでみました。
サンプルファイルはこちらから。

getLtvという関数で、使い方は下記の通り。

getLtv(①ARPU, ②継続率の範囲, ③LTVの区切り日数)

①はそのままでARPUが100円だったら"100"と入力。
②は1日目、3日目、7日目、14日目、30日目、60日目、90日目の継続率のセルを範囲指定で入れてください。
　範囲指定は"A1:G1"みたいにしてください。
③例えば90日LTVが欲しかったら"90"と入力します。

Excelにサンプルも入ってるので見ればだいたいわかると思います。

ちなみに継続率の指定が90日までしかできないですが、180日までLTV算出できます。
90日以降の計算ロジックとしては1日立つ毎に前日の継続率の99%になるようにしてますので、あくまで大体の数値を知るためのものです。
あと、2日目の継続率など間の部分は前後の中間の値になるようにして、複数日ある場合は1次関数的に傾斜付けてます。

勢いで作ったんで設計も実装も荒いですが、なんらか需要あればWEB版とか作るかも。（別に暇な訳じゃないっすよ！）