Integration Specifications

This article will help you learn about how Daasity replicates data from Zendesk, limitations to the data we can extract and where the data is stored in the Zendesk schema.

Integration Overview

The Daasity Zendesk integration 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.

Integration Availability

This integration is available for:

• Enterprise

API Endpoints

The Daasity Zendesk extractor is built based on this Zendesk API documentation. The following endpoints are used by Daasity to replicate data from Zendesk:

• Tickets

• Ticket Comments

• Ticket Fields

• Ticket Tags

• Ticket Metrics

• Users

• Organizations

• Brands

Entity Relationship Diagram (ERD)

Click here to view the ERD for the Daasity Zendesk integration illustrating the different tables and keys to join across tables.

Zendesk Schema

The Daasity Zendesk extractor creates these tables using the endpoints and replication methods listed. The data is mapped from source API endpoint to the table based on the mapping logic outlined in each table.

• Tickets

• Ticket Comments

• Ticket Custom Fields

• Ticket Tags

• Ticket Metrics

• Users

• Organizations

• Organization Domain Names

• Brands

Tickets

• Endpoint: Tickets

• Update Method: UPSERT

• Table Name: [zendesk.tickets]

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::score	satisfaction_rating_score
via::channel	via_channel
via::source::from::address	via_source_address
via::source::from::formatted_phone	via_source_phone
via::source::From::name	via_source_name_from
via::source::from::profile_url	via_source_profile_url
satisfaction_rating::comment	satisfaction_rating_comment
brand_id	brand_id
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

• Endpoint: Ticket Comments

• Update Method: UPSERT

• Table Name: [zendesk.ticket_comments]

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::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::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

Ticket Custom Fields

• Endpoint: Ticket Fields

• Update Method: UPSERT

• Table Name: [zendesk.ticket_custom_fields]

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

• Endpoint: Ticket Tags

• Update Method: UPSERT

• Table Name: [zendesk.ticket_tags]

JSON Element	Database Column
MD5(ticket_id + ':' + t)	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

• Endpoint: Tickets Metrics

• Update Method: UPSERT

• Table Name: [zendesk.ticket_metrics]

JSON Element	Database Column
MD5(ticket_id + ':' + id)	key
ticket_metric_id	id
ticket_id	ticket_id
reopens	reopens
replies	replies
assignee_updated_at	assignee_updated_at
requester_updated_at	requester_updated_at
status_updated_at	status_updated_at
initially_assigned_at	initially_assigned_at
assigned_at	assigned_at
solved_at	solved_at
latest_comment_added_at	latest_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::calendar	first_resolution_time_in_minutes__calendar
first_resolution_time_in_minutes::business	first_resolution_time_in_minutes__business
full_resolution_time_in_minutes::calendar	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 D	__synced_at

Users

• Endpoint: Users

• Update Method: UPSERT

• Table Name: [zendesk.users]

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

Organizations

• Endpoint: Organizations

• Update Method: UPSERT

• Table Name: [zendesk.organizations]

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

• Endpoint: Organizations

• Update Method: UPSERT

• Table Name: [zendesk.organization_domain_names]

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

Brands

• Endpoint: Brands

• Update Method: UPSERT

• Table Name: [zendesk.brands]

JSON Element	Database Column
id	id
name	name
brand_url	brand_url
active	is_active
subdomain	subdomain
created_at	created_at
updated_at	updated_at
Daasity: unique id for the account	_account_id
Daasity: timestamp when loaded into DB	__synced_at