dataform-google-analytics-4-example/definitions/warehouse/dwh__google_analytics_pageview_events.sqlx at main · terashim/dataform-google-analytics-4-example · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
config {
  // 更新処理の最適化のためインクリメンタルなデータセットとする
  type: "incremental",
  // BigQuery 固有の設定
  bigquery: {
    // このテーブルに対するクエリの最適化に利用するためパーティショニングを設定する
    partitionBy: "event_date",
    // 既存データと差分データの重複を判定するためにスキャンされるパーティションを絞り込む条件を指定する
    updatePartitionFilter: "event_date >= max_event_date"
  },
  // データの重複を防ぐためユニークキーを指定する
  uniqueKey: [
    "event_timestamp",
    "event_name",
    "user_id",
    "user_pseudo_id"
  ],
  // テーブルの説明文
  description: "Google アナリティクスのページビューイベント",
  // フィールドの説明文
  columns: {
    event_date: "イベントが記録された日付（アプリの登録タイムゾーンにおける日付）。",
    event_timestamp: "該当クライアントでイベントが記録された時刻（マイクロ秒単位、UTC）。",
    event_name: "イベントの名前。",
    user_id: "setUserId API によって設定されるユーザー ID。",
    user_pseudo_id: "ユーザーの仮の ID（アプリ インスタンス ID など）。",
    ga_session_id: "セッションID",
    page_location: "ページURL",
    page_title: "ページタイトル",
    page_referrer: "リファラー",
    traffic_source: "ユーザーを最初に獲得したマーケティング キャンペーンの名前。このフィールドは当日表では使用されません。",
    source: "ユーザーを最初に獲得したメディアの名前（有料検索、オーガニック検索、メールなど）。このフィールドは当日表では使用されません。",
    medium: "ユーザーを最初に獲得したネットワークの名前。このフィールドは当日表では使用されません。",
    device_category: "デバイスのカテゴリ（モバイル、タブレット、PC）。",
    mobile_device_branding: "デバイスのブランド名。",
    mobile_device_model: "デバイスのモデル名。",
    mobile_device_marketing_name: "デバイスのマーケティング名。",
    mobile_device_info: "オペレーティング システムから直接取得したデバイスのモデル情報。",
    operating_system: "デバイスのオペレーティング システム。",
    operating_system_version: "OS のバージョン。",
    browser: "ユーザーがコンテンツを閲覧したブラウザ。",
    browser_version: "ユーザーがコンテンツを閲覧したブラウザのバージョン。",
    language: "OS の言語。",
    continent: "イベントが報告された大陸（IP アドレスベース）。",
    sub_continent: "イベントが報告された亜大陸（IP アドレスベース）。",
    country: "イベントが報告された国（IP アドレスベース）。",
    region: "イベントが報告された地域（IP アドレスベース）。",
    city: "イベントが報告された都市（IP アドレスベース）。",
    metro: "イベントが報告された大都市圏（IP アドレスベース）。"
  }
}

pre_operations {
  -- 差分データの日付範囲を決定するため, 既存データの最新日付を取得する
  DECLARE max_event_date DEFAULT (
    ${when(incremental(),
    `SELECT IFNULL(MAX(event_date), DATE("2000-01-01")) FROM ${self()}`,
    `DATE("2000-01-01")`)}
  );
}

-- 全イベントを含むソーステーブルからページビューイベントのみ抽出し、データを整形するクエリ
SELECT
  event_date,
  event_timestamp,
  event_name,
  IFNULL(user_id, '') AS user_id,
  IFNULL(user_pseudo_id, '') AS user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer,
  traffic_source.name AS traffic_source,
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  device.category AS device_category,
  device.mobile_brand_name AS mobile_device_branding,
  device.mobile_model_name AS mobile_device_model,
  device.mobile_marketing_name AS mobile_device_marketing_name,
  device.mobile_os_hardware_model AS mobile_device_info,
  device.operating_system AS operating_system,
  device.operating_system_version AS operating_system_version,
  device.web_info.browser AS browser,
  device.web_info.browser_version AS browser_version,
  device.language AS language,
  geo.continent AS continent,
  geo.sub_continent AS sub_continent,
  geo.country AS country,
  geo.region AS region,
  geo.city AS city,
  geo.metro AS metro
FROM
  ${ref("stg__google_analytics_events")}
WHERE
  event_timestamp IS NOT NULL
  -- ページビューイベントのみを抽出する
  AND event_name = 'page_view'
  -- 差分データのサイズを抑えるため, 日付範囲を限定する
  AND wildcard_table_suffix >= FORMAT_DATE("%Y%m%d", max_event_date)