2019年1月22日火曜日

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版とか作るかも。(別に暇な訳じゃないっすよ!)

0 件のコメント:

コメントを投稿