Calculate DAU, WAＵ, and MAU by BigQuery

前言

近年來, 商業智慧( Business Intelligence, BI )的運用已漸漸地受到企業的重視, 決策者可以藉由 BI 提供的資料來做業務的分析與預測

在做業務分析的時候, 通常會以每日活躍使用者數( Daily Active User, DAU ), 每週活躍使用者數( Weekly Active User, WAU ), 以及每月活躍使用者數( Monthly Active User, MAU )的多寡, 來暸解目前產品的營運狀況

要計算出這些資訊其實並不難,

首先以 Group By "日期"的方式算就可以先算出 DAU

WITH DAUtbl AS (
 SELECT _PARTITIONDATE AS date, COUNT(*) as dau
 FROM my_dataset.usr
 WHERE _PARTITIONDATE BETWEEN
   DATE_SUB(DATE('2020-03-16T13:15:30Z'), INTERVAL 7 - 1 DAY)
   AND DATE('2020-03-21T13:15:30Z')
 GROUP BY _PARTITIONDATE
)

有了每日人數( DAU )之後, 接下來就是來算 WAU

計算 WAU 時, 須將當日以及前六天的 Active User 數量都加總起來
而想要取得七天內的資料, 可以先透過 CROSS JOIN 的方式將每一日的資料都複製成七份

SELECT *
FROM DAUtbl, UNNEST(GENERATE_ARRAY(0, 7 - 1 )) i

每個副本都會有個索引值 i (如下)

有了七份的副本之後, 再將資料移動到合適的群組(日期)做加總, 最後就可以獲得每一日 WAU 的數目

SELECT
     DATE_ADD(date, INTERVAL i DAY) date_grp     <--- 移動資料
   , SUM(DISTINCT IF( i < 7 ,dau, null)) wau

FROM DAUtbl, UNNEST(GENERATE_ARRAY(0, 7 - 1 )) i
GROUP BY 1
ORDER BY date_grp

以下是完整的 SQL 代碼

DECLARE PERIOD INT64;
SET PERIOD = 7;

WITH 
DAUtbl AS (
  SELECT _PARTITIONDATE AS date, COUNT(*) as dau
  FROM `my_dataset.usr` 
  WHERE _PARTITIONDATE BETWEEN DATE_SUB(DATE('2020-02-24T17:02:17Z'), INTERVAL PERIOD -1 DAY) AND DATE('2020-04-13T17:02:17Z')
  GROUP BY _PARTITIONDATE
),
WMAUtbl AS (
  SELECT 
     DATE_ADD(date, INTERVAL i DAY) date_grp
   , SUM(DISTINCT IF( i < 7 ,dau,null)) wau
   , SUM(DISTINCT IF( i < 30 ,dau,null)) mau
  FROM DAUtbl, UNNEST(GENERATE_ARRAY(0, PERIOD - 1)) i
  GROUP BY 1
  ORDER BY date_grp
)

SELECT 
    date
  , dau
  , wau
  , mau
FROM DAUtbl
JOIN WMAUtbl ON DAUtbl.date = WMAUtbl.date_grp
ORDER BY date