プロダクト改善のためにウォッチしておくべき7つの指標

データ分析部でグノシーというニュースアプリのプロダクト改善を担当している @ij_spitz です。

今回はプロダクト改善のためにウォッチしておくべき7つの指標をSQLで算出してみます。 Gunosyではこれらの指標を、プロダクトに異常があった時に検知するため、また施策の効果検証といった主に2つの目的で使用しています。

簡潔にするため、ユーザーとログインの2つのテーブルを使った算出できる指標のみを対象としています。 また、これらの指標をどうやってプロダクト改善に役立てているのかということも少しではありますが、合わせて書いていきたいと思います。

• DAU
• WAU(MAU)
• HAU
• 積み上げHAU
• 1ユーザーあたりのログイン回数
• 登録N日後継続率
• 登録日別N日後継続率

前提

今回のブログで紹介するSQLはAmazon Redshift上で動くSQLなので、MySQLやGoogle BigQueryでは動かない可能性があります(というか動きません)。 またテーブル定義は以下のようになっています。

logins

Column	Type	備考
user_id	integer
created_at	timestamp without time zone	ログイン日時

users

Column	Type	備考
user_id	integer
created_at	timestamp without time zone	ユーザーの作成日時

DAU

SELECT
    created_at::date AS date,
    COUNT(DISTINCT user_id) AS dau
FROM
    logins
WHERE
    created_at >= '2017-06-01 00:00:00'
    AND created_at < '2017-07-01 00:00:00'
GROUP BY
    date
ORDER BY
    date

みなさんおなじみだとは思いますが、1日にサービスを利用したユーザーのユニーク数を表すDAUです。 ニュースアプリは毎日使ってもらうのが大事なので、毎日どれくらいのユーザがアプリを起動したかどうかを重要視しています。

DAU = Σ 新規ユーザー数 * 継続率

と分解できるので、DAUを増やすためには新規ユーザー数を増やすことおよび、継続率を上げる(下がった時に異常に早く気付く)ことが重要になってきます。

WAU(MAU)

SELECT
    date_series.date,
    COUNT(DISTINCT user_logins.user_id) AS wau
FROM (
    SELECT
        DISTINCT created_at::date AS date
    FROM
        logins
    WHERE
        created_at >= '2017-06-01 00:00:00'
        AND created_at < '2017-07-01 00:00:00'
) AS date_series
JOIN (
    SELECT
        DISTINCT
            created_at::date AS date,
            user_id
    FROM
        logins
    WHERE
        created_at >= DATEADD(day, -7, '2017-06-01 00:00:00')
        AND created_at < '2017-07-01 00:00:00'
) AS user_logins
ON
    user_logins.date <= date_series.date
    AND user_logins.date > DATEADD(day, -7, date_series.date)
GROUP BY
    date_series.date
ORDER BY
    date_series.date

DAUを最大化しようとすると、短期的な最適化に陥ってしまう可能性があります(非常に極端な例ですが1日に10回プッシュを打ってみるなど)。

そういった状況を避け、長期的に見てユーザー数が最大化するためには、DAU以外にWAUやMAUといった指標も確認しておく必要があります。

HAU

SELECT
    EXTRACT(HOUR FROM created_at) AS hour,
    COUNT(DISTINCT user_id) AS hau
FROM
    logins
WHERE
    created_at >= '2017-06-01 00:00:00'
    AND created_at < '2017-06-02 00:00:00'
GROUP BY
    hour
ORDER BY
    hour

HAUもよく使われる指標の1つです。

グノシーでは1日に4回プッシュ通知を打っているので、そのプッシュ通知の効果がどうだったかというのを見るためにHAUも確認しています。

プッシュの開封率も確認していますが、アプリのアイコンを直接タップして起動するユーザーもいるためHAUと両方の数値を見ています。

積み上げHAU

SELECT
    hour_series.hour,
    COUNT(DISTINCT logins.user_id) AS hau
FROM (
    SELECT
        DISTINCT EXTRACT(HOUR FROM created_at) AS hour
    FROM
        logins
    WHERE
        created_at >= '2017-06-01 00:00:00'
        AND created_at < '2017-06-02 00:00:00'
) AS hour_series
JOIN
    logins
ON
    hour_series.hour >= EXTRACT(HOUR FROM created_at)
WHERE
    created_at >= '2017-06-01 00:00:00'
    AND created_at < '2017-06-02 00:00:00'
GROUP BY
    hour_series.hour
ORDER BY
    hour_series.hour

DAUを最大化するという目的に立ち返った時に、HAUばかりを見ていると同じユーザーばかり起動していて最終的にDAUはそれほど伸びていなかったというケースもあり得ます。

その日に初めての起動を促すプッシュの方がDAUへの寄与は高くなるため、積み上げHAUも同時に見ています。

1ユーザーあたりのログイン回数

SELECT
    created_at::date AS date,
    1.0 * COUNT(1) / COUNT(DISTINCT user_id) AS login_num
FROM
    logins
WHERE
    created_at >= '2017-06-01 00:00:00'
    AND created_at < '2017-07-01 00:00:00'
GROUP BY
    created_at::date
ORDER BY
    created_at::date

1ユーザーあたりの◯◯という指標もよく使います。 グノシーの場合だと1ユーザーあたりの読んだ記事数や閲覧したタブの数なども見ています。

登録N日後継続率

SELECT
    rr.num_elapsed,
    100.0 * rr.user_num / inflows.user_num AS retention_rate
FROM (
    SELECT
        DATE_DIFF('DAY', users.created_at::date, actives.date) AS num_elapsed,
        COUNT(DISTINCT actives.user_id) AS user_num
    FROM
        users
    JOIN active_users AS actives
    ON
        users.user_id = actives.user_id
    WHERE
        users.created_at >= '2017-06-01 00:00:00'
        AND users.created_at < '2017-06-08 00:00:00'
        AND actives.date >= '2017-06-01'
   GROUP BY DATE_DIFF('DAY', users.created_at::date, actives.date)
) AS rr
JOIN (
    SELECT
        COUNT(DISTINCT users.user_id) AS user_num
    FROM
        users
    WHERE
        users.created_at >= '2017-06-01 00:00:00'
        AND users.created_at < '2017-06-08 00:00:00'
) AS inflows
ON
    1 = 1
WHERE
    /* 対象登録日の最終日に十分な経過日数がある日だけに絞る */
    rr.num_elapsed  < DATE_DIFF('DAY', '2017-06-08', CURRENT_DATE)
ORDER BY num_elapsed

ユーザーが新規登録してからN日後にログインしている率を継続率と呼びます。

ABテストを行ったときは2つのグループ間でこの継続率を比較して、その施策がどれだけ継続率をリフトアップさせているか(もしくはダウンさせているのか)を確認しています。

DAU = Σ 新規ユーザー数 * 継続率

前述した上記の式からもわかるように、継続率が高いほどDAUの積み上がりも良くなるので、基本的には継続率が良くなる施策 = 良い施策となります(もちろん例外はありますが)。

登録日別N日後継続率

SELECT
    rr.created_date AS created_date,
    100.0 * rr.user_num / inflows.user_num AS retention_rate
FROM (
SELECT
    users.created_at::date AS created_date,
    DATE_DIFF('DAY', users.created_at::date, logins.created_at::date) AS num_elapsed,
    COUNT(DISTINCT logins.user_id) AS user_num
FROM
    users
JOIN
    logins
ON
    logins.user_id = users.user_id
WHERE
    users.created_at >= '2017-06-01 00:00:00'
    AND users.created_at < '2017-07-01 00:00:00'
    AND logins.created_at >= '2017-06-01 00:00:00'
GROUP BY
    created_date,
    logins.created_at::date
) AS rr
JOIN (
    SELECT
        created_at::date AS date,
        COUNT(DISTINCT users.user_id) AS user_num
    FROM
        users
    WHERE
        created_at >= '2017-06-01 00:00:00'
        AND created_at < '2017-07-01 00:00:00'
    GROUP BY
        date
) AS inflows
ON
    rr.created_date = inflows.date
WHERE
    rr.num_elapsed = 1
ORDER BY
    created_date

上のクエリはN = 1となっています。 これも継続率の1種ですが、先ほどの登録N日後継続率とは少し用途が異なります。

登録N日後継続率では主に異なる2つのグループ間での継続率の差を見るときに使用しているのですが、こちらの登録日別N日後継続率だと時系列で継続率の推移が確認できるので、異常検知に使用したり(例えば特定の広告で獲得したユーザーの継続率が低かったとか)中長期的に見て継続率がどんな傾向をしているのかを見るために使用しています。

弊社でBIツールとして主に用いているRedashでの可視化のアウトプットを見ると両者の違いがわかりやすいと思います(細かい数値は載せられないので軸が見えていない点はご了承ください)。

• 登録N日後継続率(縦軸が継続率、横軸が登録後経過日数)

• 登録日別N日後継続率(縦軸が継続率、横軸が登録日)

Redashについての記事はこちらをご覧ください。

data.gunosy.io

data.gunosy.io

おわりに

以上で終わりになります。 少し複雑なSQLもありましたが、2つのテーブルとSQLだけでこれだけの有用な指標を算出することができます。 SQLを叩く環境がもし社内に整っているなら、エンジニアでなくても、ぜひSQLをマスターして色々なデータを眺めてみることをおすすめします。 今後、アプリやWebサービスの業界では社内にSQLを叩ける環境があるということがスタンダードになってくるはずなので、覚えておいて損はないスキルだと思います(弊社ではエンジニア以外の社員もSQLを叩いて自分の見たい数値を見ています)。

Gunosyでは数値に基づいたプロダクト改善を日々行っているということを少しでも実感していただければ幸いです。