Understanding Your Zendesk Schema

The purpose of this tutorial is to familiarize you with the default Zendesk schema found in Stitch. At the conclusion of the tutorial, you’ll be able to effectively use Zendesk for your data analytics.

A schema is a collection of objects within a database, typically consisting of tables (defined below) and the relationships between those tables.

Note: The primary key in every table is denoted by 🔑

audits

This table contains information about the activity associated with a ticket.

AttributeDescription
🔑Audit ID (id)Automatically assigned when creating audits
ticket_idThe ID of the associated ticket
metadataMetadata for the audit, custom and system data
viaThis object explains how this audit was created
created_atThe time the audit was created
author_idThe user who created the audit
eventsAn array of the events that happened in this audit

zendesk_group_memberships

This table contains information about the groups your Zendesk agents are members of.

AttributeDescription
🔑Group Membership ID (id)Automatically assigned upon creation
user_idThe id of an agent
group_idThe id of a group
defaultIf true, tickets assigned directly to the agent will assume this membership’s group.
created_atThe time the membership was created
updated_atThe time of the last update of the membership

zendesk_groups

When support requests arrive in Zendesk Support, they can be assigned to a Group. This table contains information about these groups — which is how agents are organized — in your Zendesk account.

AttributeDescription
🔑Group ID (id)Automatically assigned when creating groups
urlThe API url of this group
nameThe name of the group
deletedDeleted groups get marked as such
created_atThe time the group was created
updated_atThe time of the last update of the group

zendesk_macros

This table contains information about the macros in your Zendesk account. Macros are actions defined by you that modify the values of a ticket’s fields.

AttributeDescription
🔑Macro ID (id)Automatically assigned when created
actionsAn object describing what the macro will do
activeUseful for determining if the macro should be displayed
descriptionThe description of the macro
positionThe position of the macro
restrictionWho may access this macro. Will be null when everyone in the account can access it.
titleThe title of the macro
created_atThe time the macro was created
updated_atThe time of the last update of the macro

organizations

In Zendesk Support, customers (end-users) can be segmented into organizations. This table contains information about these organizations in Zendesk.

AttributeDescription
🔑Organization ID (id)Automatically assigned when the organization is created
urlThe API url of this organization
external_idA unique external id to associate organizations to an external record
nameA unique name for the organization
created_atThe time the organization was created
updated_atThe time of the last update of the organization
domain_namesAn array of domain names associated with this organization
detailsAny details about the organization, such as the address
notesAny notes you have about the organization
group_idNew tickets from users in this organization are automatically put in this group
shared_ticketsEnd users in this organization are able to see each other’s tickets
shared_commentsEnd users in this organization are able to see each other’s comments on tickets
tagsThe tags of the organization
organization_fieldsCustom fields for this organization

zendesk_tags

This table contains a list of tags in your Zendesk account.

AttributeDescription
🔑Tag Name (name)An array of tag names
countNumber of all tickets with a certain tag

tickets

Tickets are the means through which your end users (customers) communicate with agents in Zendesk Support. This table contains information about these tickets in Zendesk.

AttributeDescription
🔑Ticket ID (id)Automatically assigned when the ticket is created
urlThe API url of this ticket
external_idAn id you can use to link Zendesk Support tickets to local records
typeThe type of this ticket. Possible values: “problem”, “incident”, “question” or “task”
subjectThe value of the subject field for this ticket
raw_subjectThe dynamic content placeholder, if present, or the “subject” value, if not. See Dynamic Content
priorityThe urgency with which the ticket should be addressed. Possible values: “urgent”, “high”, “normal”, “low”
statusThe state of the ticket. Possible values: “new”, “open”, “pending”, “hold”, “solved”, “closed”
recipientThe original recipient e-mail address of the ticket
requester_idThe user who requested this ticket
submitter_idThe user who submitted the ticket. The submitter always becomes the author of the first comment on the ticket
assignee_idThe agent currently assigned to the ticket
organization_idThe organization of the requester. You can only specify the ID of an organization associated with the requester.
group_idThe group this ticket is assigned to
collaborator_idsThe ids of users currently cc’ed on the ticket
forum_topic_idThe topic this ticket originated from, if any
problem_idFor tickets of type “incident”, the ID of the problem the incident is linked to
has_incidentsIs true, if this ticket has been marked as a problem, false otherwise
due_atIf this is a ticket of type “task” it has a due date. Due date uses ISO 8601 format.
tagsThe array of tags applied to this ticket
viaThis object explains how the ticket was created
custom_fieldsCustom fields for the ticket.
satisfaction_ratingThe satisfaction rating of the ticket, if it exists, or the state of satisfaction, ‘offered’ or ‘unoffered’
sharing_agreement_idsThe ids of the sharing agreements used for this ticket
followup_idsThe ids of the follow-ups created from this ticket. Ids are only visible once the ticket is closed
ticket_form_idEnterprise only. The id of the ticket form to render for the ticket
brand_idEnterprise only. The id of the brand this ticket is associated with
allow_channelbackIs false, if channelback is disabled, true otherwise. Only applicable for channels framework ticket
is_publicIs true, if any comments are public, false otherwise
created_atWhen this record was created
updated_atWhen this record last got updated

ticket_fields

This table contains information about the basic text and custom ticket fields in your Zendesk account.

AttributeDescription
🔑Ticket Field ID (id)Automatically assigned upon creation
urlThe URL for this resource
typeThe type of the ticket field: “checkbox”, “date”, “decimal”, “integer”, “regexp”, “tagger”, “text”, or “textarea”. *Type is not editable once created.
titleThe title of the ticket field
raw_titleThe dynamic content placeholder, if present, or the “title” value, if not.
descriptionThe description of the purpose of this ticket field, shown to users
raw_descriptionThe dynamic content placeholder, if present, or the “description” value, if not.
positionA relative position for the ticket fields that determines the order of ticket fields on a ticket. Note that positions 0 to 7 are reserved for system fields.
activeWhether this field is available
requiredIf it’s required for this field to have a value when updated by agents
collapsed_for_agentsIf this field should be shown to agents by default or be hidden alongside infrequently used fields. Classic interface only
regxp_for_validationRegular expression field only. The validation pattern for a field value to be deemed valid.
title_in_portalThe title of the ticket field when shown to end users
raw_title_in_portalThe dynamic content placeholder, if present, or the “title_in_portal” value, if not.
visible_in_portalWhether this field is available to end users
editable_in_portalWhether this field is editable by end users
required_in_portalIf it’s required for this field to have a value when updated by end users
tagA tag value to set for checkbox fields when checked
created_atThe time the ticket field was created
updated_atThe time of the last update of the ticket field
system_field_optionsPresented for a ticket field of type “tickettype”, “priority” or “status”
custom_field_optionsRequired and presented for a ticket field of type “tagger”
removableIf this field is not a system basic field that must be present for all tickets on the account

zendesk_ticket_metrics

This table contains information about the metrics associated with Zendesk tickets. This table will NOT include records for archived tickets.

AttributeDescription
🔑Ticket Metric ID (id)Automatically assigned
ticket_idId of the associated ticket
urlThe API url of this ticket metric
group_stationsNumber of groups this ticket passed through
assignee_stationsNumber of assignees this ticket had
reopensTotal number of times the ticket was reopened
repliesTotal number of times ticket was replied to
assignee_updated_atWhen the assignee last updated the ticket
requester_updated_atWhen the requester last updated the ticket
status_updated_atWhen the status was last updated
intitially_updated_atWhen the ticket was initially assigned
assigned_atWhen the ticket was last assigned
solved_atWhen the ticket was solved
latest_comment_added_atWhen the latest comment was added
first_resolution_time_in_minutesNumber of minutes to the first resolution time inside and out of business hours
reply_time_in_minutesNumber of minutes to the first reply inside and out of business hours
full_resolution_time_in_minutesNumber of minutes to the full resolution inside and out of business hours
agent_wait_time_in_minutesNumber of minutes the agent spent waiting inside and out of business hours
requester_wait_time_in_minutesNumber of minutes the requester spent waiting inside and out of business hours
created_atWhen this record was created
updated_atWhen this record last got updated

users

Zendesk Support has three types of users: End-users (your customers), agents, and administrators. This table contains information about these users.

AttributeDescription
🔑User ID (id)Automatically assigned when the user is created
emailThe user’s primary email address. *Writeable on create only. On update, a secondary email is added.
nameThe user’s name
activefalse if the user has been deleted
aliasAn alias displayed to end users
chat_onlyWhether or not the user is a chat-only agent
created_atThe time the user was created
custom_role_idA custom role if the user is an agent on the Enterprise plan
detailsAny details you want to store about the user, such as an address
external_idA unique identifier from another system. The API treats the id as case insensitive. Example: ian1 and Ian1 are the same user
last_login_atThe last time the user signed in to Zendesk Support
locale_idThe user’s locale
moderatorDesignates whether the user has forum moderation capabilities
notesAny notes you want to store about the user
only_private_commentstrue if the user can only create private comments
organization_idThe id of the organization the user is associated with
phoneThe user’s primary phone number.
photoThe user’s profile picture represented as an Attachment object
restricted_agentIf the agent has any restrictions; false for admins and unrestricted agents, true for other agents
roleThe user’s role. Possible values are “end-user”, “agent” or “admin”
sharedIf the user is shared from a different Zendesk Support instance. Ticket sharing accounts only.
shared_agentIf the user is a shared agent from a different Zendesk Support instance. Ticket sharing accounts only.
signatureThe user’s signature. Only agents and admins can have signatures.
suspendedIf the agent is suspended. Tickets from suspended users are also suspended, and these users cannot sign in to the end user portal.
tagsThe user’s tags. Only present if your account has user tagging enabled.
ticket_restrictionSpecifies which tickets the user has access to. Possible values are: “organization”, “groups”, “assigned”, “requested”, null
time_zoneThe user’s time zone.
two_factor_auth_enabledIf two-factor authentication is enabled.
updated_atThe time the user was last updated
urlThe user’s API url.
user_fieldsValues of custom fields in the user’s profile.
verifiedThe user’s primary identity is verified or not.

Foreign Keys Joins

A foreign key is a column in a table that establishes an association with another table via shared values. To accomplish this, a foreign key is populated with values of the primary key from the other table. Foreign keys are also typically titled IDs, but prepended with the name of the referenced table. Below are some of the relations you could have when joining the different Zendesk tables.

Note: All foreign key relationships are color coordinated based on each primary key in the tables above for easy identification.

users with tickets

When you want to link the users that have filed a ticket in Zendesk. Here’s how you do it: users.id <-> tickets.asignee_id

organizations with tickets

When you want to link the organizations from which a ticket is filed. Here’s how you do it: organizations.id <-> tickets.organization_id

 

Resource:

https://www.stitchdata.com/docs/integrations/saas/zendesk

Yuyan (Fiona) Mao

About Yuyan (Fiona) Mao

Hi! I'm Yuyan (Fiona) Mao. I'm currently pursuing my Master's degrees in International Business and Business Analytics from Hult International Business School and received my Bachelor in Economics from University of Maryland. I care about efficiency, profitability and feasibility. I believe in data and communication. So I'm excited to share my knowledge and keep learning at the Data School by Chartio.