サンプルクエリ集

クエリを書く際の参考として、KARTEのイベントデータに対するクエリを例を中心に、サンプルクエリをご紹介します。

特定イベントのレコードを取得

karte_event のデータは、KARTEにタグ等で連携されたイベント毎にレコードが分かれています
イベント名を指定して抽出する場合は、event_nameカラムに対してwhere句で条件指定してください

例: コンバージョンタグを取得日時順に取得

SELECT
  *
FROM
  {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
WHERE
  event_name = 'buy'
ORDER BY
  sync_date

SELECT
  *
FROM
  {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
WHERE
  event_name = 'buy'
ORDER BY
  sync_date

セグメントに該当するユーザーを抽出

karte_events テーブルのshorten_segmentsカラムには、各イベントが記録された段階で、そのユーザーが所属していた全セグメントの短縮IDが含まれています
shorten_segmentsに対して任意セグメントの短縮IDが含まれるという条件で抽出することで、セグメントに該当するユーザーを絞り込めます
- セグメントの短縮IDと名称についてはkarte_segmentテーブルを参照してください
- クエリパラメータとしてセグメントを選択させることも可能です
  - https://developers.karte.io/docs/use-datahub-query-parameter

🚧
セグメント抽出の注意事項
shorten_segmentsの内容は各イベントが記録された際にセグメントの所属状況が更新されるため、shorten_segmentsに特定の短縮IDを含むという条件で抽出した場合には「過去そのセグメントに所属していたが現在所属していない」というユーザーが含まれます。
現在(≒KARTE側で計測された最新データ)そのセグメントに所属しているユーザーを抽出したい場合は、各ユーザーの最新データに対してshorten_segmentsのID抽出を行う必要があります。
各ユーザーの最新データの抽出については、クエリコレクション上の「ユーザーの最新のセグメント」等を参照してください。

例: セグメントIDが含まれるユーザーの抽出

SELECT 
  *
FROM 
  {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
WHERE 
  shorten_segments LIKE CONCAT('%', {{segment_id}}, '%')  -- Textパラメータをパラメータ定義で設定

SELECT 
  *
FROM 
  {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
WHERE 
  shorten_segments LIKE '%{{segment_id}}%'  -- Textパラメータをパラメータ定義で設定

JSON形式データのプロパティを取得

JSON形式のデータを参照するには、JSON関数を使います
- 参考:
  - JSON関数

例: ユーザー情報を重複無しで取得

SELECT
    user_id
    , JSON_VALUE(values, '$.identify.name') name
    , JSON_VALUE(values, '$.identify.email') email
FROM
  {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
WHERE
    event_name = 'identify'
GROUP BY user_id, name, email

SELECT
    user_id
    , JSON_VALUE(values, '$.identify.name') name
    , JSON_VALUE(values, '$.identify.email') email
FROM
  {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
WHERE
    event_name = 'identify'
GROUP BY user_id, name, email

文字列操作

文字列の結合を行うには、CONCAT()を使います

例: user_idを使い、KARTE管理画面上のユーザー詳細画面URLを生成

SELECT
    CONCAT('https://admin.karte.io/p/{{project_id}}/user/', user_id ) URL
FROM
  {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
WHERE
    event_name = "identify"
GROUP BY user_id

SELECT
    CONCAT('https://admin.karte.io/p/{{project_id}}/user/', user_id ) URL
FROM
  {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
WHERE
    event_name = "identify"
GROUP BY user_id

sync_dateが最大のレコードを抽出

特定条件に合致するレコードの中で、sync_dateが最大のレコードを抽出します

ユーザー毎の最新の_karte_rankを抽出

MAX(sync_date)を使った書き方

WITH events AS (
  SELECT
    *
    , JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
  FROM
    {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
)
, max_sync_date AS (
  SELECT
    user_id
    , MAX(sync_date) AS sync_date
  FROM events
  WHERE _karte_rank IS NOT NULL
  GROUP BY user_id
)
SELECT DISTINCT
  e.user_id
  , e._karte_rank
  , m.sync_date
FROM events AS e
INNER JOIN max_sync_date AS m
  ON
    e.user_id = m.user_id
    AND e.sync_date = m.sync_date

WITH events AS (
  SELECT
    *
    , JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
  FROM
    {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
)
, max_sync_date AS (
  SELECT
    user_id
    , MAX(sync_date) AS sync_date
  FROM events
  WHERE _karte_rank IS NOT NULL
  GROUP BY user_id
)
SELECT DISTINCT
  e.user_id
  , e._karte_rank
  , m.sync_date
FROM events AS e
INNER JOIN max_sync_date AS m
  ON
    e.user_id = m.user_id
    AND e.sync_date = m.sync_date

ROW_NUMBER()を使った書き方

WITH events AS (
  SELECT
    *
    , JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
    , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sync_date DESC) AS row_num
  FROM
    {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
  WHERE JSON_VALUE(scores, '$._karte_rank') IS NOT NULL
)
SELECT DISTINCT
  user_id
  , _karte_rank
  , sync_date
FROM events
WHERE row_num = 1

WITH events AS (
  SELECT
    *
    , JSON_VALUE(scores, '$._karte_rank') AS _karte_rank
    , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sync_date DESC) AS row_num
  FROM
    {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
  WHERE JSON_VALUE(scores, '$._karte_rank') IS NOT NULL
)
SELECT DISTINCT
  user_id
  , _karte_rank
  , sync_date
FROM events
WHERE row_num = 1

特定の接客サービスが表示されたユーザーのuser_idを抽出

1234567890ab1234567890abの部分は、実際のcampaign_idで置き換えてください

SELECT 
    user_id 
    , sync_date
    , JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
FROM 
    {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
WHERE 
    event_name  = "message_open"
    AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"

SELECT 
    user_id 
    , sync_date
    , JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
FROM 
    {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
WHERE 
    event_name  = "message_open"
    AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"

さらにアクションIDで絞り込む

1234567890ab1234567890abの部分は、実際の接客サービスID(campaign_id)で置き換えてください
ZaZの部分は、実際のアクションID(shorten_id)で置き換えてください

SELECT 
    user_id 
    , sync_date
    , JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
    , JSON_VALUE(values, "$.message_open.message.shorten_id") AS shorten_id
FROM 
  {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
WHERE 
    event_name  = "message_open"
    AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"
    AND JSON_VALUE(values, "$.message_open.message.shorten_id") = "ZaZ"

SELECT 
    user_id 
    , sync_date
    , JSON_VALUE(values, "$.message_open.message.campaign_id") AS campaign_id
    , JSON_VALUE(values, "$.message_open.message.shorten_id") AS shorten_id
FROM 
  {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
WHERE 
    event_name  = "message_open"
    AND JSON_VALUE(values, "$.message_open.message.campaign_id") = "1234567890ab1234567890ab"
    AND JSON_VALUE(values, "$.message_open.message.shorten_id") = "ZaZ"

別のテーブルとJOINする

karte_eventテーブルと、外部データから作成したexternal_idsというデータテーブルをJOINする例
参考:
- WITH句
- JOIN

WITH view_urls AS ( -- 閲覧URL履歴を取得
  SELECT
    user_id
    , JSON_VALUE(values, '$.view.access.uri.url') AS url
    , sync_date
  FROM
    {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
  WHERE event_name = "view"
)
, external_ids AS ( -- external_idsテーブルを取得
  SELECT
    karte_user_id
    , foo_id
  FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.external_ids`
)
 SELECT -- 両テーブルをJOINする
  user_id
  , external_ids.foo_id
  , url
  , sync_date
FROM view_urls
INNER JOIN external_ids
ON view_urls.user_id = external_ids.karte_user_id

WITH view_urls AS ( -- 閲覧URL履歴を取得
  SELECT
    user_id
    , JSON_VALUE(values, '$.view.access.uri.url') AS url
    , sync_date
  FROM
    {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
  WHERE event_name = "view"
)
, external_ids AS ( -- external_idsテーブルを取得
  SELECT
    karte_user_id
    , foo_id
  FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.external_ids`
)
 SELECT -- 両テーブルをJOINする
  user_id
  , external_ids.foo_id
  , url
  , sync_date
FROM view_urls
INNER JOIN external_ids
ON view_urls.user_id = external_ids.karte_user_id

JSON内のオブジェクトの配列を複数レコードに展開する

以下のように展開するクエリを作成します

展開前のテーブル

user_id	favorite_items
a	[{"item_id": "001","item_name":"foo"}]
b	[{"item_id": "001","item_name":"foo"},{"item_id": "002","item_name":"bar"},{"item_id": "003","item_name":"baz"}]
c	[{"item_id": "001","item_name":"foo"},{"item_id": "004","item_name":"qux"}]

展開後のテーブル

user_id	favorite_item_id	favorite_item_name
a	001	foo
b	001	foo
b	002	bar
b	003	baz
c	001	foo
c	004	qux

オブジェクトの配列を展開するクエリ

BigQueryには文字列を配列に変換する関数がまだ無いため、SPLIT関数を使っています
- そのため、複数階層あるJSONの場合はクエリの書き換えが必要です

WITH convert_to_array AS (
  SELECT
    user_id
    , SPLIT(SUBSTR(favorite_items, 2 , LENGTH(favorite_items) - 2), '},{') AS favorite_items
  FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.favorite_items`
)
, unnested AS (
  SELECT
    user_id
    , favorite_item
  FROM convert_to_array, UNNEST(favorite_items) AS favorite_item
)
, restored_to_json AS (
  SELECT
    user_id
    , CONCAT('{', REPLACE(REPLACE(favorite_item, '{', ''), '}', ''), '}') AS favorite_item
  FROM unnested
)
SELECT
  user_id
  , JSON_VALUE(favorite_item, '$.favorite_item_id') AS favorite_item_id
  , JSON_VALUE(favorite_item, '$.favorite_item_name') AS favorite_item_name
FROM restored_to_json

複数レコードに展開されたデータを1レコードに集約する

以下のように複数レコードを集約するクエリを作成します

集約前のテーブル

user_id	favorite_item_id	favorite_item_name
a	001	foo
b	001	foo
b	002	bar
b	003	baz
c	001	foo
c	004	qux

集約後のテーブル

user_id	favorite_item_ids	favorite_item_names
a	001	foo
b	001,002,003	foo,bar,baz
c	001,004	foo,qux

複数レコードに展開されたデータを1レコードに集約するクエリ

区切り文字を指定して、文字列として集約します

SELECT
  user_id
  , STRING_AGG(favorite_item_id, ',') AS favorite_item_ids
  , STRING_AGG(favorite_item_name, ',') AS favorite_item_names
FROM `prd-karte-per-client.karte_client_ab123456789012345678901234567890.favorite_items`
GROUP BY user_id

テーブル名に日付が含まれるテーブルIDに対して問い合わせる

BigQueryの_TABLE_SUFFIXを利用することで、テーブル名のsuffixを指定して問い合わせることが可能です。
参考：ワイルドカードテーブル

SELECT
  *
FROM
  `prd-karte-per-client.karte_client_ab123456789012345678901234567890.summary_*`
WHERE
  _TABLE_SUFFIX = '20190220'

また、CURRENT_DATE() 関数を利用することで当日日付を動的に生成して条件に指定することも可能です。

SELECT
  *
FROM
  `prd-karte-per-client.karte_client_ab123456789012345678901234567890.summary_*`
WHERE
  _TABLE_SUFFIX = REPLACE(CAST(CURRENT_DATE() AS STRING) , '-', '')

user_idを最新化して抽出する

karte_eventテーブルの抽出の中で、user_id連携前のイベントについても最新のuser_idにmapして抽出したいことがあります
karte_eventテーブルからvisitor_id毎の最新のuser_idを抽出してマッピングテーブルを生成することで実現できます

WITH raw_event AS (
  SELECT * FROM {% karte_event period[0], period[1] %}  -- Periodパラメータをパラメータ定義で設定
)
, user_id_map AS (
  SELECT
    user_id
    , ANY_VALUE(merged_user_id) AS merged_user_id
  FROM
  (
    SELECT
      user_id
      , FIRST_VALUE(user_id) OVER (PARTITION BY visitor_id ORDER BY sync_date DESC) AS merged_user_id
    FROM
      raw_event
    WHERE
      visitor_id IS NOT NULL
  )
  GROUP BY
    user_id
)
, user_mapped_event AS (
  SELECT
    IFNULL(b.merged_user_id, a.user_id) AS user_id
    , sync_date
    , event_name
    , values
  FROM
    raw_event AS a
  LEFT OUTER JOIN
    user_id_map AS b
  ON a.user_id = b.user_id
)
SELECT * FROM user_mapped_event

WITH raw_event AS (
  SELECT * FROM {{ karte_event(period[0], period[1]) }}  -- Periodパラメータをパラメータ定義で設定
)
, user_id_map AS (
  SELECT
    user_id
    , ANY_VALUE(merged_user_id) AS merged_user_id
  FROM
  (
    SELECT
      user_id
      , FIRST_VALUE(user_id) OVER (PARTITION BY visitor_id ORDER BY sync_date DESC) AS merged_user_id
    FROM
      raw_event
    WHERE
      visitor_id IS NOT NULL
  )
  GROUP BY
    user_id
)
, user_mapped_event AS (
  SELECT
    IFNULL(b.merged_user_id, a.user_id) AS user_id
    , sync_date
    , event_name
    , values
  FROM
    raw_event AS a
  LEFT OUTER JOIN
    user_id_map AS b
  ON a.user_id = b.user_id
)
SELECT * FROM user_mapped_event

Updated 12 months ago

特定イベントのレコードを取得

例: コンバージョンタグを取得日時順に取得

セグメントに該当するユーザーを抽出

🚧セグメント抽出の注意事項

例: セグメントIDが含まれるユーザーの抽出

JSON形式データのプロパティを取得

例: ユーザー情報を重複無しで取得

文字列操作

例: user_idを使い、KARTE管理画面上のユーザー詳細画面URLを生成

sync_dateが最大のレコードを抽出

ユーザー毎の最新の_karte_rankを抽出

特定の接客サービスが表示されたユーザーのuser_idを抽出

さらにアクションIDで絞り込む

別のテーブルとJOINする

JSON内のオブジェクトの配列を複数レコードに展開する

展開前のテーブル

展開後のテーブル

オブジェクトの配列を展開するクエリ

複数レコードに展開されたデータを1レコードに集約する

集約前のテーブル

集約後のテーブル

複数レコードに展開されたデータを1レコードに集約するクエリ

テーブル名に日付が含まれるテーブルIDに対して問い合わせる

user_idを最新化して抽出する

🚧
セグメント抽出の注意事項