目的
- プロダクト分析（特にゲーム業界が多い？）で用いられるFQ（Frequency）のQueryサンプルを書きます
- ユーザーの定着状況を図る指標の1つです
FQを取得するメリット
- KPIには、
良質なKPIだけでなくノイズの多いKPIなどもあり、大きな粒度の数値だけでは判断を誤ることが多々あります - 特にDAUの数値はpush通知を送りまくる、SNSでバズってたなどで変動することが多い数値です
- プロダクトとしては定着するユーザーを増やすことが重要施策の1つであるはずなので、
定着ユーザーを定量的に把握するための1つの手段としてFQを用いることができます
注意事項
- プロダクトやサービスによっては1週間で見ない場合もありますし、毎日使ってもらう必要がないものもあるのですべてのプロダクトで必要な概念ではないです
- 連続を問う問わないなどのように定義が異なる場合があるので気をつけてください
-
概念がややこしいです
FQの概念
- DAUをFQで分解する考え方です
- 当日を含む過去7日間のうちに何日間アクセスしていたかを集計する指標でユーザーの利用頻度（≒定着状況）を把握するのに用います
- 今回は連続性は考慮しない定義としています
アウトプットのイメージ
- グラフにするときはFQの高い（定着している）数値を下にすることで、施策をすることでユーザーが積み上がっていっていることがわかるようにします
- バズったりしてユーザーが増えるときを考慮し、場合によってはFQ1,2は見ないなどの判断もあります
データソース
こんな感じのログイン日とuser_idが入ってるdaily_active_usersのtableから集計します
| dt | user_id |
|---|---|
| 2019-01-01 | 100 |
| 2019-01-01 | 101 |
| 2019-01-02 | 101 |
| 2019-01-02 | 103 |
| 2019-01-03 | 100 |
Query
考え方
- user_idに対して、当日を含む過去7日間で何回ログインしているか（FQ）のflgを付与し、そのFQのflg別のuser数を集計します
- そのために、同じテーブルをjoinし、基準日（DAUのDに当たる日）に対して、過去7日間のうちに何日間該当ユーザーがいたかを判断しています
COUNT(distinct(CASE WHEN t1.dt BETWEEN t2.dt AND t2.dt + integer '6' AND t1.user_id = t2.user_id THEN t2.dt ELSE NULL END)) AS FQ
SELECT
dt,
concat('FQ',FQ) AS FQ,
COUNT(distinct user_id) AS uu
FROM
(
SELECT
t1.dt,
t1.user_id,
COUNT(distinct(CASE WHEN t2.dt BETWEEN DATE_SUB(t1.dt,INTERVAL 6 DAY) AND t1.dt AND t1.user_id = t2.user_id THEN t2.dt ELSE NULL END)) AS FQ /*FQを計算*/
FROM
(
SELECT
dt,
user_id
FROM daily_active_users
)t1
LEFT OUTER JOIN
(
SELECT
dt
,user_id
FROM daily_active_users
)t2
on t1.user_id = t2.user_id
GROUP BY
t1.dt
,t1.user_id
)t
GROUP BY
dt
,concat('FQ',FQ)
さいごに
今後もこういったPM向けの分析の記事をちょこちょこ書くので、よかったらQiitaとかTwitterとかフォローしてもらえると嬉しいです！
ツイッターアカウント：https://twitter.com/ysk_ur
こういうのもありかもしれないです。型と日付にはまる、、、
COUNT(distinct(CASE WHEN t1.dt BETWEEN DATE_SUB(t2.dt,INTERVAL 6 DAY) AND t2.dt AND t1.user_id = t2.user_id THEN t2.dt ELSE NULL END)) AS FQ /*FQを計算*/