Help us understand the problem. What is going on with this article?

BigQueryでWAUやMAUを求める

  • 非エンジニアなので優しくしてください
  • 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の場合は元データの抽出期間を伸ばし、参照範囲を変えれば同様にできます。

KogaAkito
SQL職人。分析やPMをよくしてます。 統計1級とる
eversense
家族を幸せにすることで、笑顔溢れる社会をつくる。
https://eversense.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away