NK University

一つ上へ

CREATE OR REPLACE TABLE `project.analytics_123456789.export_table` AS
SELECT
  event_timestamp, -- アクセスタイムスタンプ
  DATETIME(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS event_date, -- アクセス日時
  event_name, -- イベント名
  device.category AS device_category, -- デバイスカテゴリ
  device.operating_system AS device_operating_system, -- デバイスOS
  device.language AS device_language, -- デバイス言語
  device.web_info.browser AS device_web_info_browser, -- ブラウザ
 
  (SELECT VALUE.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location') AS page_location, -- ページURL
  (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 = 'campaign') AS inflow_campaign, -- 流入キャンペーン
  traffic_source.source AS inflow_source, -- 流入ソース
  traffic_source.medium AS inflow_medium, -- 流入メディア
 
  -- 地域情報
  geo.continent AS continent, -- 大陸
  geo.country AS country, -- 国
  geo.region AS region, -- 地域
  geo.metro AS metro, -- 大都市圏
  geo.city AS city, -- 都市
 
  user_pseudo_id AS user_id, -- ユーザーID(google 発行)
  CONCAT(
    (SELECT VALUE.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id')
    ,user_pseudo_id
  ) AS session_id, -- ユーザーIDとセッションIDの結合したものを一意なセッションIDとして定義
 
FROM `project.analytics_12345678.events_*`
WHERE event_name IN ('first_visit', 'session_start', 'page_view','purchase') -- 新規アクセス、セッション開始イベント、ページビューに絞る
 
ORDER BY
  event_date