Guides

karte_eventテーブルへのクエリを作成する

Suggest Edits

karte_eventテーブルは、KARTEのタグや接客サービスなどから発生した「イベント」のログが格納されているテーブルです。
イベントテーブルは日付ごとにパーティションされています。

テーブル範囲の指定

FROM句にkarte_eventテーブルを指定する場合、BigQueryの記法とは異なるKARTE独自記法で記述する必要があります。
実際には、BigQueryの記法に置換された後でクエリ実行されます。
クエリを実行する対象日付の範囲指定は下記のように行います。

SELECT *
FROM　{% karte_event '20181201', '20181203' %}

SELECT *
FROM　{{ karte_event('20181201', '20181203') }}
  • Period型のクエリパラメータを使う場合は、次のように記述してください
period:
  type: period
  label: 抽出期間

SELECT *
FROM　{% karte_event period[0], period[1] %}

SELECT *
FROM {{ karte_event(period[0], period[1]) }}

なお、全期間を指定したい場合は次のように記述します。
※ クエリリソース消費量が大きいため、全期間を指定したクエリの実行には十分に注意してください。

SELECT
  *
FROM {% karte_event_all %}

BigQueryの関数を使ってテーブル範囲を指定したい場合

  • クエリv2で、BigQueryの関数を使って期間を絞り込みたい場合は、次のように記述してください
SELECT
  *
FROM {% karte_event_all %} AS t
WHERE
  -- クエリv2 で {% karte_event_all %} を使って karte_event テーブルを呼び出した場合にのみ、
  -- 日付別テーブルの _TABLE_SUFFIX が、 TABLE_SUFFIX という名前で出力されます
  -- TABLE_SUFFIX による絞り込みを行わないと、全期間のデータを読み込むため、クエリリソースの使用量にご注意ください
  t.TABLE_SUFFIX  >= FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))

-- 例: period型パラメータで指定した期間よりも終了日を1日分多く指定する
SELECT
  sync_date
  , {{period[0]}} AS period_0
  , FORMAT_DATE("%Y%m%d", DATE_ADD(PARSE_DATE("%Y%m%d", {{period[1]}}), INTERVAL 1 DAY)) AS period_1
FROM {% karte_event_all %} AS t
WHERE
  t.TABLE_SUFFIX  >= {{period[0]}}
  AND t.TABLE_SUFFIX  <= FORMAT_DATE("%Y%m%d", DATE_ADD(PARSE_DATE("%Y%m%d", {{period[1]}}), INTERVAL 1 DAY))
ORDER BY sync_date DESC

🚧

karte_eventテーブルの分割単位について

karte_eventテーブルは、UTC（協定世界時）ベースの日付単位で分割されています。
karte_eventの引数として指定する日付指定についても、UTCベースの日付として解釈されます。
別のタイムゾーンについて特定期間のデータを抽出する場合は、1日分長く指定した上で、WHERE句で絞り込んでください。

スキーマ

karte_eventテーブルのスキーマ

field namedisplay namedescription
sync_date送信日時タイムスタンプタイプのイベントデータが同期された日時です。タイムスタンプタイプ自体はタイムゾーンを持っていませんが、タイムスタンプタイプの値を表示した際には視認性のために特定のタイムゾーンに応じた時刻として表示されデフォルトではUTCとして表示されます。任意のタイムゾーンに変換したい場合はTIMESTAMP()等をご利用ください。
user_idユーザーIDユーザータグで連携されたユーザーIDです。ID連携前のユーザー(ビジター)については、基本的にはビジターIDの先頭に"vis-"を付加した値が格納されます。
visitor_idビジターIDブラウザ毎に付与されるIDです。Webのユーザーについては、cookieに記録されます。
session_idセッションIDセッション毎に付与されるIDです。
event_nameイベント名イベントの名前です。
valuesイベント値イベントの値です。JSON形式で格納されています。
shorten_segments短縮セグメントIDイベント発生時点の短縮セグメントIDのリストです。
scoresスコアイベント発生時点の各スコアの値です。JSON形式で格納されています。
is_visitorビジターフラグユーザーIDがタグで連携されていない(メンバー化していない)ユーザーの場合、trueになります。
sourceイベント発生ソースイベントが発生した場所の種類を示す文字列(web, native_app_webview, native_app_sdk)が入ります。
event_hashイベントハッシュイベントごとに付与されるハッシュ値です。一意であることを保証しているものではありません。sync_date などの他のフィールドと組み合わせることで、一意なフィールドを生成することが可能です。
user_fields「作成したディメンション」の値「作成したディメンション」のイベント発生時点の値です。JSON形式で格納されています。
ディメンションと値の対応については、ディメンション詳細画面に表示される「レポート用ID」をご覧ください。
dimensions.*「インストールしたディメンション」の値「インストールしたディメンション」のイベント発生時点の値です。
ディメンションと値の対応については、ディメンション詳細画面に表示される「レポート用キー」をご覧ください。
processor解析処理の種類イベントにどの解析処理が行われたかを示す文字列が入ります。
default値はnull（リアルタイム解析を行う）です。
"ingest"の場合はセグメント等の解析を行わず、基本情報のみ書き込まれたことを示します。

🚧

sync_dateのタイムゾーンについて

KARTEのイベントデータには、sync_date(送信日時)が自動で付加されます。KARTEのセグメント設定画面などでは日本時間に変更されていますが、内部的にはUTC(協定世界時)での時刻です。そのため、JST(日本標準時)に変換する際には、+9時間してください。

🚧

karte_eventテーブルのレコード重複について

KARTEに対して送信されたイベントデータは、karte_eventテーブルに対して非同期で書き込まれます。この際、処理のリトライなどによって極稀に同じ内容のイベントが複数回書き込まれてしまうケースがあります。その場合、重複書き込みされたレコードはevent_hashが同一になります。

こうしたレコードの重複を除外して厳密な集計を実施したい場合は、event_hashが同一のレコードをクエリ内で重複削除してください。

📘

karte_eventテーブルの値サンプル

sync_date

  • 2018-06-05T10:27:36.680Z

is_visitor

  • true, false

values

  • 後述

event_name

  • "identify"

user_id

  • "k1234"
  • "vis-1234567890_1234567890123_123456789"

visitor_id

  • "1234567890_1234567890123_123456789"

session_id

  • "k1234_42"

shorten_segments

  • "kBE,1a2C"

scores

  • {"foo_score":0.99,"_karte_rank":5.091739579251029}

user_fields

  • {"1c3":42,"lRF":"foo","lVo":1552467212}

processor

  • null
  • "ingest"

valuesのスキーマ

タグのサンプル

以下のようなタグでfooイベントを発生させるケースを考えます。

tracker.track('foo', { bar: 42 });

valuesのサンプル

そのイベントのvaluesは、以下のようになります。

{
    "foo": {
        "bar": 42,
        "_source": "web",
        "_session": {
            "session_id#session": "k1234_42"
        },
        "date": 1528267862
    },
    "_keys": {
        "api_key": "1234567890abcdef1234567890abcdef",
        "user_id": "k1234",
        "visitor_id": "1234567890_1234567890123_123456789",
        "session_id": "k1234_42",
        "is_visitor": false
    },
    "date": 1528267862,
    "_name": "foo"
}

valuesの主なプロパティ

_keys.api_keyAPIキーイベント発生したKARTEプロジェクトのAPIキーです。
_keys.user_idユーザーID-
_keys.visitor_idビジターID-
_keys.session_idセッションID-
_keys.is_visitorビジターフラグユーザーIDがタグで連携されていない(メンバー化していない)ユーザーの場合、trueになります。
_keys.pv_idPV IDviewイベント毎でIDが生成され、そのPVに紐づくイベントには同じIDが入ります。
※フロントエンドで発生するイベントのみ含まれます
※pv_idはユーザーを超えて重複する可能性があります。JOINのキーに利用する場合はuser_idやsession_idなどと組み合わせてご利用ください
_keys.original_pv_idORIGINAL PV IDSPA（シングルページアプリケーション）でページ内遷移したときの元のページのPV_ID。
※SPAでない場合は original_pv_id = pv_id となります
{{event_name}}.{{property_name}}イベントプロパティ値タグなどで発生させたイベントの各プロパティ値が格納されます。
{{event_name}}._sourceイベントソースイベントの発生源を示す文字列です。
{{event_name}}.dateイベント送信日時イベントが送信された日時です。

📘

valuesのサンプル

_keys.api_key

  • "1234567890abcdef1234567890abcdef"

_keys.is_visitor

  • true

{{event_name}}._source

  • "web"
  • "native_app_sdk"
  • "native_app_webview"
  • "public_api"
  • "filter"

KARTEが発生させる主なイベントの主なプロパティ

KARTEが自動で発生させる主なイベントについて、valuesに含まれる主なイベントプロパティを紹介します。

なお、KARTEでよく発生するイベントの種類については、こちらのイベントリファレンスをご覧ください。

view(閲覧)イベント

field_namedisplay_namedescription
access.os.allOS情報アクセス時のOS情報です。
access.browser.allブラウザ情報アクセス時のブラウザ情報です。
access.user-agentユーザーエージェントアクセス時のユーザーエージェントです。
access.language言語設定アクセス時の言語設定です。ブラウザ設定の言語タグを小文字変換した文字列が格納されています。
access.uri.url閲覧ページURL閲覧先ページのURLです。
access.title閲覧ページタイトル閲覧先ページのタイトルです。

📘

viewイベントのvalues値サンプル

access.os.all

  • "Mac OS/10.13.4"

access.browser.all

  • "Chrome/66.0.3359.181"

access.user-agent

  • "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.181 Safari/537.36"

access.language

  • "en-us"

access.uri.url

  • "https://example.com/"

message_open, message_click, message_closeイベントなど

field_namedisplay_namedescription
message.campaign_id接客サービスID接客サービスのIDです。
message.shorten_id接客アクションID接客アクションのIDです。

※未実施の場合は、"__"+campaign_idといった形式の文字列になります。
message.is_cgコントロールグループ(CG)フラグA/Bテストで未実施グループに割り当てられた場合に、trueになります。

📘

message_*イベントのvalues値サンプル

message.campaign_id

  • "ab1234567890bc1234567890"

message.shorten_id

  • "A0b"

message.is_cg

  • false

talk_message_send, message_replyイベントなど

field_namedisplay_namedescription
app_nameTALKプラグイン名TALKメッセージ送信に利用しているTALKのプラグイン名です。
message_idTALKメッセージIDTALKメッセージのIDです。
thread_idTALKスレッドIDTALKスレッドのIDです。特にウェブチャットで、「対応中」から「対応済み」になるまでの間が1スレッドとして管理されます。

📘

talk_message_send, message_replyイベントのvalues値サンプル

app_name

  • "webchat"
  • "push_apps"
  • "sendgrid"
  • "line"
  • "twilio"

message_id

  • "ab1234567890bc1234567890"

thread_id

  • "bc1234567890cd1234567890"

dimensions.referrer_urlの定義

dimensionsの中には、dimensions.referrer_urlという項目があります。基本的にはそのセッションのreferrer_urlが入りますが、同一セッション内で複数のreferrer_urlが送られた場合は、最新のものが入るようになっています。そのような場合、そのセッションの流入元ではなくなることもあります。

karte_eventテーブルの特別な仕様

karte_eventテーブルは他のテーブルと異なる点がいくつかあります。特に、Datahubのクエリエディタではなく外部サービス（外部BigQueryなど）からクエリを実行する場合に注意が必要です。

実際のテーブル名はkarte_eventテーブルではない

  • 実体となるテーブル名は、karte_eventではなく、krt_pockyevent_v1_*です
  • 外部サービスからクエリ実行する場合、FROM句のテーブル名指定ではkrt_pockyevent_v1_*を使ってください

🚧

karte_eventテーブルとkrt_pockyevent_v1_*テーブルの違い

krt_pockyevent_v1_*テーブルをkarte_eventテーブルとして参照する場合、一部のカラムが非公開となります。karte_eventテーブルとしては参照できないがkrt_pockyevent_v1_*テーブルに存在するカラムについては、予告なく仕様が変更されることがあるため、利用しないことを推奨しています。

抽出期間を指定したクエリ実行方法

  • karte_eventテーブル（krt_pockyevent_v1_*）は、UTCベースの日付単位で分割されています
  • Datahubクエリの以下の karte_event を使った期間指定方法は、SQLの文法を拡張したDatahub独自の記法となっています
  • 外部サービスからクエリ実行する場合、WHERE句で_TABLE_SUFFIXを用いて期間指定してください
-- Datahubクエリ
SELECT * FROM {{ karte_event('20210101', '20210102') }} /* クエリv1 */
SELECT * FROM {% karte_event '20210101', '20210102' %} /* クエリv2 */

-- 外部サービスからのクエリ
SELECT * FROM `karte-data.karte_stream_{{API_KEY}}.krt_pockyevent_v1_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210102'

※ {{API_KEY}}にはプロジェクトのAPIキーを入れてください

Updated 3 months ago