DAUやMAUを取得するSQL

DAUやMAUって聞いてピンと来る人、こない人。
この記事に当たったと言うことは、DAU、MAUを知っているはずですね。

「月毎のアクティブユーザー数を集計する」とか言うタイトルの方が、それっぽいんですが、あえて「DAU」とか「MAU」と言う単語使っていきます。

DAUとは Daily Active User の略

そもそも「DAU」って？と言う方はいないと思いますが Daily Active User の頭文字。
つまり１日毎の活動的なユーザーの数を表しています。
特にアプリなどでは重要な指標で、ウェブサイトでも会員制サイトなどはこの数値を重要な指標として扱う場合もあります。
「MAU」は Monthly Active User ですので、意味は「月毎」というだけで、DAUと同じ様な指標です。

もっと込み入った話はこの辺りがオススメです。
「MAU・WAU・DAU」とは？事例を交えながら各成果指標の意味を解説

今日はタイトルの通り「SQL」のお話

弊社開発のCMSには会員機能が備わっているので、どれくらいのユーザーがログインしてサイトを楽しんでくれてるか？というのは会員に対するサービス改善の指標になるわけで、簡単に確認できるべきと考えたので、早速実装です。（この数値は「ダッシュボード」に表示します。現在利用中のクライントしか分からないネタですみません。）

会員活動のログフォーマットについて

DAUにしてもMAUにしても会員の活動が記録されていないと始まりません。
今回は参考として「日時」、「ユーザーID」、「アクション」が最低限記録されているとします。
これを日毎（過去30日間）と月毎（過去6ヶ月間）にまとめていきいます。

MAUを取得する

まずはMAUから。サブクエリーを使用するので説明は２段階です。

Step1　１ユーザー１ヶ月１回のアクションがあればいいので、GROUP BY句でまとめて取得

SELECT
    user_id,
    DATE_FORMAT(datetime, '%Y%m') AS yyyymm
FROM
    table
GROUP BY
    user_id,
    yyyymm
WHERE
    action = "login"

WHERE句などは適当に追加、加工してください。

このSQL発行時点でデータは以下の様に取得されています。（１ユーザー１ヶ月１回分）

Step２　サブクエリーの結果を元に月毎にユーザー数を数えてまとめて取得

SELECT
    COUNT(user_id) AS count,
    yyyymm
FROM
    (SELECT
        user_id,
        DATE_FORMAT(datetime, '%Y%m') AS yyyymm
    FROM
        table
    GROUP BY
        user_id,
        yyyymm) AS aggregate
GROUP BY
    yyyymm

実行結果はこちら

このSQLの肝は、先のサブクエリーをテーブルとして扱うことと、DATE_FORMATで日時をまとめたい単位の表示形式に整形するところですね。

ここで「AS aggregate」の部分が無いとエラーになります。
名前はなんでもいいんですが、エイリアス名は必須です。

DAU用のSQLの場合は、DATE_FORMAT(datetime, ‘%Y%m’)をDATE_FORMAT(datetime, ‘%Y%m％d’)という様に日付まで含めたフォーマットで整形すれば、あとは同じSQLで動きます。

じゃあWAU（Weekly Active User）は？

DATE_FORMAT(datetime, ‘%X%U’)
でお願いします。
%X …日曜日を週の最初の日とした場合の週に使用する、4 桁の数値で表した年
%V …日曜日を週の最初の日とした場合の週（00..53）
実行するとこんな感じの値が取得できます。

上から、2019年の第26週、2019年の第30週、2019年の第32週、という感じですが、このままの表記だとちょっと分かりづらいですね。

その他の情報

もっとスマートなやり方ないかな〜と調べてみましたが、そもそもMAUやWAU取得時に『今日』を基準に算出してる方もいました。
今日含め過去７日間や30日間といった値を取得していましたが、１日毎に変化しちゃうそれってMAUとは言わないかと…
この辺りの記事とか
https://discuss.redash.io/t/dau-wau-mau-query-example/1704

確かにGoogleアナリティクスのアクティブユーザーの値はこんな感じで計測日を基準として指定期間のアクティブユーザー数を表示していますが、これをDAU,WAU,MAU言うのは違うと思います。
英語でいい表現が思い浮かびませんが「期間アクティブユーザー」とか？

マーケティング的には、この指標から読み取れる情報も変わってしまうので、指標名称は統一された認識で運用したいですね。

ああ、今回は「SQL」の話でした。最後に道がそれましたね。と言うことで今回はここまで。