WITH import_events AS (
  SELECT
    *,
    (SELECT p.value.string_value FROM e.event_params AS p WHERE p.key="page_title") AS page_title,
    (SELECT p.value.string_value FROM e.event_params AS p WHERE p.key="page_path") AS page_path,
  FROM
    `<project_id>.analytics_<GA4_ID>.events_*` AS e
),

-- page_path を整形し、ページ内リンクやクエリパラメータ付きの path をまとめた。
-- なお、トップページだけはタブ別（記事一覧、scrap 一覧、コメント一覧）に見られるようにクエリパラメータを残した。
-- ついでに、scrap か article かそれ以外（トップページなど）の区別も取得した。
normalize_page_path AS (
  SELECT
    *,
    COALESCE(REGEXP_EXTRACT(page_path, r"^.*\/tatamiya\/([a-z]+)\/*"), "others") AS page_type,
    REGEXP_EXTRACT(page_path, r"^.*(\/tatamiya\/[a-z]+\/[^#?\/]+|\/tatamiya$|\/tatamiya\?.*)") AS page_path_normalized
  FROM
    import_events
),

-- ページ名の名寄せ。標準化した page_path に対して、ページ名を一意に紐づける。
-- 記事タイトルを変えたりすると、同じ path でも別の記事名になってしまうことがあるため。
-- 標準化した page_path ごとに最も新しいページ名を取ってくることにした
page_title_table AS (
  SELECT
    page_path_normalized,
    MAX_BY(page_title, event_timestamp) AS page_title,
    MIN(event_date) AS publish_date
  FROM normalize_page_path
  GROUP BY page_path_normalized
)

-- metrics の集計
SELECT
  event_date,
  pt.page_title,
  e.page_path_normalized,
  e.page_type,
  publish_date,
  traffic_source.source AS traffic_source,
  COUNTIF(event_name="page_view") AS count_pv,
  COUNTIF(event_name="first_visit") AS count_first_visit,
  COUNT(DISTINCT user_pseudo_id) AS count_uu
FROM
  normalize_page_path AS e
JOIN page_title_table AS pt ON e.page_path_normalized=pt.page_path_normalized
GROUP BY event_date, pt.page_title, e.page_path_normalized, e.page_type, publish_date, traffic_source

WITH
  import_bytes_billed AS (
  SELECT
    DATE(creation_time, "Asia/Tokyo") AS creation_date,
    project_id,
    job_id,
    CASE
      WHEN STARTS_WITH(job_id, "scheduled_query") THEN "scheduled_query"
      WHEN STARTS_WITH(job_id, "job") THEN "job"
      WHEN STARTS_WITH(job_id, "bquxjob") THEN "bq_ux_job"
    ELSE
    "others"
  END
    AS job_id_prefix,
    job_type,
    total_bytes_billed -- 課金対象データ量
  FROM
    `region-us.INFORMATION_SCHEMA.JOBS_BY_USER` AS isch
  WHERE
    DATE(creation_time)>="2024-04-01" -- partition key になっているので、必ず指定する
    )
SELECT
  creation_date,
  job_id_prefix,
  job_type,
  SUM(total_bytes_billed) AS total_bytes_billed,
  SUM(total_bytes_billed) * POW(1024,-4) * 6.25 * 155 AS total_billing_estimated_yen, -- 円換算の課金額。為替レートはとりあえず1ドル155円に設定。
  COUNT(DISTINCT job_id) AS count_query
FROM
  import_bytes_billed
GROUP BY
  creation_date,
  job_id_prefix,
  job_type
ORDER BY
  creation_date,
  job_id_prefix,
  job_type