Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Expected Zendesk Data

Now that Zendesk is successfully connected to your Stitch account, what comes next? In this article, we'll cover:

Replicating Zendesk Data

Historical Sync

By default, a historical sync of Zendesk data goes back one year from the Stitch connection date.

Frequency

Stitch will replicate Zendesk data based on the frequency you define. The default setting is every 30 minutes, but you can change it to more or less frequent based on your needs.

Replication Method

The majority of Zendesk data is replicated using Incremental Replication. This means that every time Zendesk data is queued for replication, only new and updated rows will be replicated to your data warehouse.

There are exceptions to this, however. The following tables use Full Table Replication:

  • zendesk_tags
  • zendesk_group_memberships

This means that every time Zendesk data is queued for replication, all rows in these table - including existing, new, and updated - will be replicated.

Noticing Large Amounts of Data?
Even though the majority of tables in our Zendesk integration use Incremental Replication, a large number of rows can still be used during each replication attempt due to how Stitch de-nests nested data structures. More detailed info on this and how Zendesk tables are deconstructed is in the next section.

To sum it up: each subtable is replicated according to the Replication Method of the parent table. The zendesk_audits table, for example, has one subtable called zendesk_audits__events. Each time the zendesk_audits table is updated, the zendesk_audits__events table is updated as well. This results in new rows in not one, but two tables.

To adjust for this, many of our clients set their Replication Frequencies to a less frequent interval - for example, to every few hours instead of every 30 minutes - to keep their row counts down.

Zendesk Tables in Your Data Warehouse

Below is a list of the main and subtables that will be created in your data warehouse. Because Zendesk uses nested arrays to structure data in some of its API's endpoints, Stitch will de-nest these arrays and create subtables. Refer to the Handling of Nested Data Structures doc for more info.

Click the table name to view a list of full attributes for that table in Zendesk's API documentation.

audits
Primary key: _id

The audits table contains information about activity associated with a ticket, including status changes and both customer and agent responses.

Attributes include the audit ID (id), ticket ID, metadata, creation method, author ID, and created at timestamp.

  • audits__events - This subtable contains additional details about a ticket event. The composite key for this table - made up of the audit ID and row ID - can be used to tie it back to its parent, audits: _sdc_source_key_id: _sdc_level_0_id

    audits__events also has child tables for: attachments, attachments__thumbnails, previous_value, recipients, and value. The composite key for these child tables - made up of the audit ID and row IDs - can be used to tie them back to the top-level table, audits: _sdc_source_key_id: _sdc_level_0_id: _sdc_level_1_id

zendesk_group_memberships
Primary Key:
_id

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

Attributes include the group membership ID (id), URL, name, deletion flag, and created at and updated at dates.

Replication Method & Record Deletion
There are some important things to note about the zendesk_group_memberships table:

  • This table uses Full Table Replication, meaning new, updated, and existing rows will be replicated during each replication attempt. As a result, you may see high row counts for this table.
  • Zendesk's API currently doesn't have a way to identify deleted records. To account for this, we recommend that you periodically drop this table and then allow Stitch to re-create it. Dropping and re-populating the table is currently the only way to detect deletions.

zendesk_groups
Primary Key:
_id

The zendesk_groups table contains information about the groups - which is how agents are organized - in your Zendesk account.

Attributes include the group ID (id), URL, name, deletion flag, and created at and updated at dates.

zendesk_macros
Primary Key:
_id

The zendesk_macros 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.

Attributes include the macro ID (id), title, active boolean, position, restriction settings, and created at and updated at dates.

  • zendesk_macros__actions - This subtable records information about the actions associated with a macro. The composite key for this table - made up of the macro ID and row ID - can be used to tie it back to its parent, zendesk_macros: _sdc_source_key_id: _sdc_level_0_id

organizations
Primary Key:
_id

The organizations table contains company information about your end-users.

Attributes include the organization ID (id), name, URL, external ID, details, notes, group ID, shared tickets setting, shared comments setting, organization fields, and created at and updated at dates.

Subtables
The composite key for the following tables - made up of the organization ID and row ID - can be used to tie these tables back to their parent, organizations: _sdc_source_key_id: _sdc_level_0_id

  • organizations__doman_names - This subtable records information about the domain names associated with an organization.
  • organizations__tags - This subtable records information about the tags associated with an organization.

zendesk_tags
Primary Key:
name

The zendesk_tags table contains a list of tags in your Zendesk account.

Attributes include the tag name and application count.

tickets
Primary Key:
_id

The tickets table contains information about the tickets in your Zendesk account.

Attributes include the ticket ID (id), URL, external ID, type, subject, raw subject, priority, status, recipient, requester ID, submitter ID, assignee ID, organization ID, group ID, forum topic ID, problem ID, incident boolean, due at, creation source, satisfaction rating, sharing agreement IDs, ticket form ID, brand ID, allow channelback boolean, and created at and updated at dates.

Subtables
The composite key for the following tables - made up of the ticket ID and row ID - can be used to tie these tables back to their parent, tickets: _sdc_source_key_id: _sdc_level_0_id

  • tickets__collaborator_ids - This subtable records information about the collaborators associated with a ticket.
  • tickets__custom_fields - This subtable records information about the custom fields associated with a ticket.
  • tickets__fields - This subtable records information about the fields associated with a ticket.
  • tickets__followup_ids - This subtable records information about the followups associated with a ticket.
  • tickets__tags - This subtable records information about the tags associated with a ticket.

ticket_fields
Primary Key:
_id

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

Attributes include the field ID (id), URL, type, title, raw title, description, raw description, position, active, requirement setting, portal title, portal visibility setting, portal editing setting, portal requirement setting, tag, removable setting, and created at and updated at dates.

Subtables
The composite key for the following tables - made up of the ticket field ID and row ID - can be used to tie these tables back to their parent, ticket_fields: _sdc_source_key_id: _sdc_level_0_id

  • ticket_fields__custom_field_options - This subtable contains data about custom ticket field options.
  • ticket_fields__system_field_options - This subtable contains data about system ticket field options.

zendesk_ticket_metrics
Primary Key:
_id

The ticket_metrics table contains information about the metrics associated with Zendesk tickets.

Important!: A Note on Archived Tickets
As Zendesk's Ticket Metrics API does not currently support returning metrics for archived tickets, this table will not contain any metrics for archived tickets. If you're not seeing records in this table, we recommend checking if the missing tickets have been archived.

For more info on how Zendesk archives tickets, click here.

Attributes include the metric ID (id), ticket ID, ticket URL, counts for the number of groups, assignees, reopens, and replies; updated at dates for the last assignee update, requester update, and status update; assigned at, solved at, first and full resolution times, first reply time, agent wait time, requester wait time, and created at and updated at dates.

users
Primary Key:
_id

The users table contains information about all the users - end-users, agents, and administrators -  in your Zendesk account.

Attributes include the user ID (id), email, name, active setting, alias, chat only setting, details, external ID, last login, locale, locale ID, forum moderator setting, notes, private comments setting, organization ID, phone, restriction setting, role, sharing settings (for ticket sharing accounts), signature, suspension setting, tags, ticket restrictions, time zone, two factor auth setting, URL, user fields, verification setting, and updated at date.

Note that depending on the type of user (ex: end-user versus agent), some of the above fields may not be applicable.

  • users__photo_thumbnails - This subtable contains data about the photos associated with users. The composite key for this table - made up of the user ID and row ID - can be used to tie it back to its parent, users: _sdc_source_key_id: _sdc_level_0_id

Related:

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!