Zendeskとは?
Zendeskは、カスタマサービスのためのクラウドサービスです。https://www.zendesk.com/
チケットベースで、お客さんとの問い合わせ管理やヘルプセンターなどを提供することができるサービスです。
問い合わせ分析をする際には、基本的にはZendeskに組み込みのレポートがあるのですが、やはり他の情報と連携したり、いつも使い慣れたSQLで分析をしたいというニーズもあります。
図:Defaultのreport画面
そこで、embulk-input-zendeskがリリースされました。
embulk-input-zendeskを試す
Pluginはこちら。
https://github.com/treasure-data/embulk-input-zendesk
現在サポートしているデータは下記になります。
- tickets
- ticket_events
- users
- organizations
- ticket_fields
- ticket_forms
また、すべてのデータは時間をベースにフィルタリングして取得可能です。
バージョン情報
- Embulk v0.8.1
- embulk-input-zendesk (0.1.1)
Zendesk Pluginのインストール
$ embulk gem install embulk-input-zendesk
Guessる
$ embulk guess ./seed.yml -o zendesk.yml
seed.yml
in:
type: zendesk
login_url: https://<OWN_DOMAIN>.zendesk.com
auth_method: token # tickets, ticket_events, users, organizations, ticket_fields or ticket_formsを選択可能
target: tickets
username: <ZENDESK_EMAIL>
token: <ZENDESK_TOKEN>
start_time: 2015-01-01 00:00:00+0000
各targetの結果
下記コマンドでPreviewをします。
$ embulk preview zendesk.yml
チケット情報(tickets)
zendesk.yml
in:
type: zendesk
login_url: https://<OWN_DOMAIN>.zendesk.com
auth_method: token
target: tickets
username: <ZENDESK_EMAIL>
token: <ZENDESK_TOKEN>
start_time: 2015-01-01 00:00:00+0000
columns:
- {name: url, type: string}
- {name: id, type: long}
- {name: external_id, type: string}
- {name: created_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: updated_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: type, type: string}
- {name: subject, type: string}
- {name: raw_subject, type: string}
- {name: description, type: string}
- {name: priority, type: string}
- {name: status, type: string}
- {name: recipient, type: string}
- {name: requester_id, type: long}
- {name: submitter_id, type: long}
- {name: assignee_id, type: long}
- {name: organization_id, type: long}
- {name: group_id, type: long}
- {name: forum_topic_id, type: string}
- {name: problem_id, type: string}
- {name: has_incidents, type: boolean}
- {name: due_at, type: string}
- {name: satisfaction_rating, type: string}
- {name: ticket_form_id, type: long}
- {name: brand_id, type: long}
+------------------------------------------------------------------+---------+--------------------+-------------------------+-------------------------+-------------+--------------------------------------------------------+--------------------------------------------------------+--------------------------------------------------------------------+-----------------+---------------+------------------+-------------------+-------------------+------------------+----------------------+---------------+-----------------------+-------------------+-----------------------+---------------+----------------------------+---------------------+---------------+
| url:string | id:long | external_id:string | created_at:timestamp | updated_at:timestamp | type:string | subject:string | raw_subject:string | description:string | priority:string | status:string | recipient:string | requester_id:long | submitter_id:long | assignee_id:long | organization_id:long | group_id:long | forum_topic_id:string | problem_id:string | has_incidents:boolean | due_at:string | satisfaction_rating:string | ticket_form_id:long | brand_id:long |
+------------------------------------------------------------------+---------+--------------------+-------------------------+-------------------------+-------------+--------------------------------------------------------+--------------------------------------------------------+--------------------------------------------------------------------+-----------------+---------------+------------------+-------------------+-------------------+------------------+----------------------+---------------+-----------------------+-------------------+-----------------------+---------------+----------------------------+---------------------+---------------+
| https://<OWN_DOMAIN>.zendesk.com/api/v2/tickets/1.json | 1 | | 2015-08-11 02:36:15 UTC | 2015-08-11 02:38:15 UTC | incident | This is a sample ticket requested and submitted by you | This is a sample ticket requested and submitted by you | This is the first comment. Feel free to delete this sample ticket. | high | pending | | 1,194,094,177 | 1,194,094,177 | 1,194,094,177 | 260,937,737 | 26,191,487 | | | false | | | | 458,947 |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/tickets/2.json | 2 | | 2016-01-26 09:09:08 UTC | 2016-01-26 09:09:08 UTC | question | sample subject | sample subject | Contesnt | normal | open | | 1,194,094,257 | 1,194,094,257 | 1,194,094,257 | | 26,191,487 | | | false | | | 87,517 | 458,947 |
+------------------------------------------------------------------+---------+--------------------+-------------------------+-------------------------+-------------+--------------------------------------------------------+--------------------------------------------------------+--------------------------------------------------------------------+-----------------+---------------+------------------+-------------------+-------------------+------------------+----------------------+---------------+-----------------------+-------------------+-----------------------+---------------+----------------------------+---------------------+---------------+
チケットのイベント情報(ticket_events)
注意点としては、実際の各コメント情報は未対応です。
in:
type: zendesk
login_url: https://<OWN_DOMAIN>.zendesk.com
auth_method: token
target: tickets
username: <ZENDESK_EMAIL>
token: <ZENDESK_TOKEN>
start_time: 2015-01-01 00:00:00+0000
columns:
- {name: id, type: long}
- {name: ticket_id, type: long}
- {name: timestamp, type: long}
- {name: created_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: updater_id, type: long}
- {name: via, type: string}
$ embulk preview ticket_events.yml
2016-01-26 18:29:46.886 +0900: Embulk v0.8.1
2016-01-26 18:29:49.895 +0900 [INFO] (preview): Loaded plugin embulk-input-zendesk (0.1.1)
+----------------+----------------+----------------+-------------------------+-----------------+---------------+
| id:long | ticket_id:long | timestamp:long | created_at:timestamp | updater_id:long | via:string |
+----------------+----------------+----------------+-------------------------+-----------------+---------------+
| 52,958,183,967 | 1 | 1,439,260,695 | 2015-08-11 02:38:15 UTC | 1,194,094,177 | Sample Ticket |
| 52,958,184,327 | 1 | 1,439,260,695 | 2015-08-11 02:38:15 UTC | 1,194,094,177 | Sample Ticket |
| 52,958,184,407 | 1 | 1,439,260,695 | 2015-08-11 02:38:15 UTC | 1,194,094,177 | Sample Ticket |
| 81,875,948,387 | 2 | 1,453,799,348 | 2016-01-26 09:09:08 UTC | 1,194,094,257 | Web form |
| 82,191,366,418 | 2 | 1,453,799,916 | 2016-01-26 09:18:36 UTC | 1,194,094,257 | Web form |
| 82,191,383,628 | 1 | 1,453,799,926 | 2016-01-26 09:18:46 UTC | -1 | Web form |
+----------------+----------------+----------------+-------------------------+-----------------+---------------+
ユーザ情報(users)
in:
type: zendesk
login_url: https://<OWN_DOMAIN>.zendesk.com
auth_method: token
target: tickets
username: <ZENDESK_EMAIL>
token: <ZENDESK_TOKEN>
start_time: 2015-01-01 00:00:00+0000
columns:
- {name: id, type: long}
- {name: url, type: string}
- {name: name, type: string}
- {name: email, type: string}
- {name: created_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: updated_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: time_zone, type: string}
- {name: phone, type: string}
- {name: photo, type: string}
- {name: locale_id, type: long}
- {name: locale, type: string}
- {name: organization_id, type: long}
- {name: role, type: string}
- {name: verified, type: boolean}
- {name: external_id, type: long}
- {name: alias, type: string}
- {name: active, type: boolean}
- {name: shared, type: boolean}
- {name: shared_agent, type: boolean}
- {name: last_login_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: two_factor_auth_enabled, type: string}
- {name: signature, type: string}
- {name: details, type: string}
- {name: notes, type: string}
- {name: custom_role_id, type: string}
- {name: moderator, type: boolean}
- {name: ticket_restriction, type: string}
- {name: only_private_comments, type: boolean}
- {name: restricted_agent, type: boolean}
- {name: suspended, type: boolean}
- {name: chat_only, type: boolean}
+---------------+-------------------------------------------------------------------------+-------------------+-----------------------------+-------------------------+-------------------------+----------------------------+--------------+--------------+----------------+---------------+----------------------+-------------+------------------+------------------+--------------+----------------+----------------+----------------------+-------------------------+--------------------------------+------------------+----------------+--------------+-----------------------+-------------------+---------------------------+-------------------------------+--------------------------+-------------------+-------------------+
| id:long | url:string | name:string | email:string | created_at:timestamp | updated_at:timestamp | time_zone:string | phone:string | photo:string | locale_id:long | locale:string | organization_id:long | role:string | verified:boolean | external_id:long | alias:string | active:boolean | shared:boolean | shared_agent:boolean | last_login_at:timestamp | two_factor_auth_enabled:string | signature:string | details:string | notes:string | custom_role_id:string | moderator:boolean | ticket_restriction:string | only_private_comments:boolean | restricted_agent:boolean | suspended:boolean | chat_only:boolean |
+---------------+-------------------------------------------------------------------------+-------------------+-----------------------------+-------------------------+-------------------------+----------------------------+--------------+--------------+----------------+---------------+----------------------+-------------+------------------+------------------+--------------+----------------+----------------+----------------------+-------------------------+--------------------------------+------------------+----------------+--------------+-----------------------+-------------------+---------------------------+-------------------------------+--------------------------+-------------------+-------------------+
| 1,194,094,177 | https://<OWN_DOMAIN>.zendesk.com/api/v2/users/1194094177.json | Kazuki Ohta | <email> | 2015-08-11 02:38:14 UTC | 2016-01-26 09:18:46 UTC | Pacific Time (US & Canada) | | | 1 | en-US | 2,293,302,588 | admin | true | 1 | | true | false | false | 2015-11-23 00:12:35 UTC | | | | | | true | | false | false | false | false |
| 1,194,094,257 | https://<OWN_DOMAIN>.zendesk.com/api/v2/users/1194094257.json | Toru Takahashi | <email> | 2015-08-11 02:38:16 UTC | 2016-01-26 09:29:51 UTC | Tokyo | | | 1 | en-US | 2,293,302,588 | admin | true | 944 | | true | false | false | 2016-01-26 09:29:51 UTC | | | | | | true | | false | false | false | false |
+---------------+-------------------------------------------------------------------------+-------------------+-----------------------------+-------------------------+-------------------------+----------------------------+--------------+--------------+----------------+---------------+----------------------+-------------+------------------+------------------+--------------+----------------+----------------+----------------------+-------------------------+--------------------------------+------------------+----------------+--------------+-----------------------+-------------------+---------------------------+-------------------------------+--------------------------+-------------------+-------------------+
組織情報(organizations)
in:
type: zendesk
login_url: https://<OWN_DOMAIN>.zendesk.com
auth_method: token
target: tickets
username: <ZENDESK_EMAIL>
token: <ZENDESK_TOKEN>
start_time: 2015-01-01 00:00:00+0000
columns:
- {name: url, type: string}
- {name: id, type: long}
- {name: name, type: string}
- {name: shared_tickets, type: boolean}
- {name: shared_comments, type: boolean}
- {name: external_id, type: string}
- {name: created_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: updated_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: details, type: string}
- {name: notes, type: string}
- {name: group_id, type: string}
+---------------------------------------------------------------------------------+---------------+-----------------------------+------------------------+-------------------------+--------------------+-------------------------+-------------------------+----------------+--------------+-----------------+
| url:string | id:long | name:string | shared_tickets:boolean | shared_comments:boolean | external_id:string | created_at:timestamp | updated_at:timestamp | details:string | notes:string | group_id:string |
+---------------------------------------------------------------------------------+---------------+-----------------------------+------------------------+-------------------------+--------------------+-------------------------+-------------------------+----------------+--------------+-----------------+
| https://<OWN_DOMAIN>.zendesk.com/api/v2/organizations/260937737.json | 260,937,737 | Treasure Data, Inc. SANDBOX | false | false | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | | | |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/organizations/2293302588.json | 2,293,302,588 | TreasureData | false | false | | 2016-01-26 09:18:35 UTC | 2016-01-26 09:18:35 UTC | | | |
+---------------------------------------------------------------------------------+---------------+-----------------------------+------------------------+-------------------------+--------------------+-------------------------+-------------------------+----------------+--------------+-----------------+
チケットフィールド情報(ticket_fields)
in:
type: zendesk
login_url: https://<OWN_DOMAIN>.zendesk.com
auth_method: token
target: tickets
username: <ZENDESK_EMAIL>
token: <ZENDESK_TOKEN>
start_time: 2015-01-01 00:00:00+0000
columns:
- {name: url, type: string}
- {name: id, type: string}
- {name: type, type: string}
- {name: title, type: string}
- {name: raw_title, type: string}
- {name: description, type: string}
- {name: raw_description, type: string}
- {name: position, type: long}
- {name: active, type: boolean}
- {name: required, type: boolean}
- {name: collapsed_for_agents, type: boolean}
- {name: regexp_for_validation, type: string}
- {name: title_in_portal, type: string}
- {name: raw_title_in_portal, type: string}
- {name: visible_in_portal, type: boolean}
- {name: editable_in_portal, type: boolean}
- {name: required_in_portal, type: boolean}
- {name: tag, type: string}
- {name: created_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: updated_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: removable, type: boolean}
+-------------------------------------------------------------------------------+-----------+-------------+-----------------------------+-----------------------------+-----------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+---------------+----------------+------------------+------------------------------+------------------------------+------------------------+----------------------------+---------------------------+----------------------------+----------------------------+------------+-------------------------+-------------------------+-------------------+
| url:string | id:string | type:string | title:string | raw_title:string | description:string | raw_description:string | position:long | active:boolean | required:boolean | collapsed_for_agents:boolean | regexp_for_validation:string | title_in_portal:string | raw_title_in_portal:string | visible_in_portal:boolean | editable_in_portal:boolean | required_in_portal:boolean | tag:string | created_at:timestamp | updated_at:timestamp | removable:boolean |
+-------------------------------------------------------------------------------+-----------+-------------+-----------------------------+-----------------------------+-----------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+---------------+----------------+------------------+------------------------------+------------------------------+------------------------+----------------------------+---------------------------+----------------------------+----------------------------+------------+-------------------------+-------------------------+-------------------+
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_fields/27260667.json | 27260667 | subject | Subject | Subject | | | 1 | true | false | false | | Subject | Subject | true | true | true | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | false |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_fields/27260677.json | 27260677 | description | Description | Description | Please enter the details of your request. A member of our support staff will respond as soon as possible. | Please enter the details of your request. A member of our support staff will respond as soon as possible. | 2 | true | false | false | | Description | Description | true | true | true | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | false |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_fields/27260687.json | 27260687 | status | Status | Status | Request status | Request status | 3 | true | false | false | | Status | Status | false | false | false | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | false |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_fields/27260697.json | 27260697 | tickettype | Type | Type | Request type | Request type | 4 | true | false | false | | Type | Type | false | false | false | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | true |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_fields/27260707.json | 27260707 | priority | Priority | Priority | Request priority | Request priority | 5 | true | false | false | | Priority | Priority | true | false | false | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | true |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_fields/27260717.json | 27260717 | group | Group | Group | Request group | Request group | 6 | true | false | false | | Group | Group | false | false | false | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | false |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_fields/27260727.json | 27260727 | assignee | Assignee | Assignee | Agent assigned to your request | Agent assigned to your request | 7 | true | true | false | | Assignee | Assignee | true | false | false | | 2015-08-11 02:38:14 UTC | 2015-08-11 02:38:14 UTC | false |
+-------------------------------------------------------------------------------+-----------+-------------+-----------------------------+-----------------------------+-----------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------+---------------+----------------+------------------+------------------------------+------------------------------+------------------------+----------------------------+---------------------------+----------------------------+----------------------------+------------+-------------------------+-------------------------+-------------------+
フォーム情報(ticket_forms)
in:
type: zendesk
login_url: https://<OWN_DOMAIN>.zendesk.com
auth_method: token
target: tickets
username: <ZENDESK_EMAIL>
token: <ZENDESK_TOKEN>
start_time: 2015-01-01 00:00:00+0000
columns:
- {name: url, type: string}
- {name: id, type: long}
- {name: name, type: string}
- {name: raw_name, type: string}
- {name: display_name, type: string}
- {name: raw_display_name, type: string}
- {name: end_user_visible, type: boolean}
- {name: position, type: long}
- {name: active, type: boolean}
- {name: default, type: boolean}
- {name: created_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: updated_at, type: timestamp, format: '%Y-%m-%dT%H:%M:%S%z'}
- {name: in_all_brands, type: boolean}
+----------------------------------------------------------------------------+---------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------------+---------------+----------------+-----------------+-------------------------+-------------------------+-----------------------+
| url:string | id:long | name:string | raw_name:string | display_name:string | raw_display_name:string | end_user_visible:boolean | position:long | active:boolean | default:boolean | created_at:timestamp | updated_at:timestamp | in_all_brands:boolean |
+----------------------------------------------------------------------------+---------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------------+---------------+----------------+-----------------+-------------------------+-------------------------+-----------------------+
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_forms/87517.json | 87,517 | treasuredata sandbox support | treasuredata sandbox support | treasuredata sandbox support | treasuredata sandbox support | true | 1 | true | true | 2015-08-11 02:38:17 UTC | 2015-11-22 11:37:21 UTC | true |
| https://<OWN_DOMAIN>.zendesk.com/api/v2/ticket_forms/121427.json | 121,427 | fluentd sandbox support | fluentd sandbox support | fluentd sandbox support | fluentd sandbox support | true | 2 | true | false | 2015-11-22 01:43:00 UTC | 2015-11-22 11:37:14 UTC | true |
+----------------------------------------------------------------------------+---------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------------+---------------+----------------+-----------------+-------------------------+-------------------------+-----------------------+
未対応のデータ
EmbulkにJsonサポートが入りましたが、Zendeskでのjsonで値を返す系のデータについては未対応になっています（custom fields, tags, etc）。
また、基本的にはIDベースでいろんなデータをJoinする必要があるため、
例えば、TreasureDataにインポートを一旦して、そこでJoinした後にElasticsearchにエクスポートするなどといったことができると便利だと思います。
おわり
Zendeskはサポートサービスとして非常にメジャーでこうしたツールのデータをエクスポートして、顧客の問い合わせ分析などに活かしてきていきたいですね。