データ整備

[データ整備/整理] MAUやDAUを題材にして集計の標準化ができないものかを考えてみる

集計の標準化はできないものか

いろいろな企業で整備をしているとある程度複雑なクエリでも「前に同じ様なことやったなぁ」と思うことはままある。

ということは、よくある集計を標準化しておけば使いまわせる。あと個別に教えなくとも済みそう、なんてことを考えている。

当然各社データが違うので整備段階では無理だ。しかし指標であればインプットの型を決めてさえいればそれなりにまとめられるかもしれない。

というわけで、試しに1つMAUやDAUを例に書いてみることにする。

MAUやDAUの集計を標準化する

定義

MAU(Monthly Active User)とは月間のアクティブ(ログインやサービス利用など)になったユニークユーザー数のこと。DAU(Daily Active User)は日ごと。

「ある単位におけるユニークユーザー」であり単位が違うだけで集計の考え方は同じ。なのでまとめて扱うことにする。週や年でも同様。実は日である必要もなくフラグなども同じだが話を広げるとややこしくなるので月や日の話に絞る。

必要なデータ

ユーザーと日があればいい。ユニークである必要はない。

カラム カラム名 データ型 備考
ユーザー id 文字列型、数値型 購入者idや店舗idなど
日付 d date型,datetime型,timestamp型

アウトプット

カラム名 データ型 備考
d 日付型 date_truncで指定した単位
cnt_au 数値型 指定した単位でのユニークユーザー数
cnt_value 数値型 指定した単位でのトランザクション数

クエリ

クエリの本体。最初のWithにインプットとなるクエリを入れる。そこまでのクエリが複雑なら一旦テーブルに書き出して*でとるようにした方がいいかもしれない。


with a as
(
#ここにインプットを作るクエリを書く
)
,

b as (
select
date_trunc(date(d),month) as d #DAUならば「month」を「day」に変える 
,id
,sum(1) as cnt_value
from a
group by 1,2
)

select
d
,count(1) as cnt_au
,sum(cnt_value) as cnt_value
from b
group by 1
order by 1

クエリの詳細

実行の確認はBigQueryのみ。

日付型に統一してdate_truncで集計したい単位に変換。monthやday以外にもweekやyearも取れる。詳細は DATE_TRUNCを参照。

集計する単位においてidをユニークにするためトランザクションを集計しているがdistinctでも同じ。

一緒に売り上げも出せるけど

MAUやDAUと組み合わせたい指標(salesとしよう)はsum(1) の代わりにsum(sales)にすればcnt_tranととして一緒に出てくる。sumを増やせばいくつでもいけるけどMAUやDAUの話だけにしたいのでここでとめておく。

MAUやDAUだけすぐに欲しければすぐ書ける

あえて日付×idにしているのは後でidにいろいろ付与したりするだろうことを見込んで使回しできるようにしている。

MAUやDAUだけだすならwithの後すぐにselectを書く下記のクエリでいい。


with a as
(
#ここにインプットを作るクエリを書く
)

select
date_trunc(date(d),month) as d
,count(distinct id) as cnt_au
from a
group by 1
order by 1

GA4+BigQueryでの使用例

実際にGA4+BigQueryを例にMAUを出してみよう。

ユーザーはuser_pseudo_idがある。日付はevent_timestampから作る。あとはカラム名を揃える。

実行して問題なく結果がでたら、MAUのクエリの「#ここにインプットを作るクエリを書く」をそっくり入れ替えて実行。問題なく動いてMAUが取れた。


SELECT 
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS d
,user_pseudo_id as id
FROM `xxxxxxxxxx.xxxxxxxxxx.events_*` 
where event_name='session_start'
and _table_suffix<= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
and _table_suffix>=  '20210516'

試しに作ってみた感想

もやっと思っていたことをひたすら書いてみたが、少々くどい。MAUやDAUは直接書いてもたいしたことがないからだろうか。もっと複雑なクエリにするか「ここに貼り付けたら結果が返ってくる」ぐらいにしないとありがたみが見えないかも。

あと実際には日付とidを取ってくるまでの方が大変(例で出したGA4+BigQueryなんて簡単な方だ)なのでどれぐらい労力が削れるかは未知数。

次は「〇〇してからn日以内にm回△した」ぐらいは書くつもりでいるけど、あまり効果がなさそうだったらまた考えよう。

複雑でなくともいいのだけど、これよく見かけるなぁ(でも毎回教えたりするのめんどいなぁ)というクエリがあったら教えてください。書いてみます。

人気のある記事

HOME > データ整備 > [データ整備/整理] MAUやDAUを題材にして集計の標準化ができないものかを考えてみる