google-bigquery - 如何使用 BigQuery(参与度)计算 DAU/MAU

Question

DAU 和 MAU(日活跃用户和月活跃用户)是衡量用户参与度的既定方法。

如何使用 SQL 和 Google BigQuery 获取这些数字？

Answer 1

2019 标准 SQL 更新:

• https://stackoverflow.com/a/49866033/132438

---

(要了解 DAU/MAU 的实用性，请参阅 http://blog.compariscope.wefi.com/mobile-app-usage-dau-mau 等文章)

让我们来研究一下存储在 BigQuery 中的 reddit 评论数据。我们想在每日滚动的基础上找出“AskReddit”subreddit 的 dau/mau 比率:

SELECT day, dau, mau, INTEGER(100*dau/mau) daumau
FROM (
  SELECT day, EXACT_COUNT_DISTINCT(author) dau, FIRST(mau) mau
  FROM (
    SELECT DATE(SEC_TO_TIMESTAMP(created_utc)) day, author
    FROM [fh-bigquery:reddit_comments.2015_09]
    WHERE subreddit='AskReddit') a
  JOIN (
    SELECT stopday, EXACT_COUNT_DISTINCT(author) mau
    FROM (SELECT created_utc, subreddit, author FROM [fh-bigquery:reddit_comments.2015_09], [fh-bigquery:reddit_comments.2015_08]) a
    CROSS JOIN (
      SELECT DATE(SEC_TO_TIMESTAMP(created_utc)) stopday
      FROM [fh-bigquery:reddit_comments.2015_09]
      GROUP BY 1
    ) b
    WHERE subreddit='AskReddit'
    AND SEC_TO_TIMESTAMP(created_utc) BETWEEN DATE_ADD(stopday, -30, 'day') AND TIMESTAMP(stopday)
    GROUP BY 1
  ) b
  ON a.day=b.stopday
  GROUP BY 1
)
ORDER BY 1

此查询获取 9 月每一天的 DAU，并查看 8 月的数据以获取在每个 DAU 日结束的每个 30 天期间的 MAU。这需要大量处理(30 倍)，如果我们只计算 9 月的一个 MAU 并继续使用该值作为分母，我们可以获得几乎相同的结果:

SELECT day, dau, mau, INTEGER(100*dau/mau) daumau
FROM (
  SELECT DATE(SEC_TO_TIMESTAMP(created_utc)) day, EXACT_COUNT_DISTINCT(author) dau, FIRST(mau) mau
  FROM [fh-bigquery:reddit_comments.2015_09] a
  CROSS JOIN (
    SELECT EXACT_COUNT_DISTINCT(author) mau
    FROM [fh-bigquery:reddit_comments.2015_09]
    WHERE subreddit='AskReddit'
  ) b
  WHERE subreddit='AskReddit'
  GROUP BY 1
)
ORDER BY 1

这是一个更简单的查询，可以更快地为我们带来几乎相同的结果。

现在获取该 subreddit 在本月的平均值:

SELECT ROUND(100*AVG(dau/mau), 2) daumau
FROM (
  SELECT DATE(SEC_TO_TIMESTAMP(created_utc)) day, EXACT_COUNT_DISTINCT(author) dau, FIRST(mau) mau
  FROM [fh-bigquery:reddit_comments.2015_09] a
  CROSS JOIN (
    SELECT EXACT_COUNT_DISTINCT(author) mau
    FROM [fh-bigquery:reddit_comments.2015_09]
    WHERE subreddit='AskReddit'
  ) b
  WHERE subreddit='AskReddit'
  GROUP BY 1
)

这告诉我们“AskReddit”在 9 月份的参与度为 8.95%。

最后一站，如何比较不同 subreddits 中的参与度:

SELECT ROUND(100*AVG(dau)/MAX(mau), 2) avg_daumau, MAX(mau) mau, subreddit
FROM (
  SELECT a.subreddit, DATE(SEC_TO_TIMESTAMP(created_utc)) day,
         EXACT_COUNT_DISTINCT(author) dau, FIRST(mau) mau
  FROM [fh-bigquery:reddit_comments.2015_09] a
  JOIN (
    SELECT EXACT_COUNT_DISTINCT(author) mau, subreddit
    FROM [fh-bigquery:reddit_comments.2015_09]
    GROUP BY 2
  ) b
  ON a.subreddit=b.subreddit
  WHERE mau>50000
  GROUP BY 1, 2
)

GROUP BY subreddit
ORDER BY 1