😸
BigQueryでWAUやMAUを求める
※qiitaからの転載です。
※公開日は2019年12月09日なので内容に古いところがあるかもしれません。
環境
- firebase&BigQuery環境
- StandardSQL
DAUとかは比較的簡単に求められるわりに、WAUとかMAUが地味に考えにくかった＆調べてもあんまり出てこなかったので書いておきます。
firebaseを見ればわかるっちゃわかるんですが、自分で書けるようにしておくと理解が深まる＆セグメントしたくなったときに扱いやすいので。
やりたかったこと
WAU(weekly active user)はその名の通り「1週間のうちに起動したユーザーの数」。
今回は推移を出したかったので「ある時点から過去1週間のうちに起動したユーザーの数」としています。
このWAUを1週間ごとではなく、日ごとの推移(任意の日付からさかのぼって7日間)を出したい。
こんなイメージ
| 日付 | wau |
|---|---|
| 2019-12-01 | 10,000 |
| 2019-12-02 | 10,500 |
| 2019-12-03 | 11,000 |
| : | ： |
MAUの場合も同様
実際のクエリ
firebaseの実際の数字と比べてみましたが、ほぼほぼ合ってる感じだったのでこれでできてるのではと思います。(間違ってたら指摘ください)
-- 日ごとに起動したユーザーのリストをつくる
with user_open as (
SELECT
id
,date(timestamp_micros( event_timestamp ),"Asia/Tokyo") open_date
FROM `テーブル名_*`
-- 過去35日間のデータを参照する（弊社の仕様）
where _table_suffix between format_date('%Y%m%d', date_sub(current_date("Asia/Tokyo"), interval 35 day)) and format_date('%Y%m%d', date_sub(current_date("Asia/Tokyo"), interval 1 day))
and event_name = "session_start"
group by id
,open_date
)
-- 集計に使う配列を生成する
,date_table as (
select
date
from
unnest(GENERATE_DATE_ARRAY(date_sub(current_date("Asia/Tokyo"),interval 28 day),date_sub(current_date("Asia/Tokyo"),interval 1 day))) date
)
-- 2つのテーブルを交差結合する
-- 邪魔な行をwhereで削っておく(差が7日以内になるように絞る)
,cross_table as (
select id
,open_date
,date
from user_open
cross join date_table
where date_diff(date,open_date,day) < 7
and date_diff(date,open_date,day) >= 0
)
-- dateでgroupしてidの個数を数えれば推移が出る
select
date
,count(distinct id) wau
from cross_table
group by date
order by date asc
交差結合して全パターンの組み合わせを作り、7日の範囲でしぼっていくのがミソです。
これで日付のgroup byすればその日から7日以内に起動した人数が求まります。
MAUの場合は元データの抽出期間を伸ばし、参照範囲を変えれば同様にできます。
Discussion