embulk-input-zendeskでZendeskのサポート情報を取得する

# Zendeskとは?

Zendeskは、カスタマサービスのためのクラウドサービスです。https://www.zendesk.com/
チケットベースで、お客さんとの問い合わせ管理やヘルプセンターなどを提供することができるサービスです。
問い合わせ分析をする際には、基本的にはZendeskに組み込みのレポートがあるのですが、やはり他の情報と連携したり、いつも使い慣れたSQLで分析をしたいというニーズもあります。

![image](https://i.gyazo.com/9861603f109c09d4283ec4a46fd9160f.png)
図:Defaultのreport画面



そこで、embulk-input-zendeskがリリースされました。

![image](http://www.janium.com/wp-content/uploads/2015/08/zendesk1.png)


# 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)


[![https://gyazo.com/431f80e37b305440dc6f208b9148daf1](https://i.gyazo.com/431f80e37b305440dc6f208b9148daf1.png)](https://gyazo.com/431f80e37b305440dc6f208b9148daf1)

```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)

注意点としては、実際の各コメント情報は未対応です。

[![https://gyazo.com/a6774682357d9ab67a5f3c1eca30f456](https://i.gyazo.com/a6774682357d9ab67a5f3c1eca30f456.png)](https://gyazo.com/a6774682357d9ab67a5f3c1eca30f456)

```
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)

[![https://gyazo.com/10b1d077c68a4b31acd3b10f1bb28c0e](https://i.gyazo.com/10b1d077c68a4b31acd3b10f1bb28c0e.png)](https://gyazo.com/10b1d077c68a4b31acd3b10f1bb28c0e)

```
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)

[![https://gyazo.com/4f74211bffc9d0213d445083592eb9a7](https://i.gyazo.com/4f74211bffc9d0213d445083592eb9a7.png)](https://gyazo.com/4f74211bffc9d0213d445083592eb9a7)

```
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)

[![https://gyazo.com/415957da8ae67947a94d9e15a0caa05a](https://i.gyazo.com/415957da8ae67947a94d9e15a0caa05a.png)](https://gyazo.com/415957da8ae67947a94d9e15a0caa05a)

```
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)


[![https://gyazo.com/971a22823796f0f018ee573983ef99fd](https://i.gyazo.com/971a22823796f0f018ee573983ef99fd.png)](https://gyazo.com/971a22823796f0f018ee573983ef99fd)


```
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はサポートサービスとして非常にメジャーでこうしたツールのデータをエクスポートして、顧客の問い合わせ分析などに活かしてきていきたいですね。