いつものやつ

この記事は Gunosy Advent Calendar 2017、9日目の記事です(フライング)。

はじめに

Gunosyデータ分析部の大曽根です。好きなギタリストはジミ・ヘンドリクスです。前日の@ij_spitzに引き続きKPI管理に関しての記事を書こうかと思います。

f:id:dr_paradi:20171208161809p:plain

なぜ将来を予測することが重要か

ニュースアプリの場合には、毎日開いてくれるユーザが何人いるかが非常に重要です(売上 = DAU * ARPUで表現できます)。そのため、現在のDAUが目標値に達しているのかいないのか、どの程度の割合で達成しているのかをモニタリングすることが必要になります。予測に対しての達成割合により、

「成長で売り上げ目標が達成可能なのかの判断をする」
「そもそも成長可能なビジネスなのか判断する」
「好調な要因を特定し、施策に反映する」
「達成できない要因を改善する施策を出す」

などの、不確定な状況への対応ができ、目標達成へより近づくことができます。

概要

データ分析においては、ただ集計された数字を眺めるだけではなく「比較すること」が必要です。前回のブログでは下記のように書かれています。

data.gunosy.io

『Gunosyでは各指標に対して過去の実績値から目標値を日次で設定しているので、目標値も同時にモニタリングできるように可視化しています。これによって現在のプロダクトがどのような状況にあるのかを数値的に見ることができます。』

例えば、昨日のとあるKPIが「100」という値であった場合に、前日が「90」なら上昇していると判断できます (当たり前)。また、「100」という数字がいいのか悪いのかを判断するために、時系列のトレンドだけではなく、事業の目標にあっているのかをモニタリングすることが重要になります。本記事では、SQLで目標値を算出する方法を書いていきます。

準備するもの

例えば、昨日DAUは Σ 特定の日からの昨日までの経過日数における継続率 * 特定日の獲得 で表現できるので、「特定の日の獲得」と「特定の日からの昨日までの経過日数における継続率」がわかれば、DAUを予測することができます。

日々の獲得の予算
継続率の予算

以下のような感じのテーブルを用意して見ます。

日々の獲得の予算

CREATE TABLE user_acquisition (
  `date` date DEFAULT NULL,
  `budget` int(10) DEFAULT NULL
)

中身

date	budget
2018-01-01	1000
2018-01-02	1000
2018-01-03	1000
・	・
・	・
2018-12-31	1000

継続率の予算

CREATE TABLE retention_rate (
  `date_duration` int(10) DEFAULT NULL,
  `retention_rate` double DEFAULT NULL
)

中身

date_duration	retention_rate
0	1
1	0.495
2	0.408
・	・
・	・
365	0.0098

※ 継続率は[0,1] の単調減少関数なので、 $R(i) = \prod_{i=1}^n a(1 - (\frac{1}{2}) ^ i )$ で雑に定義しています (R(0) = 1、iは初回起動からの日数、 a = 0.99の定数) *1。こんな感じの関数です。

f:id:dr_paradi:20171208164504p:plain

SQLでの算出

それでは、材料を元に将来のDAUをSQLで算出して見ましょう。

昨日DAUは Σ 特定の日からの昨日経過日の継続率 * 特定日の獲得 で表現できます(算数ですね)。

ため、下記のクェリで記述することができます。

基本編

SELECT
    target_date,
    sum(active_users) AS users
FROM
(SELECT
    u1.date AS regist_date,
    u2.date AS target_date,
    TRUNCATE(u1.budget * r.retention_rate, 0) AS active_users,
    datediff(u2.date, u1.date) AS duration
FROM
    user_acquisition u1, user_acquisition u2, retention_rate r
WHERE
    u1.date <= u2.date
    AND datediff(u2.date, u1.date) = r.date_duration) predict
GROUP BY target_date
ORDER BY target_date

Redashで可視化すると下記のようになります。先ほど定義した継続率で、1日平均1000人獲得すると年末のDAUが4万人程度になることがわかります。これにARPUをかけてあげれば日々の売り上げも予測できますね。予測により、獲得が足りない、継続率が足りない、などの議論ができるので予測から逆算することは非常に重要です。

f:id:dr_paradi:20171208164808p:plain

サブクェリでは特定の日(regist_date)に初回起動(登録)をしたユーザ群がn日後の特定日(target_date)に何人になっているか計算しています。

SELECT
    u1.date AS regist_date,
    u2.date AS target_date,
    TRUNCATE(u1.budget * r.retention_rate, 0) AS active_users,
    r.retention_rate
FROM
    user_acquisition u1, user_acquisition u2, retention_rate_test r
WHERE
    u1.date <= u2.date
    AND datediff(u2.date, u1.date) = r.date_duration
ORDER BY
    regist_date, target_date

結果はこのようにないります。仮定した継続率では、1000人獲得したユーザが、1年後には9人になってしまう計算になります。悲しいですね。

regist_date	target_date	active_users	retention_rate
2018-01-01	2018-01-01	1000	1.00
2018-01-01	2018-01-02	495	0.495
2018-01-01	2018-01-03	408	0.408
・	・
・	・
2018-01-01	2018-12-31	9	0.0098

また、月別獲得数などもキャンペーンなどの影響での獲得チャネルの変動を確認する上で重要になります。登録月でグループ化し、可視化することでより影響がわかりやすくなるでしょう。

SELECT
    target_date,
    date_format(regist_date, '%Y-%m') AS regist_month,
    sum(active_users) as users
FROM
(SELECT
    u1.date AS regist_date,
    u2.date AS target_date,
    truncate(u1.budget * r.retention_rate, 0) AS active_users,
    datediff(u2.date, u1.date) AS duration
FROM
    user_acquisition u1, user_acquisition u2, retention_rate r
WHERE
    u1.date <= u2.date
    AND datediff(u2.date, u1.date) = r.date_duration) predict
GROUP BY target_date, regist_month
ORDER BY target_date, regist_month

f:id:dr_paradi:20171208165514p:plain

もし、ここで「年末のDAU低くない？」と思ったりした方は獲得や継続率をいじってみましょう。

継続率を $R(i) = \prod_{i=1}^n a(1 - (\frac{1}{3}) ^ i )$ にしてa = 0.995にして見ましょう。継続率を変更した場合の結果が以下の図になります。

f:id:dr_paradi:20171208165211p:plain

なんと年末のDAUが9万人を超えました。倍以上です。

このように皮算用ができます*2。

応用

例えば下記のようなテーブルを用意すれば都度のキャンペーンにも対応できます。また、獲得チャネルごとに予算を用意することもできます。継続率も同様です。

CREATE TABLE user_acquisition_by_channel (
  `date` date DEFAULT NULL,
  `channel_name` varchar(32) DEFAULT NULL,
  `budget` int(11) DEFAULT NULL
)

dailyだけでなくweekly、monthlyでの指標に関しても同様の予測が可能です。

おわりに

本記事では、日々のKPI管理のために予算テーブルから、SQLで予測されたDAUを算出する方法を記述しました。目標から逆算し、何が足りているか、いないのかを考え、意思決定をすることは非常に重要です。

本記事ではあまり言及しませんでしたが、予算テーブルの継続率や獲得も、過去ログから近似することもできます。より精度の高い予測をすることで、意思決定の障壁となるノイズなどを除去できるでしょう。 *3

*1:aは(1 - 日々の離脱率)のようなイメージです。

*2:予算に組み込む際にはぬか喜びになりかねないので注意してください。継続率はそう簡単にはあがりません

*3:その分管理も大変そうですが。

Gunosyデータ分析ブログ

Gunosyで働くデータエンジニアが知見を共有するブログです。

SQL: 継続率と獲得数で将来のDAUをさくっと予測してみる