今回は、月次ページビュー数の前月比を集計するSQLクエリを紹介する。

SQLクエリ概要

ウィンドウ関数（ナビゲーション関数）の LAG を利用して前月の合計PV数を取得し、今月の合計PV数と比較することで前月比 month_over_month_ratio を計算している。

月次PV数の集計方法に関してはこちらの記事でも解説している。

https://www.ga4query.com/blog/ga4bigquery%e3%81%a7%e6%9c%88%e6%ac%a1%e3%81%ae%e3%83%9a%e3%83%bc%e3%82%b8%e3%83%93%e3%83%a5%e3%83%bc%e6%95%b0%e3%82%92%e9%9b%86%e8%a8%88%e3%81%99%e3%82%8b%e3%82%af%e3%82%a8%e3%83%aa/

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

ナビゲーション関数 > LAG関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions?hl=ja#lag

ウィンドウ関数（LAG）

WITH prep AS (
  SELECT
    CONCAT(SUBSTR(event_date, 1, 4), '-', SUBSTR(event_date, 5, 2)) AS month,
    COUNT(*) AS PV
  FROM
    `technogram-354804.analytics_291973511.events_intraday_2022*`
  WHERE
    event_name = 'page_view'
    AND event_date < '20221001'
  GROUP BY
    month
  ORDER BY
    month
)

SELECT
  month,
  PV,
  LAG(PV) OVER(ORDER BY month) AS prev_month_PV,
  ROUND(PV / LAG(PV) OVER(ORDER BY month), 2) AS month_over_month_ratio
FROM
  prep
ORDER BY
  month
;

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになった。