アクセスログの最後の日から30日間ごとのユーザー数を計算する方法

サービスやプロダクトのユーザー数を理解するための指標として月ごとのユーザー数であるMAU（マンスリー・アクティブ・ユーザー）という指標があります。

MAUはアクティブなユーザー数を理解するために便利な指標ですが、例えば2020年の6月のMAUは7月になってからしか集計できないということがあります。

そこで、このノートでは以下のようにアクセスログの最後の日からさかのぼって、30日間ごとのアクティブ・ユーザー数を集計することで、月の途中であっても、30日単位で、過去のアクティブユーザー数を集計できる方法を紹介します。

今回はユーザーの行動ログのデータを使用していきます。このデータは一行がユーザーのアクセスのデータになっています。

1. 「日」ごとの計算ができるように、アクセス日時のデータを日付のデータに変換する

今回は30「日」ごとのグループ分けたいので、日ごとの計算を進めることになるので、「時間」の情報は不要となります。そこで、現在のアクセス日時の情報をアクセス「日」のデータに切り捨てていきます。

「timestamp」の列ヘッダーメニューから、データタイプの変換、Date（日付）タイプに変換を選択します。

そのまま実行します。

日ごとのデータに変換できました。

2. データ上の各アクセスが、データの最後の日から何日前のアクセスだったか計算する

続いて各アクセスがデータの最後の日から何日前であったのかの情報を取得します。

timestampの列ヘッダーメニューから表計算（Window Calculation）を作成、…からの違い、最後の日（max）を選択します。

続いて計算エディタの中を確認すると、以下のようにアクセス日と最後のアクセス日の差を求める計算式が入っていることが分かります。

今回知りたいのは、データ上の最後のアクセス日と、各アクセス日の差なので、計算式の順番を以下のように逆にして実行します。

これで各アクセスが、データの最後の日から何日前のものか分かるようになりました。

続いて「30日」ごとのグループを作っていきたいので、データ・タイプを数値型に変換します。

timestamp_diff_maxの列ヘッダーメニューから、データタイプを変換、Numeric（数値）タイプに変換を選択します。

数値型のデータに変換できました。

3. データの最後の日と各アクセス日の差を利用して、データの最後の日から30日ごとのグループに分ける

続いて先程計算した、データの最後の日と各アクセス日の差を30日ごとのグループに分けるために、30（日）で割ります。

先程求めた最後のアクセス日とアクセス日の差である、timestamp_diff_maxの列ヘッダーメニューから計算を作成を選択します。

計算エディタにtimestamp_diff_max / 30とタイプします。

この時、新しく列を作成にチェックをつけて、列名をn_30days_beforeにして、実行します。

これで、各アクセスを最後の日から30日ごとのグループに分けたときに、何番目のグループに入るのかが分かるようになりました。

ただし実際のデータは小数点を含むデータのままです。例えば7.9番目のグループのアクセスは7番目のグループに入るので今回のケースでは小数点以下のデータは不要となるため、切り捨てていきます。

n_30days_beforeの列ヘッダーメニューから数値関数を使うを選択し、切り捨て関数となるfloorを選択します。

そのまま実行します。

小数点で切り捨てることで、30日ごとのグループに分けることができました。

4. 最後のアクセス日から30日ごとのグループで、アクティブユーザー数を計算する

30日ごとのグループに分けることができたので、後は30日ごとのユーザー数の計算をすれば良いだけです。

n_30days_beforeの列ヘッダーメニューから、集計（Summarize）を選択します。

グループ化にn_30days_beforeを選択します。値にuseridを選択し、集計関数に一意な値の数（unique）を選択します。

続いて列名の変更アイコンをクリックして、列名を30days_unique_usersに変更します。

すると、以下のように最後の日から30日ごとのユーザー数の集計ができました。

ただし、この集計により、元のデータに存在していた日付の情報がなくなってしまいました。

例えば最後のアクセス日から30日ごとのユーザー数をMAUの代わりに利用して、DAU/MAUを計算するときは日ごとに計算する必要があるので、日ごとに最後の日から30日ごとのユーザ数を知りたいということがあります。

そこで今回は以下のように日付の情報を残したまま、最後の日から30日ごとのグループのユーザ数を計算して、列として追加していきたいと思います。

まずは先程作成した集計のステップを削除します。

今回は30日単位のグループごとのユーザー数を計算したいので、n_30days_beforeの列ヘッダーメニューからグループ化を選択します。

これで、30日ごとのグループに分けることができました。

ユーザー数を計算したいので、ueridの列ヘッダーメニューから、計算を作成を選択します。

計算エディタに一意な値の数を数えるn_distinctという関数を利用してn_distinct(userid)とタイプします。

続いて、新しく列を作成にチェックをつけて列名をn_30days_unique_usersとして実行します。

グループ化を解除します。

これで日ごとに、最後の日から30日ごとのユーザ数が計算できました。