Zendesk Extractor Documentation

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

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 

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 

email 

email 

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