閲覧数を取得するために BigQuery + GA4 連携をしたらクエリの料金が想定外に膨らんだのを解決したい

 問題サービスに投稿された作品の閲覧数を表示したい。そのために BigQuery に GA4 をリンクし、BigQuery で閲覧数を取得していた。しかし、毎回クエリのスキャン量が数十 MiB に達しており、すぐに無料枠を食い潰してしまう。
URL /works/:id の閲覧数を得るために下記の感じのクエリで取得している。

これが現在 48 MiB 消費する。
WITH page_views AS (
  SELECT
    work_id,
    COUNT(1) AS page_views_count
  FROM (
    SELECT
      REGEXP_EXTRACT(SPLIT(param.value.string_value, '?')[SAFE_OFFSET(0)], r'/works/([^/]+)') AS work_id
    FROM
      `xxx.analytics_xxx.events_*`,
      UNNEST(event_params) AS param
    WHERE
      event_name = 'page_view'
      AND param.key = 'page_location'
    
    UNION ALL
    
    SELECT
      REGEXP_EXTRACT(SPLIT(param.value.string_value, '?')[SAFE_OFFSET(0)], r'/works/([^/]+)') AS work_id
    FROM
      `xxx.analytics_xxx.events_intraday*`,
      UNNEST(event_params) AS param
    WHERE
      event_name = 'page_view'
      AND param.key = 'page_location'
  )
  GROUP BY
    work_id
)
SELECT * FROM page_views;

 達成したいことと妥協できることほとんど無料で運用したい
作品数数万件くらいまで

全期間の閲覧数と、毎時の閲覧数を多い順に一定件数取得したい
閲覧数はリアルタイムでなくていい

特定の作品の閲覧数を取得したい
閲覧数はある程度リアルタイムに近い速さで反映したい

9sako6

 案Redis などの KV ストアで閲覧数をカウントし、定期的に DB に書き込む
Upstash Redis が安そう

Firestore を使い increment する
1秒に1回以上の書き込みがあると問題が起こる可能性
1日50,000件まで無料で書き込める
毎日バッチ処理で Redis から Firestore に閲覧数を書き込む


なぜ Firestore？

現在 Data store には Firestore, BigQuery を使っている。閲覧数順の検索においては、これらの Data Store に載せると今の仕組みに乗っかって実現できる。

 却下案Google Analytics Data API (REST API) を使う
データの反映に数時間かかることがあるようなので、毎時ランキングを取得するのは不可能
API にレートリミットがあるのが不安

9sako6

 方針Upstash Redis でアクセス数をカウントする。（1日10,000コマンドまで無料、従量課金制なら 100,000コマンドあたり $0.2）
1日1回、バッチ処理で Firestore にアクセス数を永続化する。（5万件まで無料で対応できる。）
全期間のアクセス数が多い順にほしい場合、Firestore から取得する。

毎時のアクセス数が多い順にほしい場合、Upstash Redis から取得する。

特定の作品のアクセス数がほしい場合、Upstash Redis から取得する。

9sako6

BigQuery で直近3日間でコンピュート時間を消費しているクエリを算出するクエリ。

SELECT
  query,
  user_email,
  COUNT(*) AS execution_count,  -- クエリの実行回数
  ROUND(SUM(total_bytes_billed) / POWER(1024, 4), 3) AS total_billed_tera_bytes,  -- 総処理データ量 (TB)
  ROUND(SUM(total_bytes_billed) / COUNT(*) / POWER(1024, 4), 6) AS avg_billed_tera_bytes_per_query  -- 1回あたりの処理データ量 (TB)
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  TIMESTAMP(creation_time) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND cache_hit = FALSE
  AND state = 'DONE'
GROUP BY
  query, user_email
ORDER BY
  total_billed_tera_bytes DESC
LIMIT 10;

このスクラップは2ヶ月前にクローズされました