This article will help you understand how Daasity retrieves data from Zendesk, how to setup the data feed from Zendesk, limitations on the current data feed and how data is stored in the Zendesk schema
Key Topics
Click on the links below to take you to the section where you can learn more about this View and where it is used and how it is created
- Extraction Overview
- Entity Relationship Diagram (ERD)
- Tickets Table
- Ticket Custom Fields Table
- Ticket Tags Table
- Ticket Metrics Table
- Ticket Comments Table
- Users Table
- Brands Table
- Organization Domain Table
- Release Updates
- Related Resources
Integration Availability
This integration is available for:
- Pro
ZENDESK DATA AND DASHBOARDS:
Please note that at this time the Zendesk integration requires some additional work for setup, and some additional work to setup the customer service dashboard.
Extraction Overview
The Daasity<>Zendesk extractor allows merchants to glean broad and deep insights into Zendesk support tickets, including ticket tags and comments as well as response and resolution times. Untransformed Zendesk data gathered through the extractor is then joined together in a stand-alone model that gives merchants the ability to explore data related to tickets, users, tags, etc. That model is then used to populate the out-of-the-box Zendesk dashboard.
This document provides context on what kind of data is being gathered through this extractor, which endpoints that data is coming from, and how the extracted tables relate to each other.
This API extractor was built referencing the Zendesk API documentation found here: https://developer.zendesk.com/api-reference/, using the following supported endpoints:
|
Users |
/api/v2/incremental/users |
|
Organizations |
/api/v2/search&='organization' |
|
Organization Domain Names |
/api/v2/search&='organization' |
|
Tickets |
/api/v2/incremental/tickets |
|
Ticket Comments |
/api/v2/incremental/tickets |
|
Ticket Metrics |
/api/v2/incremental/tickets |
|
Ticket Custom Fields |
/api/v2/incremental/tickets |
|
Ticket Tags |
/api/v2/incremental/tickets |
|
Brands |
/api/v2/brands |
Entity Relationship Diagram (ERD)
The diagram below outlines the relationship between the base or “driving” table, the tickets table, and all the secondary tables that are being joined to the base table to create the Zendesk looker model. Hover over the embedded diagram to reveal controls to zoom and scroll.
Tickets Table
This table is generated from the API Endpoint /api/v2/incremental/tickets using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
id |
id |
|
external_id |
external_id |
|
type |
type |
|
subject |
subject |
|
priority |
priority |
|
status |
status |
|
recipient |
recipient |
|
requester_id |
requester_id |
|
submitter_id |
submitter_id |
|
assignee_id |
assignee_id |
|
organization_id |
organization_id |
|
due_at |
due_at |
|
satisfaction_rating |
satisfaction_rating_score |
|
satisfaction_rating::comment |
satisfaction_rating_comment |
|
brand_id |
brand_id |
|
via::channel |
via_channel |
|
via::source_address |
via_source_address |
|
via::source_phone |
via_source_phone |
|
via::source_name_from |
via_source_name_from |
|
via::source_profile_url |
via_source_profile_url |
|
created_at |
created_at |
|
updated_at |
updated_at |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Ticket Custom Fields Table
This table is generated from the API Endpoint /api/v2/incremental/tickets using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
MD5(ticket_id || ':' || id) |
key |
|
ticket_id |
ticket_id |
|
id |
id |
|
value |
value |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Ticket Tags Table
This table is generated from the API Endpoint /api/v2/incremental/tickets using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
MD5(ticket_id || ':' || tag) |
key |
|
ticket_id |
ticket_id |
|
t |
tag_name |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Ticket Metrics Table
This table is generated from the API Endpoint /api/v2/incremental/tickets using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
MD5(ticket_id || ':' || id) |
key |
|
ticket_id |
ticket_id |
|
id |
id |
|
reopens |
reopens |
|
replies |
replies |
|
assignee_updated_at |
assignee_updated_at |
|
requester_updated_at |
requester_updated_at |
|
status_updated_at |
status_updated_at |
|
initially_updated_at |
initially_updated_at |
|
assigned_at |
assigned_at |
|
solved_at |
solved_at |
|
last_comment_added_at |
last_comment_added_at |
|
reply_time_in_minutes::calendar |
reply_time_in_minutes__calendar |
|
reply_time_in_minutes::business |
reply_time_in_minutes__business |
|
first_resolution_time_in_minutes::calender |
first_resolution_time_in_minutes__calendar |
|
first_resolution_time_in_minutes::business |
first_resolution_time_in_minutes__business |
|
full_resolution_time_in_minutes::calender |
full_resolution_time_in_minutes__calendar |
|
full_resolution_time_in_minutes::business |
full_resolution_time_in_minutes__business |
|
agent_wait_time_in_minutes::calendar |
agent_wait_time_in_minutes__calendar |
|
agent_wait_time_in_minutes::business |
agent_wait_time_in_minutes__business |
|
requester_wait_time_in_minutes::calendar |
requester_wait_time_in_minutes__calendar |
|
requester_wait_time_in_minutes::business |
requester_wait_time_in_minutes__business |
|
on_hold_time_in_minutes::calendar |
on_hold_time_in_minutes__calendar |
|
on_hold_time_in_minutes::business |
on_hold_time_in_minutes__business |
|
created_at |
created_at |
|
updated_at |
updated_at |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Ticket Comments Table
This table is generated from the API Endpoint /api/v2/incremental/tickets using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
ticket_id |
ticket_id |
|
ticket_id::id |
ticket_comment_id |
|
comment::type |
type |
|
comment::author_id |
author_id |
|
comment::plain_body::body |
body |
|
comment::via::channel |
channel |
|
comment::data::location |
voice_location |
|
comment::data::transcription_text |
voice_transcription_text |
|
comment::data::answered_by_id |
voice_answered_by_id |
|
comment::via::source::rel |
via_source_rel |
|
comment::via::source::from::phone |
via_source_from_phone |
|
comment::via::source::from::name |
via_source_from_name |
|
comment::via::source::from::email_address |
via_source_from_email_address |
|
comment::via::source::to::phone |
via_source_to_phone |
|
comment::via::source::to::name |
via_source_to_name |
|
comment::via::source::to::email_address |
via_source_to_email_address |
|
comment::metadata::system::location |
location |
|
comment::metadata::system::latitude |
latitude |
|
comment::metadata::system::longitude |
longitude |
|
comment::metadata::system::client |
web_client |
|
comment::metadata::system::ip_address |
web_ip_address |
|
comment::created_at |
created_at |
|
MD5(ticket_id || ':' || id) |
key |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Users Table
This table is generated from the API Endpoint /api/v2/incremental/users using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
id |
id |
|
name |
name |
|
role |
role |
|
|
|
|
phone |
phone |
|
time_zone |
time_zone |
|
last_login_at |
last_login_at |
|
active |
active |
|
created_at |
created_at |
|
updated_at |
updated_at |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Brands Table
This table is generated from the API Endpoint /api/v2/brands using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
id |
id |
|
external_id |
external_id |
|
name |
name |
|
created_at |
created_at |
|
updated_at |
updated_at |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Organization Domain Names Table
This table is generated from the API Endpoint /api/v2/search&='organization' using a daily UPSERT refresh.
|
JSON Element |
Database Column |
|
MD5(organization_id || ':' || subdomain) |
key |
|
organization_id |
organization_id |
|
domain_name |
domain_name |
|
Daasity: unique id for the account |
_account_id |
|
Daasity: timestamp when loaded into DB |
__synced_at |
Release Updates
This section will highlight the date of a release that impacted the extractor and a brief summary of the changes in the release. There will be an incoming link from the release and should highlight the changes that were made with the release date
- 2021/05/04 – New API endpoint to extract [table] was added
Related Resources