GA4

[GA4 SQL] ディレクトリ配下のURLのみBigQueryから抽出

2025.01.10

Looker StudioなどのBIツールでディメンションにURLを指定する際に、ドメイン名は固定値などでなくてもよく、また、プロトコル・ホスト・ドメインがあることでURL名称が長くなりコントロールボタンから肝心なディレクトリが確認できないため、これら不要な要素を取り除いた列を追加してテーブルを作成する必要が案外実務では必須であったりする。また、パラメータも不要なので合わせて除外する。

ディレクトリ配下のURLのみ抽出

https://sem-report.com/blog/ga4/3433/?~
↓
/blog/ga4/3433/

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');

WITH
  processed_url AS (
    SELECT
      page_location,
      REGEXP_REPLACE(
        REGEXP_REPLACE(
          page_location,r'^https?://[^/]+', ""
        ), r"\?.*", ""
      ) AS processed_url
    FROM (
      SELECT
        DISTINCT (
          REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) p WHERE p.key = 'page_location'), r"\?.*", "")
        ) AS page_location
      FROM
        `<project>.<dataset>.events_*`
      WHERE
          _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    )
  )

SELECT
  *
FROM
  processed_url