Zoho Desk Ticket Data Reference

Overview

When your UltraCart account is connected to Zoho Desk, every support ticket is copied into your BigQuery data warehouse alongside your orders, customers, and auto orders. That co-location is the point. Zoho Desk can already report on tickets by itself, but only the data warehouse can answer a question that spans both systems, such as whether your highest-value customers contact support more often than everyone else.

This page describes the shape of the ticket data. For queries that use it, see Zoho Desk Ticket Reporting Queries and Reporting on Zoho Desk Custom Fields.

Where the tickets land

Ticket data lives in the view uc_zoho_desk_tickets, inside the same Google Cloud project as the rest of your data warehouse. The project is named after your UltraCart merchant ID in lowercase:

ultracart-dw-yourmerchantid.ultracart_dw_low.uc_zoho_desk_tickets
                ^^^^^^^^^^^^                                        <- your merchant ID, lowercase

A merchant ID of DEMO gives a project ID of ultracart-dw-demo. The project ID is always lowercase even though merchant_id values inside the data preserve their original case.

Ticket data appears only for accounts with the Zoho Desk integration connected. Setup is covered in Zoho Desk.

Which dataset holds what

The same view is published in four datasets. They differ only in how much personal information they expose, so the right one depends on what your report needs to show.

Dataset	Ticket content	Typical use
ultracart_dw_low	Subject, description, resolution, customer email and phone, agent names, and all message and comment bodies are removed. Custom fields, classification, status_type, and assignee_id remain.	Counts, rates, and trends. The safest dataset to share with a wider audience.
ultracart_dw	Same content as ultracart_dw_low.	Interchangeable with ultracart_dw_low.
ultracart_dw_medium	Full content, including subjects, message bodies, agent names, and customer email addresses.	Reports that name agents, quote ticket text, or join tickets to orders by email address.
ultracart_dw_high	Same content as ultracart_dw_medium.	Interchangeable with ultracart_dw_medium.

These datasets carry the warehouse-wide security levels described under Data Warehouse (BigQuery), so a user who can query one is not guaranteed to reach the others. The table above covers only what those levels mean for ticket data.

What one row represents

One row is one ticket, holding its most recent known state. A ticket that was reopened and closed three times still produces a single row, showing the latest status. There is no history of intermediate states, so questions like "how many times was this ticket reopened" cannot be answered from this view.

Ticket history begins when the Zoho Desk integration was connected, not when your UltraCart account was created. Early months usually show a partial ramp as the first sync completes, which makes them poor baselines for a trend.

Columns that matter most

The view carries 74 columns in the lower tiers and 79 in the upper tiers. These are the ones most reports use.

Column	Type	Notes
partition_date	DATE	Weekly bucket used for query cost control. See below.
id	STRING	Zoho's internal ticket ID. Unique per row.
ticket_number	STRING	The human-facing ticket number agents see.
created_time	DATETIME	When the ticket was opened. The basis for almost every date filter.
closed_time	DATETIME	When the ticket was closed. Empty while a ticket is open.
status_type	STRING	Open, Closed, or On Hold. Reliable for grouping.
status	STRING	The specific Zoho status name, which merchants customize freely. Less reliable for grouping than status_type.
classification	STRING	The category assigned to the ticket, either by an agent or by an AI agent, depending on your Zoho configuration.
channel	STRING	Where the ticket arrived from, such as email, phone, or chat.
thread_count	STRING	Number of messages on the ticket. Stored as text, not a number.
is_spam	BOOLEAN	Marks tickets Zoho classified as spam.
assignee_id	STRING	The agent the ticket is assigned to. Empty on unassigned tickets.
assignee	RECORD	Agent detail. first_name, last_name, and email are populated in the upper tiers.
email	STRING	The customer's email address, in the upper tiers only. The join key to uc_orders.
cf	REPEATED RECORD	Your Zoho Desk custom fields, keyed by API name.
custom_fields	REPEATED RECORD	The same custom field values, keyed by display name.
threads	REPEATED RECORD	The email messages on the ticket. Bodies appear in the upper tiers only.
comments	REPEATED RECORD	Internal agent notes. Bodies appear in the upper tiers only.
web_url	STRING	A direct link back to the ticket in Zoho Desk.

How custom fields arrive

Every custom field you have defined in Zoho Desk arrives twice, in two repeated records that hold identical values:

• cf is keyed by the field's API name, such as cf_refund_approved.
• custom_fields is keyed by the field's display name, such as Refund Approved.

cf is the more dependable of the two, because API names stay fixed when someone renames a field in the Zoho Desk admin screens. Reading a value means picking it out of the array by key rather than referring to a column:

(SELECT value FROM UNNEST(cf) WHERE key = 'cf_refund_approved') AS refund_approved

Custom field names are specific to your Zoho Desk configuration, so no two merchants have the same list. Reporting on Zoho Desk Custom Fields covers how to list yours.

Empty custom fields arrive as the text "null"

An unfilled custom field is not SQL NULL. It is the four-character string null, which means IS NULL never matches it and IFNULL never replaces it. A count of filled-in values written the obvious way returns every ticket rather than the answered ones.

NULLIF converts the placeholder into a real NULL:

-- Returns every ticket, because the value is text, not SQL NULL.
COUNTIF((SELECT value FROM UNNEST(cf) WHERE key = 'cf_refund_approved') IS NOT NULL)

-- Returns only tickets where an agent filled the field in.
COUNTIF(NULLIF((SELECT value FROM UNNEST(cf) WHERE key = 'cf_refund_approved'), 'null') IS NOT NULL)

Checkbox fields follow the same rule. Their values are the strings 'true' and 'false', not BigQuery booleans, so they need quoted comparisons.

partition_date is a weekly bucket, not a load date

partition_date is the Sunday that begins the week a ticket was created. It exists to keep query costs down: filtering on it lets BigQuery skip whole weeks of data instead of reading the entire table.

It is calculated in local time while created_time is stored in UTC, so a small number of tickets created in the early hours of a Sunday land in the previous week's bucket. Filtering on partition_date alone therefore drops a few tickets at each end of a range.

Pairing a padded partition_date filter with an exact created_time filter gives you both low cost and correct results:

WHERE partition_date BETWEEN DATE_SUB(start_date, INTERVAL 7 DAY) AND end_date
  AND DATE(created_time) BETWEEN start_date AND end_date

tip

This pairing is worth keeping in every ticket query you write. A single month pulled this way reads a few megabytes. The same month without a partition_date filter reads the whole table, which runs to several gigabytes on an established account.

Columns that are often empty

Zoho Desk populates a column only when the matching feature is switched on and in use. These are commonly empty, and a report built on one can silently return nothing:

Column	Common state
assignee.name	Empty even when the agent is known. first_name and last_name carry the value.
contact.name	Empty in the same way. first_name and last_name carry the value.
threads.sent_time	Frequently empty, which puts first-response-time reporting out of reach.
response_due_date	Populated only when Zoho Desk service level agreements are configured.
sentiment	Populated only on Zoho plans that include sentiment analysis.
sub_category	Populated only if agents fill it in. Often unused.
category	Zoho's own category field, which is separate from classification and often unused.

Confirming that a column holds data before building a report on it saves a puzzling debugging session later.

Values that need cleaning before grouping

Two conventions cause double-counting in a GROUP BY:

• channel arrives with inconsistent capitalization, so Email and EMAIL group separately even though they mean the same thing. Wrapping the column in UPPER() merges them.
• thread_count, comment_count, and the other _count columns are stored as text. Sorting them as-is orders 10 before 9. SAFE_CAST(thread_count AS INT64) converts them for arithmetic and returns NULL rather than failing if a value is not a number.

Related documentation

• Zoho Desk Ticket Reporting Queries covers volume, resolution time, agent workload, and joins to your order data.
• Reporting on Zoho Desk Custom Fields covers listing your own custom fields and reporting on them.
• Data Warehouse (BigQuery) covers access, pricing, and setup.
• Zoho Desk covers connecting the integration.