notebook

都内でWEB系エンジニアやってます。

BigQueryの分析関数でGA4の過去7日、30日、合計のPV数を集計する

GoogleCloudPlatform BigQuery

使わないとすぐ忘れるのでメモの意味で残しておく

題材はGA4でExportしたデータを使ってpagepathごとの過去7日間、30日間、合計のPV数を集計するという感じでやってみる

公式の解説

分析関数に関してのドキュメントは次にある

標準 SQL の分析関数のコンセプト  |  BigQuery  |  Google Cloud

cloud.google.com

実際のSQL

WITH
  flattened AS(
  # 日毎pagepath毎のPV数
  # page_locationの値がURLなのでパスの部分のみを抜き出す
  # 日付は日本時間で集計する
  SELECT
    DATE( TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo' ) AS d,
    REGEXP_REPLACE( REGEXP_REPLACE( params.value.string_value, r"https?://swfz.hatenablog.com(.*)", "\\1" ), r"\?.*", "" ) AS pagepath,
    COUNT(params.key) AS pageview
  FROM
    `sample-project-1111111.analytics_123456789.events_*`,
    UNNEST(event_params) AS params
  WHERE
    event_name = 'page_view'
    AND params.key = 'page_location'
  GROUP BY
    pagepath,
    d ),
  # 後の集計で最新の日時のレコードが無いと困るので一律でレコードを追加する
  # 前日分のデータが最新となるので実行時の日付をpagepathの数だけ追加する
  # 当日分のデータも含める場合はレコードがある場合はそっち、ない場合は追加みたいな感じの書き方にする必要がある
  combined AS ( (
    SELECT
      CURRENT_DATE() AS d,
      pagepath,
      0 AS pageview
    FROM
      flattened
    GROUP BY
      pagepath )
  UNION ALL (
    SELECT
      *
    FROM
      flattened ) )
# 分析関数で過去7日間、過去30日間のPV数をpagepath毎に取得する
SELECT
  d,
  pagepath,
  pageview,
  d >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AS is_last7day,
  d >= DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY) AS is_last30day,
  ROW_NUMBER() OVER(PARTITION BY pagepath ORDER BY d DESC ) AS rn,
  SUM(pageview) OVER(PARTITION BY pagepath, d >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY)
  ORDER BY
    d ) AS last7days_pv,
  SUM(pageview) OVER(PARTITION BY pagepath, d >= DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)
  ORDER BY
    d ) AS last30days_pv,
  SUM(pageview) OVER(PARTITION BY pagepath) AS total_pv
FROM
  combined
WHERE
  TRUE QUALIFY rn = 1

f:id:swfz:20211009222651p:plain

結果はこんな感じ

分析関数の部分のメモ書き

下準備の部分は後で見返したときに思い出せるようにコメントで補足した

分析関数を使っている部分に関して

SELECT
  d,
  pagepath,
  pageview,
  d >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AS is_last7day,
  d >= DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY) AS is_last30day,
  ROW_NUMBER() OVER(PARTITION BY pagepath ORDER BY d DESC ) AS rn,
  SUM(pageview) OVER(PARTITION BY pagepath, d >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY)
  ORDER BY
    d ) AS last7days_pv,
  SUM(pageview) OVER(PARTITION BY pagepath, d >= DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)
  ORDER BY
    d ) AS last30days_pv,
  SUM(pageview) OVER(PARTITION BY pagepath) AS total_pv
FROM
  combined
WHERE
  TRUE QUALIFY rn = 1

QUALIFY rn = 1

でrow numberが1のレコードのみを絞っている

この部分をコメントアウトすると理解しやすい

適当なデータを用意して試してみる

現在日時は2021-10-09

WITH sample AS(
  SELECT * FROM UNNEST(ARRAY<STRUCT<d DATe, pagepath STRING, pageview INT64>>
    [
      ("2021-08-01", "/hoge", 1),
      ("2021-09-20", "/hoge", 1),
      ("2021-10-07", "/hoge", 1),
      ("2021-09-30", "/fuga", 1),
      ("2021-10-03", "/fuga", 1),
      ("2021-10-04", "/fuga", 1)
    ]
  )
)
SELECT
  d,
  pagepath,
  pageview,
  d >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AS is_last7day,
  d >= DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY) AS is_last30day,
  ROW_NUMBER() OVER(PARTITION BY pagepath ORDER BY d DESC ) AS rn,
  SUM(pageview) OVER(PARTITION BY pagepath, d >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) ORDER BY d ) AS last7days_pv,
  SUM(pageview) OVER(PARTITION BY pagepath, d >= DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY) ORDER BY d ) AS last30days_pv,
  SUM(pageview) OVER(PARTITION BY pagepath) AS total_pv
FROM
  sample
  • 結果

f:id:swfz:20211009222658p:plain

  • rn

    • PARTITIONを用いてpagepath毎で区切りORDER BYを用いて日付で降順の行番号を出す

  • is_last7day,is_last30day

    • dが過去7,30日以内かどうかの真偽値

  • last7days_pv,last30days_pv

    • pageviewのSUMをpagepathと過去7日以内かどうかの真偽値d >= DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY→区切り日付で昇順に並べて集計する
    • is_last7dayが出現するたびに加算されていく感じ

  • total_pv

    • pageviewのSUMをpagepathごとに区切り集計する

OVERとORDERを調整してrn = 1の行にほしい計算結果が出力されるようにする

で最終的な集計結果をQUALIFY rn = 1で絞るとpagepath毎に過去7日、30日、総計のPV数を算出できる

感想

  • 以前分析関数を調べながらSQL書いたが、あとで読んでみたら理解するまで結構時間かかったので今回は残すのと復習も兼ねてメモしたので次使うときは大丈夫なはず
  • 日毎の移動平均の算出なども同様な感じで割とサクッと算出できそう
  • 下準備が割と面倒（日付×pagepathのデータを出す）でPVがないレコードも埋めたりするとかそういうのが発生してくるともっと面倒な感じになる（今回はやっていない）

他にも良い書き方などありそうだなと思うのでご指摘があれば教えていただけると嬉しいです