GA4でBigQueryを学ぶ

数値型・ブール型・文字列型・バイト型
時間に関する型: 日付型・日時型・時刻型・タイムスタンプ型
構造に関する型: 配列型・構造体型
ワイルドカードを使ったテーブルアクセス: TABLESUFFIXを使う
ワイルドカードを使ったテーブルアクセス: WITH句内のワイルドカードのTABLESUFFIXを使う
UNIONを使って、擬似テーブルを作成する
UNNESTとSPLITを使って、カンマ区切りの文字列から擬似テーブルを作成する
UNNESTとARRAYを使って、擬似テーブルを作成する
UNNESTとGENERATE_ARRAYを使って、擬似テーブルを作成する
UNNESTとGENERATEDATEARRAYを使って、擬似テーブルを作成する
GROUP BY句をつかった、合計・集計・条件付き集計・最大・最小・平均
GROUP BY句をつかった、データの集約方法
ARRAYCONCSTAGGを使って配列を結合する
ビット演算　
論理演算
GROUP BY句とROLLUPをつかった、総計・小計を集計する。
数値を区切ってGROUP BYする
トラフィックソース情報: traffic_source
デバイス情報: device
位置情報: geo
アプリ情報: app_info
プライバシー情報: privacy_info
ライフタイムバリュー: user_ltv
eコマースに関する情報: ecommerce
///: event_dimensions
event_timestampを日付・時間に変換する
日付・時間型を任意の部分で切り詰める。
event_timestampを任意の日時文字列に変換する
event_dateをDATE型に変換する
月初と月末の作り方
TIMESTAMP,DATETIME,DATE,TIMEの加算・減算
TIMESTAMP,DATETIME,DATE,TIMEの差分をとる。
Bigquery:DECLARE・SETを使用して変数を使う
URLを使いやすく分解するUDF
日付文字列を操作するUDF
PIVOTを使った縦から横への変換
UNPIVOTを使った横から縦への変換
JSON文字列をカラムに入れる方法
JSON文字列から値を取り出す。

Bigqueryのデータ型の話

BigQueryには、さまざまな型がある。データ型 | BigQuery | Google Cloud

数値型・ブール型・文字列型・バイト型

よく使われる基本的な型。

数値型: INT64 / NUMERIC / BIGNUMERIC / FLOAT64
ブール型: BOOL
文字列型: STRING
バイト型: BYTES

時間に関する型: 日付型・日時型・時刻型・タイムスタンプ型

日付型: DATE
日時型: DATETIME
時刻型: TIME
タイムスタンプ型: TIMESTAMP

構造に関する型: 配列型・構造体型

配列型: ARRAY
構造体型: STRUCT
地理型: GEOGRAPHY

GA4イベントの種類について

GA4は、大きく3つのイベントの種類があります。

自動的に収集されるイベント: SDK/gtag.jsを導入した時点で収集が開始されるイベント群
- [GA4] 自動的に収集されるイベント - アナリティクスヘルプ
測定機能の強化イベント: オプションとして用意されていて、Google アナリティクス管理画面で有効無効を切り替えることができます。変更時の追加実装は必要ありません。
- [GA4] 測定機能の強化イベント - アナリティクスヘルプ
推奨イベント: ユーザーの「任意の状態」・「任意の行動時」に送信するイベント群。自動的に送信されることはなく、随時実装が必要です。
- 自身サービスに合わせて実装するかどうかを判断する必要があります。
- [GA4] 推奨イベント - アナリティクスヘルプ

クエリのデータ量を減らす。

BigQueryは、クエリで処理されるデータ量によって課金が発生します。そのため、下記の点に留意してクエリを描く必要があります。

必要な列を指定して列方向のデータ量を制限する。何も考えずに*を使ってはいけない！
パーティションテーブルを利用して、行方向のデータ量を制限する。
パーティション分割テーブルの概要 | BigQuery | Google Cloud
テーブルを分割して作成し、TABLESUFFIXを使って任意のテーブルにアクセスする。

ワイルドカードを使ったテーブルアクセス: TABLESUFFIXを使う

GA4のデータセットは、下記の二つのテーブルで構成されています。

events_YYYYMMDD: 日付別でデータがインポートされるテーブルです。
eventsintradayYYYYMMDD: 当日のデータがリアルタイムで追加されていくテーブルです。

そのため、2021/01/01~2021/12/31は下記のようなテーブルが作成される。

events_20210101
events_20210102
events_20210103
events_20210104
.....
events_20211228
events_20211229
events_20211230
events_20211231

上記のような、規則性のあるテーブルから任意のテーブル(GAの場合は期間)を指定してテーブルへアクセスするには、ワイルドカードを使ったテーブルアクセスが便利です。

例えば、2021/01の期間のデータだけが必要な場合は下記のように、TABLESUFFIXを利用してテーブルを絞り込むことが可能です。

xxx.yyy.events_でテーブルの一部を指定する。
*は、TABLESUFFIXを使って指定する。

SELECT
  event_name,
  user_id,
  event_timestamp
FROM
  `xxx.yyy.events_*`
WHERE
  BETWEEN _TABLE_SUFFIX = '20210101' AND _TABLE_SUFFIX = '20210131'

ワイルドカードを使ったテーブルアクセス: WITH句内のワイルドカードのTABLESUFFIXを使う

WITH句の中でワイルドカードを使ったテーブルアクセスを行いたいときは、下記のようにしてTABLESUFFIXをWITH句の外に出してあげる必要があります。

WITH句内のSELECT句でTABLESUFFIXを選択する。
TABLESUFFIXに別名をつける
WITH句の外でTABLESUFFIXに条件を設定する

WITH origin AS (
  SELECT
    event_name,
    user_id,
    event_timestamp,
    _TABLE_SUFFIX AS table_suffix
  FROM
    `xxx.yyy.events_*`
)
SELECT
  *
FROM
  origin
WHERE
  BETWEEN table_suffix = '20210101' AND table_suffix = '20210131'

WITH句をつかって、擬似テーブルを作成する

データが少なく、テーブルを作る必要はないが、クエリ内でテーブルとして使いたい時に色々な方法でテーブルを作成することができます。

UNIONを使って、擬似テーブルを作成する

一番簡単に思いつく方法です。一行づつ全てのカラムをASで値を指定します。

WITH origin AS (
    SELECT 1 AS index, "aaaa" AS event_name
    UNION ALL
    SELECT 2 AS index, "bbbb" AS event_name
    UNION ALL
    SELECT 3 AS index, "cccc" AS event_name
)
SELECT
    *
FROM
    origin

UNNESTとSPLITを使って、カンマ区切りの文字列から擬似テーブルを作成する

WITH origin AS (
    SELECT
        ROW_NUMBER() OVER() AS index,
        event_name
    FROM
        UNNEST(SPLIT("aaaa,bbbb,cccc", ",")) AS event_name
)
SELECT
    *
FROM
    origin

UNNESTとARRAYを使って、擬似テーブルを作成する

WITH origin AS (
    SELECT
        *
    FROM
        UNNEST([
            STRUCT(1 AS index, 'aaaa' AS event_name),
            STRUCT(2 AS index, 'bbbb' AS event_name),
            STRUCT(3 AS index, 'cccc' AS event_name)
        ])
)
SELECT
    *
FROM
    origin

UNNESTとGENERATE_ARRAYを使って、擬似テーブルを作成する

WITH origin AS (
    SELECT
        *
    FROM
        UNNEST(GENERATE_ARRAY(1, 3, 1)) AS index
)
SELECT
    *
FROM
    origin

UNNESTとGENERATEDATEARRAYを使って、擬似テーブルを作成する

WITH origin AS (
    SELECT
        ROW_NUMBER() OVER() AS index,
        *
    FROM
        UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2021-01-10', INTERVAL 1 DAY)) AS date
)
SELECT
    *
FROM
    origin

RANDを使ってランダムなデータを作り出す。

クエリを試してみたいときに使うランダムなデータを用意する方法を考えました。

CREATE TEMP FUNCTION rand_range(min_value INT64, max_value INT64) AS (
    CAST(ROUND(min_value + RAND() * (max_value - min_value)) AS INT64)
);
CREATE TEMP FUNCTION rand_date(min_date DATE, max_date DATE) AS (
    TIMESTAMP_SECONDS(
        CAST(
            ROUND(UNIX_SECONDS(CAST(min_date AS TIMESTAMP)) + RAND() * (UNIX_SECONDS(CAST(max_date AS TIMESTAMP)) - UNIX_SECONDS(CAST(min_date AS TIMESTAMP))))
        AS INT64)
    )
);
WITH users AS (
    SELECT
        id,
        rand_range(0, 1) AS gender,
        rand_range(1, 47) AS prefecture_code
    FROM UNNEST(GENERATE_ARRAY(1, 10)) AS id
),purchases AS  (
    SELECT
        id,
        rand_range(1, 10) AS user_id,
        rand_range(1000, 5000) AS price,
        rand_range(1, 10) AS quantity,
        DATE(rand_date("2021-01-01", "2021-12-31"), "Asia/Tokyo") AS purchase_at,
    FROM UNNEST(GENERATE_ARRAY(1, 500)) AS id
), test_data AS (
    SELECT
        *
    FROM users
    INNER JOIN purchases ON users.id = purchases.user_id
)
SELECT * FROM test_data

GROUP BY句をつかって、グループ別で集計する。

GROUP BYはデータを、指定したカラムが同じ値のグループに分けることができます。例えば、「男女で分けて、****したい」と言ったような時に使います。 GROUP BYを使った後のSELECT句では、下記のようなカラムしか選択することはできません。

GROUP BYで指定したカラム
GROUP BYで指定しなかったカラムに集計関数を適用する

GROUP BY句をつかった、合計・集計・条件付き集計・最大・最小・平均

AVG: 平均値を算出します。
MAX: 最大値を算出します。
MIN: 最小値を算出します。
SUM: 合計値を算出します。
COUNT: 行数を算出します。
COUNTIF: 条件式を満たした行数を算出します。

CREATE TEMP FUNCTION rand_range(min_value INT64, max_value INT64) AS (
    CAST(ROUND(min_value + RAND() * (max_value - min_value)) AS INT64)
);
CREATE TEMP FUNCTION rand_date(min_date DATE, max_date DATE) AS (
    TIMESTAMP_SECONDS(
        CAST(
            ROUND(UNIX_SECONDS(CAST(min_date AS TIMESTAMP)) + RAND() * (UNIX_SECONDS(CAST(max_date AS TIMESTAMP)) - UNIX_SECONDS(CAST(min_date AS TIMESTAMP))))
        AS INT64)
    )
);
WITH users AS (
    SELECT
        id,
        rand_range(0, 1) AS gender,
        rand_range(1, 47) AS prefecture_code
    FROM UNNEST(GENERATE_ARRAY(1, 10)) AS id
),purchases AS  (
    SELECT
        id,
        rand_range(1, 10) AS user_id,
        rand_range(1000, 5000) AS price,
        rand_range(1, 10) AS quantity,
        DATE(rand_date("2021-01-01", "2021-12-31"), "Asia/Tokyo") AS purchase_at,
    FROM UNNEST(GENERATE_ARRAY(1, 500)) AS id
), test_data AS (
    SELECT
        *
    FROM users
    INNER JOIN purchases ON users.id = purchases.user_id
)
SELECT
    user_id,
    COUNT(1) AS number_of_purchases,
    SUM(price) AS total_purchase_price,
    MAX(price) AS maximum_purchase_price,
    MIN(price) AS minimum_purchase_price,
    AVG(price) AS average_purchase_price,
    COUNTIF(price > 2000) AS number_of_purchases_of_2000_more
FROM test_data GROUP BY user_id

GROUP BY句をつかった、データの集約方法

ANY_VALUE: データの中から一つ値を取り出します。
ARRAY_AGG: データを配列として集約します。
STRING_AGG: データを文字列として集約します。第二引数を指定することで、区切り文字を変更することができます。下記のクエリは、性別でデータを分割し、できたグループのユーザーIDを配列化したり、文字列化したりしています。また、購入額が高い順に3人を取り出しています。

CREATE TEMP FUNCTION rand_range(min_value INT64, max_value INT64) AS (
    CAST(ROUND(min_value + RAND() * (max_value - min_value)) AS INT64)
);
CREATE TEMP FUNCTION rand_date(min_date DATE, max_date DATE) AS (
    TIMESTAMP_SECONDS(
        CAST(
            ROUND(UNIX_SECONDS(CAST(min_date AS TIMESTAMP)) + RAND() * (UNIX_SECONDS(CAST(max_date AS TIMESTAMP)) - UNIX_SECONDS(CAST(min_date AS TIMESTAMP))))
        AS INT64)
    )
);
WITH users AS (
    SELECT
        id,
        rand_range(0, 1) AS gender,
        rand_range(1, 47) AS prefecture_code
    FROM UNNEST(GENERATE_ARRAY(1, 10)) AS id
),purchases AS  (
    SELECT
        id,
        rand_range(1, 10) AS user_id,
        rand_range(1000, 5000) AS price,
        rand_range(1, 10) AS quantity,
        DATE(rand_date("2021-01-01", "2021-12-31"), "Asia/Tokyo") AS purchase_at,
    FROM UNNEST(GENERATE_ARRAY(1, 500)) AS id
), test_data AS (
    SELECT
        *
    FROM users
    INNER JOIN purchases ON users.id = purchases.user_id
)
SELECT
    gender,
    ANY_VALUE(user_id) AS any_user_id,
    ARRAY_AGG(DISTINCT user_id) AS array_of_user_id,
    STRING_AGG(DISTINCT CAST(user_id AS STRING), "-") AS string_of_user_id,
    ARRAY_AGG(STRUCT(user_id, price) ORDER BY price DESC LIMIT 3) AS top_three
FROM test_data GROUP BY gender

ARRAYCONCSTAGGを使って配列を結合する

SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
    SELECT ["a", "b", "c"] AS x
    UNION ALL
    SELECT ["x", "y", "z"]
)

ビット演算　

// WRITE ME!

BIT_AND
BIT_OR
BIT_XOR

論理演算

// WRITE ME!

LOGICAL_AND
LOGICAL_OR

GROUP BY句とROLLUPをつかった、総計・小計を集計する。

ROLLUPを使うと、GROUP BYでグループに対して集計すると同時に、グループ化前の状態に対しても集計関数を適用することが可能です。性別(0~1)と都道府県(1~47)を持つデータに対して、ROLLUPを適用します。 ROLLUPは、指定するカラムの順序で処理が変化します。

ROLLUP(性別, 都道府県)

すると、下記のようなグループに対して集計が行われます。

性別をグループ化・都道府県をグループ化
性別をグループ化・都道府県はグループ化無し
性別グループ化無し・都道府県はグループ化無し

ROLLUP(都道府県, 性別)

すると、下記のようなグループに対して集計が行われます。

都道府県をグループ化・性別をグループ化
都道府県をグループ化・性別グループ化無し
都道府県はグループ化無し・性別グループ化無し

CREATE TEMP FUNCTION rand_range(min_value INT64, max_value INT64) AS (
    CAST(ROUND(min_value + RAND() * (max_value - min_value)) AS INT64)
);
CREATE TEMP FUNCTION rand_date(min_date DATE, max_date DATE) AS (
    TIMESTAMP_SECONDS(
        CAST(
            ROUND(UNIX_SECONDS(CAST(min_date AS TIMESTAMP)) + RAND() * (UNIX_SECONDS(CAST(max_date AS TIMESTAMP)) - UNIX_SECONDS(CAST(min_date AS TIMESTAMP))))
        AS INT64)
    )
);
WITH users AS (
    SELECT
        id,
        rand_range(0, 1) AS gender,
        rand_range(1, 47) AS prefecture_code
    FROM UNNEST(GENERATE_ARRAY(1, 10)) AS id
),purchases AS  (
    SELECT
        id,
        rand_range(1, 10) AS user_id,
        rand_range(1000, 5000) AS price,
        rand_range(1, 10) AS quantity,
        DATE(rand_date("2021-01-01", "2021-12-31"), "Asia/Tokyo") AS purchase_at,
    FROM UNNEST(GENERATE_ARRAY(1, 500)) AS id
), test_data AS (
    SELECT
        users.*,
        purchases.* EXCEPT(id)
    FROM users
    INNER JOIN purchases ON users.id = purchases.user_id
)
SELECT
    gender,
    prefecture_code,
    SUM(price) AS total_of_price,
    SUM(quantity)  AS total_of_quantity
FROM test_data
GROUP BY ROLLUP(gender, prefecture_code)
ORDER BY gender, prefecture_code

数値を区切ってGROUP BYする

// WRITE ME!

ユーザー数、セッション数、ページビュー数を出す。

「サービスを利用したユーザーは何人か？」
「セッション数は？」
「閲覧されたページビューは何人か？」

知るためには、下記のカラムを利用します。

user_id: setUserId APIによって設定されるユーザーID。ログイン時にセットすることで、デバイスを超えて同じIDになります。
userpseudoid: ユーザーの仮のID（アプリインスタンスIDなど）。
platform: アプリケーションが構築されているプラットフォーム。IOS/ANDROID/WEBの値が入っています。
event_timestamp: 該当クライアントでイベントが記録された時刻（ミリ秒単位、UTC）。
sessionstart#gasession_id: セッションを識別するID

各数値を下記のように定義します。

ユーザー数: sessionstartを行ったuserpseudo_idの数
セッション数: sessionstartを行った、userpseudoidとgasession_idを結合した文字列の数

ページビュー数: プラットフォームで集計方法が異なる

WEB: page_viewイベントのイベント数

IOS/ANDROID: screen_viewイベントのイベント数

WITH origin AS (
SELECT
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
user_pseudo_id,
platform,
CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING) AS ga_session_id,
event_name
FROM `xxx.yyy.events_*`
WHERE
_TABLE_SUFFIX >= '2021101'
AND (
event_name = 'session_start'
OR
event_name = 'page_view'
OR
event_name = 'screen_view'
)
), pageviews AS (
SELECT
hitdate,
platform,
SUM(CASE
WHEN platform = "WEB" AND event_name = 'page_view' THEN 1
WHEN (platform = "IOS" OR platform = "ANDROID") AND event_name = 'screen_view' THEN 1
ELSE 0
END
) AS pageviews
FROM origin
WHERE event_name = 'page_view' OR event_name = 'screen_view'
GROUP BY hitdate, platform
), sessions AS (
SELECT
hitdate,
platform,
COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS sessions
FROM origin
WHERE event_name = 'session_start'
GROUP BY hitdate, platform
), users AS (
SELECT
hitdate,
platform,
COUNT(DISTINCT user_pseudo_id) AS users
FROM origin
WHERE event_name = 'session_start'
GROUP BY hitdate, platform
)
SELECT
u.hitdate,
u.platform,
u.users,
s.sessions,
p.pageviews
FROM users AS u
LEFT JOIN sessions AS s ON u.hitdate = s.hitdate
LEFT JOIN pageviews AS p ON u.hitdate = p.hitdate

GA4の中で、STRUCT型で定義されているデータにアクセスする

GA4の中で、STRUCT型で定義されているデータは下記がある。

traffic_source
device
geo
app_info
privacy_info
user_ltv
event_dimensions
ecommerce

トラフィックソース情報: traffic_source

traffic_sourceは流入元の情報が格納されています。この情報はトリビューションは、クロスチャネルのラストクリックに基づいています。また、値はユーザーがインストール後に次のキャンペーンを操作しても変更されません。

name: ユーザーを最初に獲得したマーケティングキャンペーンの名前。
medium: ユーザーを最初に獲得したメディアの名前（有料検索、オーガニック検索、メールなど）
source: ユーザーを最初に獲得したネットワークの名前。

// TODO イベント数で見るには、どのイベントを見ればいいのか？

WITH origin AS (
  SELECT
    DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
    traffic_source.name AS name,
    traffic_source.medium AS medium,
    traffic_source.source AS source,
    user_pseudo_id
  FROM `xxx.yyy.events_*`
  WHERE _TABLE_SUFFIX > "20211101"
)
SELECT
    hitdate,
    name,
    medium,
    source,
    COUNT(1) AS number_of_events,
    COUNT(DISTINCT user_pseudo_id) AS number_of_users
FROM origin
GROUP BY hitdate, name, medium,source

デバイス情報: device

deviceには、ログを送信したデバイスの情報が含まれています。

category: デバイスのカテゴリ（モバイル、タブレット、PC）。
mobilebrandname: デバイスのブランド名。
mobilemodelname: デバイスのモデル名。
mobilemarketingname: デバイスのマーケティング名。
mobileoshardware_model: オペレーティングシステムから直接取得したデバイスのモデル情報。
operating_system: デバイスのオペレーティングシステム。
operatingsystemversion: OSのバージョン。
language: OSの言語。
timezoneoffset_seconds: GMTとの時差（秒単位）。
web_info.browser: ユーザーがコンテンツを閲覧したブラウザ。
webinfo.browserversion: ユーザーがコンテンツを閲覧したブラウザのバージョン。
web_info.hostname: ログに記録されたイベントに関連付けられたホスト名。
vendor_id: IDFV（IDFAを収集していない場合にのみ使用）。
advertising_id: 広告IDまたはIDFA。
islimitedad_tracking: デバイスの広告トラッキング制限の設定。iOS14 以降では、IDFAがゼロ以外の場合、falseが返されます。

例:ブラウザの種類別

WITH origin AS (
  SELECT
    DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
    device,
    user_pseudo_id
  FROM `xxxx.yyyy.events_*`
  WHERE _TABLE_SUFFIX > "20211101"
)
SELECT
    hitdate,
    device.web_info.browser,
    COUNT(1) AS number_of_events,
    COUNT(DISTINCT user_pseudo_id) AS number_of_users
FROM origin
GROUP BY hitdate, device.web_info.browser

例:言語設定別

WITH origin AS (
  SELECT
    DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
    device,
    user_pseudo_id
  FROM `xxxx.yyyy.events_*`
  WHERE _TABLE_SUFFIX > "20211101"
)
SELECT
    hitdate,
    device.language,
    COUNT(1) AS number_of_events,
    COUNT(DISTINCT user_pseudo_id) AS number_of_users
FROM origin
GROUP BY hitdate, device.language

位置情報: geo

geoには、IPアドレスから推測された位置情報が格納されています。

continent: イベントが報告された大陸
sub_continent: イベントが報告された亜大陸
country: イベントが報告された国
region: イベントが報告された地域
metro: イベントが報告された大都市圏
city: イベントが報告された都市

WITH origin AS (
  SELECT
    DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
    geo,
    user_pseudo_id
  FROM `xxxx.yyyy.events_*`
  WHERE _TABLE_SUFFIX > "20211101"
)
-- SELECT * FROM origin
SELECT
    hitdate,
    geo.continent,
    geo.sub_continent,
    geo.country,
    geo.region,
    geo.metro,
    geo.city,
    COUNT(1) AS number_of_events,
    COUNT(DISTINCT user_pseudo_id) AS number_of_users
FROM origin
GROUP BY hitdate, geo.continent, geo.sub_continent, geo.country, geo.region, geo.metro, geo.city
ORDER BY hitdate DESC, geo.continent, geo.sub_continent, geo.country, geo.region, geo.metro, geo.city

アプリ情報: app_info

app_infoには、アプリ情報が格納されています。 Webの場合は、nullが設定されています。

id: アプリのパッケージ名またはバンドルID。
firebaseappid: アプリに関連付けられているFirebaseアプリID。
install_source: アプリをインストールしたストア。
version: アプリのversionNam（Android の場合）または short bundle version。

WITH origin AS (
  SELECT
    DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
    app_info,
    user_pseudo_id
  FROM `xxxx.yyyy.events_*`
  WHERE _TABLE_SUFFIX > "20211101"
)
-- SELECT * FROM origin
SELECT
    hitdate,
    app_info.id,
    app_info.firebase_app_id,
    app_info.install_source,
    app_info.install_store,
    app_info.version,
    COUNT(1) AS number_of_events,
    COUNT(DISTINCT user_pseudo_id) AS number_of_users
FROM origin
GROUP BY hitdate, app_info.id, app_info.version, app_info.install_store, app_info.firebase_app_id, app_info.install_source
ORDER BY hitdate DESC, app_info.id, app_info.version, app_info.install_store, app_info.firebase_app_id, app_info.install_source

プライバシー情報: privacy_info

privacy_infoには、プライバシー設定が格納されています。各データは、Yes、No、Unsetのいずれかが設定されています。

ads_storage: ユーザーに対して広告ターゲティングが有効になっているかどうか。
analytics_storage: ユーザーに対してアナリティクスのデータ保存が有効になっているかどうか。

usestransienttoken: ウェブユーザーがアナリティクスでのデータ保存を拒否し、デベロッパーがサーバーデータの一時的なトークンに基づいて Cookie を使用しない測定を有効にしているかどうか。

WITH origin AS (
SELECT
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
privacy_info,
user_pseudo_id
FROM `xxxx.yyyy.events_*`
WHERE _TABLE_SUFFIX > "20211101"
)
-- SELECT * FROM origin
SELECT
hitdate,
privacy_info.analytics_storage,
privacy_info.ads_storage,
privacy_info.uses_transient_token,
COUNT(1) AS number_of_events,
COUNT(DISTINCT user_pseudo_id) AS number_of_users
FROM origin
GROUP BY hitdate, privacy_info.analytics_storage, privacy_info.ads_storage, privacy_info.uses_transient_token
ORDER BY hitdate DESC, privacy_info.analytics_storage, privacy_info.ads_storage, privacy_info.uses_transient_token

ライフタイムバリュー: user_ltv

user_ltvには、ユーザーのライフタイムバリューに関する情報を格納されています。このフィールドは、当日表では使用されません。

revenue: ライフタイムバリュー（収益）
currency: ライフタイムバリュー（通貨）

eコマースに関する情報: ecommerce

///: event_dimensions

GA4の中で、ARRAY型で定義されているデータにアクセスする

GA4で、日付・時間を理解する

DATE: DATE 型は、タイムゾーンに関係なく、論理カレンダー日を表します。DATE 型の値は特定の 24 時間の期間を表していません。所定の DATE 値は、解釈されるタイムゾーンによって異なる 24 時間の期間を表します。また、夏時間の移行期には 1 日が通常よりも短くなったり、長くなったりする場合があります。絶対的な時刻を表すには、タイムスタンプを使用します。
- 範囲: 0001-01-01～9999-12-31
- 正規形式: 'YYYY-[M]M-[D]D'
- YYYY: 4 桁の年
- [M]M: 1 桁または 2 桁の月
- [D]D: 1 桁または 2 桁の日
DATETIME: DATETIME オブジェクトは、タイムゾーンに依存せずにカレンダーや時計に表示される日時を表します。これには年、月、日、時、分、秒、サブ秒が含まれます。絶対的な時刻を表すには、タイムスタンプを使用します。
- 範囲: 0001-01-01 00:00:00～9999-12-31 23:59:59.999999
- 正規形式: YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.F]]
- YYYY: 4 桁の年
- [M]M: 1 桁または 2 桁の月
- [D]D: 1 桁または 2 桁の日
- ( |T): スペースまたは区切り文字「T」
- [H]H: 1 桁または 2 桁の時（有効な値は 00～23）
- [M]M: 1 桁または 2 桁の分（有効な値は 00～59）
- [S]S: 1 桁または 2 桁の秒（有効な値は 00～59）
- [.F]: 最大で小数第 6 位まで（マイクロ秒の精度）
TIME: TIME オブジェクトは、特定の日付とタイムゾーンとは関係なく、時計に表示される時刻を表します。絶対的な時刻を表すには、タイムスタンプを使用します。
- 範囲: 00:00:00 から 23:59:59.999999
- 正規形式: [H]H:[M]M:[S]S[.DDDDDD|.F]
- [H]H: 1 桁または 2 桁の時（有効な値は 00～23）
- [M]M: 1 桁または 2 桁の分（有効な値は 00～59）
- [S]S: 1 桁または 2 桁の秒（有効な値は 00～59）
- [.F]: 最大で小数第 6 位まで（マイクロ秒の精度）
TIMESTAMP: TIMESTAMP オブジェクトは、タイムゾーンや夏時間などの慣習に関係なく、マイクロ秒精度の絶対的な時刻を表します。
- カレンダーに表示される日付を表すには、DATE オブジェクトを使用します。
- 時計に表示される時刻を表すには、TIME オブジェクトを使用します。
- カレンダーと時計に表示される日付と時刻を表すには、DATETIME オブジェクトを使用します。
- 範囲: 0001-01-01 00:00:00～9999-12-31 23:59:59.999999 UTC
- 正規形式: YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.F]][time zone]
- YYYY: 4 桁の年
- [M]M: 1 桁または 2 桁の月
- [D]D: 1 桁または 2 桁の日
- ( |T): スペースまたは区切り文字「T」
- [H]H: 1 桁または 2 桁の時（有効な値は 00～23）
- [M]M: 1 桁または 2 桁の分（有効な値は 00～59）
- [S]S: 1 桁または 2 桁の秒（有効な値は 00～59）
- [.F]: 最大で小数第 6 位まで（マイクロ秒の精度）
- [time zone]: タイムゾーンを表す文字列。タイムゾーンが明示的に指定されていない場合は、デフォルトのタイムゾーンである UTC が使用されます。詳しくは、タイムゾーンのセクションをご覧ください。
タイムゾーン: タイムゾーンは、タイムスタンプの構文を解析したり、タイムスタンプの形式を設定して表示したりするときに使用されます。タイムスタンプの値自体には、特定のタイムゾーンは格納されず、タイムゾーンオフセットを適用するときに変更されることもありません。
- タイムゾーンは、次の 2 つの正規形式のいずれかで、文字列によって示されます。
- 協定世界時（UTC）からのオフセット、または UTC を表す文字 Z
  - 正規形式: (+|-)H[H]:M[M]] Z
  - 例: -08:00 -8:15 +3:00 +07:30 -7 Z
- tz database からのタイムゾーン名
  - 正規形式: continent/[region/]city
  - 例: List of tz database time zones - Wikipedia

event_timestampを日付・時間に変換する

event_timestampは、クライアントでイベントが記録された時刻（ミリ秒単位、UTC）がINTEGER型で格納されています。この値をTIMESTAMP,DATETIME,DATE,TIMEに変換するクエリは下記のようになります。

DATETIME: DATETIMEオブジェクトを構築します。
DATE: DATEオブジェクトを構築します。
TIME: TIMEオブジェクトを構築します。
EXTRACT: TIMESTAMP/DATETIME/DATE/TIMEの中から、任意の部分を取り出します。

WITH origin AS (
    SELECT
        event_date,
        event_timestamp,
        TIMESTAMP_MICROS(event_timestamp) AS timestamp_micros,
    FROM `xxxx.yyyy.events_*`
    WHERE _TABLE_SUFFIX > "20211101"
) ,
extracts AS (
    SELECT
        DATETIME(timestamp_micros, 'Asia/Tokyo') AS datetime_use_datetime_funcion,
        DATE(timestamp_micros, 'Asia/Tokyo') AS date_use_date_function,
        TIME(timestamp_micros, 'Asia/Tokyo') AS time_use_time_function,
        EXTRACT(DATETIME FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS datetime,
        EXTRACT(DATE FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS date,
        EXTRACT(TIME FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS time,
        EXTRACT(YEAR FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS year,
        EXTRACT(MONTH FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS month,
        EXTRACT(DAY FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS day,
        EXTRACT(HOUR FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS hour,
        EXTRACT(MINUTE FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS minute,
        EXTRACT(SECOND FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS second,
        EXTRACT(MILLISECOND FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS millisecond,
        EXTRACT(MICROSECOND FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS microsecond,
        EXTRACT(DAYOFYEAR FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS dayofyear,
        EXTRACT(DAYOFWEEK FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS dayofweek,
        EXTRACT(WEEK(SUNDAY) FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS week,
        EXTRACT(QUARTER FROM timestamp_micros AT TIME ZONE 'Asia/Tokyo') AS quarter,
    FROM origin ORDER BY event_timestamp DESC
)
SELECT * FROM extracts ORDER BY datetime DESC

日付・時間型を任意の部分で切り詰める。

TIMESTAMP,DATETIME,DATE,TIMEを任意の部分を指定して、情報を切り詰めます。例: 年と月までの情報が必要で、日は1、時間は0にしたい切り詰めるには****_TRUNC系の関数を使います。

TIMESTAMP_TRUNC
DATETIME_TRUNC
DATE_TRUNC

TIME_TRUNC

WITH origin AS (
SELECT
    event_date,
    event_timestamp,
    TIMESTAMP_MICROS(event_timestamp) AS timestamp_micros,
FROM `xxxx.yyyy.events_*`
WHERE _TABLE_SUFFIX > "20211101"
) ,
functions AS (
SELECT
    TIMESTAMP_TRUNC(timestamp_micros, YEAR, 'Asia/Tokyo') AS year,
    TIMESTAMP_TRUNC(timestamp_micros, MONTH, 'Asia/Tokyo') AS month,
    TIMESTAMP_TRUNC(timestamp_micros, DAY, 'Asia/Tokyo') AS day,
    TIMESTAMP_TRUNC(timestamp_micros, HOUR, 'Asia/Tokyo') AS hour,
    TIMESTAMP_TRUNC(timestamp_micros, MINUTE, 'Asia/Tokyo') AS minute,
    TIMESTAMP_TRUNC(timestamp_micros, SECOND, 'Asia/Tokyo') AS second,
    TIMESTAMP_TRUNC(timestamp_micros, MILLISECOND, 'Asia/Tokyo') AS millisecond,
    TIMESTAMP_TRUNC(timestamp_micros, MICROSECOND, 'Asia/Tokyo') AS microsecond,
    TIMESTAMP_TRUNC(timestamp_micros, WEEK(SUNDAY), 'Asia/Tokyo') AS week,
    TIMESTAMP_TRUNC(timestamp_micros, QUARTER, 'Asia/Tokyo') AS quarter,
FROM origin ORDER BY event_timestamp DESC
)
SELECT
*,
FROM functions

event_timestampを任意の日時文字列に変換する

日付・時間型をyyyyMMddのような文字列にしたい場合は、FORMAT_***系の関数を使います。

FORMAT_TIMESTAMP
FORMAT_TIME
FORMAT_DATE
FORMAT_DATETIME

WITH origin AS (
    SELECT
        event_date,
        event_timestamp,
        TIMESTAMP_MICROS(event_timestamp) AS timestamp_micros,
    FROM `xxxx.yyyy.events_*`
    WHERE _TABLE_SUFFIX > "20211101"
) ,
functions AS (
    SELECT
        FORMAT_TIMESTAMP("%Y", timestamp_micros , 'Asia/Tokyo') AS Y,
        FORMAT_TIMESTAMP("%m", timestamp_micros , 'Asia/Tokyo') AS m,
        FORMAT_TIMESTAMP("%d", timestamp_micros , 'Asia/Tokyo') AS d,
        FORMAT_TIMESTAMP("%H", timestamp_micros , 'Asia/Tokyo') AS H,
        FORMAT_TIMESTAMP("%M", timestamp_micros , 'Asia/Tokyo') AS M,
        FORMAT_TIMESTAMP("%S", timestamp_micros , 'Asia/Tokyo') AS S,
        FORMAT_TIMESTAMP("%Y-%m", timestamp_micros , 'Asia/Tokyo') AS Y_m
    FROM origin ORDER BY event_timestamp DESC
)
SELECT
    *,
FROM functions

event_dateをDATE型に変換する

event_dateは、yyyyMMddという形式の日付文字列が格納されています。これを、DATE型に変換する。

WITH origin AS (
    SELECT
        event_date
    FROM `xxxx.yyyy.events_*`
    WHERE _TABLE_SUFFIX > "20211101"
) ,
functions AS (
    SELECT
        PARSE_DATE("%Y%m%d", event_date) AS date
    FROM origin ORDER BY event_timestamp DESC
)
SELECT
    *,
FROM functions

月初と月末の作り方

月初めの日付と月の最後の日付を作るには下記をそれぞれ使います。

DATE_TRUNC: DATE型の任意の部分以下の情報に対して初期値を適用します。
LAST_DAY: DATE型の任意の範囲における最終日を返却します。

WITH origin AS (
    SELECT
        event_date,
        event_timestamp,
        TIMESTAMP_MICROS(event_timestamp) AS timestamp_micros,
        DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS date,
    FROM `xxxx.yyyy.events_*`
    WHERE _TABLE_SUFFIX > "20211101"
) ,
functions AS (
    SELECT
    DATE_TRUNC(date, MONTH) AS beginning_of_the_day,
    LAST_DAY(date, MONTH) AS end_of_the_day,
    FROM origin ORDER BY event_timestamp DESC
)
SELECT
    *,
FROM functions

TIMESTAMP,DATETIME,DATE,TIMEの加算・減算

TIMESTAMP,DATETIME,DATE,TIMEに対して、加算・減算を行う場合、下記の二つの方法がある。

+-演算子を使って日単位の加算・減算を行う

関数を使う

TIMESTAMPADD・TIMESTAMPSUB
DATETIMEADD・DATETIMESUB
DATEADD・DATESUB

TIMEADD・TIMESUB

WITH origin AS (
SELECT
event_date,
event_timestamp,
TIMESTAMP_MICROS(event_timestamp) AS timestamp_micros,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS date,
FROM `xxxx.yyyy.events_*`
WHERE _TABLE_SUFFIX > "20211101"
) ,
functions AS (
SELECT
date,
date + 1 AS next_day,
DATE_ADD(date, INTERVAL 1 DAY) AS next_day_func,
DATE_ADD(date, INTERVAL 1 MONTH) AS next_month,
DATE_ADD(date, INTERVAL 1 YEAR) AS next_year,

date - 1 AS previous_day,
DATE_ADD(date, INTERVAL -1 DAY) AS previous_day_func,
DATE_ADD(date, INTERVAL -1 MONTH) AS previous_month,
DATE_ADD(date, INTERVAL -1 YEAR) AS previous_year,
FROM origin ORDER BY event_timestamp DESC
)
SELECT
*,
FROM functions

TIMESTAMP,DATETIME,DATE,TIMEの差分をとる。

TIMESTAMP,DATETIME,DATE,TIMEの二つのオブジェクトの差分を取るには下記の関数を使います。

TIMESTAMP_DIFF
DATETIME_DIFF
DATE_DIFF
TIME_DIFF

WITH origin AS (
    SELECT
        event_date,
        event_timestamp,
        CURRENT_DATETIME('Asia/Tokyo') AS now,
        DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS datetime,
        TIMESTAMP_MICROS(event_timestamp) AS timestamp_micros,
    FROM `xxxx.yyyy.events_*`
    WHERE _TABLE_SUFFIX > "20211101"
) ,
functions AS (
    SELECT
        DATETIME_DIFF(now, datetime, YEAR) AS year,
        DATETIME_DIFF(now, datetime, MONTH) AS month,
        DATETIME_DIFF(now, datetime, DAY) AS day,
        DATETIME_DIFF(now, datetime, HOUR) AS hour,
        DATETIME_DIFF(now, datetime, MINUTE) AS minute,
        DATETIME_DIFF(now, datetime, SECOND) AS second,
        DATETIME_DIFF(now, datetime, MILLISECOND) AS millisecond,
        DATETIME_DIFF(now, datetime, MICROSECOND) AS microsecond,
        DATETIME_DIFF(now, datetime, WEEK(SUNDAY)) AS week,
        DATETIME_DIFF(now, datetime, QUARTER) AS quarter,
    FROM origin ORDER BY event_timestamp DESC
)
SELECT
    *
FROM functions

Bigqueryでスクリプトを使う

Bigquery:DECLARE・SETを使用して変数を使う

Bigqueryではクエリ上で使う変数を定義、使用することが可能です。変数を利用することで、同じクエリを使いまわすことが可能になります。

変数を宣言するには、DECLAREを使い、DEFAULT句でデフォルト値を、SETで値を設定します。同じ型の変数はまとめて宣言することができます、しかし、初期値は同じ値しか設定できません。また、DEFAULT句でデフォルト値を設定する場合は、型は推定されるので型を省略することが可能です。

DEFAULT句では、SELECT文を発行することができるので、データベースの中の値を変数に設定することが可能です。

DECLARE intX, intY INT64 DEFAULT 1;
DECLARE stringX, stringY STRING DEFAULT "a";
DECLARE dateX DATE DEFAULT DATE("2021-12-13");
DECLARE arrayX ARRAY<INT64> DEFAULT [1,2,3];
DECLARE eventDate STRUCT<max DATE, min DATE> DEFAULT (SELECT STRUCT(PARSE_DATE("%Y%m%d", MAX(_TABLE_SUFFIX)), PARSE_DATE("%Y%m%d", MIN(_TABLE_SUFFIX))) FROM `xxxx.yyyy.zzzz.events_*`);
DECLARE events DEFAULT (SELECT ARRAY_AGG(DISTINCT event_name ORDER BY event_name) FROM `xxxx.yyyy.zzzz.events_*`);

SET intZ = 1;
SELECT
    intX, intY,
    stringX, stringY,
    dateX,
    arrayX,
    eventDate,
    events

DECLARE | BigQuery | Google Cloud

ユーザー定義関数UDF(User-Defined Functions)を使う

ユーザー定義関数(UDF)は、SQL式やJavaScriptコードを使用して繰り返し呼び出し可能な関数を作成できます。 UDFは入力列を受け取って入力に対するアクションを実行し、それらのアクションの結果を値として返します。使うキーワードは下記の通りです。

CREATE TEMPORARY FUNCTION: クエリ内にUDFを記述する際に使います。
CREATE FUNCTION: データセットを跨いで使う永続UDFを記述する際に使います。

URLを使いやすく分解するUDF

URLを分析するには、必要な情報だけを抜き出さないといけない。正規表現もゴリゴリ使うので、基本的な分解を行うUDFを作っておくことで分析が楽になるかも？

    CREATE TEMP FUNCTION PARSE_QUERY_TO_JSON_STRING(q STRING)
    RETURNS STRING
    LANGUAGE js AS
        """
        var ret = {}
        var params = q.split("&")
        params.forEach(row => {
            var keyValue = row.split("=")
            ret[keyValue[0]] = keyValue[1]
        })
        return JSON.stringify(ret);
        """
    ;

    CREATE TEMP FUNCTION PARSE_QUERY(q STRING)
    RETURNS ARRAY<STRUCT<key STRING, value STRING>>
    LANGUAGE js AS
        """
        var ret = []
        var params = q.split("&")
        params.forEach(row => {
            var keyValue = row.split("=")
            ret.push({
            "key" : keyValue[0],
            "value": keyValue[1]
            })
        })
        return ret;
        """
    ;

    CREATE TEMP FUNCTION PARSE_URL (url STRING) AS (
        STRUCT(
            url AS original,
            REGEXP_EXTRACT(url, r'^(.*)\?') as url,
            REGEXP_EXTRACT(url, r'^([a-zA-Z]+)://') as protocol,
            REGEXP_EXTRACT(url, r'(?:[a-zA-Z]+://)?([a-zA-Z0-9-.]+)/?') as host,
            REGEXP_EXTRACT(url, r'(?:[a-zA-Z]+://)?(?:[a-zA-Z0-9-.]+)/{1}([a-zA-Z0-9-./]+)') as path,
            REGEXP_EXTRACT(url, r'\?(.*)') as query,
            PARSE_QUERY(REGEXP_EXTRACT(url, r'\?(.*)')) as queries,
            PARSE_QUERY_TO_JSON_STRING(REGEXP_EXTRACT(url, r'\?(.*)')) as queries_json,
            REGEXP_EXTRACT(url, r'#(.*)') as ref
        )
    );

    WITH data AS (
    SELECT "https://game.yonayona.info/apex/tournaments/20?tab=teams" AS url
    )
    SELECT
    PARSE_URL(url) AS parsed_url
    FROM data

日付文字列を操作するUDF

日付文字列("yyyymmdd")を渡すと、その日付に関連する各種日付を取得するUDF

Date型: 当日
Date型: 前日
Date型: 3日前
Date型: 7日前
Date型: 10日前
Date型: 14日前
Date型: 30日前
Struct型: 今月の開始日と終了日
Struct型: 前月の開始日と終了日

CREATE TEMPORARY FUNCTION CREATE_N_DAY (yyyymmdd STRING, n INT64) AS ((PARSE_DATE("%Y%m%d", yyyymmdd) + n));
CREATE TEMPORARY FUNCTION CREATE_MONTH_PERIOD (yyyymmdd STRING, n INT64) AS (
    STRUCT(
        DATE_ADD(DATE_TRUNC(PARSE_DATE("%Y%m%d", yyyymmdd), MONTH), INTERVAL +n MONTH) AS beginning_of_the_day ,
        LAST_DAY(DATE_ADD(DATE_TRUNC(PARSE_DATE("%Y%m%d", yyyymmdd), MONTH), INTERVAL +n MONTH), MONTH) AS end_of_the_day
    )
);
CREATE TEMPORARY FUNCTION GET_DATE_STRUCT(yyyymmdd STRING) AS (
    STRUCT(
        CREATE_N_DAY(yyyymmdd, 0) AS day,
        CREATE_N_DAY(yyyymmdd, -1) AS one_days_ago,
        CREATE_N_DAY(yyyymmdd, -3) AS three_days_ago,
        CREATE_N_DAY(yyyymmdd, -7) AS seven_days_ago,
        CREATE_N_DAY(yyyymmdd, -10) AS ten_days_ago,
        CREATE_N_DAY(yyyymmdd, -14) AS fifteen_days_ago,
        CREATE_N_DAY(yyyymmdd, -30) AS thirty_days_ago,
        CREATE_MONTH_PERIOD(yyyymmdd, 0) AS this_month,
        CREATE_MONTH_PERIOD(yyyymmdd, -1) AS last_month
    )
);

SELECT GET_DATE_STRUCT("20211201") AS date_struct

SELECTの中から、EXCEPTをを使い任意のカラムを除外し、出力されないようにする。

SELECTの中で、下記のように指定することで、出力結果に出力されるカラムを制御することができます。

出力結果にカラムを含める指定方法
- カラム名を指定して、カラムを出力結果に含める。
- *を使って、対象の全てのカラムを出力結果に含める。
出力結果にカラムを含めない指定方法
- EXCEPTを使って、任意のカラムを出力結果に含めない。

下記の例では、usersテーブルとpurchasesテーブルで同じ意味を持つカラムの片方を除外するために使っている。

users.id
purchases.user_id

CREATE TEMP FUNCTION rand_range(min_value INT64, max_value INT64) AS (
    CAST(ROUND(min_value + RAND() * (max_value - min_value)) AS INT64)
);
CREATE TEMP FUNCTION rand_date(min_date DATE, max_date DATE) AS (
    TIMESTAMP_SECONDS(
        CAST(
            ROUND(UNIX_SECONDS(CAST(min_date AS TIMESTAMP)) + RAND() * (UNIX_SECONDS(CAST(max_date AS TIMESTAMP)) - UNIX_SECONDS(CAST(min_date AS TIMESTAMP))))
        AS INT64)
    )
);
WITH users AS (
    SELECT
        id,
        rand_range(0, 1) AS gender,
        rand_range(1, 47) AS prefecture_code
    FROM UNNEST(GENERATE_ARRAY(1, 10)) AS id
),purchases AS  (
    SELECT
        id,
        rand_range(1, 10) AS user_id,
        rand_range(1000, 5000) AS price,
        rand_range(1, 10) AS quantity,
        DATE(rand_date("2021-01-01", "2021-12-31"), "Asia/Tokyo") AS purchase_at,
    FROM UNNEST(GENERATE_ARRAY(1, 500)) AS id
), test_data AS (
    SELECT
        users.*　EXCEPT(id),
        purchases.*
    FROM users
    INNER JOIN purchases ON users.id = purchases.user_id
)
SELECT * FROM test_data

ARRAY型で定義されているデータにアクセスする

縦横変換

PIVOTを使った縦から横への変換

PIVOTを使うことで、列方向に長いデータを行方向に集計し変換することができます。プラットフォーム別のユーザー数を集計し、列方向にデータを並べます。 GROUP BYだと、集計後のデータが行方向に並びますが、PIVOTを使うと、集計値がプラットフォーム別にカラムとして配置されます。

WITH origin AS (
  SELECT
    DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS hitdate,
    platform,
    user_pseudo_id
  FROM `xxxx.yyyy.events_*`
  WHERE _TABLE_SUFFIX > "20211101"
)
SELECT * FROM origin
PIVOT(
    COUNT(DISTINCT user_pseudo_id) AS count_of_users
    FOR platform IN ("WEB" AS web,  "IOS" AS ios, "ANDROID" AS Android)
)

UNPIVOTを使った横から縦への変換

BigqueryでJSONを取り扱う

JSON 関数

JSON文字列をカラムに入れる方法

SQLで使用されるデータ型をJSON文字列へ変換するには、TOJSONSTRINGを使用します。この関数は、SQL値を受け取り、対応した文字列をJSON形式のSTRINGにして返却します。また、省略可能な第二引数に対して、TRUEを設定することで人間が読みやすいように整形した状態で返却します。各SQL値は、対応する形式にエンコードされます。

WITH origin AS (
    SELECT
        TO_JSON_STRING(user, TRUE)
    FROM
        UNNEST([
            STRUCT(
                1 AS id, 'aaaa' AS name, CURRENT_DATE() - 10 AS created_at, FALSE AS is_deleted,
                [
                    STRUCT(1 AS id, "item_1" AS name, 100 AS price),
                    STRUCT(2 AS id, "item_2" AS name, 200 AS price),
                    STRUCT(3 AS id, "item_3" AS name, 300 AS price)
                ] AS items
            ),
            STRUCT(
                2 AS id, 'bbbb' AS name, CURRENT_DATE() - 5 AS created_at, TRUE AS is_deleted,
                [
                    STRUCT(1 AS id, "item_1" AS name, 100 AS price),
                    STRUCT(3 AS id, "item_3" AS name, 300 AS price)
                ] AS items
            ),
            STRUCT(
                3 AS id, 'cccc' AS name, CURRENT_DATE() - 0 AS created_at, FALSE AS is_deleted,
                [
                    STRUCT(1 AS id, "item_1" AS name, 100 AS price)
                ] AS items
            )
        ]) AS user
)
SELECT * FROM origin

JSON文字列から値を取り出す。

JSONから文字列から値やJSON文字列を取り出す関数は下記の4つがあります。全ての関数は第一引数にJSON文字列、第二引数にJSONPathが設定できます。対象のJSON文字列において、妥当なJSONPathを設定しなかった場合、NULLが返却されます。

JSON_QUERY
- 対象のJSON文字列から、JSONPathを指定して、JSON形式の文字列を取り出します。
- 取得可能なJSON文字列に制限はありません。JSON値（配列やオブジェクトなど）/JSON スカラー値（文字列、数値、ブール値など）を抽出します。
JSON_VALUE
- スカラー値を抽出し、文字列で返却されます。
- スカラー値は、文字列、数値、またはブール値のいずれかです。
- スカラー以外の値が選択されている場合、NULLを返します。
JSONQUERYARRAY
- 配列やオブジェクトなどの JSON値の配列と、文字列、数値、ブール値などの JSON スカラー値を抽出します。
JSONVALUEARRAY
- スカラー値の配列を抽出します。
- スカラー値は、文字列、数値、またはブール値を表すことができます。
- 選択した値が配列でない場合、またはスカラー値のみを含む配列でない場合、NULLを返します。

WITH origin AS (
    SELECT
        TO_JSON_STRING(user, TRUE) AS json_text
    FROM
        UNNEST([
            STRUCT(
                1 AS id, 'aaaa' AS name, CURRENT_DATE() - 10 AS created_at, FALSE AS is_deleted,
                [
                    STRUCT(1 AS id, "item_1" AS name, 100 AS price),
                    STRUCT(2 AS id, "item_2" AS name, 200 AS price),
                    STRUCT(3 AS id, "item_3" AS name, 300 AS price)
                ] AS items,
                [
                    "Japan",
                    "China"
                ] AS nationality
            ),
            STRUCT(
                2 AS id, 'bbbb' AS name, CURRENT_DATE() - 5 AS created_at, TRUE AS is_deleted,
                [
                    STRUCT(1 AS id, "item_1" AS name, 100 AS price),
                    STRUCT(3 AS id, "item_3" AS name, 300 AS price)
                ] AS items,
                [
                    "China"
                ] AS nationality
            ),
            STRUCT(
                3 AS id, 'cccc' AS name, CURRENT_DATE() - 0 AS created_at, FALSE AS is_deleted,
                [
                    STRUCT(1 AS id, "item_1" AS name, 100 AS price)
                ] AS items,
                [
                    "Japan"
                ] AS nationality
            )
        ]) AS user
),
standard AS (
    SELECT
        json_text,
        JSON_QUERY(json_text, '$') AS JSON_QUERY_object,
        JSON_QUERY(json_text, '$.id') AS JSON_QUERY_id,
        JSON_QUERY(json_text, '$.items') AS JSON_QUERY_items,
        JSON_QUERY(json_text, '$.items[0]') AS JSON_QUERY_index_number_0_item,
        JSON_QUERY(json_text, '$.items[0].name') AS JSON_QUERY_index_number_0_item_name,
        JSON_VALUE(json_text, '$.id') AS JSON_VALUE_id,
        JSON_VALUE(json_text, '$.items[0].name') AS JSON_VALUE_index_number_0_item_name,
        JSON_QUERY_ARRAY(json_text, '$.items') AS JSON_QUERY_ARRAY_items,
        JSON_QUERY_ARRAY(json_text, '$.nationality') AS JSON_QUERY_ARRAY_nationality,
        JSON_VALUE_ARRAY(json_text, '$.nationality') AS JSON_VALUE_ARRAY_nationality,
    FROM origin
)
SELECT * FROM standard;

準備中

日時操作
- DATE型とTIME型の結合
- DATE型からTIMESTAMP型へ
User Property
Items
ARRAY
- 集合演算
- 和集合
- 積集合
- 差集合
- 対称差集合
ユーザー行動
- 離脱・直帰
- どこのページから来たか？
- PV/UU
GROUP BY
- 階層別
ランダムサンプリング
ウィンドウ関数
- 移動平均

未確認の関数ページ

参考リンク

Published 30 Nov 2021

Yona .Yona on Twitter