今回は、ユーザーのアクティブ頻度とその構成比を集計する方法を紹介する。

SQLクエリの概要

まず対象となる期間において、各ユーザーID user_pseudo_id ごとに event_date のユニーク数を集計することで、アクティブ日数 active_day_count を集計している（※アクティブ = 「何かしらのアクションログが発生している」と定義）。

そして過去のアクティブ日数active_day_count ごとにユーザーID user_pseudo_id をユニークカウントして、各アクティブ日数ごとのユーザー数を集計している。

最後にウィンドウ関数 SUM を用いて構成比を計算、さらに ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW でウィンドウ関数の集計範囲を「先頭行から現在行まで」とすることで構成比の累計を導出している。

■ BigQuery公式ドキュメント参考

ウィンドウ関数の呼び出し > 総計を計算する

https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls?hl=ja#compute_a_grand_total

ウィンドウ関数の呼び出し > 累計を計算する

https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls?hl=ja#compute_a_cumulative_sum

ユーザーアクティブ頻度の構成比累計を算出するSQL

WITH prep AS (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT event_date) AS active_day_count
  FROM
    `technogram-354804.analytics_291973511.events_intraday_2022*`  
  GROUP BY
    user_pseudo_id
)

SELECT
  active_day_count,
  COUNT(DISTINCT user_pseudo_id) AS user_count,
  ROUND(COUNT(DISTINCT user_pseudo_id) / SUM(COUNT(DISTINCT user_pseudo_id)) OVER() * 100, 2)  AS percent,
  ROUND(SUM(COUNT(DISTINCT user_pseudo_id)) OVER(ORDER BY active_day_count ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 
    SUM(COUNT(DISTINCT user_pseudo_id)) OVER() * 100, 2) AS cum_percent
FROM
  prep
GROUP BY
  active_day_count
ORDER BY
  active_day_count
;

BigQueryでの集計結果