yigarashiのブログ

学んだことや考えていることを書きます

BigQueryっぽい小技を組み合わせてGA4のデータのページタイトルを最新の値に正規化する

Google Analytics 4（通称GA4）は最近利用できるようになったGoogle Analyticsの新しいプロパティで、全てのデータを簡単にBigQueryにエクスポートできるのが大きな特徴です。私もGA4を使ってこのブログのアクセス解析を行っており、BigQueryでナイスなビューを作成してデータポータルで可視化しています。その中でも「ページごとの閲覧数」が意外と曲者だったので、それを紹介します。

「ページごとの閲覧数」と言ったとき、多くの人が期待するのは、各行にページのタイトルと閲覧数が並んでいるような表だと思います。しかし次のような課題があります。

  • ページのタイトルが不変とは限らない
    • 例えば、はてなブログはブログ名が各記事のタイトルに入るので、ブログ名を変えると全ての記事のタイトルが変わります。他にも「（追記あり）」と付けてみるだとか、タイトルが変わる要因は無数に考えられます
    • 実際GA4レポート画面の「エンゲージメント > ページとスクリーン」はページタイトルで集計されているので、ページタイトルが変わると表示回数などの集計も分かれます
  • ページのURLだけだと表から情報を読み取りづらい
    • タイトルが変わるならURLで集計したら良いと考えるわけですが、素朴にページのURLと閲覧数で表を作ってみると想像以上に見づらい表になります。タイトルだと一瞬で情報を掴めたのに、ひとたびURLに変えると各行をグッと睨んでどんなページだったか 考えないと何も読み取れないのです。不思議ですね

こうした課題を解決するために、以下のようにして正規化したタイトルを取得した上で集計するのが望ましいと考えました。

  • 各URLの最新のタイトルを取得する - (1)
  • ページのURLごとに閲覧数を集計する - (2)
  • (1)と(2)をJOINして最新のタイトルと閲覧数の組にする

ここからは実際にクエリを見ながら解説していきます。

ステップ1: 必要な期間から必要なレコードだけ抜き出す

今回は過去28日のpage_viewイベントがあれば十分なので以下のようにします。

WITH records_of_last_28_days AS (
  SELECT
    event_date,
    (SELECT value.string_value FROM UNNEST(event_params) x WHERE x.key = 'page_location') AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) x WHERE x.key = 'page_title') AS page_title,
    (SELECT value.string_value FROM UNNEST(event_params) x WHERE x.key = 'page_referrer') AS page_referrer
  FROM
    `xxx.yyy.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 29 DAY))
    AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY))
    AND event_name = "page_view"
),

BigQueryっぽい見どころは以下の2つです。

取り組んでいる時の様子です。

ステップ2: 各ページURLの最新のタイトルを取得する

以下のようにします。

location_to_title AS (
  SELECT page_location, page_title FROM (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY page_location ORDER BY event_date DESC) AS rn
    FROM records_of_last_28_days 
  ) WHERE rn = 1
),

まず分析関数のPARTITION BYでpage_location（つまりページのURL）ごとにレコードを分割しそれを新しい順に並べます。ROW_NUMBERは分割したパーティションごとに行数を返す関数です。ここまでやると、WHERE rn = 1でpage_locationごとに最新のpage_titleが入っている行を取得できます。

この手法は数百GBのデータをMySQLからBigQueryへ同期する | メルカリエンジニアリングから着想を得ました。分析関数については公式ドキュメントのこちらのページが詳しいです。

ステップ3: page_locationごとにイベント数を集計してpage_titleとJOINする

以下のようにします。page_locationに加えてpage_referrerでもGROUP BYしてカウントしておくと、データポータルでいい感じにドリルダウンできるのでオススメです。

pv_by_location_and_referrer AS (
  SELECT page_location, page_referrer, COUNT(*) AS pv FROM records_of_last_28_days GROUP BY page_location, page_referrer
) SELECT page_title, page_referrer, pv FROM pv_by_location_and_referrer JOIN location_to_title USING (page_location);

データポータルで便利なデータの形というのはなかなか感覚が掴めておらず難しく感じます。

以上です。自分のブログにGA4のタグを入れて、BigQueryにデータを流しておくだけでおもちゃがひとつ増えるのでオススメです。